Drizzle ORM

Drizzle ORM natively supports libSQL driver, we embrace SQL dialects and dialect specific drivers and syntax and mirror most popular SQLite-like all, get, values and run query methods syntax.

npm

yarn

pnpm

bun

npm i drizzle-orm @libsql/client
npm i -D drizzle-kit

yarn add drizzle-orm @libsql/client
yarn add -D drizzle-kit

pnpm add drizzle-orm @libsql/client
pnpm add -D drizzle-kit

bun add drizzle-orm @libsql/client
bun add -D drizzle-kit

import { drizzle } from 'drizzle-orm/libsql';
import { createClient } from '@libsql/client';

const client = createClient({ url: 'DATABASE_URL', authToken: 'DATABASE_AUTH_TOKEN' });

const db = drizzle(client);

const result = await db.select().from(users).all()

Unless you plan on writing every SQL query by hand, a table declaration is helpful:

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

const users = sqliteTable('users', {
  id: text('id'),
  textModifiers: text('text_modifiers').notNull().default(sql`CURRENT_TIMESTAMP`),
  intModifiers: integer('int_modifiers', { mode: 'boolean' }).notNull().default(false),
});

For more details about column types, see the SQLite column types in Drizzle.

Cloudflare D1

According to the official website, D1 is Cloudflare’s first queryable relational database.

Drizzle ORM fully supports the Cloudflare D1 database and Cloudflare Workers environment. We embrace SQL dialects and dialect specific drivers and syntax and mirror most popular SQLite-like all, get, values and run query methods syntax.

To setup project for your Cloudflare D1 please refer to official docs.

### your wrangler.toml will look something like this:

name = "YOUR PROJECT NAME"
main = "src/index.ts"
compatibility_date = "2022-11-07"
node_compat = true

[[ d1_databases ]]
binding = "DB"
database_name = "YOUR DB NAME"
database_id = "YOUR DB ID"

Initialize local database and run server locally:

wrangler d1 execute <DATABASE_NAME> --local --file=./drizzle/0000_short_lockheed.sql
wrangler dev ## on wrangler versions below 3.0.0, add the --local and --persist flags

Install Drizzle ORM:

npm

yarn

pnpm

bun

npm i drizzle-orm
npm i -D drizzle-kit

yarn add drizzle-orm
yarn add -D drizzle-kit

pnpm add drizzle-orm
pnpm add -D drizzle-kit

bun add drizzle-orm
bun add -D drizzle-kit

Make your first D1 query:

import { drizzle } from 'drizzle-orm/d1';

export interface Env {
  <BINDING_NAME>: D1Database;
}

export default {
  async fetch(request: Request, env: Env) {
    const db = drizzle(env.<BINDING_NAME>);
    const result = await db.select().from(users).all()
    return Response.json(result);
  },
};

Unless you plan on writing every SQL query by hand, a table declaration is helpful:

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

const users = sqliteTable('users', {
  id: text('id'),
  textModifiers: text('text_modifiers').notNull().default(sql`CURRENT_TIMESTAMP`),
  intModifiers: integer('int_modifiers', { mode: 'boolean' }).notNull().default(false),
});

For more details about column types, see the SQLite column types in Drizzle.

Bun SQLite

According to the official website, Bun is a fast all-in-one JavaScript runtime.

Drizzle ORM natively supports bun:sqlite module and it’s crazy fast 🚀

We embraces SQL dialects and dialect specific drivers and syntax and unlike any other ORM, for synchronous drivers like bun:sqlite we have both async and sync APIs and we mirror most popular SQLite-like all, get, values and run query methods syntax.

npm

yarn

pnpm

bun

npm i drizzle-orm
npm i -D drizzle-kit

yarn add drizzle-orm
yarn add -D drizzle-kit

pnpm add drizzle-orm
pnpm add -D drizzle-kit

bun add drizzle-orm
bun add -D drizzle-kit

import { drizzle } from 'drizzle-orm/bun-sqlite';
import { Database } from 'bun:sqlite';

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

const result = await db.select().from(users);

Unless you plan on writing every SQL query by hand, a table declaration is helpful:

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

const users = sqliteTable('users', {
  id: text('id'),
  textModifiers: text('text_modifiers').notNull().default(sql`CURRENT_TIMESTAMP`),
  intModifiers: integer('int_modifiers', { mode: 'boolean' }).notNull().default(false),
});

For more details about column types, see the SQLite column types in Drizzle.

If you want to use sync APIs:

import { drizzle } from 'drizzle-orm/bun-sqlite';
import { Database } from 'bun:sqlite';

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

const result = db.select().from(users).all();
const result = db.select().from(users).get();
const result = db.select().from(users).values();
const result = db.select().from(users).run();

More on sync and async APIs for sqlite - read here.

Expo SQLite

According to the official website, Expo is an ecosystem of tools to develop, build and ship applications on React Native. It’s powered by Hermes JavaScript runtime and Metro bundler, Drizzle Expo driver is built to natively support both.

npm

yarn

pnpm

bun

npm i drizzle-orm expo-sqlite@next
npm i -D drizzle-kit

yarn add drizzle-orm expo-sqlite@next
yarn add -D drizzle-kit

pnpm add drizzle-orm expo-sqlite@next
pnpm add -D drizzle-kit

bun add drizzle-orm expo-sqlite@next
bun add -D drizzle-kit

import { drizzle } from "drizzle-orm/expo-sqlite";
import { openDatabaseSync } from "expo-sqlite/next";

