Node.js: SQLite and Prisma ORM

Q: What is the difference between Prisma migrate and db push ?

migrate generates migration files, which are suitable for team collaboration and production deployment; db push directly synchronizes the schema to the database, which is suitable for prototyping.

Q: How do I switch to PostgreSQL?

Change the provider in schema.prisma to postgresql , update the DATABASE_URL connection string, and run prisma migrate reset to rebuild the database.

Q: How do you solve the N+1 problem in Prisma?

Use INCLUDE to preload related data, or use SELECT to precisely select fields, avoiding querying related records one by one in a loop.

Last updated: 2026-08-26

Charlie is developing a desktop note-taking app that requires local data storage, but he doesn’t want users to have to install a separate MySQL service. SQLite is a zero-configuration embedded database where a single file constitutes the entire database; when paired with Prisma ORM, it provides full type safety for SQL operations.

1. better-sqlite3: An Embedded Database with a Synchronous API

(1) Why choose better-sqlite3?

There are several SQLite libraries in the Node.js ecosystem, and better-sqlite3 is known for its synchronous API—it returns immediately upon invocation, without the need for await or nested callbacks. It compiles SQLite in C++ at the low-level, resulting in performance that far exceeds that of asynchronous wrapper libraries.

Feature	Description
Sync API	No callback hell, linear and readable code
Zero Configuration	No need to install a database service—`npm install` is ready to use
Single-file storage	The entire database is a single `.db` file
Transaction Support	Nested transactions and prepared statements—all included
High Performance	C++ bindings, 2–3 times faster than node-sqlite3
Cross-platform	Can be compiled on Windows, macOS, and Linux

(2) Installation and Basic Connections

BASH

npm init -y
npm install better-sqlite3

JAVASCRIPT

const Database = require('better-sqlite3');
const db = new Database('myapp.db');

db.pragma('journal_mode = WAL');
db.pragma('foreign_keys = ON');

console.log('SQLite Version:', db.prepare('SELECT sqlite_version()').get());

(3) Quick Reference Guide to Common Methods

Method	Purpose	Example
`db.prepare(sql)`	Create a precompiled statement	`const stmt = db.prepare('SELECT * FROM users WHERE id = ?')`
`stmt.run(...params)`	Execute INSERT/UPDATE/DELETE	`stmt.run(1, 'Charlie')`
`stmt.get(...params)`	Return to single-line object	`stmt.get(1)`
`stmt.all(...params)`	Return the array of all rows	`stmt.all()`
`stmt.values(...params)`	Return value array (no key names)	`stmt.values()`
`db.exec(sql)`	Execute Multiple SQL Statements	`db.exec(schemaSql)`
`db.transaction(fn)`	Create Transaction Function	`const insertMany = db.transaction((items) => {...})`
`db.pragma(cmd)`	Set/Query PRAGMA	`db.pragma('journal_mode = WAL')`

▶ Example: Complete CRUD Workflow with better-sqlite3

JAVASCRIPT

const Database = require('better-sqlite3');
const db = new Database('notes.db');

db.exec(`
  CREATE TABLE IF NOT EXISTS notes (
    id    INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    body  TEXT DEFAULT '',
    created_at TEXT DEFAULT (datetime('now'))
  )
`);

const insert = db.prepare('INSERT INTO notes (title, body) VALUES (?, ?)');
const result = insert.run('First Note', 'Hello SQLite!');
console.log('Insert a row ID:', result.lastInsertRowid);

const find = db.prepare('SELECT * FROM notes WHERE id = ?');
console.log('Search Results:', find.get(1));

const update = db.prepare('UPDATE notes SET title = ? WHERE id = ?');
update.run('Revised Title', 1);

const remove = db.prepare('DELETE FROM notes WHERE id = ?');
remove.run(1);

const listAll = db.prepare('SELECT * FROM notes ORDER BY created_at DESC');
console.log('All Notes:', listAll.all());

db.close();

▶ Try it Yourself

(4) Transactional Usage

JAVASCRIPT

const insertMany = db.transaction((notes) => {
  for (const n of notes) {
    insert.run(n.title, n.body);
  }
});

