How to Use Drizzle ORM with Capacitor and SQLite

If you're building a Capacitor app that needs a local database, you've probably dealt with writing raw SQL strings and mapping results manually. It works, but it's error-prone and doesn't scale well. Drizzle ORM offers a better approach: a lightweight, type-safe ORM that lets you define your schema in TypeScript and write queries that feel like SQL — with full autocompletion and compile-time checks. In this guide, you'll learn how to set up Drizzle ORM with the SQLite plugin using the new @capawesome/capacitor-sqlite-drizzle adapter.

What is Drizzle ORM?

Drizzle ORM is a TypeScript ORM designed to be lightweight, type-safe, and close to SQL. Unlike heavier ORMs that abstract SQL away entirely, Drizzle uses a query API that mirrors SQL syntax. If you know SQL, you already know most of Drizzle.

Here's what makes it a good fit for Capacitor apps:

• Type safety — Your schema is defined in TypeScript, so queries are checked at compile time. No more runtime surprises from mistyped column names.
• No code generation — Unlike some ORMs, Drizzle doesn't require a separate code generation step. Your schema files are regular TypeScript.
• SQL-like syntax — Queries read like SQL, which makes them easy to understand and debug.
• Built-in migrations — Drizzle Kit can generate and manage SQL migrations from your schema changes automatically.
• Lightweight — Drizzle has zero runtime dependencies and a small bundle size, which matters for mobile apps.

Prerequisites

Before you begin, make sure you have a Capacitor project with the SQLite plugin installed. To install the plugin, please refer to the Installation section in the plugin documentation.

Installation

Install the Drizzle adapter along with Drizzle ORM:

npm install @capawesome/capacitor-sqlite-drizzle drizzle-orm

You'll also want to install Drizzle Kit as a dev dependency for schema migrations:

npm install -D drizzle-kit

Setting Up the Database

To get started, open a database using the SQLite plugin and pass it to the drizzle() function:

import { Sqlite } from '@capawesome-team/capacitor-sqlite';
import { drizzle } from '@capawesome/capacitor-sqlite-drizzle';

const { databaseId } = await Sqlite.open({ path: 'my.db' });
const db = drizzle(Sqlite, { databaseId });

The drizzle() function accepts the Sqlite plugin instance and a configuration object. The databaseId is the unique identifier returned by open(...) and is required to route queries to the correct database.

You can also pass additional Drizzle configuration options like schema and logger:

import * as schema from './schema';

const db = drizzle(Sqlite, { databaseId, schema, logger: true });

Passing schema enables Drizzle's relational query API (more on that later), and logger: true logs all executed SQL statements to the console — useful during development.

Defining a Schema

Drizzle uses a schema-as-code approach. You define your tables as TypeScript objects, which Drizzle uses for type inference and query building. Create a schema.ts file in your project:

import { integer, sqliteTable, text } from 'drizzle-orm/sqlite-core';

export const users = sqliteTable('users', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  name: text('name').notNull(),
  email: text('email').notNull().unique(),
  createdAt: integer('created_at', { mode: 'timestamp' })
    .notNull()
    .$defaultFn(() => new Date()),
});

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

A few things to note here:

• sqliteTable defines a table with its columns and constraints.
• Column types like integer and text map directly to SQLite types.
• The mode: 'timestamp' option on createdAt tells Drizzle to automatically convert between JavaScript Date objects and integer timestamps.
• references(() => users.id) creates a foreign key constraint linking posts.authorId to users.id.
• $defaultFn sets a default value at the application level, not in the database.

This schema definition serves as the single source of truth for both your TypeScript types and your database structure.

Running Queries

With the schema in place, you can run type-safe CRUD operations. All queries return promises and use the familiar SQL patterns.

Insert

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

Select

import { eq } from 'drizzle-orm';

// Select all users
const allUsers = await db.select().from(users);

