Building an Offline-First Production-Ready Expo App with Drizzle ORM and SQLite

Developer working on a laptop with code editor open while holding a mobile phone, illustrating mobile app development workflow

Figure 1: Mobile-first development environment

Introduction

At DETL, we’re a software engineering and design company based in Toronto, specializing in building AI-powered applications and mobile experiences for forward-thinking companies. We recently tackled a challenging problem: building a reliable offline-first mobile application for the trucking industry, where drivers frequently lose connectivity but can’t afford to lose data. This post shares the architecture and patterns we used to solve it.

Why Offline-First Matters

Network connectivity is unreliable. Whether your users are driving through rural areas, working in basements, or dealing with spotty subway Wi-Fi, intermittent connections are a reality. An offline-first approach means your app writes data locally first, then syncs when connectivity returns. This prevents data loss and keeps the user experience smooth, even when the network isn’t cooperating.

Architecture Overview

Our stack combines three key pieces:

Drizzle ORM on Expo SQLite for typed local storage
React Query for managing server state
Tuple guards (guardAsync) to handle errors explicitly without try/catch sprawl

Setup and Installation

First, install the required dependencies:

1npm install drizzle-orm@~0.44.2 expo-sqlite@~15.2.14 @tanstack/react-query@~5.81.5
2npm install -D drizzle-kit

Database Initialization

The database setup happens in layers. Create a DatabaseProvider that wraps the app and handles SQLite initialization and migrations.

1// src/components/database-provider.tsx
2import { createContext, type PropsWithChildren, useContext, useMemo } from 'react';
3import type { SQLiteDatabase } from 'expo-sqlite';
4import { SQLiteProvider, useSQLiteContext } from 'expo-sqlite';
5import { drizzle, type ExpoSQLiteDatabase } from 'drizzle-orm/expo-sqlite';
6import { migrate } from 'drizzle-orm/expo-sqlite/migrator';
7import migrations from '../../drizzle/migrations';
8
9const Logger = console;
10const databaseName = 'app-db.db';
11
12const DrizzleContext = createContext<ExpoSQLiteDatabase | null>(null);
13
14export function useDrizzle() {
15  const context = useContext(DrizzleContext);
16  if (!context) {
17    throw new Error('useDrizzle must be used within a DrizzleProvider');
18  }
19  return context;
20}
21
22function DrizzleProvider({ children }: PropsWithChildren) {
23  const sqliteDb = useSQLiteContext();
24  
25  const db = useMemo(() => {
26    Logger.info('Creating Drizzle instance');
27    return drizzle(sqliteDb);
28  }, [sqliteDb]);
29
30  return (
31    <DrizzleContext.Provider value={db}>
32      {children}
33    </DrizzleContext.Provider>
34  );
35}
36
37async function migrateAsync(db: SQLiteDatabase) {
38  const drizzleDb = drizzle(db);
39  await migrate(drizzleDb, migrations);
40}
41
42const options = { enableChangeListener: true };
43
44export function DatabaseProvider({ children }: PropsWithChildren) {
45  return (
46    <SQLiteProvider
47      databaseName={databaseName}
48      onError={Logger.error}
49      onInit={migrateAsync}
50      options={options}
51    >
52      <DrizzleProvider>
53        {children}
54      </DrizzleProvider>
55    </SQLiteProvider>
56  );
57}

What’s happening:

SQLiteProvider opens the database and runs migrations on initialization
DrizzleProvider wraps the raw SQLite database with Drizzle's type-safe API
useDrizzle() hook provides access to the database anywhere in your app
enableChangeListener: true allows useLiveQuery to detect changes

Wrap your app with the necessary providers:

1// src/components/common-providers.tsx
2import { type PropsWithChildren } from 'react';
3import { GestureHandlerRootView } from 'react-native-gesture-handler';
4import { DatabaseProvider } from '@/components/database-provider';
5import { ConnectivityProvider } from '@/contexts/connectivity-context';
6import { QueryProvider } from '@/data/remote/query-provider';
7
8export function CommonProviders({ children }: PropsWithChildren) {
9  return (
10    <GestureHandlerRootView className="flex-1">
11      <DatabaseProvider>
12        <ConnectivityProvider>
13          <QueryProvider>
14            {children}
15          </QueryProvider>
16        </ConnectivityProvider>
17      </DatabaseProvider>
18    </GestureHandlerRootView>
19  );
20}

Schema and Migrations

