Connecting a database

So far the posts lived in an array and disappeared when the server restarted. Let us store them in an SQLite database — a single file on disk, no separate server needed. We will work with it through Drizzle ORM: a library where tables are described in TypeScript and queries are written with methods instead of SQL strings.

Installing

npm install drizzle-orm better-sqlite3
npm install -D drizzle-kit @types/better-sqlite3

better-sqlite3 is the SQLite driver for Node.js, drizzle-kit is a command-line tool that creates tables from the schema.

The schema

Create db/schema.ts in the project root:

// db/schema.ts
import { integer, sqliteTable, text } from 'drizzle-orm/sqlite-core';
 
export const posts = sqliteTable('posts', {
  slug: text('slug').primaryKey(),
  title: text('title').notNull(),
  body: text('body').notNull(),
});
 
export const comments = sqliteTable('comments', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  postSlug: text('post_slug').notNull().references(() => posts.slug),
  author: text('author').notNull(),
  text: text('text').notNull(),
});

Each sqliteTable call describes a table: its name in the database and its columns with types.

Creating the tables

The tool needs a drizzle.config.ts settings file in the root:

// drizzle.config.ts
import { defineConfig } from 'drizzle-kit';
 
export default defineConfig({
  dialect: 'sqlite',
  schema: './db/schema.ts',
  dbCredentials: { url: 'blog.db' },
});

npx drizzle-kit push

The command creates a blog.db file with the posts and comments tables. You can view and fill the tables in the browser with npx drizzle-kit studio.

Connecting

// db/index.ts
import Database from 'better-sqlite3';
import { drizzle } from 'drizzle-orm/better-sqlite3';
 
export const db = drizzle({ client: new Database('blog.db') });

The db object is imported wherever queries are needed.

Queries instead of an array

Rewrite lib/posts.ts. Pages and actions call the same functions, so they do not need changes:

// lib/posts.ts
import { eq } from 'drizzle-orm';
import { db } from '@/db';
import { posts } from '@/db/schema';
 
export type Post = typeof posts.$inferSelect;
 
export async function getPosts(): Promise<Post[]> {
  return db.select().from(posts);
}
 
export async function getPost(slug: string): Promise<Post | undefined> {
  const [post] = await db.select().from(posts).where(eq(posts.slug, slug));
  return post;
}
 
export async function addPost(post: Post): Promise<void> {
  await db.insert(posts).values(post);
}

The Post type is now inferred from the schema. Add a post through the /posts/new form and restart npm run dev — the post stays. Rewrite lib/comments.ts for the comments table the same way.

Basic queries

Task	Code
All rows	`db.select().from(posts)`
With a condition	`.where(eq(posts.slug, slug))`
Insert	`db.insert(posts).values(post)`
Update	`db.update(posts).set(data).where(...)`
Delete	`db.delete(posts).where(...)`

For PostgreSQL only the driver, the dialect and the pgTable function instead of sqliteTable change.

Common mistake

Changing the schema and forgetting to apply it to the database. A query to the new column fails with "no such column". After every edit of db/schema.ts run npx drizzle-kit push, and add the blog.db file to .gitignore.

Thanks for reading!