insertMany([
  { title: 'Notes A', body: 'Content A' },
  { title: 'Notes B', body: 'Content B' },
]);

2. Review of SQL Statement Basics

(1) The Four Core Operations: CRUD

Operation	SQL	Keyword
Create	INSERT	`INSERT INTO table (col) VALUES (val)`
Read	SELECT	`SELECT col FROM table WHERE cond`
Update	UPDATE	`UPDATE table SET col=val WHERE cond`
Delete	DELETE	`DELETE FROM table WHERE cond`

(2) Common Query Clauses

TEXT 📖 Display only

SELECT Listed
FROM Table Name
WHERE Conditions
GROUP BY Grouping Column
HAVING Grouping Criteria
ORDER BY Sorted List ASC|DESC
LIMIT Quantity OFFSET offset

(3) Join Queries

JAVASCRIPT

db.exec(`
  CREATE TABLE IF NOT EXISTS authors (
    id   INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL
  )
`);

db.exec(`
  CREATE TABLE IF NOT EXISTS books (
    id        INTEGER PRIMARY KEY AUTOINCREMENT,
    title     TEXT NOT NULL,
    author_id INTEGER REFERENCES authors(id)
  )
`);

const joinQuery = db.prepare(`
  SELECT books.title, authors.name AS author
  FROM books
  JOIN authors ON books.author_id = authors.id
`);

3. Prisma Installation and Initialization

(1) What is Prisma?

Prisma is a next-generation Node.js/TypeScript ORM. Its core workflow is as follows:

flowchart LR
    A["schema.prisma"] -->|"prisma migrate dev"| B["Migration SQL"]
    A -->|"prisma generate"| C["Prisma Client"]
    C -->|"Type-Safe Queries"| D[("Database")]
    B --> D

schema.prisma: Declarative definition of data models
Prisma Migrate: Automatically generates and executes migration SQL
Prisma Client: An automatically generated, type-safe query client

(2) Project Initialization

BASH

mkdir prisma-notes && cd prisma-notes
npm init -y
npm install prisma --save-dev
npm install @prisma/client
npx prisma init --datasource-provider sqlite

Generated after initialization:

TEXT 📖 Display only

prisma-notes/
├── prisma/
│   └── schema.prisma
├── .env
└── package.json

.env File contents:

TEXT 📖 Display only

DATABASE_URL="file:./dev.db"

(3) Prisma Command Quick Reference

Command	Purpose
`npx prisma init`	Initializing a Prisma project
`npx prisma migrate dev`	Create and Apply a Development Migration
`npx prisma migrate deploy`	Production Environment Application Migration
`npx prisma generate`	Generate Prisma Client
`npx prisma studio`	Open the Visual Management Interface
`npx prisma db push`	Push the schema directly during the prototype phase (without generating migration files)
`npx prisma db seed`	Run the seed data script

4. schema.prisma: Defining the Model

(1) Basic Structure

JAVASCRIPT

// prisma/schema.prisma
generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "sqlite"
  url      = env("DATABASE_URL")
}

