Drizzle

npm: @powersync/drizzle-driver

This package enables using Drizzle with the PowerSync React Native and JavaScript Web SDKs.

Setup

Set up the PowerSync Database and wrap it with Drizzle.

import { wrapPowerSyncWithDrizzle } from '@powersync/drizzle-driver';
import { PowerSyncDatabase } from '@powersync/web';
import { relations } from 'drizzle-orm';
import { index, integer, sqliteTable, text } from 'drizzle-orm/sqlite-core';
import { AppSchema } from './schema';

export const lists = sqliteTable('lists', {
  id: text('id'),
  name: text('name')
});

export const todos = sqliteTable('todos', {
  id: text('id'),
  description: text('description'),
  list_id: text('list_id'),
  created_at: text('created_at')
});

export const listsRelations = relations(lists, ({ one, many }) => ({
  todos: many(todos)
}));

export const todosRelations = relations(todos, ({ one, many }) => ({
  list: one(lists, {
    fields: [todos.list_id],
    references: [lists.id]
  })
}));

export const drizzleSchema = {
  lists,
  todos,
  listsRelations,
  todosRelations
};

// As an alternative to manually defining a PowerSync schema, generate the local PowerSync schema from the Drizzle schema with the `DrizzleAppSchema` constructor:
// import { DrizzleAppSchema } from '@powersync/drizzle-driver';
// export const AppSchema = new DrizzleAppSchema(drizzleSchema);
//
// This is optional, but recommended, since you will only need to maintain one schema on the client-side
// Read on to learn more.

export const powerSyncDb = new PowerSyncDatabase({
  database: {
    dbFilename: 'test.sqlite'
  },
  schema: AppSchema
});

// This is the DB you will use in queries
export const db = wrapPowerSyncWithDrizzle(powerSyncDb, {
  schema: drizzleSchema
});

Schema Conversion

The DrizzleAppSchema constructor simplifies the process of integrating Drizzle with PowerSync. It infers the client-side PowerSync schema from your Drizzle schema definition, providing a unified development experience. As the PowerSync schema only supports SQLite types (text, integer, and real), the same limitation extends to the Drizzle table definitions. To use it, define your Drizzle tables and supply the schema to the DrizzleAppSchema function:

import { DrizzleAppSchema } from '@powersync/drizzle-driver';
import { sqliteTable, text } from 'drizzle-orm/sqlite-core';

// Define a Drizzle table
const lists = sqliteTable('lists', {
  id: text('id').primaryKey().notNull(),
  created_at: text('created_at'),
  name: text('name').notNull(),
  owner_id: text('owner_id')
});

export const drizzleSchema = {
  lists
};

// Infer the PowerSync schema from your Drizzle schema
export const AppSchema = new DrizzleAppSchema(drizzleSchema);

Defining PowerSync Options

The PowerSync table definition allows additional options supported by PowerSync’s app schema beyond that which are supported by Drizzle. They can be specified as follows. Note that these options exclude indexes as they can be specified in a Drizzle table.

import { DrizzleAppSchema } from '@powersync/drizzle-driver';
// import { DrizzleAppSchema, type DrizzleTableWithPowerSyncOptions} from '@powersync/drizzle-driver'; for TypeScript

const listsWithOptions = { tableDefinition: logs, options: { localOnly: true } };
// const listsWithOptions: DrizzleTableWithPowerSyncOptions = { tableDefinition: logs, options: { localOnly: true } }; for TypeScript

export const drizzleSchemaWithOptions = {
  lists: listsWithOptions
};

export const AppSchema = new DrizzleAppSchema(drizzleSchemaWithOptions);

Converting a Single Table From Drizzle to PowerSync

Drizzle tables can also be converted on a table-by-table basis with toPowerSyncTable.

import { toPowerSyncTable } from '@powersync/drizzle-driver';
import { Schema } from '@powersync/web';
import { sqliteTable, text } from 'drizzle-orm/sqlite-core';

// Define a Drizzle table
const lists = sqliteTable('lists', {
  id: text('id').primaryKey().notNull(),
  created_at: text('created_at'),
  name: text('name').notNull(),
  owner_id: text('owner_id')
});

const psLists = toPowerSyncTable(lists); // converts the Drizzle table to a PowerSync table
// toPowerSyncTable(lists, { localOnly: true }); - allows for PowerSync table configuration

export const AppSchema = new Schema({
  lists: psLists // names the table `lists` in the PowerSync schema
});

Compilable Queries

To use Drizzle queries in your hooks and composables, they currently need to be converted using toCompilableQuery.

