Node.js - sqlstring alternative which allows named named replacements
The sqlstring node module allows creating of queries using an ordered array. So if I have a template query like:
sqlstring.format('Select * from users where id = ?', ['my_id'])
It will become:
Select * from users where id = 'my_id'
However here I need to remember the order of the question marks, so if the same thing is being in multiple places it becomes a hassle. Is there an alternative which allows me to do the following:
sqlstring.format('Select :id + :foo as bar from users where id = :id', {id: 1, foo: 3})
Which would become:
Select 1 + 3 as bar from users where id = 1
I know knex query builder does this, but I don't want install the entirety of knex just for the query builder.
sql node.js templates npm
edited Nov 14 '18 at 13:57
vahdet
1,59831330
asked Nov 14 '18 at 13:46
user3690467user3690467
4562615
add a comment |
The sqlstring node module allows creating of queries using an ordered array. So if I have a template query like:
sqlstring.format('Select * from users where id = ?', ['my_id'])
It will become:
Select * from users where id = 'my_id'
However here I need to remember the order of the question marks, so if the same thing is being in multiple places it becomes a hassle. Is there an alternative which allows me to do the following:
sqlstring.format('Select :id + :foo as bar from users where id = :id', {id: 1, foo: 3})
Which would become:
Select 1 + 3 as bar from users where id = 1
I know knex query builder does this, but I don't want install the entirety of knex just for the query builder.
sql node.js templates npm
edited Nov 14 '18 at 13:57
vahdet
1,59831330
asked Nov 14 '18 at 13:46
user3690467user3690467
4562615
Squel has a fluent approach. Maybe it can be a more readible alternative for your named params.
– vahdet
Nov 14 '18 at 13:54
If you can handle input sanitization (or don't care about it), you could also consider Template literals
– woozyking
Nov 14 '18 at 14:12
add a comment |
The sqlstring node module allows creating of queries using an ordered array. So if I have a template query like:
sqlstring.format('Select * from users where id = ?', ['my_id'])
It will become:
Select * from users where id = 'my_id'
However here I need to remember the order of the question marks, so if the same thing is being in multiple places it becomes a hassle. Is there an alternative which allows me to do the following:
sqlstring.format('Select :id + :foo as bar from users where id = :id', {id: 1, foo: 3})
Which would become:
Select 1 + 3 as bar from users where id = 1
I know knex query builder does this, but I don't want install the entirety of knex just for the query builder.
sql node.js templates npm
edited Nov 14 '18 at 13:57
vahdet
1,59831330
asked Nov 14 '18 at 13:46
user3690467user3690467
4562615
The sqlstring node module allows creating of queries using an ordered array. So if I have a template query like:
sqlstring.format('Select * from users where id = ?', ['my_id'])
It will become:
Select * from users where id = 'my_id'
However here I need to remember the order of the question marks, so if the same thing is being in multiple places it becomes a hassle. Is there an alternative which allows me to do the following:
sqlstring.format('Select :id + :foo as bar from users where id = :id', {id: 1, foo: 3})
Which would become:
Select 1 + 3 as bar from users where id = 1
I know knex query builder does this, but I don't want install the entirety of knex just for the query builder.
sql node.js templates npm
sql node.js templates npm
edited Nov 14 '18 at 13:57
vahdet
1,59831330
asked Nov 14 '18 at 13:46
user3690467user3690467
4562615
edited Nov 14 '18 at 13:57
vahdet
1,59831330
asked Nov 14 '18 at 13:46
user3690467user3690467
4562615
edited Nov 14 '18 at 13:57
vahdet
1,59831330
edited Nov 14 '18 at 13:57
vahdet
1,59831330
edited Nov 14 '18 at 13:57
vahdet
1,59831330
1,59831330
asked Nov 14 '18 at 13:46
user3690467user3690467
4562615
asked Nov 14 '18 at 13:46
user3690467user3690467
4562615
asked Nov 14 '18 at 13:46
user3690467user3690467
4562615
4562615
Squel has a fluent approach. Maybe it can be a more readible alternative for your named params.
– vahdet
Nov 14 '18 at 13:54
If you can handle input sanitization (or don't care about it), you could also consider Template literals
– woozyking
Nov 14 '18 at 14:12
add a comment |
Squel has a fluent approach. Maybe it can be a more readible alternative for your named params.
– vahdet
Nov 14 '18 at 13:54
If you can handle input sanitization (or don't care about it), you could also consider Template literals
– woozyking
Nov 14 '18 at 14:12
Squel has a fluent approach. Maybe it can be a more readible alternative for your named params.
– vahdet
Nov 14 '18 at 13:54
Squel has a fluent approach. Maybe it can be a more readible alternative for your named params.
– vahdet
Nov 14 '18 at 13:54
If you can handle input sanitization (or don't care about it), you could also consider Template literals
– woozyking
Nov 14 '18 at 14:12
If you can handle input sanitization (or don't care about it), you could also consider Template literals
– woozyking
Nov 14 '18 at 14:12
add a comment |
1 Answer
1
active
oldest
votes
You can use mysql2 package, that support that format:
Named placeholders
You can use named placeholders for parameters by setting
namedPlaceholders config value or query/execute time option. Named
placeholders are converted to unnamed ? on the client (mysql protocol
does not support named parameters). If you reference parameter
multiple times under the same name it is sent to server multiple
times.
connection.config.namedPlaceholders = true;
connection.execute('select :x + :y as z', {x: 1, y: 2}, function (err, rows) {
// statement prepared as "select ? + ? as z" and executed with [1,2] values
// rows returned: [ { z: 3 } ]
});
connection.execute('select :x + :x as z', {x: 1}, function (err, rows) {
// select ? + ? as z, execute with [1, 1]
});
connection.query('select :x + :x as z', {x: 1}, function (err, rows) {
// query select 1 + 1 as z
});
answered Nov 14 '18 at 14:06
Marcos CasagrandeMarcos Casagrande
12.6k32740
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53301708%2fnode-js-sqlstring-alternative-which-allows-named-named-replacements%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
You can use mysql2 package, that support that format:
Named placeholders
You can use named placeholders for parameters by setting
namedPlaceholders config value or query/execute time option. Named
placeholders are converted to unnamed ? on the client (mysql protocol
does not support named parameters). If you reference parameter
multiple times under the same name it is sent to server multiple
times.
connection.config.namedPlaceholders = true;
connection.execute('select :x + :y as z', {x: 1, y: 2}, function (err, rows) {
// statement prepared as "select ? + ? as z" and executed with [1,2] values
// rows returned: [ { z: 3 } ]
});
connection.execute('select :x + :x as z', {x: 1}, function (err, rows) {
// select ? + ? as z, execute with [1, 1]
});
connection.query('select :x + :x as z', {x: 1}, function (err, rows) {
// query select 1 + 1 as z
});
answered Nov 14 '18 at 14:06
Marcos CasagrandeMarcos Casagrande
12.6k32740
add a comment |
You can use mysql2 package, that support that format:
Named placeholders
You can use named placeholders for parameters by setting
namedPlaceholders config value or query/execute time option. Named
placeholders are converted to unnamed ? on the client (mysql protocol
does not support named parameters). If you reference parameter
multiple times under the same name it is sent to server multiple
times.
connection.config.namedPlaceholders = true;
connection.execute('select :x + :y as z', {x: 1, y: 2}, function (err, rows) {
// statement prepared as "select ? + ? as z" and executed with [1,2] values
// rows returned: [ { z: 3 } ]
});
connection.execute('select :x + :x as z', {x: 1}, function (err, rows) {
// select ? + ? as z, execute with [1, 1]
});
connection.query('select :x + :x as z', {x: 1}, function (err, rows) {
// query select 1 + 1 as z
});
answered Nov 14 '18 at 14:06
Marcos CasagrandeMarcos Casagrande
12.6k32740
add a comment |
You can use mysql2 package, that support that format:
Named placeholders
You can use named placeholders for parameters by setting
namedPlaceholders config value or query/execute time option. Named
placeholders are converted to unnamed ? on the client (mysql protocol
does not support named parameters). If you reference parameter
multiple times under the same name it is sent to server multiple
times.
connection.config.namedPlaceholders = true;
connection.execute('select :x + :y as z', {x: 1, y: 2}, function (err, rows) {
// statement prepared as "select ? + ? as z" and executed with [1,2] values
// rows returned: [ { z: 3 } ]
});
connection.execute('select :x + :x as z', {x: 1}, function (err, rows) {
// select ? + ? as z, execute with [1, 1]
});
connection.query('select :x + :x as z', {x: 1}, function (err, rows) {
// query select 1 + 1 as z
});
answered Nov 14 '18 at 14:06
Marcos CasagrandeMarcos Casagrande
12.6k32740
You can use mysql2 package, that support that format:
Named placeholders
You can use named placeholders for parameters by setting
namedPlaceholders config value or query/execute time option. Named
placeholders are converted to unnamed ? on the client (mysql protocol
does not support named parameters). If you reference parameter
multiple times under the same name it is sent to server multiple
times.
connection.config.namedPlaceholders = true;
connection.execute('select :x + :y as z', {x: 1, y: 2}, function (err, rows) {
// statement prepared as "select ? + ? as z" and executed with [1,2] values
// rows returned: [ { z: 3 } ]
});
connection.execute('select :x + :x as z', {x: 1}, function (err, rows) {
// select ? + ? as z, execute with [1, 1]
});
connection.query('select :x + :x as z', {x: 1}, function (err, rows) {
// query select 1 + 1 as z
});
answered Nov 14 '18 at 14:06
Marcos CasagrandeMarcos Casagrande
12.6k32740
answered Nov 14 '18 at 14:06
Marcos CasagrandeMarcos Casagrande
12.6k32740
answered Nov 14 '18 at 14:06
Marcos CasagrandeMarcos Casagrande
12.6k32740
answered Nov 14 '18 at 14:06
Marcos CasagrandeMarcos Casagrande
12.6k32740
12.6k32740
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53301708%2fnode-js-sqlstring-alternative-which-allows-named-named-replacements%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Squel has a fluent approach. Maybe it can be a more readible alternative for your named params.
– vahdet
Nov 14 '18 at 13:54
If you can handle input sanitization (or don't care about it), you could also consider Template literals
– woozyking
Nov 14 '18 at 14:12