model Note {
  id        Int      @id @default(autoincrement())
  title     String
  body      String   @default("")
  pinned    Boolean  @default(false)
  tags      String   @default("")
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

(2) Quick Reference for Field Types

Prisma Type	SQLite Mapping	Description
`String`	TEXT	string
`Int`	INTEGER	32-bit integer
`BigInt`	INTEGER	64-bit integer
`Float`	REAL	Floating-point number
`Boolean`	INTEGER	0 or 1
`DateTime`	TEXT	ISO 8601 string
`Json`	TEXT	JSON string
`Bytes`	BLOB	Binary data
`Decimal`	TEXT	High-precision decimals

Note: The SQLite type system differs from that of PostgreSQL; Prisma handles the underlying adaptation. When switching to provider, the field type mappings will be automatically adjusted.

(3) Properties and Modifiers

Modifier	Purpose	Example
`@id`	primary key	`id Int @id`
`@default`	Default value	`@default(autoincrement())` / `@default(now())` / `@default("active")`
`@unique`	Unique Constraint	`email String @unique`
`@relation`	Relationship Definition	`@relation(fields: [authorId], references: [id])`
`@map` / `@@map`	Column/Table Name Mapping	`@map("created_at")`
`@@unique`	Composite Unique	`@@unique([firstName, lastName])`
`@@index`	Composite Index	`@@index([categoryId, createdAt])`
`?`	Optional field	`bio String?`

(4) Relationship Definitions

JAVASCRIPT

model User {
  id    Int    @id @default(autoincrement())
  email String @unique
  name  String
  notes Note[]
}

model Note {
  id       Int   @id @default(autoincrement())
  title    String
  body     String @default("")
  authorId Int
  author   User   @relation(fields: [authorId], references: [id], onDelete: Cascade)
}

▶ Example: Defining Multi-Model Relationships

JAVASCRIPT

// prisma/schema.prisma — User, Note, and Tag models with relationships
model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String
  notes     Note[]
  createdAt DateTime @default(now())
}

model Note {
  id        Int        @id @default(autoincrement())
  title     String
  body      String     @default("")
  published Boolean    @default(false)
  authorId  Int
  author    User       @relation(fields: [authorId], references: [id], onDelete: Cascade)
  tags      NoteTag[]
  createdAt DateTime   @default(now())
  updatedAt DateTime   @updatedAt
}

model Tag {
  id    Int       @id @default(autoincrement())
  name  String    @unique
  notes NoteTag[]
}

model NoteTag {
  noteId Int
  tagId  Int
  note   Note @relation(fields: [noteId], references: [id], onDelete: Cascade)
  tag    Tag  @relation(fields: [tagId], references: [id], onDelete: Cascade)

  @@id([noteId, tagId])
  @@index([tagId])
}

▶ Try it Yourself

This example defines three models with two kinds of relationships:

User → Note: one-to-many, cascade delete
Note ↔ Tag: many-to-many via the join table NoteTag, using a composite primary key @@id and index @@index

5. Prisma Migrate Migration

(1) Create and apply a migration

BASH

npx prisma migrate dev --name init

After execution:

TEXT 📖 Display only

prisma/
├── schema.prisma
└── migrations/
    └── 20260703_init/
        └── migration.sql

Generated migration.sql:

SQL

CREATE TABLE "Note" (
    "id"        INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    "title"     TEXT NOT NULL,
    "body"      TEXT NOT NULL DEFAULT '',
    "pinned"    BOOLEAN NOT NULL DEFAULT false,
    "tags"      TEXT NOT NULL DEFAULT '',
    "createdAt" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" DATETIME NOT NULL
);

(2) Migration Workflow

TEXT 📖 Display only

Development Phase:  schema Edit → npx prisma migrate dev --name Description
Testing Phase:  npx prisma migrate deploy（App Only，Do not create a new migration）
Prototyping Phase:  npx prisma db push（Skip migrating files，Rapid Iteration）
Reset Data:  npx prisma migrate reset（Clear the database and replay all migrations）

(3) Production Deployment

BASH

npx prisma migrate deploy

migrate deploy Runs only unapplied migrations; does not create new migrations or reset data—ideal for CI/CD pipelines.

6. Prisma Client CRUD

(1) Initialize the Client

JAVASCRIPT

const { PrismaClient } = require('@prisma/client');
const prisma = new PrismaClient();

async function main() {
  // CRUD Instructions are written here
}

main()
  .catch(console.error)
  .finally(() => prisma.$disconnect());

(2) Create — Create

JAVASCRIPT

const note = await prisma.note.create({
  data: {
    title: 'Study Prisma',
    body: 'Prisma Making Database Operations Type-Safe',
    pinned: true,
  },
});

const notes = await prisma.note.createMany({
  data: [
    { title: 'Notes A', body: 'Content A' },
    { title: 'Notes B', body: 'Content B', pinned: true },
  ],
});

(3) Read — Query

JAVASCRIPT

const one = await prisma.note.findUnique({ where: { id: 1 } });