Define your tables using Drizzle’s schema builder:

1// src/db/schema.ts
2import { sqliteTable, text, integer } from 'drizzle-orm/sqlite-core';
3
4export const items = sqliteTable('items', {
5  id: text('id').primaryKey(),
6  title: text('title').notNull(),
7  status: text('status').notNull(), // 'active' | 'archived'
8  updatedAt: integer('updated_at').notNull(),
9});
10
11export type ItemEntity = typeof items.$inferSelect;
12export type NewItemEntity = typeof items.$inferInsert;

Generate migrations when schema changes:

1npx drizzle-kit generate:sqlite

Migrations run automatically on app startup via migrateAsync.

Error Handling with Guard Tuples

Instead of try/catch blocks, use a guard helper that returns result/error tuples:

1// lib/utils.ts
2export async function guardAsync<T, E = Error>(
3  promise: Promise<T>,
4): Promise<[T, null] | [null, E]> {
5  try {
6    return [await promise, null];
7  } catch (error) {
8    return [null, error as E];
9  }
10}
11

Usage:

1const [result, error] = await guardAsync(someAsyncOperation());
2
3if (error) {
4  console.error('Operation failed:', error);
5  return;
6}
7
8console.log('Success:', result);

Local Data Operations

Writing Data Locally

When a user creates data, write it to SQLite immediately with a pending status:

1// src/data/local/add-item.ts
2import { useDrizzle } from '@/components/database-provider';
3import { items } from '@/db/schema';
4import { guardAsync } from '@/lib/utils';
5
6export async function addItem(payload: { id: string; title: string }) {
7  const db = useDrizzle();
8  const now = Date.now();
9  
10  const [_, error] = await guardAsync(
11    db.insert(items).values({ 
12      ...payload,
13      status: 'pending',
14      updatedAt: now,
15    })
16  );
17  
18  return error;
19}

Use in a component:

1async function handleSubmit() {
2  const error = await addItem({
3    id: generateId(),
4    title: taskTitle,
5  });
6  
7  if (error) {
8    Alert.alert('Error', 'Failed to create item');
9    return;
10  }
11  
12  Alert.alert('Success', 'Item created. Will sync when online.');
13}

Reading Local Data Reactively

Use useLiveQuery to automatically update UI when data changes:

1// src/data/local/use-items-local.ts
2import { useLiveQuery } from 'drizzle-orm/expo-sqlite';
3import { useDrizzle } from '@/components/database-provider';
4import { items } from '@/db/schema';
5
6export function useItemsLocal() {
7  const db = useDrizzle();
8  return useLiveQuery(db.select().from(items));
9}

The UI updates automatically when items are inserted, updated, or deleted.

Reading Server Data Offline

For data that originates on the server, cache it locally so users can access it offline.

Local Cache Queries

1// src/data/local/queries.ts
2import { useDrizzle } from '@/components/database-provider';
3import { items } from '@/db/schema';
4import type { NewItemEntity } from '@/db/schema';
5
6export const selectItems = () => {
7  const db = useDrizzle();
8  return db.select().from(items).all();
9};
10
11export const replaceItems = (rows: NewItemEntity[]) => {
12  const db = useDrizzle();
13  return db.transaction(tx => {
14    tx.delete(items).run();
15    rows.forEach(row => tx.insert(items).values(row).run());
16  });
17};

React Query Hook for Server Data

1// src/data/remote/use-get-items.ts
2import { useQuery } from '@tanstack/react-query';
3
4export const ITEMS_QUERY_KEY = ['items'];
5
6export function useGetItems(options?: { enabled?: boolean }) {
7  return useQuery({
8    queryKey: ITEMS_QUERY_KEY,
9    queryFn: async () => {
10      const response = await fetch('https://api.example.com/items');
11      if (!response.ok) throw new Error('Failed to fetch items');
12      return response.json();
13    },
14    retry: 1,
15    staleTime: 60_000,
16    ...options,
17  });
18}

Unified Hook: Local Cache + Remote Fetch

Combine local hydration with remote fetching:

