Data Access Options in Node.js: node:sqlite, pg, a Query Builder (Kysely) and an ORM (Prisma) — Which One When?

Version note: The examples in this post were run with Node.js 26.13.1 (LTS; with its bundled SQLite 3.54.1), PostgreSQL 18.7, pg 8.23.1, Kysely 0.29.9, Prisma 7.10.1 and TypeScript 7.0.2.

Guess First

In this post we set up the catalog tables of Bookstore, the online bookstore used as the shared example across this site's series, in PostgreSQL. The first query calculates the total value of the classics waiting on the shelf and the sum of their prices:

import pg from "pg";

const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL });

// Total value of the classics on the shelf: price * stock, added up
const { rows } = await pool.query("SELECT b.price, b.stock FROM books b JOIN categories c ON c.id = b.category_id WHERE c.slug = $1", ["classics"]);
const total = rows.reduce((sum, row) => sum + row.price * row.stock, 0);
const prices = rows.reduce((sum, row) => sum + row.price, 0);
console.log("stock value:", total);
console.log("sum of prices:", prices);
await pool.end();

Do both lines print a number?

stock value: 12007
sum of prices: 0245.00229.90310.00199.90

The first is right, the second is a string. pg returns PostgreSQL's numeric type as a string so that no precision is lost. Because * converts strings to numbers, the product happened to come out right; + concatenated the strings. TypeScript did not warn either, because rows is typed any. In post 13 we saw the traps of SQL Server's decimal turning into a JavaScript number; here the problem comes from the other direction. In this post we try four data access approaches in Node.js with the same "books by category" query: the embedded node:sqlite, plain SQL with pg, a type-safe query builder with Kysely, and an ORM with Prisma. We look at when each one fits, the SQL it produces, and its .NET counterpart.

node:sqlite: A Database With Nothing to Install

Since Node 22.5, SQLite is built into Node; there is no package to install. In the Node 26.13.1 documentation the module's level is "Release candidate" (1.2): the API is no longer expected to change, but it is not stable yet. The API is synchronous and resembles Microsoft.Data.Sqlite in .NET:

// Prepared statements with ? parameters; one transaction around the inserts
const insertBook = db.prepare("INSERT INTO books VALUES (?, ?, ?, ?, ?, ?, ?)");
db.exec("BEGIN");
db.prepare("INSERT INTO categories (slug, name) VALUES (?, ?), (?, ?)").run("classics", "Classics", "science-fiction", "Science Fiction");
db.prepare("INSERT INTO authors (name) VALUES (?), (?), (?)").run("Jane Austen", "George Eliot", "Frank Herbert");
insertBook.run("9780141439587", "Emma", 1, 1, 24500, "TRY", 12);
insertBook.run("9780141439518", "Pride and Prejudice", 1, 1, 22990, "TRY", 30);
insertBook.run("9780141439549", "Middlemarch", 2, 1, 31000, "TRY", 7);
insertBook.run("9780441172719", "Dune", 3, 2, 34990, "TRY", 18);
db.exec("COMMIT");

const booksByCategory = db.prepare(`
    SELECT b.isbn, b.title, a.name AS author, b.price_minor AS priceMinor, b.currency
    FROM books b
    JOIN authors a ON a.id = b.author_id
    JOIN categories c ON c.id = b.category_id
    WHERE c.slug = ?
    ORDER BY b.title
    LIMIT ?`);
const rows = booksByCategory.all("classics", 10) as { title: string; author: string; priceMinor: number; currency: string }[];
console.log(rows.map((r) => `${r.title} | ${r.author} | ${r.priceMinor} ${r.currency}`).join("\n"));

// SQLite has dynamic types: a column declared INTEGER happily stores text
insertBook.run("9780000000002", "Typo", 1, 1, "245,00", "TRY", 1);
const typo = db.prepare("SELECT price_minor, typeof(price_minor) AS stored FROM books WHERE isbn = ?").get("9780000000002");
console.log(typo);

// STRICT tables (SQLite 3.37 and later) refuse the wrong type
db.exec("CREATE TABLE prices (price_minor INTEGER NOT NULL) STRICT");
try {
    db.prepare("INSERT INTO prices VALUES (?)").run("245,00");
} catch (err) {
    console.log((err as Error).message);
}
db.close();

Emma | Jane Austen | 24500 TRY
Middlemarch | George Eliot | 31000 TRY
Pride and Prejudice | Jane Austen | 22990 TRY
[Object: null prototype] { price_minor: '245,00', stored: 'text' }
cannot store TEXT value in INTEGER column prices.price_minor

