Drizzle for Expo SQLite

[AlexBlokh]

With Drizzle we want to deliver the best DX for native mobile development of local first applications

Based on our practical experience of mobile development - we narrowed down on 4 parts of the puzzle:

• Drizzle ORM Driver for Expo SQLite
• Convenient automatic migrations for schema iterations and bundling into application
• Drizzle Studio dev tools plugin for local development with best in class SQL data browsing
• Live Queries

Drizzle ORM and migrations

As of v0.29.2 - Drizzle ORM natively supports Expo SQLite

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);

we've implemented Expo SQLite support for Drizzle Kit to conveniently generate SQL migrations. Unfortunately there's no way to run generate migrations from the CLI(like on the backend), since there's an isolated local database on each phone your applications are installed and you have to bundle migrations into application and run them on the device. We made sure it's as simple as possible - see docs

npm install babel-plugin-inline-import

// update babel.config.js

module.exports = function(api) {
  api.cache(true);
  return {
    presets: ['babel-preset-expo'],
    plugins: [["inline-import", { "extensions": [".sql"] }]] // <-- add this
  };
};

// update metro.config.js

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;

// update drizzle.config.ts

import { defineConfig } from 'drizzle-kit';

export default defineConfig({
  schema: './db/schema.ts',
  out: './drizzle',
  dialect: 'sqlite',
  driver: 'expo', // <--- very important
});

now you can generate migrations with drizzle-kit generate and import them to your app

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;
}

Drizzle Studio

For Drizzle Studio to work with Expo SQLite the only way is to have a dev tool plugin, so we build one - see here

npm i expo-drizzle-studio-plugin

import { useDrizzleStudio } from "expo-drizzle-studio-plugin";
import * as SQLite from "expo-sqlite";
import { View } from "react-native";

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

export default function App() {
    useDrizzleStudio(db);

    return <View></View>;
}

That's it, you can now run Expo App on the simulator or physical device with npx expo start then press shift + m and chooze expo-drizzle-studio-plugin from the list!

Live Queries 🎉

As of v0.31.1 Drizzle ORM now has native support for Expo SQLite Live Queries!
We've implemented a native useLiveQuery React Hook which observes necessary database changes and automatically re-runs database queries. It works with both SQL-like and Drizzle Queries:

import { useLiveQuery, drizzle } from 'drizzle-orm/expo-sqlite';
import { openDatabaseSync } from 'expo-sqlite/next';
import { users } from './schema';
import { Text } from 'react-native';

const expo = openDatabaseSync('db.db', { enableChangeListener: true }); // <-- enable database change listener
const db = drizzle(expo);

const App = () => {
  // Re-renders automatically when data changes
  const { data } = useLiveQuery(db.select().from(users));

  // const { data, error, updatedAt } = useLiveQuery(db.query.users.findFirst());
  // const { data, error, updatedAt } = useLiveQuery(db.query.users.findMany());


  return <Text>{JSON.stringify(data)}</Text>;
};

export default App;

We've intentionally not changed the API of ORM itself to stay with conventional React Hook API, so we have useLiveQuery(databaseQuery) as opposed to db.select().from(users).useLive() or db.query.users.useFindMany()

We've also decided to provide data, error and updatedAt fields as a result of hook for concise explicit error handling following practices of React Query and Electric SQL

[lvolkmann]

Hey @AlexBlokh , really awesome feature! Just one callout for documentation when ya'll get a moment, but I found that useLiveQuery isn't actually "live" unless you set enableChangeListener to true as an option for the db connection (which appears to default to false).

so change...

const expo = openDatabaseSync('db.db');

to

const expo = openDatabaseSync("db.db", { enableChangeListener: true })

And it works like a dream. Thanks again for this stellar feature; excited to put it to use! 🚀

[AlexBlokh]

thank you, we've missed that one, will update everywhere right now

[Alekzv9]

Thanks! it works great.

I don't know if this just happened to me but, I had a table with a underscore mobile_operation and the migration command (drizzle-kit migrate) was failing every time. After trying different things I changed the table name to mobileOperation and the migrations works.

"expo": "^51.0.18",
"drizzle-orm": "^0.31.4",
"drizzle-kit": "^0.22.8",

Test environment:
Expo Go 51
Google pixel 8

[Alekzv9]

