Node.js Reference ยท node:sqlite and Runtime Services
Node.js database.exec() method
Reference for database.exec() in node:sqlite: run one or many SQL statements at once, why it returns nothing, how it fails halfway and why it takes no parameters.
database.exec(sql) runs the SQL text you give it, one statement after another, and returns undefined. It is the right tool for statements where you do not need a result: creating tables and indexes, PRAGMA settings, BEGIN and COMMIT, and loading a schema or a seed script from a file.
Because exec() returns nothing and accepts no parameters, it is the wrong tool for anything that reads rows or contains values from outside your program. For those use database.prepare(), which compiles a statement you can run with bound parameters.
import { DatabaseSync } from "node:sqlite"; const db = new DatabaseSync(":memory:"); db.exec("CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT)");
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.exec(sql)
| Parameter | Description |
|---|---|
sql |
A string with one or more SQL statements separated by semicolons. |
Returns. undefined. To learn how many rows were affected or the new row id, run the statement with prepare(...).run() instead.
Errors.
- A failing statement throws an
Errorwithcode: "ERR_SQLITE_ERROR", pluserrcodeanderrstrproperties from SQLite. The message is SQLite's own text, such asno such table: missing_table. - A non-string argument throws
ERR_INVALID_ARG_TYPE. - Calling
exec()on a closed database throwsERR_INVALID_STATE. - Statements run in order and stop at the first error. Earlier statements are not undone unless you opened a transaction.
Usage notes
exec()accepts no bound values. Never build a statement from user input with a template string; that is how SQL injection happens. Useprepare()and parameters for any value that comes from outside.- An empty string is allowed and does nothing.
- Use it for a seed or migration script. Read the SQL file with
fs.readFileSyncand pass the text straight in. - A script that mixes schema changes with data is faster when wrapped in
BEGINandCOMMIT, because SQLite then syncs to disk once instead of once per statement. exec()can run aSELECT, but it throws the rows away, so there is rarely a reason to.- Extra arguments after the SQL text are ignored, not an error, which is why a misplaced parameter silently disappears. The second example shows the symptom: the statement still contains an unbound
?, which SQLite treats asNULL, so aNULLrow is inserted. - Run
PRAGMAsettings throughexec()before other work:PRAGMA journal_mode = WALandPRAGMA busy_timeout = 5000are the two most common.