prepare returns a prepared statement, ? parameters travel separately from the SQL, and you open the transaction yourself with BEGIN and COMMIT. The second and third lines of output show SQLite's most important difference: column types are a suggestion. The text "245,00" went into an INTEGER column without complaint; STRICT tables, available since SQLite 3.37, refuse it. Use STRICT in a new SQLite schema.

The synchronous API matters: every query stops the event loop. So the place for node:sqlite is not the main database of a busy API; it is running real SQL in tests, CLI tools (post 19), an application's local cache, or single-user tools. If you must run a large query inside an API, move it to a worker as in post 21.

pg: Plain SQL and a Connection Pool

The most common PostgreSQL driver is pg (node-postgres). The patterns of mssql in post 13 apply here too: one pool per process, parameterised queries, and a single client from the pool for a transaction:

// A transaction needs one client from the pool for every statement in it
async function reserveStock(isbn: string, quantity: number): Promise<void> {
    const client = await pool.connect();
    try {
        await client.query("BEGIN");
        const result = await client.query("UPDATE books SET stock = stock - $2 WHERE isbn = $1 AND stock >= $2", [isbn, quantity]);
        if (result.rowCount !== 1) {
            throw new Error(`not enough stock for ${isbn}`);
        }
        await client.query("COMMIT");
    } catch (err) {
        await client.query("ROLLBACK");
        throw err;
    } finally {
        client.release();
    }
}
await reserveStock("9780141439587", 2);
await reserveStock("9780141439471", 1).catch((err: Error) => console.log(err.message));
await pool.end();

Emma | Jane Austen | 245.00 TRY
Frankenstein | Mary Shelley | 199.90 TRY
Middlemarch | George Eliot | 310.00 TRY
Pride and Prejudice | Jane Austen | 229.90 TRY
{ price: 'string', stock: 'string', addedAt: true }
not enough stock for 9780141439471

Parameters are written as $1, $2 and sent separately from the SQL text; that is still how you guard against SQL injection. The UPDATE ... WHERE stock >= $2 in the transaction is the same stock check as in post 13: Frankenstein's stock is 0, so no row was updated and the transaction was rolled back. The type mapping is as in Guess First: numeric and bigint come back as strings, timestamptz as a Date. For money the safest route is what we did in post 13: turn the value into whole minor units in SQL ((price * 100)::int); converting numeric to number with pg.types.setTypeParser is easy but loses precision for large values. The .NET counterpart of this approach is hand-written SQL with Npgsql, or Dapper (post 11 of the PostgreSQL series).

Kysely: A Type-Safe Query Builder

What plain SQL lacks is that TypeScript knows nothing about the query. Kysely is a query builder that lets you assemble SQL piece by piece in TypeScript; it is not an ORM, and the SQL it produces stays very close to the code you write. You describe the tables with an interface (in a real project kysely-codegen generates it from the database):

// The query as a function of the Kysely instance; every table and column name is checked by tsc
function booksByCategory(db: Kysely<Database>, slug: string, limit: number) {
    return db
        .selectFrom("books as b")
        .innerJoin("authors as a", "a.id", "b.author_id")
        .innerJoin("categories as c", "c.id", "b.category_id")
        .select(["b.isbn", "b.title", "a.name as author", "b.price", "b.currency"])
        .where("c.slug", "=", slug)
        .orderBy("b.title")
        .limit(limit);
}

const db = new Kysely<Database>({ dialect: new PostgresDialect({ pool: new pg.Pool({ connectionString: process.env.DATABASE_URL }) }) });
const query = booksByCategory(db, "classics", 10);
const compiled = query.compile();
console.log(compiled.sql);
console.log(compiled.parameters);
const rows = await query.execute();
console.log(rows.map((r) => `${r.title} | ${r.author} | ${r.price}`).join("\n"));
await db.destroy();

select "b"."isbn", "b"."title", "a"."name" as "author", "b"."price", "b"."currency" from "books" as "b" inner join "authors" as "a" on "a"."id" = "b"."author_id" inner join "categories" as "c" on "c"."id" = "b"."category_id" where "c"."slug" = $1 order by "b"."title" limit $2
[ 'classics', 10 ]
Emma | Jane Austen | 245.00
Frankenstein | Mary Shelley | 199.90
Middlemarch | George Eliot | 310.00
Pride and Prejudice | Jane Austen | 229.90
select "b"."isbn", "b"."title", "a"."name" as "author", "b"."price", "b"."currency" from "books" as "b" inner join "authors" as "a" on "a"."id" = "b"."author_id" inner join "categories" as "c" on "c"."id" = "b"."category_id" where "c"."slug" = @1 order by "b"."title" limit @2
select top(10) "b"."isbn", "b"."title" from "books" as "b" inner join "categories" as "c" on "c"."id" = "b"."category_id" where "c"."slug" = @1 order by "b"."title"