1// src/data/use-items.ts
2import { useEffect, useMemo, useState } from 'react';
3import { useGetItems } from './remote/use-get-items';
4import { selectItems, replaceItems } from './local/queries';
5
6type Props = { 
7  shouldFetchRemote: boolean; // false when offline
8};
9
10export function useItems({ shouldFetchRemote }: Props) {
11  const [items, setItems] = useState<Item[]>([]);
12  
13  // 1. Hydrate from local cache immediately (works offline)
14  useEffect(() => {
15    selectItems()
16      .then(rows => setItems(rows))
17      .catch(console.error);
18  }, []);
19  
20  // 2. Fetch from server (gated by shouldFetchRemote)
21  const itemsQuery = useGetItems({ enabled: shouldFetchRemote });
22  
23  useEffect(() => {
24    if (!itemsQuery.data) return;
25    
26    const freshItems = itemsQuery.data.items;
27    
28    // Cache to SQLite for offline reuse
29    replaceItems(freshItems).catch(console.error);
30    
31    // Update UI state
32    setItems(freshItems);
33  }, [itemsQuery.data]);
34  
35  return useMemo(() => ({
36    items,
37    isLoading: itemsQuery.isLoading,
38    isRefreshing: itemsQuery.isFetching,
39    error: itemsQuery.error,
40    refresh: itemsQuery.refetch,
41  }), [items, itemsQuery]);
42}

How this works:

Local hydration runs first — UI populates immediately from SQLite cache, even offline
Remote fetch is gated — Only runs when shouldFetchRemote is true (when online)
Cache write-back — Successful server responses are saved to SQLite
State updates — UI shows fresh data after server fetch

Usage in a Screen

1function ItemsScreen() {
2  const { isOnline } = useConnectivity();
3  const { items, isRefreshing, refresh } = useItems({
4    shouldFetchRemote: isOnline,
5  });
6  
7  return (
8    <ScrollView
9      refreshControl={
10        <RefreshControl
11          refreshing={isRefreshing}
12          onRefresh={refresh}
13        />
14      }
15    >
16      <FlatList
17        data={items}
18        renderItem={({ item }) => <Text>{item.title}</Text>}
19      />
20    </ScrollView>
21  );
22}

When offline, items still renders from the SQLite cache. When online, pulling to refresh fetches fresh data and updates the cache.

Sync Engine: Pushing Pending Changes

When connectivity returns, sync pending items to your server:

1// src/data/sync/sync-pending-items.ts
2import { useDrizzle } from '@/components/database-provider';
3import { items } from '@/db/schema';
4import { guardAsync } from '@/lib/utils';
5import { queryClient } from '@/data/remote/query-client';
6import { eq } from 'drizzle-orm';
7
8export async function syncPendingItems(
9  sendToServer: (item: ItemEntity) => Promise<void>
10) {
11  const db = useDrizzle();
12  
13  const pendingItems = db
14    .select()
15    .from(items)
16    .where(eq(items.status, 'pending'))
17    .all();
18  
19  for (const item of pendingItems) {
20    const [_, error] = await guardAsync(sendToServer(item));
21    
22    await db
23      .update(items)
24      .set({ status: error ? 'failed' : 'synced' })
25      .where(eq(items.id, item.id));
26  }
27  
28  await queryClient.invalidateQueries({ queryKey: ['items'] });
29}

Step-by-step:

Find all items with status = 'pending'
Loop through and send each to the server
Update status to synced on success or failed on error
Invalidate React Query cache to refetch fresh data

Implement the server function:

1async function sendItemToServer(item: ItemEntity) {
2  const response = await fetch('https://api.example.com/items', {
3    method: 'POST',
4    headers: { 'Content-Type': 'application/json' },
5    body: JSON.stringify(item),
6  });
7  
8  if (!response.ok) {
9    throw new Error(`Server returned ${response.status}`);
10  }
11}
12
13await syncPendingItems(sendItemToServer);

Network Detection

Detect connectivity and trigger sync when coming online:

1npm install @react-native-community/netinfo

Create a connectivity context:

1// src/contexts/connectivity-context.tsx
2import { createContext, useContext, useEffect, useState, type PropsWithChildren } from 'react';
3import NetInfo from '@react-native-community/netinfo';
4
5const ConnectivityContext = createContext<{ isOnline: boolean } | null>(null);
6
7export function useConnectivity() {
8  const context = useContext(ConnectivityContext);
9  if (!context) throw new Error('useConnectivity must be used within ConnectivityProvider');
10  return context;
11}
12
13export function ConnectivityProvider({ children }: PropsWithChildren) {
14  const [isOnline, setIsOnline] = useState(true);
15  
16  useEffect(() => {
17    const unsubscribe = NetInfo.addEventListener(state => {
18      setIsOnline(!!state.isConnected);
19    });
20    return unsubscribe;
21  }, []);
22  
23  return (
24    <ConnectivityContext.Provider value={{ isOnline }}>
25      {children}
26    </ConnectivityContext.Provider>
27  );
28}

