sql
Execute SQL queries against the database.
Usage
Access or destructure sql
from the SQLocal
client.
import { SQLocal } from 'sqlocal';
export const { sql } = new SQLocal('database.sqlite3');
NOTE
If you are using the Kysely Query Builder or Drizzle ORM for type-safe queries, you will initialize the client with a child class of SQLocal
. See the corresponding setup page. Usage is the same otherwise.
sql
is used as a tagged template. Values interpolated into the query string will be passed to the database as parameters to that query.
const item = 'Bread';
const quantity = 2;
await sql`INSERT INTO groceries (name, quantity) VALUES (${item}, ${quantity})`;
SELECT queries and queries with the RETURNING clause will return the matched records as an array of objects.
const data = await sql`SELECT * FROM groceries`;
console.log(data);
Example result:
[
{ id: 1, name: 'Rice', quantity: 4 },
{ id: 2, name: 'Milk', quantity: 1 },
{ id: 3, name: 'Bread', quantity: 2 },
];
Multiple statements can be passed in the query, but note that the results returned will only include results from the first value-returning statement. Also, only one statement in the query can have parameter bindings. Because of these restrictions, it is recommended to pass only one SQL statement per call to sql
. To run multiple statements together, use the batch
method.
// Warning: only returns the row with id 1.
const result = await sql`
SELECT * FROM foo WHERE id = 1;
SELECT * FROM foo WHERE id = 2;
`;
// Recommended: one statement per query
const result1 = await sql`SELECT * FROM foo WHERE id = 1;`;
const result2 = await sql`SELECT * FROM foo WHERE id = 2;`;