Node.js Reference ยท node:sqlite and Runtime Services
Node.js database.prepare() method
Reference for database.prepare() in node:sqlite: compile SQL into a reusable StatementSync, then use run, get, all and iterate, with columns() and sourceSQL.
database.prepare(sql) compiles one SQL statement and returns a StatementSync object. You run that object as many times as you like, each time with different parameter values. Preparing once and running many times is faster than parsing the SQL every time, and it is the safe way to put values into SQL, because bound values can never turn into SQL code.
The statement object has four ways to run: run() for writes, get() for one row, all() for every row and iterate() for streaming rows. Each is covered on its own page. This page covers prepare() itself and the settings and properties of the statement.
import { DatabaseSync } from "node:sqlite"; const db = new DatabaseSync(":memory:"); db.exec("CREATE TABLE t (x)"); const insert = db.prepare("INSERT INTO t VALUES (?)"); insert.run(1);
Try it
Press Run (or Ctrl + Enter) to see the result here.
Program input ยท what you'd type while the program runs
Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.
Press Run (or Ctrl + Enter) to see the result here.
Program input ยท what you'd type while the program runs
Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.
Syntax
database.prepare(sql) // returns StatementSync
| Parameter | Description |
|---|---|
sql |
A string containing one SQL statement. Placeholders are ?, ?NNN, :name, @name or $name. |
Returns. A StatementSync.
Statement members.
| Member | Description |
|---|---|
run(...params) |
Executes and returns { changes, lastInsertRowid }. |
get(...params) |
Returns the first row, or undefined when there is none. |
all(...params) |
Returns an array of all rows. |
iterate(...params) |
Returns an iterator of rows. |
columns() |
An array describing the result columns: name, column, table, database and type. Computed columns show null for everything except name. |
sourceSQL |
The SQL text you passed to prepare(). |
expandedSQL |
The SQL with the most recently bound values filled in, useful for logging. |
setReadBigInts(flag) |
Return integers as bigint for this statement. |
setReturnArrays(flag) |
Return rows as arrays instead of objects for this statement. |
setAllowBareNamedParameters(flag) |
Allow { name } for :name without the prefix. |
setAllowUnknownNamedParameters(flag) |
Ignore object keys that match no placeholder. |
close() |
Finalizes the statement and frees its resources. |
Errors. Invalid SQL throws an Error with code: "ERR_SQLITE_ERROR", with SQLite's message such as near "SELEC": syntax error, as soon as you call prepare(). A string with no statements in it (only spaces) throws ERR_INVALID_ARG_VALUE. Using a statement after close() throws ERR_INVALID_STATE ("statement has been finalized").
Usage notes
- Create statements once, at module level or in a class constructor, and keep them. Preparing inside a hot loop discards the saving.
- Only the first statement in the text is prepared. Anything after the first semicolon is ignored without an error, so use database.exec() for scripts.
- Table and column names cannot be parameters. Choose them from an allow-list in JavaScript and build the text from that list.
- Per-statement settings such as
setReturnArrays()change only that statement. Constructor options on DatabaseSync set the default for all statements. - Statements are cleaned up when the database closes. Calling
close()on a statement yourself matters only for long-lived connections that create many one-off statements. - A statement is bound to the database that created it and cannot be moved between connections.
- Because rows have a
nullprototype, they do not havehasOwnProperty()ortoString(). UseObject.hasOwn(row, "name")or spread them into a new object when you need those.