Skip to content
Tutorials Center
My learning

Topics

Node.js Reference ยท node:sqlite and Runtime Services

SQLite value types in node:sqlite

How JavaScript values map to SQLite types in node:sqlite: numbers, BigInt, strings, Uint8Array blobs and null, large integers, booleans, dates and column affinity.

SQLite has five storage classes: NULL, INTEGER, REAL, TEXT and BLOB. JavaScript has more types than that, and node:sqlite has to convert in both directions: when you bind a parameter, and when you read a row. Knowing the conversion table removes most surprises, especially around integers, booleans and dates.

Reading is simple and predictable. Writing has a few traps, because a JavaScript number is always a double. The table below shows what you get and what you must convert yourself.

NODE
const row = db.prepare("SELECT 1 AS i, 1.5 AS r, 'a' AS t, x'01' AS b, NULL AS n").get();
// { i: 1, r: 1.5, t: "a", b: Uint8Array [1], n: null }

Try it

Node.js Reading each storage class

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 Binding: numbers, BigInt, booleans and dates

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

Reading (SQLite to JavaScript):

SQLite JavaScript
NULL null
INTEGER number, or bigint after setReadBigInts(true)
REAL number (including Infinity)
TEXT string (UTF-8 decoded)
BLOB Uint8Array

Writing (JavaScript to SQLite):

JavaScript Bound as
null NULL
number A floating-point value; NaN becomes NULL
bigint INTEGER, within the signed 64-bit range
boolean INTEGER 1 or 0
string TEXT
Buffer, Uint8Array, other typed arrays, DataView BLOB (the raw bytes)
anything else Rejected with ERR_INVALID_ARG_TYPE, except a lone plain object or Date, which is taken as the parameter object

Errors and edge cases.

  • Reading an INTEGER outside the safe-integer range as a number throws ERR_OUT_OF_RANGE ("Value is too large to be represented as a JavaScript number") instead of returning a rounded value.
  • Binding a bigint outside the signed 64-bit range throws ERR_INVALID_ARG_VALUE ("BigInt value is too large to bind").
  • undefined and Symbol values throw ERR_INVALID_ARG_TYPE.

Usage notes

  • Column affinity decides what is stored. A column declared INTEGER turns the double 5.0 back into the integer 5, so ordinary integer columns behave as you expect. A column with no type keeps the value as REAL, and a TEXT column stores 5 as the text "5.0". Declare column types, and bind bigint when exactness or storage class matters.
  • Integers up to 2^53 round-trip exactly as numbers, but anything larger needs bigint on both sides.
  • SQLite has no boolean type: true is stored as 1. Read it back with Boolean(row.flag) or row.flag === 1.
  • SQLite has no date type either. Store ISO 8601 text (toISOString()) or integer epoch milliseconds. Built-in functions such as date(), datetime() and strftime() understand ISO text and Unix seconds.
  • A Uint8Array read from a blob is a view of the bytes. Use Buffer.from(bytes) for toString("hex") and similar helpers.
  • Objects and arrays are not values. Store them as JSON text with JSON.stringify() and read them back with JSON.parse(), or use SQLite's json_extract() in queries.
  • LENGTH() of text counts characters, not bytes. LENGTH(CAST(x AS BLOB)) counts bytes.
  • Rows are objects with a null prototype, so they print with [Object: null prototype].