const first = await prisma.note.findFirst({
  where: { pinned: true },
  orderBy: { createdAt: 'desc' },
});

const all = await prisma.note.findMany();

const filtered = await prisma.note.findMany({
  where: {
    pinned: true,
    title: { contains: 'Prisma' },
  },
});

(4) Update — Update

JAVASCRIPT

const updated = await prisma.note.update({
  where: { id: 1 },
  data: { title: 'Updated Title', pinned: false },
});

const count = await prisma.note.updateMany({
  where: { pinned: false },
  data: { tags: 'archived' },
});

(5) Delete — Delete

JAVASCRIPT

const deleted = await prisma.note.delete({ where: { id: 1 } });

const deleteCount = await prisma.note.deleteMany({
  where: { pinned: false },
});

(6) List of Query Filters

Filter	Meaning	Example
`equals`	equals	`{ title: { equals: 'Hello' } }`
`not`	is not equal to	`{ id: { not: 1 } }`
`contains`	include	`{ title: { contains: 'Prisma' } }`
`startsWith`	Prefix	`{ title: { startsWith: 'Learn' } }`
`endsWith`	suffix	`{ email: { endsWith: '@test.com' } }`
`in`	In the list	`{ id: { in: [1, 2, 3] } }`
`notIn`	Not in the list	`{ id: { notIn: [4, 5] } }`
`lt` / `lte`	Less than / Less than or equal to	`{ id: { lte: 10 } }`
`gt` / `gte`	Greater than / Greater than or equal to	`{ id: { gte: 5 } }`
`AND`	And	`{ AND: [{ pinned: true }, { title: { contains: 'A' } }] }`
`OR`	or	`{ OR: [{ pinned: true }, { pinned: false }] }`
`NOT`	Not	`{ NOT: { title: 'Hello' } }`

JAVASCRIPT

const { PrismaClient } = require('@prisma/client');
const prisma = new PrismaClient();

async function queryNotes() {
  // Find published notes with their author and tags
  const notes = await prisma.note.findMany({
    where: {
      published: true,
      title: { contains: 'Prisma' },
    },
    include: {
      author: {
        select: { id: true, name: true, email: true },
      },
      tags: {
        include: {
          tag: { select: { id: true, name: true } },
        },
      },
    },
    orderBy: { createdAt: 'desc' },
    take: 10,
  });

  console.log(`Found ${notes.length} notes`);
  for (const note of notes) {
    const tagNames = note.tags.map((nt) => nt.tag.name).join(', ');
    console.log(`${note.title} — by ${note.author.name} [${tagNames}]`);
  }
}

queryNotes()
  .catch(console.error)
  .finally(() => prisma.$disconnect());

▶ Try it Yourself

This example uses include to eagerly load related author and tags, and select to limit fields to only what is needed. The result includes nested data without additional queries.

7. Sorting and Pagination

(1) Sorting

JAVASCRIPT

const sorted = await prisma.note.findMany({
  orderBy: [
    { pinned: 'desc' },
    { createdAt: 'desc' },
  ],
});

(2) Pagination

JAVASCRIPT

const PAGE_SIZE = 10;

const page1 = await prisma.note.findMany({
  skip: 0,
  take: PAGE_SIZE,
  orderBy: { createdAt: 'desc' },
});

const page2 = await prisma.note.findMany({
  skip: PAGE_SIZE,
  take: PAGE_SIZE,
  orderBy: { createdAt: 'desc' },
});

(3) Cursor-based pagination (recommended for large datasets)

JAVASCRIPT

const first = await prisma.note.findMany({
  take: 10,
  orderBy: { id: 'asc' },
});

const cursor = first[first.length - 1].id;

const next = await prisma.note.findMany({
  take: 10,
  skip: 1,
  cursor: { id: cursor },
  orderBy: { id: 'asc' },
});

▶ Example: Search and Cursor Pagination

JAVASCRIPT

const { PrismaClient } = require('@prisma/client');
const prisma = new PrismaClient();

