Run and inspect the first query

Before creating any table, let’s prove the whole path works: TypeScript file, Drizzle, the SQLite driver, the environment, the database file. One tiny query is enough.

Drizzle has a sql template tag for writing SQL by hand. On SQLite, you run it with db.get() for one row or db.all() for many rows. Let’s ask SQLite for its version:

import { sql } from 'drizzle-orm'
import { db } from './db'

const result = db.get(sql`select sqlite_version() as version`)
console.log(result)

Run it with npx tsx src/index.ts and you get something like:

{ version: '3.53.1' }

If this prints, every layer agrees. If it fails, debug in this order: the environment variable, the database file, the driver import, then the query. Don’t touch schema code that hasn’t run yet.

The template is not string concatenation

This is the part that matters for security. Values you interpolate into sql do not become part of the SQL text. They become bound parameters, sent to SQLite separately from the statement.

Try it with a value that contains a quote:

const name = "O'Reilly"
console.log(db.get(sql`select ${name} as value`))

{ value: "O'Reilly" }

The quote came back as data. It never had a chance to break the statement. With string concatenation, that same quote would have produced a syntax error at best and an injection at worst.

Look at the SQL Drizzle writes

The query builder can show you its output without running it. Every query has a .toSQL() method:

const query = db
  .select({ id: users.id })
  .from(users)
  .where(eq(users.email, '[email protected]'))

console.log(query.toSQL())

{
  sql: 'select "id" from "users" where "users"."email" = ?',
  params: [ '[email protected]' ]
}

We haven’t defined users yet, so keep this for the next module. But remember it exists. When a filter returns surprising rows, I print toSQL() first. Nine times out of ten the SQL tells me what I got wrong.

When to write raw SQL

Raw SQL is the right tool for database-specific features and diagnostics: pragma statements, explain query plan, a version check like this one. Keep it narrow, bind every value, and go back to the query builder for normal application queries. The builder’s types catch the mistakes that raw strings let through.