datalanche / node-pg-format Goto Github PK
View Code? Open in Web Editor NEWNode.js implementation of PostgreSQL's format() to safely create dynamic SQL queries.
License: MIT License
Node.js implementation of PostgreSQL's format() to safely create dynamic SQL queries.
License: MIT License
const query = format('INSERT INTO users (%1$I) VALUES (%2$L)', fields, values)
values is an array containing number types but the format function called with the L parameter returns everything as a string.
i.e. the format function is adding quotes (' ') around numeric types
In PSQL, escaped strings are prefixed with an E
. When using
let query1 = format('INSERT INTO survey (%s) VALUES %L ON CONFLICT DO NOTHING RETURNING id', headers, values);
I'm getting
...
VALUES
(
E 'ul. Bema 99\\113',
) ON CONFLICT DO NOTHING RETURNING id
It would seem that the space between E
and the quoted string is a mistake. Reading the source code seems like it is not responsible for inserting this space. Any clue what's going on?
9.4 was released recently. Update the reserved word list with SQL keyword additions for 9.4 such as LATERAL
.
I understand that this function allows me to write "dynamic" insert queries, given an array of column names and one with the corresponding values:
let query = format(
INSERT INTO sometable(%I) VALUES(%L);, arrColumn, arrValue);
However, how can I do the same with an UPDATE query where the syntax uses pairs (... SET column1=value1, column2=value2
, ...)?
I'm sure there must be a way but didn't find yet. Thanks in advance for your help.
Trying to submit multiple rows into postgres where one of the columns is an array. Any nests beyond two are not handled correctly.
For example:
format('%L', ['a', 'b', ['c', ['d', 'e']]])
this returns 'a','b', ('c', 'd','e')
instead of 'a','b', ('c', ('d', 'e'))
.
Is this intended? Is there a way to work around this?
Hello,
I really like this project, but there's one thing I'm kind of missing; being able to specify positional arguments.
This is how I would imagine it:
const sql = format(`
SELECT x
FROM table_a
WHERE foo = %1$L
UNION
SELECT x
FROM table_b
WHERE foo = %1$L`,
'bar'
);
The optional n$
embedded in the %L
(or other placeholders) would refer to the nth given argument.
Is this something you could easily add? If not, I'm willing to give it a shot and send a PR, but you're more familiar with the code naturally.
Thanks!
In some cases some Unicode points that are not valid in string appeared in JSON like:
{
"model" : "Canon EOS 5D Mark II\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000",
....
}
Format this JSON directly could cause an error: unsupported Unicode escape sequence
thrown by PostgreSQL. Otherwise, if I stringify
it manually without the explicit cast ::jsonb
, then PostgreSQL will accept the string.
I think we could add a method after the sentence literal = JSON.stringify(value);
to sanitize the string, like str.replace(/\\0000/g, '')
maybe.
Hi, Here is my test code
var pg = require('pg')
pg.format = require('pg-format')
var conString = "...";
pg.connect(conString, function(err, client) {
var query = `
SELECT * FROM myTable
WHERE (id, name) = ANY(ARRAY[%L])
;`
var params = [[1, 'Jack'], [2, 'Tom']]
console.log(pg.format(query, params))
client.query(pg.format(query, params), (err, data) => {
console.log(err)
})
})
The formatted query is
SELECT * FROM myTable
WHERE (id, name) = ANY(ARRAY[('1', 'Jack'), ('2', 'Tom')])
Here are may 2 issues:
If change the query to
SELECT * FROM myTable
WHERE (id, name) = ANY(ARRAY[('1'::int, 'Jack'::varchar), ('2'::int, 'Tom'::varchar)])
it could get right result.
Thanks
i want to create method to do upsert using library pg-format.
Example, the id is serial field.
// Example data to save:
const data = [
{id: null, name:'a', desc: 'b'},
{id: 20, name: 'aa', desc: 'bb'}
]
const sql = 'INSERT INTO table_name(id, a, b)
VALUES(%L)
ON CONFLICT (id)
DO UPDATE SET (a,b) =
(EXCLUDED.a, EXCLUDED.b)';
const params = []
for (obj of data) {
const id = obj.id ?? 'DEFAULT';
params.push([id, obj.name, obj.desc])
}
format(sql, params);
// Result:
// INSERT INTO table_name(id, a, b)
// VALUES('DEFAULT', 'a', b), ('20', 'aa', 'bb')
// ON CONFLICT (id)
// DO UPDATE SET (a,b) =
// (EXCLUDED.a, EXCLUDED.b)
So I got an error because "DEFAULT" is in quotation marks, so it is a string and not the keyword postgres. I try using %I but it also not work. The only way i found was to use %s but it is not esceped so it's unsave.
I also try to make combination of this marks like this VALUES(%s, %L) or VALUES(%I, %L) but i i can have both id as a number and as a null so it's a incorrect way.
Mayby is diffret way to create upsert query or to bind this?
There's a custom require call in the lib/index file which is not compatible with the serverless-webpack
. The lambdas attempt to require that file in the runtime. I suggest a change to a regular commonjs require call.
not sure what's going on, but i can't get it to work using the example you provided.
Hello and thank you for your work! :)
Please guide on using db functions such as newid() or now() in pg-format queries with arrays. How to call them in values in INSERT INTO?
Highly appreciated! :)
Thank you!
I Have following geojson object with 2 features: My question is: How do I go about inserting this 2 features in a Postgresql Table with pg-format?
{
"type": "FeatureCollection",
"crs": {
"type": "name",
"properties": {
"name": "EPSG:4326"
}
},
"features": [
{
"type": "Feature",
"id": 6195898,
"geometry": {
"type": "Polygon",
"coordinates": [
[
[
19.75889040800007,
-31.466254463
],
[
19.75889586500006,
-31.46648844699997
],
[
19.759105202000057,
-31.46648545899996
],
[
19.759100205000035,
-31.466250463999966
],
[
19.75889040800007,
-31.466254463
]
]
]
},
"properties": {
"OBJECTID": 6195898,
"GID": 2168525,
"PRCL_KEY": "N065C015000200003929000001",
"PRCL_TYPE": "E",
"LSTATUS": "R",
"WSTATUS": "C",
"GEOM_AREA": 518.0799,
"COMMENTS": " ",
"TAG_X": 19.758913,
"TAG_Y": -31.46638,
"TAG_VALUE": "RE/3929",
"TAG_SIZE": 0.000026,
"TAG_ANGLE": 0.002022,
"TAG_JUST": "L",
"ID": "C01500020000392900000",
"DATE_STAMP": 1517529600000,
"DATE_PROCESSED": 1635015671000,
"SHAPE_Length": 0.000888289181740557,
"SHAPE_Area": 4.915952156191992e-8,
"PROVINCE": "NORTHERN CAPE",
"MAJ_REGION": "CALVINIA",
"MAJ_CODE": "C0150000",
"MIN_REGION": "CALVINIA",
"MIN_CODE": "C0150002",
"PARCEL_NO": 3929,
"PORTION": 0,
"SS_NAME": " ",
"DSG_NO": " ",
"SR_NO": " ",
"SS_NO": " "
}
},
{
"type": "Feature",
"id": 6195899,
"geometry": {
"type": "Polygon",
"coordinates": [
[
[
23.068871573000024,
-28.336274698999947
],
[
23.069115587000056,
-28.336471700999937
],
[
23.069310598000072,
-28.336282697999923
],
[
23.069308603000028,
-28.336240698999973
],
[
23.069092589000036,
-28.33606670399996
],
[
23.068871573000024,
-28.336274698999947
]
]
]
},
"properties": {
"OBJECTID": 6195899,
"GID": 2168614,
"PRCL_KEY": "N085C031000300000638000000",
"PRCL_TYPE": "E",
"LSTATUS": "R",
"WSTATUS": "C",
"GEOM_AREA": 1013.394276,
"COMMENTS": " ",
"TAG_X": 23.069061,
"TAG_Y": -28.336332,
"TAG_VALUE": "638",
"TAG_SIZE": 0.000031,
"TAG_ANGLE": 6.269028,
"TAG_JUST": "L",
"ID": "C03100030000063800000",
"DATE_STAMP": 1591315200000,
"DATE_PROCESSED": 1635015671000,
"SHAPE_Length": 0.0012081009337222054,
"SHAPE_Area": 9.317254943786774e-8,
"PROVINCE": "NORTHERN CAPE",
"MAJ_REGION": "HAY",
"MAJ_CODE": "C0310000",
"MIN_REGION": "POSTMASBURG",
"MIN_CODE": "C0310003",
"PARCEL_NO": 638,
"PORTION": 0,
"SS_NAME": " ",
"DSG_NO": " ",
"SR_NO": " ",
"SS_NO": " "
}
}
]
}
Hi,
first of all great npm package
second how can i insert multiple insert statements into postgres
like for example
pg.connect(connectionString,function(err, client, done) {
if(err)
{
console.log(err)
}
var basescrp=format('INSERT INTO srt (starttime, endtime,content,showname,season,ep,createdat,updatedat) VALUES %L', data)
var stream = client.query(copyFrom('COPY srt FROM STDIN'));
var rs = new QueryStream(basescrp)
rs.pipe(stream).on('finish', done).on('error', function (err) {
console.log(err);
done();
});
})
sadly the above does not work
any pointers, suggestions on how to stream multiple insert into postgres !
This causes a parse error in my sql query. Consider the following example:
CREATE TABLE thing(
tags string[] NOT NULL
)
const format = require('pg-format')
format('INSERT INTO %I (%I) VALUES(%L)', 'thing', 'tags', [])
Now this get's formatted into the following query:
INSERT INTO thing (tags) VALUES()
Which gives the following error:
error: syntax error at or near ")"
I believe that the correct query output would have been:
INSERT INTO thing (tags) VALUES('{}')
A workaround is to send in the string '{}'
to the format function, but that is very ugly and breaks my validation of data since it expects an array of strings...
How would I best use pg-format to handle the insertion of arrays into columns with an array type.
Imagine a table with a column 'roles' of type text[].
To insert into that table the statement would look something like
INSERT INTO table (name, roles) VALUES ("Josh", ARRAY["role1", "role2"])
or
INSERT INTO table(name, roles) VALUES ("Josh", {"role1", "role2"})
However, when pg-format encounters an array it formats as a literal like this:
INSERT INTO table(name, roles) VALUES ("Josh", ("role1", "role2")) which is invalid. That format ("role1", "role2") works great for IN statements so clearly is the correct handling for arrays in some circumstances.
Recommendations for UPDATES and INSERTS where the value being written is an Array?
Hi,
I have a script that uses a bulk insert using a t set.
Basically, what we do is loop over an excel spreadsheet, create an array of all the object we wish to insert and then pass it over to pgformt, if there is no entry in the field we pass over an empty array so the variable is set to [] if there are no values.
The query is
format(`update attendee_data as t set
fname = c.attendee_fname,
lname = c.attendee_lname,
title = c.attendee_title,
email = c.attendee_email,
phone = c.attendee_phone,
company = c.attendee_company,
description = c.attendee_des,
links = CAST (c.attendee_links AS JSON),
grouplistid = c.attendee_groups,
attendeeonly = c.attendee_atonly
from (values
%L
) as c(attendee_fname,attendee_lname,attendee_title,attendee_email,attendee_phone,attendee_company,attendee_des,attendee_links,attendee_groups,attendee_atonly,attendee_id)
where CAST (c.attendee_id AS bigint) = CAST (t.sid AS bigint) AND aid = ${req.session.AccountID} AND cid= ${req.session.cID}`, attendeeUpdate, []));
This normally works great, however one of the columns is of type JSON. When trying to run the above code it throws and error
error: syntax error at or near ","
Is there a way to have the bulk insert work when there is a column that may be an array?
I saw a similar question posted on here but it does not look like there was ever a resolution to it, especially for a bulk insert.
Thanks!
Is there anyway to insert an object into a column of type jsonb? Because I see you do a JSON stringify now on objects.
I want to create 1 update statement that updates multiple rows and created the following:
format("UPDATE stores AS s SET details = t.details FROM (VALUES %L) AS t(placeid, details) WHERE s.placeid = t.placeid", stores)
Details is a column of type jsonb.
The stores array in Javascript is a double array with the first column (placeid) being a string and the second column (details) is JSON.
Hi
Is it possible to construct my query dynamically depending upon what parameters are passed through, so if I pass this Object to the below method
QueryObject {
day: 'tomorrow',
leagueName: 'England Premier League',
homeTeam:
{ columnOne: 'column_1',
columnOneValue: '80' },
awayTeam:
{ columnOne: 'column_2',
columnOneValue: '100' }
}
function buildConditions(queryObject) {
const columns = [];
const conditions = [];
const values = [];
if (typeof queryObject.homeTeam.columnOne !== 'undefined') {
columns.push(queryObject.homeTeam.columnOne);
conditions.push('%I = $1');
values.push(queryObject.homeTeam.columnOneValue);
}
if (typeof queryObject.awayTeam.columnOne !== 'undefined') {
columns.push(queryObject.awayTeam.columnOne);
conditions.push('%I = $2');
values.push(queryObject.awayTeam.columnOneValue);
}
return {
where: conditions.length ? conditions.join(' AND ') : '1',
values,
columns,
};
}
It seems like I am pretty close but i get the error Error: too few arguments
const conditions = buildConditions(queryObject);
console.log(conditions);
// { where: '%I = $1 AND %I = $2, values: [ '100', '100' ], columns: [ 'column_1', 'column_2' ] }
const string = 'SELECT * FROM fixtures WHERE ' + conditions.where + ', ' + conditions.columns.join(', ');
console.log(string)
// SELECT * FROM fixtures WHERE %I = $1 AND %I = $2, column_1, column_2
sql = format(string);
Seems I get the error when passing the string into format
What I don't understand at this point is if I was to construct the query as below, it works
sql = format("SELECT * FROM fixtures WHERE %I = $1 AND %I = $2", queryObject.homeTeam.columnOne, queryObject.awayTeam.columnOne);
Now that demo is fast approaching v1.0, I was looking to see if the modules I use will work on deno
.
The pg-format
module is not natively supported in deno
because it used the CJS require and module.exports statements.
Is there a chance of either moving pg-format
to the now supported ESM format, or building an ESM version?
I was thinking about forking this project to migrate it to import/export however it would be better to keep it here and add the support for ESM directly.
Greetings! :)
How can I implement parameter from nested array in query in "on conflict" clause?
For example:
let arr = [ [1,2], [5,6] ]
pgformat('insert into sample_table (foo,bar) values %L on conflict (foo,bar) do update set foo=???, bar=??? ; ', arr)
Thanks beforehands
Hi,
We can save multiple records into one table using pg-format. like
const roleQuery = format('INSERT INTO user_roles (user_id, practice_id, role_id) VALUES %L;',[practiceArray]);
But what if we need to add multiple records in multiple tables? I used following login but it didn't work. it's returning 'Error: too few arguments'
const roleQuery = format( 'with new_practice as(INSERT INTO gp_checklist.user_practices (user_id, practice_id) VALUES %L) INSERT INTO user_roles (user_id, practice_id, role_id) VALUES %L;', [practiceArray, practiceRoleArray]);
Anyone? who has done this kind of work?
The following code in index.js causes files built using rollup to throw error during runtime.
(Error: Could not dynamically require "/build/reserved.js". ).
// index.js
var reservedMap = require(__dirname + '/reserved.js');
For now I've used dynamicRequireTargets with @rollup/plugin-commonjs plugin to resolve this issue.
plugins:[...,
commonjs({
dynamicRequireTargets: ["node_modules/pg-format/lib/*.js"],
})
],
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.