async function searchNotes(searchTerm, cursorId = null) {
  const PAGE_SIZE = 5;
  const results = await prisma.note.findMany({
    take: PAGE_SIZE + 1,
    skip: cursorId ? 1 : 0,
    cursor: cursorId ? { id: cursorId } : undefined,
    where: {
      OR: [
        { title: { contains: searchTerm } },
        { body: { contains: searchTerm } },
      ],
    },
    include: { author: { select: { name: true } } },
    orderBy: { id: 'asc' },
  });

  const hasMore = results.length > PAGE_SIZE;
  if (hasMore) results.pop();

  return {
    items: results,
    nextCursor: hasMore ? results[results.length - 1].id : null,
    hasMore,
  };
}

async function main() {
  let cursor = null;
  for (let page = 1; page <= 3; page++) {
    const { items, nextCursor, hasMore } = await searchNotes('Prisma', cursor);
    console.log(`Page ${page}: ${items.length} results`);
    items.forEach((n) => console.log(`  ${n.id}: ${n.title}`));
    if (!hasMore) break;
    cursor = nextCursor;
  }
}

main()
  .catch(console.error)
  .finally(() => prisma.$disconnect());

▶ Try it Yourself

Combines full-text search across title and body with cursor-based pagination. Fetches one extra record to reliably determine if a next page exists, then removes it before returning.

8. Database Selection Comparison

(1) SQLite vs MySQL vs PostgreSQL vs MongoDB

Dimension	SQLite	MySQL	PostgreSQL	MongoDB
Type	Embedded	Client-server	Client-server	Document-based
Installation	No installation required (included with the npm package)	Requires installation of a service	Requires installation of a service	Requires installation of a service
Concurrent Writes	Single Writer	Multiple Writers	Multiple Writers	Multiple Writers
Data Size	Small to Medium (GB-scale)	Large (TB-scale)	Large (TB-scale)	Large (TB-scale)
Use Cases	Desktop Applications / Prototypes / Testing	Web Applications / Medium-Sized Projects	Complex Queries / Geospatial Data	Flexible Schema / Logs
JSON Support	Limited (JSON-1 extensions)	Supported	Native JSONB	Native documentation
Full-Text Search	FTS5 Extension	Full-Text Index	tsvector	Text Index
Transactions	Full ACID	Full ACID	Full ACID	4.0+ Multi-document Transactions
License	Public Domain	GPL / Commercial	PostgreSQL	SSPL

(2) Comparison of ORM Frameworks

Dimension	Mongoose	Prisma	Sequelize	TypeORM
Language	JavaScript	TypeScript preferred	JavaScript	TypeScript preferred
Database	MongoDB only	SQLite/MySQL/PostgreSQL/MongoDB	MySQL/PostgreSQL/SQLite/MSSQL	MySQL/PostgreSQL/SQLite/MSSQL
Schema Definition	JS Object	`.prisma` Declarative File	JS Model Definition	Decorator / Entity Class
Type Safety	Weak (Manual)	Strong (Autogenerated)	Weak	Medium (Decorator Types)
Migration Tool	Not Built-in	`prisma migrate`	`sequelize-cli`	Built-in
Query Method	Chained API	Chained Object	Chained / Native SQL	QueryBuilder / Native
N+1 Problem	Needs populate	Auto includes	Needs eager/lazy	Needs relations
Community Size	Large	Rapidly Growing	Large	Large
Suitable Projects	MongoDB Projects	Full-Stack TypeScript	Traditional Node.js	NestJS Ecosystem

9. Comprehensive Example: Note Management Data Layer

Build a complete CRUD data layer for note management using Prisma and SQLite.

▶ Example: Prisma + SQLite Note Management

Step 1 — Schema Definition

JAVASCRIPT

// prisma/schema.prisma
generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "sqlite"
  url      = env("DATABASE_URL")
}

model Note {
  id        Int      @id @default(autoincrement())
  title     String
  body      String   @default("")
  pinned    Boolean  @default(false)
  tags      String   @default("")
  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @updatedAt      @map("updated_at")

  @@map("notes")
}

▶ Try it Yourself

Step 2 — Migration

BASH

npx prisma migrate dev --name notes_init