nvm if I just tried in another device with an older version of the app, and it throws error when trying to create a table [DrizzleError: Failed to run the query 'CREATE TABLE tableName. I guess it's because the __drizzle_migrations it's empty(?). I tried to modify the migration_file.sql manually to create table if not exists but the change is ignored

[HoaiHuynh]

Hello @Alekzv9, have you solved this bug? I have faced the same problem.

[Alekzv9]

No, I ended up doing a mix. I create the table manually and use drizzle for everything else (queries, inserts, etc)

[anstapol]

Hey guys, very nice update, thanks for that!

I have one question thought, what if I want to subscribe to a query with dynamic where parameters?

  const todos = useLiveQuery(
    db.query.todos.findMany({
      where: between(schema.todos.created_at, from, to),
    })
  )

The from & to dates come from a parent.

To be clear I've tried it and it didn't work 🥲

[mianala]

+1 This would literally get rid of tens of useQuery files in my codebase.

[Veronika11111]

I have also one question, I am not sure, if I understand this correctly.

This type of live query is not refreshing when I add new collection item:

  const actualCollectionData = useLiveQuery(
    databaseService.db.query.collections.findFirst({
      where: eq(collections.id, 6),
      with: {
        materialsToCollections: true,
      },
    })
  );

with this schema:

export const materials = sqliteTable('materials', {
  code: integer('code').primaryKey(),
  type: text('type', { enum: ['PET', 'CAN'] }).notNull(),
  createdAt: text('created_at').default(sql`(CURRENT_TIMESTAMP)`),
  updatedAt: text('updated_at')
    .default(sql`(CURRENT_TIMESTAMP)`)
    .$onUpdate(() => sql`(CURRENT_TIMESTAMP)`),
});

export const collectionItems = sqliteTable(
  'collection_items',
  {
    id: integer('id').primaryKey(),
    materialCode: integer('material_code')
      .references(() => materials.code)
      .notNull(),
    collectionId: integer('collection_id')
      .references(() => collections.id)
      .notNull(),
    qrCode: text('qr_code').notNull(),
  },
  collectionItems => ({
    qrCode: index('qrCodeIdx').on(collectionItems.qrCode),
  })
);

export const materialsRelations = relations(materials, params => ({
  materialsToCollections: params.many(collectionItems),
}));

export const collections = sqliteTable(
  'collections',
  {
    id: integer('id').primaryKey(),
    state: text('state', { enum: ['PENDING', 'COMPLETED', 'CANCELLED'] }).notNull(),
    createdAt: text('created_at').default(sql`(CURRENT_TIMESTAMP)`),
    updatedAt: text('updated_at')
      .default(sql`(CURRENT_TIMESTAMP)`)
      .$onUpdate(() => sql`(CURRENT_TIMESTAMP)`),
  },
  collections => ({
    state: index('stateIdx').on(collections.state),
    createdAtIdx: index('createdAtIdx').on(collections.createdAt),
  })
);

export const collectionsRelations = relations(collections, params => ({
  materialsToCollections: params.many(collectionItems),
}));

export const materialsToCollectionsRelations = relations(collectionItems, params => ({
  material: params.one(materials, {
    fields: [collectionItems.materialCode],
    references: [materials.code],
  }),
  collection: params.one(collections, {
    fields: [collectionItems.collectionId],
    references: [collections.id],
  }),
}));

[ahwelgemoed]

Hey - Thanks for the great implementation, it's been really fun working with Drizzle in Expo/React Native.
I do however have a question / observation / bug.

I am using useLiveQuery a lot and noticed something

Some Pseudo Code

# userId coming from route or props
 const { data } = useLiveQuery(
    db
      .select()
      .from(lotsTable)
      .where(
       eq(userId, userId),
      ),
    [userId],
  );

This work as expected, if userId is 1 and I make changes in the DB, it updates and re-renders, however, any change in this table is causing updates here. So changes on userId 2 will cause userId 1 to also re-render.

Im working on optimising for slower devices and this is a big one if implemented in a provider/context

I did some poking around and here it seems like addDatabaseChangeListener is triggering on any change (correctly) and that triggers the query to re-run, but as the query is only "selecting" a subset of data, based on the dependant array, it will select nothing so data here is null or an empty array. Setting that then causes a state change and a unnecessary re-render

My expectations is: if the data my query is selecting is changing ,re-render, not re-render on any changes in the table, but that might be wrong?

Seems like it could be a simple fix, but I could be missing something bigger, happy to submit a PR if you think this is the correct direction.

    const handleData = (data: any) => {
      // if data is empty or undefined exit early
      if (!data || data.length === 0) {
        return;
      }
      setData(data);
      setUpdatedAt(new Date());
    };
    

[mirague]

Sounds like a reasonable expectation and would be a great performance improvement!

[Jacob-Diebold]

Hello, I'm running into what I think are related issues to what @ahwelgemoed posted about.

It seems like the dep array isn't doing anything. I have this test function that anytime I update the "users" table, it causes the "groups" query to re-render if they are in the same component. If I remove the users query from the component and update that table, it does not cause the groups query to re-render.

function UserAndGroupTest() {
  const users = useLiveQuery(
    db.select().from(SCHEMA.users).orderBy(asc(SCHEMA.users.createdAt)),
    [],
  );
  const groups = useLiveQuery(
    db.select().from(SCHEMA.groups).orderBy(asc(SCHEMA.groups.createdAt)),
    [],
  );
  useEffect(() => {
    console.log("Groups", groups);
  }, [groups]);
  return (
    <View />
      
    
  );
}