Create a sync manager:

1// src/components/sync-manager.tsx
2import { useEffect } from 'react';
3import { useConnectivity } from '@/contexts/connectivity-context';
4import { syncPendingItems } from '@/data/sync/sync-pending-items';
5import { sendItemToServer } from '@/data/remote/api';
6
7export function SyncManager() {
8  const { isOnline } = useConnectivity();
9  
10  useEffect(() => {
11    if (isOnline) {
12      syncPendingItems(sendItemToServer).catch(console.error);
13    }
14  }, [isOnline]);
15  
16  return null;
17}

Add to your app:

1export function App() {
2  return (
3    <CommonProviders>
4      <SyncManager />
5      <RootNavigator />
6    </CommonProviders>
7  );
8}

Cache Refresh with React Query

Centralize cache invalidation and expose loading states:

1// src/data/use-refresh-data.ts
2import { useQueryClient } from '@tanstack/react-query';
3
4export function useRefreshData() {
5  const queryClient = useQueryClient();
6  
7  const isItemsRefreshing = queryClient.isFetching({ 
8    queryKey: ['items'], 
9    stale: false 
10  }) > 0;
11  
12  const refreshItems = () => {
13    queryClient.invalidateQueries({ queryKey: ['items'] });
14  };
15  
16  return { isItemsRefreshing, refreshItems };
17}

Use in pull-to-refresh:

1function ItemsScreen() {
2  const { isItemsRefreshing, refreshItems } = useRefreshData();
3  
4  return (
5    <ScrollView
6      refreshControl={
7        <RefreshControl
8          refreshing={isItemsRefreshing}
9          onRefresh={refreshItems}
10        />
11      }
12    >
13      {/* Content */}
14    </ScrollView>
15  );
16}

Preventing Data Loss on Exit

Block navigation when un-synced data exists:

1// src/data/local/has-pending-data.ts
2import { sql, eq } from 'drizzle-orm';
3import { useDrizzle } from '@/components/database-provider';
4import { items } from '@/db/schema';
5
6export function hasPendingItems(): boolean {
7  const db = useDrizzle();
8  
9  const result = db
10    .select({ count: sql<number>`count(*)` })
11    .from(items)
12    .where(eq(items.status, 'pending'))
13    .get();
14  
15  return result.count > 0;
16}

Use in a navigation guard:

1import { useEffect } from 'react';
2import { Alert } from 'react-native';
3import { useNavigation } from '@react-navigation/native';
4import { hasPendingItems } from '@/data/local/has-pending-data';
5
6function EditItemScreen() {
7  const navigation = useNavigation();
8  
9  useEffect(() => {
10    const unsubscribe = navigation.addListener('beforeRemove', (e) => {
11      if (!hasPendingItems()) return;
12      
13      e.preventDefault();
14      
15      Alert.alert(
16        'Unsaved Changes',
17        'You have unsynced data. Are you sure you want to leave?',
18        [
19          { text: 'Stay', style: 'cancel' },
20          { 
21            text: 'Leave Anyway', 
22            style: 'destructive',
23            onPress: () => navigation.dispatch(e.data.action) 
24          },
25        ]
26      );
27    });
28    
29    return unsubscribe;
30  }, [navigation]);
31  
32  return (/* Your screen UI */);
33}

Testing the Offline Layer

Test offline logic with in-memory SQLite:

1// __tests__/offline.test.ts
2import { drizzle } from 'drizzle-orm/better-sqlite3';
3import Database from 'better-sqlite3';
4import { items } from '@/db/schema';
5import { eq } from 'drizzle-orm';
6import { guardAsync } from '@/lib/utils';
7
8describe('Offline data layer', () => {
9  let db: ReturnType<typeof drizzle>;
10  let sqlite: Database.Database;
11  
12  beforeEach(() => {
13    sqlite = new Database(':memory:');
14    db = drizzle(sqlite);
15    
16    sqlite.exec(`
17      CREATE TABLE items (
18        id TEXT PRIMARY KEY,
19        title TEXT NOT NULL,
20        status TEXT NOT NULL,
21        updated_at INTEGER NOT NULL
22      )
23    `);
24  });
25  
26  afterEach(() => {
27    sqlite.close();
28  });
29  
30  it('marks items as failed when sync errors', async () => {
31    const now = Date.now();
32    
33    await db.insert(items).values({ 
34      id: '1', 
35      title: 'Failing Item',
36      status: 'pending', 
37      updatedAt: now,
38    });
39    
40    const sendToServer = jest.fn().mockRejectedValue(new Error('Network error'));
41    
42    const pending = db.select().from(items).where(eq(items.status, 'pending')).all();
43    
44    for (const item of pending) {
45      const [_, error] = await guardAsync(sendToServer(item));
46      
47      await db
48        .update(items)
49        .set({ status: error ? 'failed' : 'synced' })
50        .where(eq(items.id, item.id));
51    }
52    
53    const failed = db.select().from(items).where(eq(items.status, 'failed')).all();
54    
55    expect(failed).toHaveLength(1);
56    expect(failed[0].id).toBe('1');
57  });
58});

Performance Considerations

Index frequently queried columns:

1-- drizzle/0001_add_indexes.sql
2CREATE INDEX idx_items_status ON items(status);
3CREATE INDEX idx_items_updated_at ON items(updated_at);

Use live queries over polling:

1// Good - only updates when data changes
2const { data: items } = useLiveQuery(db.select().from(items));
3
4// Bad - polls every second
5useEffect(() => {
6  const interval = setInterval(() => {
7    const newItems = db.select().from(items).all();
8    setItems(newItems);
9  }, 1000);
10  return () => clearInterval(interval);
11}, []);
12```
13
14## The Complete Flow
15
16### 1. User Creates an Item Offline
17```
18User taps "Create Item"
19  ↓
20addItem() writes to SQLite with status='pending'
21  ↓
22useLiveQuery detects change
23  ↓
24ItemsList component re-renders
25  ↓
26Item appears instantly in UI (no network needed)
27```
28
29### 2. Network Comes Back Online
30```
31NetInfo detects connection
32  ↓
33SyncManager.useEffect() runs
34  ↓
35syncPendingItems() queries for status='pending'
36  ↓
37For each pending item:
38  - sendToServer() POSTs to API
39  - If success: UPDATE status='synced'
40  - If error: UPDATE status='failed'
41  ↓
42queryClient.invalidateQueries(['items'])
43  ↓
44React Query refetches server data
45  ↓
46UI now shows server's version
47```
48
49### 3. User Opens App Offline
50```
51App launches
52  ↓
53useItems() hydrates from SQLite cache
54  ↓
55setItems(cachedRows) populates UI immediately
56  ↓
57isOnline = false, so shouldFetchRemote = false
58  ↓
59No network request made
60  ↓
61User sees last cached data
62```
63
64### 4. User Pulls to Refresh While Online
65```
66User pulls down on ScrollView
67  ↓
68refreshItems() calls invalidateQueries
69  ↓
70React Query marks cache as stale
71  ↓
72isItemsRefreshing becomes true
73  ↓
74RefreshControl shows spinner
75  ↓
76React Query fetches from server
77  ↓
78Fresh data saved to SQLite via replaceItems()
79  ↓
80UI updates with fresh data
81  ↓
82isItemsRefreshing becomes false
83  ↓
84Spinner disappears
85```
86
87### 5. User Tries to Leave with Unsaved Data
88```
89User taps back button
90  ↓
91navigation.beforeRemove listener fires
92  ↓
93hasPendingItems() queries COUNT(*)
94  ↓
95If count > 0:
96  - e.preventDefault() blocks navigation
97  - Alert.alert() shows confirmation
98  - User can choose "Stay" or "Leave Anyway"
99  ↓
100If count === 0:
101  - Navigation proceeds normally

Conclusion

Offline-first architecture isn’t just about handling network failures. It’s about building trust. When users know their data is safe regardless of connectivity, they can focus on their work instead of worrying about whether their changes saved.

The combination of Drizzle ORM, Expo SQLite, and React Query gives you a solid foundation for building reliable offline experiences. Local writes happen instantly, server data is cached for offline access, live queries keep your UI reactive, and the sync engine reconciles with your server when possible.

This architecture powers production apps used by thousands of users daily in areas with unreliable connectivity. Try it in your next Expo project.

Next Blog

Invalid Date

Building Pixel-Perfect Diagnostic Charts for 1,000+ Truck Drivers: A React Native Deep Dive