Step 3 — Data Access Layer

JAVASCRIPT

const { PrismaClient } = require('@prisma/client');
const prisma = new PrismaClient();

async function createNote(data) {
  return prisma.note.create({ data });
}

async function getNoteById(id) {
  return prisma.note.findUnique({ where: { id } });
}

async function updateNote(id, data) {
  return prisma.note.update({ where: { id }, data });
}

async function deleteNote(id) {
  return prisma.note.delete({ where: { id } });
}

async function listNotes({ page = 1, pageSize = 10, pinned, keyword } = {}) {
  const where = {};
  if (pinned !== undefined) where.pinned = pinned;
  if (keyword) where.title = { contains: keyword };

  const [items, total] = await Promise.all([
    prisma.note.findMany({
      where,
      orderBy: [{ pinned: 'desc' }, { createdAt: 'desc' }],
      skip: (page - 1) * pageSize,
      take: pageSize,
    }),
    prisma.note.count({ where }),
  ]);

  return { items, total, page, pageSize, totalPages: Math.ceil(total / pageSize) };
}

async function togglePin(id) {
  const note = await prisma.note.findUnique({ where: { id } });
  if (!note) throw new Error('The note does not exist.');
  return prisma.note.update({
    where: { id },
    data: { pinned: !note.pinned },
  });
}

module.exports = {
  createNote,
  getNoteById,
  updateNote,
  deleteNote,
  listNotes,
  togglePin,
};

Step 4 — Example of Use

JAVASCRIPT

const db = require('./note-service');