The first line is the SQL Kysely produced for PostgreSQL: the same query we wrote by hand, with the parameters still separate. The gain is at compile time. Misspell a column, or compare category_id with a string, and tsc fails (I created this file only to try it, then deleted it):

export function typo(db: Kysely<Database>) {
    return db.selectFrom("books").select(["isbn", "titel"]).where("category_id", "=", "classics");
}

typo-check.ts(5,42): error TS2769: No overload matches this call.
  The last overload gave the following error.
    Argument of type 'string[]' is not assignable to parameter of type 'SelectExpression<Database, "books">'.
typo-check.ts(5,87): error TS2345: Argument of type '"classics"' is not assignable to parameter of type 'OperandValueExpressionOrList<Database, "books", "category_id">'.

Kysely also has a ready-made dialect for SQL Server (MssqlDialect, with tedious and tarn). Without a connection, we looked at what the same function would produce for SQL Server:

// The same function compiled for SQL Server without a connection: what would tedious receive?
const sqlServer = new Kysely<Database>({
    dialect: {
        createAdapter: () => new MssqlAdapter(),
        createDriver: () => new DummyDriver(),
        createIntrospector: (db) => new MssqlIntrospector(db),
        createQueryCompiler: () => new MssqlQueryCompiler(),
    },
});
console.log(booksByCategory(sqlServer, "classics", 10).compile().sql);

// SQL Server has no LIMIT: there the query needs top() (or offset/fetch)
const tsql = sqlServer
    .selectFrom("books as b")
    .innerJoin("categories as c", "c.id", "b.category_id")
    .top(10)
    .select(["b.isbn", "b.title"])
    .where("c.slug", "=", "classics")
    .orderBy("b.title");
console.log(tsql.compile().sql);

The last two lines of the Kysely output are the result of this code. The first ends with limit @2, but SQL Server has no LIMIT; this query would not run on SQL Server. A query builder translates syntax between dialects, but it does not carry every feature to every database; on SQL Server you need top() or offset/fetch. Do not choose it expecting "write once, run on any database". We did not run these SQL Server queries against a real SQL Server in this post; we only showed the SQL produced. .NET has no exact counterpart; it sits somewhere between Dapper and LINQ.

Prisma: Schema, Migrations and a Generated Client

Prisma is the option closest to EF Core: models are defined in a schema file, prisma generate produces a typed client from that schema, and prisma migrate turns schema changes into SQL migrations. Prisma 7 brings two changes: the client is generated as TypeScript into a folder you choose instead of node_modules, and it connects to the database through a driver adapter (here @prisma/adapter-pg with pg; there is @prisma/adapter-mssql for SQL Server). Since we created the tables with SQL, we wrote the schema by hand and first compared it with the database:

node --env-file=.env node_modules/prisma/build/index.js migrate diff --from-config-datasource --to-schema prisma/schema.prisma --script

-- DropForeignKey
ALTER TABLE "books" DROP CONSTRAINT "books_author_id_fkey";

-- DropForeignKey
ALTER TABLE "books" DROP CONSTRAINT "books_category_id_fkey";

-- AddForeignKey
ALTER TABLE "books" ADD CONSTRAINT "books_author_id_fkey" FOREIGN KEY ("author_id") REFERENCES "authors"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "books" ADD CONSTRAINT "books_category_id_fkey" FOREIGN KEY ("category_id") REFERENCES "categories"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

Good thing we checked: Prisma's default relation settings (ON UPDATE CASCADE) differ from those in our SQL, and the migration wanted to change them. After adding onDelete: NoAction, onUpdate: NoAction to the relations, the difference disappeared and the same command produced an empty migration. In EF Core, too, differences produced by conventions only show up when you look at the migration; the same habit pays off here. The query:

const books = await prisma.book.findMany({
    where: { category: { slug: "classics" } },
    select: { isbn: true, title: true, price: true, currency: true, author: { select: { name: true } } },
    orderBy: { title: "asc" },
    take: 10,
});
console.log(books.map((b) => `${b.title} | ${b.author.name} | ${b.price.toFixed(2)}`).join("\n"));
// numeric(10,2) becomes Prisma.Decimal: exact, unlike number
console.log(`price is Prisma.Decimal: ${books[0].price instanceof Prisma.Decimal}, queries: ${queries}`);

