I am a front end developer and this is my first time using Slonik with postgresql.
I want to know how I can make this query dynamic by inserting the data (which is hardcoded) using function parameters:
const addMany = async (connection = slonik) => {
const useResult = await connection.query(sql`
INSERT into
users (username, email)
VALUES
('amite', '[email protected]'),
('nilesh', '[email protected]'),
('nikhil', '[email protected]')
RETURNING *;
`);
return useResult;
};
Do I need to create tuples using string concatenation? I am confused
('amite', '[email protected]'),
('nilesh', '[email protected]'),
('nikhil', '[email protected]')
What I have tried so far is:
const addManyUsers = async(connection = slonik) => {
const keys = [
'username', 'email'
];
const values = [
['nilesh', 'bailey'],
['[email protected]', '[email protected]']
]
const identifiers = keys.map((key) => {
return sql.identifier([key]);
});
const query = sql`
INSERT INTO users
(${sql.join(identifiers, sql`, `)})
VALUES
(${sql.unnest(values, sql`, `)})
RETURNING *
`
const records = await connection.query(query)
return records
}
When I run this I get the error:
(node:5975) UnhandledPromiseRejectionWarning: Error: **Column types length must match tuple member length.**
at Object.createUnnestSqlFragment (/Users/shreekant/Documents/code/node/postgres-starter/node_modules/slonik/dist/sqlFragmentFactories/createUnnestSqlFragment.js:29:19)
at Object.createSqlTokenSqlFragment (/Users/shreekant/Documents/code/node/postgres-starter/node_modules/slonik/dist/factories/createSqlTokenSqlFragment.js:27:39)
at sql (/Users/shreekant/Documents/code/node/postgres-starter/node_modules/slonik/dist/factories/createSqlTag.js:39:65)
at addManyUsers (/Users/shreekant/Documents/code/node/postgres-starter/app/models/db.js:58:20)
at Object.<anonymous> (/Users/shreekant/Documents/code/node/postgres-starter/app/models/db.js:72:1)
at Module._compile (internal/modules/cjs/loader.js:1063:30)
at Object.Module._extensions..js (internal/modules/cjs/loader.js:1092:10)
at Module.load (internal/modules/cjs/loader.js:928:32)
at Function.Module._load (internal/modules/cjs/loader.js:769:14)
at Function.executeUserEntryPoint [as runMain] (internal/modules/run_main.js:72:12)
at internal/main/run_main_module.js:17:47
(Use `node --trace-warnings ...` to show where the warning was created)
(node:5975) UnhandledPromiseRejectionWarning: Unhandled promise rejection. This error originated either by throwing inside of an async function without a catch block, or by rejecting a promise which was not handled with .catch(). To terminate the node process on unhandled promise rejection, use the CLI flag `--unhandled-rejections=strict` (see https://nodejs.org/api/cli.html#cli_unhandled_rejections_mode). (rejection id: 2)
(node:5975) [DEP0018] DeprecationWarning: Unhandled promise rejections are deprecated. In the future, promise rejections that are not handled will terminate the Node.js process with a non-zero exit code.
This is what my table structure looks like. I am using `varchar(50)
What am I doing wrong?
@RaghavGarg. here is the updated code as per your suggestion:
const keys = [
'username',
'email',
];
const identifiers = keys.map((key) => {
return sql.identifier([key]);
});
const values = [
['nilesh', '[email protected]'], // single full record
['bailey', '[email protected]'], // single full record
]
const values_types = ['varchar', 'varchar'];
const main = async(connection = slonik) => {
let query = sql`
INSERT INTO users
(${sql.join(identifiers, sql`, `)})
VALUES
(${sql.unnest(values, values_types)})
RETURNING *
`
try {
const results = await connection.query(query)
console.log(results);
return results
} catch (error) {
console.error(error);
}
}
main()
The query above expands out to:
{
sql: '\n' +
'INSERT INTO users\n' +
' ("username", "email")\n' +
'VALUES\n' +
' (unnest($1::"varchar(50)"[], $2::"varchar(50)"[]))\n' +
'RETURNING *\n',
type: 'SLONIK_TOKEN_SQL',
values: [
[ 'nilesh', 'bailey' ],
[ '[email protected]', '[email protected]' ]
]
}
The error I get from this now is:
error: type "varchar(50)[]" does not exist
at Parser.parseErrorMessage (/Users/shreekant/Documents/code/node/postgres-starter/node_modules/pg-protocol/dist/parser.js:278:15)
at Parser.handlePacket (/Users/shreekant/Documents/code/node/postgres-starter/node_modules/pg-protocol/dist/parser.js:126:29)
at Parser.parse (/Users/shreekant/Documents/code/node/postgres-starter/node_modules/pg-protocol/dist/parser.js:39:38)
at Socket.<anonymous> (/Users/shreekant/Documents/code/node/postgres-starter/node_modules/pg-protocol/dist/index.js:10:42)
at Socket.emit (events.js:315:20)
at Socket.EventEmitter.emit (domain.js:486:12)
at addChunk (_stream_readable.js:309:12)
at readableAddChunk (_stream_readable.js:284:9)
at Socket.Readable.push (_stream_readable.js:223:10)
at TCP.onStreamRead (internal/stream_base_commons.js:188:23) {
length: 100,
severity: 'ERROR',
code: '42704',
detail: undefined,
hint: undefined,
position: '81',
internalPosition: undefined,
internalQuery: undefined,
where: undefined,
schema: undefined,
table: undefined,
column: undefined,
dataType: undefined,
constraint: undefined,
file: 'parse_type.c',
line: '274',
routine: 'typenameType',
notices: []
}
PostgreSQL INSERT Multiple Rows First, specify the name of the table that you want to insert data after the INSERT INTO keywords. Second, list the required columns or all columns of the table in parentheses that follow the table name. Third, supply a comma-separated list of rows after the VALUES keyword.
INSERT-SELECT-UNION query to insert multiple records Thus, we can use INSERT-SELECT-UNION query to insert data into multiple rows of the table. The SQL UNION query helps to select all the data that has been enclosed by the SELECT query through the INSERT statement.
There is an issue with the arguments passed to method sql.unnest
. It takes the data array as the first argument and type array as the second argument.
That's what the error also says
Column types length must match tuple member length
So your code should change to something like
const values_types = ['text', 'text'];
const query = sql`
INSERT INTO users
(${sql.join(identifiers, sql`, `)})
VALUES
(${sql.unnest(values, values_types)})
RETURNING *
`
Docs for sql.unnest
( tuples: $ReadOnlyArray<$ReadOnlyArray>, columnTypes: $ReadOnlyArray ): UnnestSqlTokenType;
Also, you should consider wrapping your code in try/catch
block and handle the errors properly.
(node:5975) [DEP0018] DeprecationWarning: Unhandled promise rejections are deprecated. In the future, promise rejections that are not handled will terminate the Node.js process with a non-zero exit code.
The variable value_types
is an array containing the type for each column that your inserting using the query.
So value_types
should always have an equal number of elements as of any member of values
values[i].length === values_types.length
and every index in types array should correspond to the correct value of a member. So
// for
values_types = ["text", "text", "int4"]
// any values[i] should be
values[i] = ["nilesh", "[email protected]", 123]
I missed one thing, the values
is also wrong, every member should be a single valid record i.e. all the column values for a single record.
The variable should look something like this
const values = [
['nilesh', '[email protected]'], // single full record
['bailey', '[email protected]'], // single full record
]
So your final code look something like this
const values = [
['nilesh', '[email protected]'], // single full record
['bailey', '[email protected]'], // single full record
]
const values_types = ['text', 'text'];
const query = sql`
INSERT INTO users
(${sql.join(identifiers, sql`, `)})
VALUES
(${sql.unnest(values, values_types)})
RETURNING *
`
This is what finally worked. I needed to use a SELECT * FROM
instead of VALUES
let query = sql`
INSERT INTO users
(${sql.join(identifiers, sql`, `)})
SELECT * FROM
${sql.unnest(values, values_types)}
RETURNING *
`
Here is the whole function:
const keys = [
'username',
'email',
];
const identifiers = keys.map((key) => {
return sql.identifier([key]);
});
const values = [
['nilesh', '[email protected]'], // single full record
['bailey', '[email protected]'], // single full record
]
const values_types = [`varchar`,`varchar`];
const main = async(connection = slonik) => {
let query = sql`
INSERT INTO users
(${sql.join(identifiers, sql`, `)})
SELECT * FROM
${sql.unnest(values, values_types)}
RETURNING *
`
try {
const results = await connection.query(query)
console.log(results);
return results
} catch (error) {
console.error(error);
}
}
main()
This is what the query now expands to:
{
sql: '\n' +
'INSERT INTO users\n' +
' ("username", "email")\n' +
'SELECT * FROM\n' +
' unnest($1::"varchar"[], $2::"varchar"[])\n' +
'RETURNING *\n',
type: 'SLONIK_TOKEN_SQL',
values: [
[ 'nilesh', 'bailey' ],
[ '[email protected]', '[email protected]' ]
]
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With