import { toCompilableQuery } from "@powersync/drizzle-driver";

const query = db.select().from(users);
const { data: listRecords, isLoading } = useQuery(toCompilableQuery(query));

Usage Examples

Below are examples comparing Drizzle and PowerSync syntax for common database operations.

Select Operations

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

// [{ id: '1', name: 'user1' }, { id: '2', name: 'user2' }]

const result = await powerSyncDb.getAll('SELECT * from users');

// [{ id: '1', name: 'user1' }, { id: '2', name: 'user2' }]

Insert Operations

await db.insert(users).values({ id: '1', name: 'John' });
const result = await db.select().from(users);

// [{ id: '1', name: 'John' }]

await powerSyncDb.execute('INSERT INTO users (id, name) VALUES(1, ?)', ['John']);
const result = await powerSyncDb.getAll('SELECT * from users');

// [{ id: '1', name: 'John' }]

Delete Operations

await db.insert(users).values({ id: '2', name: 'Ben' });
await db.delete(users).where(eq(users.name, 'Ben'));
const result = await db.select().from(users);

// []

await powerSyncDb.execute('INSERT INTO users (id, name) VALUES(2, ?)', ['Ben']);
await powerSyncDb.execute(`DELETE FROM users WHERE name = ?`, ['Ben']);
const result = await powerSyncDb.getAll('SELECT * from users');

// []

Update Operations

await db.insert(users).values({ id: '3', name: 'Lucy' });
await db.update(users).set({ name: 'Lucy Smith' }).where(eq(users.name, 'Lucy'));
const result = await db.select({ name: users.name }).from(users).get();

// 'Lucy Smith'

await powerSyncDb.execute('INSERT INTO users (id, name) VALUES(3, ?)', ['Lucy']);
await powerSyncDb.execute('UPDATE users SET name = ? WHERE name = ?', ['Lucy Smith', 'Lucy']);
const result = await powerSyncDb.get('SELECT name FROM users WHERE name = ?', ['Lucy Smith'])

// 'Lucy Smith'

Watched Queries

For watched queries with Drizzle it’s recommended to use the watch() function from the Drizzle integration which takes in a Drizzle query.

const query = db.select().from(users);

db.watch(query, {
  onResult(results) {
    console.log(results);
  },
});

// [{ id: '1', name: 'John' }]

powerSyncDb.watch("select * from users", [], {
  onResult(results) {
    console.log(results.rows?._array);
  },
});

// [{ id: '1', name: 'John' }]

Transactions

await db.transaction(async (transaction) => {
  await transaction.insert(users).values({ id: "4", name: "James" });
  await transaction
    .update(users)
    .set({ name: "Lucy James Smith" })
    .where(eq(users.name, "James"));
});

const result = await db.select({ name: users.name }).from(users).get();

// 'James Smith'

await powerSyncDb.writeTransaction(async (transaction) => {
  await transaction.execute('INSERT INTO users (id, name) VALUES(4, ?)', ['James']);
  await transaction.execute("UPDATE users SET name = ? WHERE name = ?", ['James Smith', 'James']);
})
const result = await powerSyncDb.get('SELECT name FROM users WHERE name = ?', ['James Smith'])

// 'James Smith'

Developer Notes

Table Constraint Restrictions

The Drizzle ORM relies on the underlying PowerSync table definitions which are subject to certain limitations. This means that most Drizzle constraint features (such as cascading deletes, foreign checks, unique) are currently not supported.

Get Started

Configuration Details

Sync Streams & Rules

Client SDKs

Handling Writes

Maintenance & Ops

Integrations

Tools

Resources

npm: @powersync/drizzle-driver

Setup

Schema Conversion

Defining PowerSync Options

Converting a Single Table From Drizzle to PowerSync

Compilable Queries

Usage Examples

Select Operations

Insert Operations

Delete Operations

Update Operations

Watched Queries

Transactions

Developer Notes

Table Constraint Restrictions

npm: @powersync/drizzle-driver

​Setup

​Schema Conversion

​Defining PowerSync Options

​Converting a Single Table From Drizzle to PowerSync

​Compilable Queries

​Usage Examples

​Select Operations

​Insert Operations

​Delete Operations

​Update Operations

​Watched Queries

​Transactions

​Developer Notes

​Table Constraint Restrictions

Setup

Schema Conversion

Defining PowerSync Options

Converting a Single Table From Drizzle to PowerSync

Compilable Queries

Usage Examples

Select Operations

Insert Operations

Delete Operations

Update Operations

Watched Queries

Transactions

Developer Notes

Table Constraint Restrictions