Drizzle ORM with SQLite

Type-safe queries, schema push, migrations, and joins

What You’ll Build

After following this guide, you will have a working implementation of drizzle orm with sqlite in your project. Use Drizzle ORM with SQLite for a lightweight, type-safe database layer. Drizzle has zero dependencies, SQL-like syntax, and excellent TypeScript inference. Perfect for local-first apps, CLI tools, and small-to-medium projects.

Use Cases & Problems Solved

• Set up a production-ready database layer with type-safe queries
• Manage schema changes through migrations without data loss
• Avoid raw SQL injection risks and inconsistent data access patterns

Prerequisites

• Node.js 18+
• TypeScript project

Step-by-Step Implementation

Install Drizzle with SQLite

The following snippet shows how to install drizzle with sqlite. Copy this into your project and adjust the values for your environment.

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

Define schema

The following snippet shows how to define schema. Copy this into your project and adjust the values for your environment.

// src/db/schema.ts
import { sqliteTable, text, integer } from 'drizzle-orm/sqlite-core';

export const users = sqliteTable('users', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  name: text('name').notNull(),
  email: text('email').unique().notNull(),
});

export const posts = sqliteTable('posts', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  title: text('title').notNull(),
  userId: integer('user_id').references(() => users.id),
});

Initialize and query

The following snippet shows how to initialize and query. Copy this into your project and adjust the values for your environment.

// src/db/index.ts
import { drizzle } from 'drizzle-orm/better-sqlite3';
import Database from 'better-sqlite3';
import { users, posts } from './schema';
import { eq } from 'drizzle-orm';

const sqlite = new Database('app.db');
const db = drizzle(sqlite);

// Insert
await db.insert(users).values({ name: 'Alice', email: 'alice@example.com' });

// Select with join
const result = await db.select()
  .from(posts)
  .leftJoin(users, eq(posts.userId, users.id));

⚠️ Don’t Do This

// SQL injection vulnerability!
const name = req.query.name;
db.run(`SELECT * FROM users WHERE name = '${name}'`);

import { eq } from 'drizzle-orm';
const result = await db.select().from(users).where(eq(users.name, name));
// Generates: SELECT * FROM users WHERE name = ? (parameterized)

Testing

Verify your implementation with these tests:

// __tests__/drizzle-orm-with-sqlite.test.ts
import { describe, it, expect } from 'vitest';

describe('Drizzle ORM with SQLite', () => {
  it('should initialize without errors', () => {
    // Test that the setup completes successfully
    expect(() => setup()).not.toThrow();
  });

  it('should handle the primary use case', async () => {
    const result = await execute();
    expect(result).toBeDefined();
    expect(result.success).toBe(true);
  });

  it('should handle edge cases', async () => {
    // Test with empty/null input
    const result = await execute(null);
    expect(result.error).toBeDefined();
  });
});

Verification

npx drizzle-kit push
# Run your app and check app.db exists
# Use: npx drizzle-kit studio  (opens visual DB browser)