const expo = openDatabaseSync("db.db");
const db = drizzle(expo);

await db.select().from(users);

You can use Drizzle Kit for SQL migration generation.
Please make sure to check how Drizzle Kit migrations work before proceeding.
Expo / React Native requires you to have SQL migrations bundled into the app and we’ve got you covered.

Install babel plugin

It’s necessary to bundle SQL migration files as string directly to your bundle.

npm install babel-plugin-inline-import

Update config files.

You will need to update babel.config.js, metro.config.js and drizzle.config.ts files

module.exports = function(api) {
  api.cache(true);

  return {
    presets: ['babel-preset-expo'],
    plugins: [["inline-import", { "extensions": [".sql"] }]] // <-- add this
  };
};

const { getDefaultConfig } = require('expo/metro-config');

/** @type {import('expo/metro-config').MetroConfig} */
const config = getDefaultConfig(__dirname);

config.resolver.sourceExts.push('sql'); // <--- add this

module.exports = config;

Make sure to have driver: 'expo' in Drizzle Kit config

import type { Config } from 'drizzle-kit';

export default {
  schema: './db/schema.ts',
  out: './drizzle',
  driver: 'expo', // <--- very important
} satisfies Config;

Generate migrations

After creating SQL schema file and drizzle.config.ts file, you can generate migrations

npx drizzle-kit generate:sqlite

Add migrations to your app

Now you need to import migrations.js file into your Expo/React Native app from ./drizzle folder. You can run migrations on application startup using our custom useMigrations migrations hook on in useEffect hook manually as you want.

import { drizzle } from "drizzle-orm/expo-sqlite";
import { openDatabaseSync } from "expo-sqlite/next";
import { useMigrations } from 'drizzle-orm/expo-sqlite/migrator';
import migrations from './drizzle/migrations';

const expoDb = openDatabaseSync("db.db");

const db = drizzle(expoDb);

export default function App() {
  const { success, error } = useMigrations(db, migrations);

  if (error) {
    return (
      <View>
        <Text>Migration error: {error.message}</Text>
      </View>
    );
  }

  if (!success) {
    return (
      <View>
        <Text>Migration is in progress...</Text>
      </View>
    );
  }

  return ...your application component;
}

React Native SQLite

Please use Expo SQLite to run Drizzle ORM with React Native apps.
The only popular library we’ve found does not support new Hermes JavaScript runtime, which is a standard out of the box runtime for React Native and Expo now.

better-sqlite3

According to the official docs, BetterSqlite3 is the fastest and simplest library for SQLite3 in Node.js.

Drizzle ORM embraces SQL dialects and dialect specific drivers and syntax and unlike any other ORM, for synchronous drivers like better-sqlite3 both async and sync APIs and we mirror most popular SQLite-like all, get, values and run query methods syntax.

npm

yarn

pnpm

bun

npm i drizzle-orm better-sqlite3
npm i -D drizzle-kit

yarn add drizzle-orm better-sqlite3
yarn add -D drizzle-kit

pnpm add drizzle-orm better-sqlite3
pnpm add -D drizzle-kit

bun add drizzle-orm better-sqlite3
bun add -D drizzle-kit

import { drizzle } from 'drizzle-orm/better-sqlite3';
import Database from 'better-sqlite3';

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

const result = await db.select().from(users);

Unless you plan on writing every SQL query by hand, a table declaration is helpful:

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

const users = sqliteTable('users', {
  id: text('id'),
  textModifiers: text('text_modifiers').notNull().default(sql`CURRENT_TIMESTAMP`),
  intModifiers: integer('int_modifiers', { mode: 'boolean' }).notNull().default(false),
});

For more details about column types, see the SQLite column types in Drizzle.

ℹ️

If your db.insert() returns data, append all() to your query, otherwise append run()

If you want to use sync APIs:

import { drizzle, BetterSQLite3Database } from 'drizzle-orm/better-sqlite3';
import Database from 'better-sqlite3';

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

const result = db.select().from(users).all();
const result = db.select().from(users).get();
const result = db.select().from(users).values();
const result = db.select().from(users).run();

More on sync and async APIs for sqlite - read here.

HTTP proxy

Drizzle ORM also supports simply using asynchronous callback function for executing SQL.

sql is a query string with placeholders.
params is an array of parameters.
One of the following values will set for method depending on the SQL statement - run, all, values or get.

Drizzle always waits for {rows: string[][]} or {rows: string[]} for the return value.

When the method is get, you should return a value as {rows: string[]}.
Otherwise, you should return {rows: string[][]}.

import { drizzle } from 'drizzle-orm/sqlite-proxy';

const db = drizzle(async (sql, params, method) => {
  try {
    const rows = await axios.post('http://localhost:3000/query', { sql, params, method });

    return { rows: rows.data };
  } catch (e: any) {
    console.error('Error from sqlite proxy server: ', e.response.data)
    return { rows: [] };
  }
});

Unless you plan on writing every SQL query by hand, a table declaration is helpful:

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

const users = sqliteTable('users', {
  id: text('id'),
  textModifiers: text('text_modifiers').notNull().default(sql`CURRENT_TIMESTAMP`),
  intModifiers: integer('int_modifiers', { mode: 'boolean' }).notNull().default(false),
});

For more details about column types, see the SQLite column types in Drizzle.

Learn

Drizzle Stories

Become a Gold Sponsor