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.
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
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
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
INTEGERoutside the safe-integer range as anumberthrowsERR_OUT_OF_RANGE("Value is too large to be represented as a JavaScript number") instead of returning a rounded value. - Binding a
bigintoutside the signed 64-bit range throwsERR_INVALID_ARG_VALUE("BigInt value is too large to bind"). undefinedandSymbolvalues throwERR_INVALID_ARG_TYPE.
Usage notes
- Column affinity decides what is stored. A column declared
INTEGERturns the double5.0back into the integer5, so ordinary integer columns behave as you expect. A column with no type keeps the value asREAL, and aTEXTcolumn stores5as the text"5.0". Declare column types, and bindbigintwhen exactness or storage class matters. - Integers up to 2^53 round-trip exactly as numbers, but anything larger needs
biginton both sides. - SQLite has no boolean type:
trueis stored as1. Read it back withBoolean(row.flag)orrow.flag === 1. - SQLite has no date type either. Store ISO 8601 text (
toISOString()) or integer epoch milliseconds. Built-in functions such asdate(),datetime()andstrftime()understand ISO text and Unix seconds. - A
Uint8Arrayread from a blob is a view of the bytes. UseBuffer.from(bytes)fortoString("hex")and similar helpers. - Objects and arrays are not values. Store them as JSON text with
JSON.stringify()and read them back withJSON.parse(), or use SQLite'sjson_extract()in queries. LENGTH()of text counts characters, not bytes.LENGTH(CAST(x AS BLOB))counts bytes.- Rows are objects with a
nullprototype, so they print with[Object: null prototype].