stuck granite
Just playing around with dynamic values in SQL statements and I came accross following issue that I do not understand.
I have a query.sql file that is executed at the beginning.
DROP TABLE IF EXISTS users;
CREATE TABLE IF NOT EXISTS users (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
created_at TIMESTAMP DEFAULT NOW ()
);
import { SQL } from 'bun';
const db = new SQL({
hostname: 'localhost',
port: 5432,
...
});
await db.file('query.sql');
Then I define some values to dynamically set those in queries.
const secondName = 'Maria';
const secondEmail = '[email protected]';
const table = 'users';
const column_1 = 'name';
const column_2 = 'email';
Next I insert values into the database table dynamically and want to get one column to log out.
This first example works. It logs all columns with * or one column with 'name'
const maria = await db`
INSERT INTO ${db(table)} (${db(column_1)}, ${db(column_2)}) VALUES (${secondName}, ${secondEmail})
returning *
`; // returning * or 'name'
console.log(maria);
The second works and returns the requested column. But the values are not dynamic.
const maria = await db`
INSERT INTO ${db(table)} (${db(column_1)}, ${db(column_2)}) VALUES ('Maria', '[email protected]')
returning ${db(column_1)}
`; // don't do returning ${column_1} it is going to set "?column?": "name",
console.log(maria);
The expected behavior was the following example but this trows a syntax error at or near "$3". Table, columns and values should be dynamic.
const maria = await db`
INSERT INTO ${db(table)} (${db(column_1)}, ${db(column_2)}) VALUES (${secondName}, ${secondEmail})
returning ${db(column_1)}
`; // the same for returning ${column_1} without db()
console.log(maria);
Did I miss something in the documentation? Someone who knows how to resolve?
dire moat