// The N+1 trap: the author loaded in a loop, one query per book
queries = 0;
const plain = await prisma.book.findMany({ where: { category: { slug: "classics" } }, orderBy: { title: "asc" } });
for (const book of plain) {
    const author = await prisma.author.findUnique({ where: { id: book.authorId } });
    void author;
}
console.log(`N+1 version: ${plain.length} books, queries: ${queries}`);

Emma | Jane Austen | 245.00
Frankenstein | Mary Shelley | 199.90
Middlemarch | George Eliot | 310.00
Pride and Prejudice | Jane Austen | 229.90
price is Prisma.Decimal: true, queries: 2
N+1 version: 4 books, queries: 5

We saw the SQL behind the query through the log setting; the first query ran two SQL statements:

  SQL: SELECT "public"."books"."isbn", "public"."books"."title", "public"."books"."price", "public"."books"."currency", "public"."books"."author_id" FROM "public"."books" LEFT JOIN "public"."categories" AS "j0" ON ("j0"."id") = ("public"."books"."category_id") WHERE ("j0"."slug" = $1 AND ("j0"."id" IS NOT NULL)) ORDER BY "public"."books"."title" ASC LIMIT $2 OFFSET $3
  SQL: SELECT "public"."authors"."id", "public"."authors"."name" FROM "public"."authors" WHERE "public"."authors"."id" IN ($1,$2,$3,$4) OFFSET $5

Prisma did not load the relation with a single JOIN; it fetched the books and the authors with two separate queries (IN (...)) and joined them in memory. This resembles split queries in EF Core, and it is two queries whatever the number of books. In the second version we read the author one by one inside the loop: five queries for four books. The query count grows with the number of books; this is the N+1 problem. With an ORM, always ask for the relation inside the query (include or select) and keep the query log on during development. On the type side, Prisma turns numeric(10,2) into a lossless Prisma.Decimal; the mistake from Guess First does not happen here.

Which One When?

In this series the Bookstore API has used mssql and hand-written SQL since post 13; a choice parallel to the Dapper series, and enough for an API with a small number of well-known queries. Drizzle is another frequently mentioned option; its SQL Server support exists only in the 1.0 RC today (the peer dependencies of 0.45.4, the latest on npm, have no mssql, those of 1.0.0-rc.5 do); I did not try Drizzle for this post.

Production Notes

Pin your package versions. Today the latest tags of Prisma's two packages on npm point to different majors:

npm view prisma dist-tags.latest
npm view @prisma/client dist-tags.latest

8.0.0-rc.33
7.10.1

npm install prisma installs a release candidate today, while the client stays at 7.10.1. That is why the lab's package.json pins the Prisma packages to an exact version (7.10.1) and the lock file is committed (post 04). The same care applies to node:sqlite: before putting an RC-level API on a critical production path, accept that it may change in upgrades until it is stable. Installing Prisma also downloads the schema engine binary through an install script; npm 11 warns about it as a script not approved through allowScripts (the lifecycle script topic of post 04).

Size the pool by the database's connection limit: the number of replicas times the pool size must not exceed PostgreSQL's max_connections. Close the pool last during shutdown (post 14).

Common Pitfalls

Loading relations in a loop with an ORM. It produces N+1 queries; ask for the relation inside the query.

Converting database types to JavaScript types blindly. numeric and bigint arrive as strings; turning decimal into number loses precision (post 13).

Assuming a query builder is database-independent. limit does not work on SQL Server.

Trusting SQLite's column types. A table that is not STRICT silently stores the wrong type.

Installing an RC or latest-tagged version without checking. Pin the version and commit the lock file.

A Small Experiment

★☆☆ Fix labs/22/guess.ts by converting the prices to minor units in SQL ((b.price * 100)::int) and computing the total as an integer.

★★☆ Make the tables in 1-sqlite.ts STRICT, turn the same query into a node:test test, and use a new :memory: database for each test.

★★★ Write a PostgreSQL implementation of the Bookstore API's BookRepository interface with Kysely: paging and sorting should follow the contract of post 12, and the API tests of post 16 should pass with this repository too.

The series ends here. Starting from V8 and libuv and moving through the module system, npm, build-free TypeScript, the event loop, streams, Express 5, SQL Server, production readiness, security and testing, we built the Node.js side of Bookstore; in the last five posts we added what every backend needs day to day: an HTTP client, CLI tools, cryptography, worker threads and data access options. The natural next step is to put this API in a container image and measure it: the Docker series and the Observability series pick up right from here.