Skip to content
Tutorials Center
My learning

Topics

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.

NODE
import { DatabaseSync } from "node:sqlite";

const db = new DatabaseSync(":memory:");
db.exec("CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT)");

Try it

Node.js A whole schema in one call

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.

Node.js What happens when a statement fails

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

NODE
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 Error with code: "ERR_SQLITE_ERROR", plus errcode and errstr properties from SQLite. The message is SQLite's own text, such as no such table: missing_table.
  • A non-string argument throws ERR_INVALID_ARG_TYPE.
  • Calling exec() on a closed database throws ERR_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. Use prepare() 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.readFileSync and pass the text straight in.
  • A script that mixes schema changes with data is faster when wrapped in BEGIN and COMMIT, because SQLite then syncs to disk once instead of once per statement.
  • exec() can run a SELECT, 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 as NULL, so a NULL row is inserted.
  • Run PRAGMA settings through exec() before other work: PRAGMA journal_mode = WAL and PRAGMA busy_timeout = 5000 are the two most common.