async function main() {
  const n1 = await db.createNote({ title: 'Study Prisma', body: 'Type Safety ORM', pinned: true });
  const n2 = await db.createNote({ title: 'SQLite Key Points', body: 'Zero-Configuration Embedded Database' });
  const n3 = await db.createNote({ title: 'Prisma Migration', body: 'migrate dev Driver' });

  console.log('Single-Record Query:', await db.getNoteById(n1.id));

  await db.updateNote(n2.id, { body: 'What's New' });
  await db.togglePin(n3.id);

  const result = await db.listNotes({ page: 1, pageSize: 10, keyword: 'Prisma' });
  console.log('Search Results:', result);

  await db.deleteNote(n2.id);

  const all = await db.listNotes({ page: 1, pageSize: 10 });
  console.log('Remaining Notes:', all);
}

main()
  .catch(console.error)
  .finally(() => require('@prisma/client').PrismaClient &&
    require('./node_modules/.prisma/client').$disconnect?.());

Execute:

BASH

node index.js

Step 5 — Visual Management

BASH

npx prisma studio

Open http://localhost:5555 in your browser to view and edit the data visually.

10. Summary of This Lesson

SQLite is a zero-configuration embedded database; better-sqlite3 provides a high-performance synchronization API
Prisma uses schema.prisma to define models declaratively, automatically generating migration and type-safe clients
prisma migrate dev for driver development and migration; prisma migrate deploy for production deployment
Prisma Client supports findMany, create, update, delete, and a wide range of filtering, sorting, and pagination options
Database selection: Choose SQLite for desktop/prototype applications; choose MySQL or PostgreSQL for web applications; choose MongoDB for flexible schemas
Choosing an ORM: Use Mongoose with MongoDB, Prisma for full-stack TypeScript, and TypeORM with NestJS

❓ FAQ

Q What is the difference between Prisma and Sequelize?

A Prisma uses a declarative schema to generate type-safe clients, while Sequelize uses decorators or defineModel. Prisma offers better type safety, while Sequelize has a more mature ecosystem.

Q Is SQLite suitable for production environments?

A It is suitable for low-concurrency scenarios (more reads than writes, single-server deployment), such as personal projects, internal tools, and embedded applications. For high-concurrency write operations, consider PostgreSQL or MySQL.

Q What is the difference between Prisma migrate and db push?

A migrate generates migration files, which are suitable for team collaboration and production deployment; db push directly synchronizes the schema to the database, which is suitable for prototyping.

Q How do I switch to PostgreSQL?

A Change the provider in schema.prisma to postgresql, update the DATABASE_URL connection string, and run prisma migrate reset to rebuild the database.

Q How do you solve the N+1 problem in Prisma?

A Use INCLUDE to preload related data, or use SELECT to precisely select fields, avoiding querying related records one by one in a loop.

Q: Does SQLite support concurrency? A: It supports multiple reads and a single write; in WAL mode, reads can be performed concurrently, but writes remain serialized. For high-concurrency write scenarios, you should switch to MySQL or PostgreSQL.
Q: Which is better, Prisma or Sequelize? A: Prisma offers stronger type safety, a better migration experience, and queries free of N+1 issues; Sequelize has a more mature ecosystem and supports more database dialects. We recommend Prisma for new projects.
Q: Why is better-sqlite3 synchronous? A: SQLite itself is an in-process library, and I/O operations take only microseconds; using asynchronous operations would actually increase event loop overhead. The synchronous API eliminates nested callbacks, resulting in cleaner code.
Q: What is the maximum size of an SQLite database? A: The theoretical limit is 281 TB, but in practice, it is subject to the operating system's single-file size limit; in typical scenarios, databases of several dozen GB are no problem at all.
Q: When should you migrate from SQLite to PostgreSQL? A: You should migrate when you need concurrent writes across multiple processes, native JSONB queries, full-text search, geospatial data, row-level security policies, or when you exceed the storage capacity of a single machine.
Q: Does Prisma support MongoDB? A: Version 2.0 and later support MongoDB, but the feature set is not as comprehensive as that for relational databases; for complex aggregations, we recommend using Mongoose.
Q: How should Prisma Client instances be managed in a production environment? A: Use a global singleton to avoid creating a new instance with every request; in hot-reload environments such as Next.js, use globalThis caching to prevent connection leaks.

📖 Summary

1 better-sqlite3: Core Concepts and Usage of the Synchronous API for Embedded Databases
2 Key Concepts and Usage of SQL Statements: A Review of the Basics
3 Key Concepts and Usage of Prisma Installation and Initialization
4 schema.prisma: Defining Core Concepts and Usage of the Model
5 Core Concepts and Usage of Prisma Migrate
6 Core Concepts and Usage of Prisma Client CRUD
7 Core Concepts and Usage of Sorting and Pagination
8 Key Concepts and Methods for Comparing and Selecting Databases

📝 Exercises

Use better-sqlite3 to create a users table, implement insert, query by email, update, and delete operations, and use transactions to ensure atomicity.
Initialize a Prisma project, define two models—User and Post (a one-to-many relationship)—and, after running the migrations, use Prisma Client to create users, publish articles, and query users and all their articles.
Building on the note management example, add the Category model to enable filtering of notes by category, and support filtering by category ID in listNotes.
Compare the implementation of the same set of CRUD operations using better-sqlite3 native SQL and the Prisma Client, noting the differences in line count and readability.
Write a script using prisma.note.findMany to implement keyword search, prioritize top results, and pagination, and compare the performance differences between offset pagination and cursor pagination when processing 10,000 records.

Web-Tutorial Tech Team

A team of developers maintaining programming tutorials. Each tutorial is written and reviewed by developers with expertise in that field. We work to keep our content accurate and reliable — if you spot an issue, please let us know.

About Us

Node.js: SQLite and Prisma ORM

1. better-sqlite3: An Embedded Database with a Synchronous API

▶ Example: Complete CRUD Workflow with better-sqlite3

2. Review of SQL Statement Basics

3. Prisma Installation and Initialization

4. schema.prisma: Defining the Model

▶ Example: Defining Multi-Model Relationships

5. Prisma Migrate Migration

6. Prisma Client CRUD

▶ Example: Including Related Data and Filtering

7. Sorting and Pagination

▶ Example: Search and Cursor Pagination

8. Database Selection Comparison

9. Comprehensive Example: Note Management Data Layer

▶ Example: Prisma + SQLite Note Management

10. Summary of This Lesson

❓ FAQ

📖 Summary

📝 Exercises

🙏 帮我们做得更好