← Back to the Build Your Homepage series

💾

EPISODE 04

better-sqlite3 · prepared statements · transactions · schema · migrate · client · type-safe queries · documents · collections · find · aggregation · Atlas

Node + SQLite · Prisma ORM · MongoDB

Use SQLite from Node with better-sqlite3 — a fast, synchronous, zero-config SQL database. Cover prepared statements and transactions. Stop writing raw SQL strings. Prisma gives you a schema file, automatic migrations, and a fully typed client — works with SQLite, PostgreSQL, and more. Try a NoSQL approach with MongoDB: schemaless documents, flexible queries, aggregation pipelines, and managed hosting on Atlas.

SQLitebetter-sqlite3prepared statementtransactionPrismaORMmigrationtype-safeMongoDBNoSQLAtlasaggregation

Duration

⏱ About 2 hours + About 2.5 hours + About 2.5 hours

Level

📊 Intermediate

Prerequisite

🎯 db-03 + node-04

OUTCOME

Wire an Express API to SQLite with safe prepared statements / A type-safe data layer with auto-generated client and migrations / A working Node app talking to a MongoDB Atlas cluster

What you'll learn

1Install and initialize better-sqlite3
2Run prepared statements to prevent SQL injection
3Group writes in a transaction for atomicity
4Migrate the JSON-based REST API to SQLite
5Define a Prisma schema
6Run prisma migrate to evolve the database
7Use the auto-generated PrismaClient with full typing
8Model 1-to-many and many-to-many relations
9Sign up for MongoDB Atlas (free tier)
10Connect from Node with the official driver
11Query with find, findOne, insertOne, updateOne, deleteOne
12Use the aggregation pipeline for grouping and joining

Node + SQLite

Use SQLite from Node with better-sqlite3 — a fast, synchronous, zero-config SQL database. Cover prepared statements and transactions.

1. Setup

bash

npm install better-sqlite3

javascript

import Database from "better-sqlite3";
const db = new Database("app.db");

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

2. Prepared Statements

javascript

// Insert
const insert = db.prepare("INSERT INTO tasks (title) VALUES (?)");
const { lastInsertRowid } = insert.run("Buy milk");

// Query
const all = db.prepare("SELECT * FROM tasks ORDER BY id");
const tasks = all.all();

const byId = db.prepare("SELECT * FROM tasks WHERE id = ?");
const task = byId.get(1);

⚠️

Always use placeholders (?). String concatenation in SQL is the path to SQL injection.

3. Transactions

javascript

const transfer = db.transaction((fromId, toId, amount) => {
  db.prepare("UPDATE accounts SET balance = balance - ? WHERE id = ?").run(amount, fromId);
  db.prepare("UPDATE accounts SET balance = balance + ? WHERE id = ?").run(amount, toId);
});

transfer(1, 2, 100);   // both UPDATEs succeed or both roll back

4. Wire to Express

javascript

app.get("/tasks", (req, res) => {
  res.json(all.all());
});

app.post("/tasks", (req, res) => {
  const info = insert.run(req.body.title);
  res.status(201).json(byId.get(info.lastInsertRowid));
});

Prisma ORM

Stop writing raw SQL strings. Prisma gives you a schema file, automatic migrations, and a fully typed client — works with SQLite, PostgreSQL, and more.

1. Setup

bash

npm install prisma @prisma/client
npx prisma init --datasource-provider sqlite

prisma

// prisma/schema.prisma
datasource db {
  provider = "sqlite"
  url      = env("DATABASE_URL")
}

generator client {
  provider = "prisma-client-js"
}

model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String?
  posts     Post[]
  createdAt DateTime @default(now())
}

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String?
  published Boolean  @default(false)
  authorId  Int
  author    User     @relation(fields: [authorId], references: [id])
}

2. Migrate

bash

npx prisma migrate dev --name init
# Generates SQL, applies it, regenerates the client

3. Use the Client

javascript

import { PrismaClient } from "@prisma/client";
const prisma = new PrismaClient();

// Create
const user = await prisma.user.create({
  data: {
    email: "alice@example.com",
    name: "Alice",
    posts: { create: { title: "Hello" } },
  },
  include: { posts: true },
});

// Read
const users = await prisma.user.findMany({
  where: { posts: { some: { published: true } } },
  include: { posts: true },
});

// Update
await prisma.post.update({ where: { id: 1 }, data: { published: true } });

// Delete
await prisma.user.delete({ where: { id: 1 } });

💡

Every query result is fully typed — if you ask for { posts: true }, TypeScript knows the result includes a posts array.

4. Prisma Studio

bash

npx prisma studio   # GUI to browse and edit your data

MongoDB

Try a NoSQL approach with MongoDB: schemaless documents, flexible queries, aggregation pipelines, and managed hosting on Atlas.

1. Document Model

json

// A single document
{
  "_id": "658a...",
  "name": "Alice",
  "email": "alice@example.com",
  "tags": ["admin", "editor"],
  "profile": { "city": "Seoul" }
}

No fixed schema — but applying one in code (with Mongoose, Zod, etc.) saves you from data drift.

2. Connect from Node

bash

npm install mongodb

javascript

import { MongoClient } from "mongodb";

const client = new MongoClient(process.env.MONGO_URL);
await client.connect();
const db = client.db("myapp");
const users = db.collection("users");

3. CRUD

javascript

// Insert
await users.insertOne({ name: "Alice", email: "a@example.com" });

// Read
const one = await users.findOne({ email: "a@example.com" });
const many = await users.find({ "profile.city": "Seoul" }).limit(10).toArray();

// Update
await users.updateOne({ _id: id }, { $set: { name: "Alicia" } });
await users.updateMany({ active: true }, { $inc: { loginCount: 1 } });

// Delete
await users.deleteOne({ _id: id });

4. Aggregation Pipeline

javascript

const result = await db.collection("orders").aggregate([
  { $match: { status: "paid" } },
  { $group: { _id: "$customerId", total: { $sum: "$amount" } } },
  { $sort:  { total: -1 } },
  { $limit: 10 },
]).toArray();

Pipelines feed documents through stages — match, group, sort, project, lookup (the NoSQL equivalent of JOIN).

Example code / lecture materials

All lecture materials and example code are openly available on GitHub.

View on GitHub ↗

Comments 0

No comments yet. Be the first.

Only members can comment.

Comments with ads, abuse, personal information or illegal content are hidden or removed. Comments are shown with your nickname. Privacy Policy