Open SQLite with Drizzle

The database client needs one obvious home. Every query file imports it from there. Connection setup lives in one place, and nothing else needs to know the filename.

Create src/db/index.ts. It loads the environment, refuses to start without a filename, and opens the database:

import 'dotenv/config'
import { drizzle } from 'drizzle-orm/node-sqlite'

if (!process.env.DB_FILE_NAME) {
  throw new Error('DB_FILE_NAME is required')
}

export const db = drizzle(process.env.DB_FILE_NAME)

drizzle-orm/node-sqlite is the driver for Node’s built-in SQLite. Passing a string opens (or creates) that file. The db object it returns is what we use for the whole course: it builds queries and sends them to SQLite.

Fail early, not late

Notice the check before drizzle(). Without it, a missing variable would pass undefined to the driver, and you’d get a confusing error from deep inside SQLite, or a database file with a strange name. A clear message at startup is worth three lines.

The official docs write process.env.DB_FILE_NAME! instead. The ! tells TypeScript to trust that the value exists. That’s fine for a quick example. I prefer the explicit check in a real project, because the environment is exactly the thing that differs between your laptop and the server.

Run it

Add a first line to src/index.ts and run it:

import { db } from './db'

console.log('database opened')

npx tsx src/index.ts

You see database opened, and a notes.sqlite file appears in the project root. Empty for now, but real.

Now try it without the variable:

DB_FILE_NAME= npx tsx src/index.ts

The script stops with Error: DB_FILE_NAME is required. That’s the failure we designed. Check git status too: notes.sqlite must not show up, because we ignored it in the previous lesson.

Two things to keep in mind

A local file is convenient, but it is still shared state. Two tests that use the same file can affect each other. Later in the course we create a fresh temporary database for each test, and we keep connection choices out of the query code so that swap is painless.

And never build the filename from a request value. The environment decides where the database lives. Users only get to influence the data that goes into queries, and we bind that as parameters. Infrastructure and data are different inputs with different trust levels.