// Select with a filter
const user = await db
  .select()
  .from(users)
  .where(eq(users.email, 'alice@example.com'));

Update

await db
  .update(users)
  .set({ name: 'Bob' })
  .where(eq(users.id, 1));

Delete

await db.delete(users).where(eq(users.id, 1));

Every query is fully typed. The allUsers variable, for example, is automatically inferred as an array of objects matching the users table schema — no manual type annotations needed.

Relational Queries

When you pass a schema to the drizzle() function, you unlock Drizzle's relational query API. This lets you load related data in a single query without writing manual joins:

const usersWithPosts = await db.query.users.findMany({
  with: { posts: true },
});

This returns all users along with their associated posts, based on the foreign key relationship defined in the schema. The result is fully typed and nested — each user object includes a posts array.

You can also use findFirst to retrieve a single record:

const user = await db.query.users.findFirst({
  where: eq(users.id, 1),
  with: { posts: true },
});

Transactions

For operations that need to succeed or fail together, use transactions. Drizzle sends BEGIN, COMMIT, and ROLLBACK statements automatically:

await db.transaction(async (tx) => {
  const [user] = await tx
    .insert(users)
    .values({ name: 'Alice', email: 'alice@example.com' })
    .returning();
  await tx
    .insert(posts)
    .values({ title: 'Hello World', content: '...', authorId: user.id });
});

If any statement inside the callback throws an error, the entire transaction is rolled back. This is essential for maintaining data integrity, especially when inserting related records across multiple tables.

Migrations

Manually managing database schema changes with raw SQL is tedious and error-prone. Drizzle Kit solves this by generating SQL migration files from your schema changes. The adapter provides a migrate() function to apply these migrations at runtime.

1. Configure Drizzle Kit

Create a drizzle.config.ts file in your project root:

import { defineConfig } from 'drizzle-kit';

export default defineConfig({
  schema: './src/schema.ts',
  out: './src/drizzle',
  dialect: 'sqlite',
  driver: 'expo',
});

Note

The driver: 'expo' setting is required. It tells Drizzle Kit to generate a bundled migrations.js file that works in non-Node environments like Capacitor.

2. Generate Migrations

Whenever you change your schema, run the following command to generate migration files:

npx drizzle-kit generate

This creates SQL migration files and a migrations.js bundle in the output directory (e.g. ./src/drizzle/).

Note

The generated migrations.js file imports .sql files as strings. Depending on your bundler, you may need a plugin to handle this. For Vite-based projects, use vite-plugin-plain-text. For Babel-based setups, use babel-plugin-inline-import.

3. Apply Migrations

Import the generated migrations and apply them when your app starts:

import { Sqlite } from '@capawesome-team/capacitor-sqlite';
import { drizzle, migrate } from '@capawesome/capacitor-sqlite-drizzle';
import migrations from './drizzle/migrations';

const { databaseId } = await Sqlite.open({ path: 'my.db' });
const db = drizzle(Sqlite, { databaseId });

await migrate(Sqlite, databaseId, migrations);

Each migration runs inside its own transaction. The adapter automatically creates a __drizzle_migrations table to track which migrations have already been applied, so calling migrate() multiple times is safe — only pending migrations are executed.

Stay Updated

Want to stay up to date with the latest features and guides? Subscribe to the Capawesome newsletter.

Subscribe to the Capawesome Newsletter

Conclusion

With the @capawesome/capacitor-sqlite-drizzle adapter, you can use Drizzle ORM's type-safe queries, schema-as-code approach, and automated migrations in your Capacitor apps. The setup is straightforward: define your schema in TypeScript, generate migrations with Drizzle Kit, and run queries using a familiar SQL-like API — all without sacrificing type safety.

For the full API reference and source code, check out the adapter on GitHub. If you have questions or feedback, join the Capawesome Discord server to connect with the community. And subscribe to the Capawesome newsletter to stay updated on the latest news.