Kysely Expo SQLite Dialect

A Kysely dialect for Expo SQLite, allowing you to use the powerful Kysely query builder with SQLite databases in React Native/Expo applications.

Installation

npm install kysely-expo-sqlite-dialect kysely expo-sqlite
# or
yarn add kysely-expo-sqlite-dialect kysely expo-sqlite
# or
pnpm add kysely-expo-sqlite-dialect kysely expo-sqlite

Usage

Basic Setup

import { Kysely } from 'kysely';
import { ExpoSQLiteDialect } from 'kysely-expo-sqlite-dialect';

// Define your database schema
interface Database {
  users: {
    id: number;
    name: string;
    email: string;
    created_at: Date;
  };
  posts: {
    id: number;
    user_id: number;
    title: string;
    content: string;
    created_at: Date;
  };
}

// Create Kysely instance
const db = new Kysely<Database>({
  dialect: new ExpoSQLiteDialect({
    database: 'myapp.db', // Database file name
  }),
});

// With additional options
const db = new Kysely<Database>({
  dialect: new ExpoSQLiteDialect({
    database: 'myapp.db',
    debug: __DEV__, // Enable debug logging in development
    pragmas: {
      // Note: WAL mode is not supported in wa-sqlite (web platform)
      journal_mode: 'WAL', // Use Write-Ahead Logging (iOS/Android only)
      synchronous: 'NORMAL', // Balance between safety and speed
      foreign_keys: 1, // Enable foreign key constraints
      cache_size: -2000, // 2MB cache
      temp_store: 'MEMORY', // Store temp tables in memory
    },
    onCreateConnection: async (db) => {
      // Custom initialization
      await db.execAsync('CREATE EXTENSION IF NOT EXISTS ...');
    }
  }),
});

// With advanced features
const advancedDb = new Kysely<Database>({
  dialect: new ExpoSQLiteDialect({
    database: 'myapp.db',
    enableChangeListener: true, // Enable reactive queries
    finalizeUnusedStatementsBeforeClosing: true, // Clean up prepared statements
  }),
});

Creating Tables

async function createTables() {
  await db.schema
    .createTable('users')
    .addColumn('id', 'integer', (col) => col.primaryKey().autoIncrement())
    .addColumn('name', 'text', (col) => col.notNull())
    .addColumn('email', 'text', (col) => col.notNull().unique())
    .addColumn('created_at', 'datetime', (col) => col.defaultTo('CURRENT_TIMESTAMP'))
    .execute();

  await db.schema
    .createTable('posts')
    .addColumn('id', 'integer', (col) => col.primaryKey().autoIncrement())
    .addColumn('user_id', 'integer', (col) => 
      col.references('users.id').onDelete('cascade').notNull()
    )
    .addColumn('title', 'text', (col) => col.notNull())
    .addColumn('content', 'text', (col) => col.notNull())
    .addColumn('created_at', 'datetime', (col) => col.defaultTo('CURRENT_TIMESTAMP'))
    .execute();
}

Inserting Data

async function insertUser(name: string, email: string) {
  const result = await db
    .insertInto('users')
    .values({
      name,
      email,
    })
    .returningAll()
    .executeTakeFirstOrThrow();
  
  return result;
}

async function insertPost(userId: number, title: string, content: string) {
  const result = await db
    .insertInto('posts')
    .values({
      user_id: userId,
      title,
      content,
    })
    .returningAll()
    .executeTakeFirstOrThrow();
  
  return result;
}

Querying Data

// Select all users
async function getAllUsers() {
  const users = await db
    .selectFrom('users')
    .selectAll()
    .execute();
  
  return users;
}

// Select user by ID
async function getUserById(id: number) {
  const user = await db
    .selectFrom('users')
    .selectAll()
    .where('id', '=', id)
    .executeTakeFirst();
  
  return user;
}

// Select posts with user information
async function getPostsWithUsers() {
  const posts = await db
    .selectFrom('posts')
    .innerJoin('users', 'users.id', 'posts.user_id')
    .select([
      'posts.id',
      'posts.title',
      'posts.content',
      'posts.created_at',
      'users.name as author_name',
      'users.email as author_email',
    ])
    .orderBy('posts.created_at', 'desc')
    .execute();
  
  return posts;
}

Updating Data

async function updateUser(id: number, updates: { name?: string; email?: string }) {
  const result = await db
    .updateTable('users')
    .set(updates)
    .where('id', '=', id)
    .execute();
  
  return result;
}

Deleting Data

async function deleteUser(id: number) {
  const result = await db
    .deleteFrom('users')
    .where('id', '=', id)
    .execute();
  
  return result;
}

Working with Binary Data (Blobs)

Expo SQLite supports binary data through Uint8Array:

// Create a table with blob column
await db.schema
  .createTable('files')
  .addColumn('id', 'integer', (col) => col.primaryKey().autoIncrement())
  .addColumn('name', 'text', (col) => col.notNull())
  .addColumn('data', 'blob')
  .addColumn('mime_type', 'text')
  .execute();

// Insert binary data
async function saveFile(name: string, data: Uint8Array, mimeType: string) {
  const result = await db
    .insertInto('files')
    .values({
      name,
      data, // Uint8Array is automatically handled
      mime_type: mimeType,
    })
    .returningAll()
    .executeTakeFirstOrThrow();
  
  return result;
}

// Read binary data
async function getFile(id: number) {
  const file = await db
    .selectFrom('files')
    .selectAll()
    .where('id', '=', id)
    .executeTakeFirst();
  
  if (file?.data) {
    // data will be returned as Uint8Array
    console.log('File size:', file.data.byteLength, 'bytes');
  }
  
  return file;
}

// Example usage
const imageData = new Uint8Array([0xFF, 0xD8, 0xFF, 0xE0, /* ... */]);
await saveFile('photo.jpg', imageData, 'image/jpeg');

How It Works

This dialect is designed as a thin wrapper around Expo SQLite, providing Kysely's type-safe query builder without adding unnecessary complexity:

• SELECT queries → Uses getAllAsync() internally
• INSERT/UPDATE/DELETE → Uses runAsync() internally
• Streaming queries → Uses getEachAsync() for efficient row-by-row processing
• No hidden optimizations - The dialect doesn't try to be smart about query execution

Direct Expo SQLite Access

When you need specific Expo SQLite features, you have several options:

// Option 1: Use raw SQL through Kysely
const result = await db.executeQuery({
  sql: 'SELECT * FROM users WHERE email = ?',
  parameters: ['[email protected]'],
});

// Option 2: Access Expo SQLite directly for advanced features
import * as SQLite from 'expo-sqlite';

const directDb = await SQLite.openDatabaseAsync('myapp.db');

// Use prepared statements explicitly
const stmt = await directDb.prepareAsync('INSERT INTO users (name, email) VALUES (?, ?)');
try {
  await stmt.executeAsync(['John', '[email protected]']);
} finally {
  await stmt.finalizeAsync();
}

// Use getFirstAsync for single row optimization
const user = await directDb.getFirstAsync('SELECT * FROM users WHERE id = ?', [1]);

await directDb.closeAsync();

Transactions

async function createUserWithPost(
  userData: { name: string; email: string },
  postData: { title: string; content: string }
) {
  return await db.transaction().execute(async (trx) => {
    // Insert user
    const user = await trx
      .insertInto('users')
      .values(userData)
      .returningAll()
      .executeTakeFirstOrThrow();
    
    // Insert post
    const post = await trx
      .insertInto('posts')
      .values({
        user_id: user.id,
        ...postData,
      })
      .returningAll()
      .executeTakeFirstOrThrow();
    
    return { user, post };
  });
}

Streaming Query Results

For memory-efficient processing of large result sets, use the .stream() method:

// Stream rows one at a time
const stream = db
  .selectFrom('users')
  .selectAll()
  .where('active', '=', true)
  .stream();

for await (const { rows } of stream) {
  // Process each row individually
  // Only one row is kept in memory at a time
  console.log('Processing user:', rows[0].name);
}

// Stream with custom chunk size for batch processing
const chunkedStream = db
  .selectFrom('posts')
  .selectAll()
  .orderBy('created_at', 'desc')
  .stream(100); // Process 100 rows at a time

for await (const { rows } of chunkedStream) {
  // Process batch of rows
  console.log(`Processing ${rows.length} posts`);
  await processBatch(rows);
}

// Stream with complex queries
const joinStream = db
  .selectFrom('posts')
  .innerJoin('users', 'users.id', 'posts.user_id')
  .select(['users.name', 'posts.title', 'posts.created_at'])
  .where('posts.created_at', '>', new Date('2024-01-01'))
  .orderBy('posts.created_at')
  .stream(50);

let totalProcessed = 0;
for await (const { rows } of joinStream) {
  totalProcessed += rows.length;
  // Process without loading entire result set into memory
}
console.log(`Processed ${totalProcessed} posts`);

Benefits of streaming:

• Memory Efficiency: Process millions of rows without running out of memory
• Real-time Processing: Start processing results immediately as they arrive
• Backpressure Control: Process at your own pace without overwhelming the system

Using with React Native/Expo

import React, { useEffect, useState } from 'react';
import { View, Text, FlatList } from 'react-native';
import { Kysely } from 'kysely';
import { ExpoSQLiteDialect } from 'kysely-expo-sqlite-dialect';

// Initialize database
const db = new Kysely<Database>({
  dialect: new ExpoSQLiteDialect({
    database: 'myapp.db',
  }),
});

function UserList() {
  const [users, setUsers] = useState<User[]>([]);

  useEffect(() => {
    async function loadUsers() {
      const userList = await db
        .selectFrom('users')
        .selectAll()
        .execute();
      
      setUsers(userList);
    }

    loadUsers();
  }, []);

  return (
    <FlatList
      data={users}
      keyExtractor={(item) => item.id.toString()}
      renderItem={({ item }) => (
        <View>
          <Text>{item.name}</Text>
          <Text>{item.email}</Text>
        </View>
      )}
    />
  );
}

Database Change Listeners

Enable reactive queries by listening to database changes:

import { ExpoSQLiteDialect, ExpoSQLiteDriver, DatabaseChangeEvent } from 'kysely-expo-sqlite-dialect';

// Create database with change listeners enabled
const dialect = new ExpoSQLiteDialect({
  database: 'myapp.db',
  enableChangeListener: true, // Must be true to use listeners
});

const driver = dialect.createDriver() as ExpoSQLiteDriver;
await driver.init();

const db = new Kysely<Database>({ dialect });

// Add a change listener
const subscription = driver.addChangeListener((event: DatabaseChangeEvent) => {
  console.log('Database changed:', {
    database: event.databaseName,
    table: event.tableName,
    rowId: event.rowId,
    changeType: event.typeId, // 'insert', 'update', or 'delete'
  });
  
  // React to specific table changes
  if (event.tableName === 'users') {
    // Refresh your UI or cache
  }
});

// Remove listener when done
subscription?.remove();

// React component example with change listeners
function ReactiveUserList() {
  const [users, setUsers] = useState<User[]>([]);
  const [driver, setDriver] = useState<ExpoSQLiteDriver | null>(null);

  useEffect(() => {
    // Initialize database with change listeners
    const initDb = async () => {
      const dialect = new ExpoSQLiteDialect({
        database: 'myapp.db',
        enableChangeListener: true,
      });
      
      const driver = dialect.createDriver() as ExpoSQLiteDriver;
      await driver.init();
      setDriver(driver);
      
      const db = new Kysely<Database>({ dialect });
      
      // Load initial data
      const users = await db.selectFrom('users').selectAll().execute();
      setUsers(users);
    };
    
    initDb();
  }, []);

  useEffect(() => {
    if (!driver) return;
    
    // Listen for changes
    const subscription = driver.addChangeListener((event) => {
      if (event.tableName === 'users') {
        // Reload users when the table changes
        loadUsers();
      }
    });
    
    return () => subscription?.remove();
  }, [driver]);

  return <UserList users={users} />;
}

Migrations

For migrations, you can create a simple migration system:

interface Migration {
  version: number;
  up: (db: Kysely<any>) => Promise<void>;
}

const migrations: Migration[] = [
  {
    version: 1,
    up: async (db) => {
      await db.schema
        .createTable('migrations')
        .addColumn('version', 'integer', (col) => col.primaryKey())
        .addColumn('migrated_at', 'datetime', (col) => col.defaultTo('CURRENT_TIMESTAMP'))
        .execute();
    },
  },
  {
    version: 2,
    up: async (db) => {
      await db.schema
        .createTable('users')
        .addColumn('id', 'integer', (col) => col.primaryKey().autoIncrement())
        .addColumn('name', 'text', (col) => col.notNull())
        .addColumn('email', 'text', (col) => col.notNull().unique())
        .execute();
    },
  },
  // Add more migrations as needed
];

async function runMigrations(db: Kysely<any>) {
  // Ensure migrations table exists
  await migrations[0].up(db);
  
  // Get applied migrations
  const appliedMigrations = await db
    .selectFrom('migrations')
    .select('version')
    .execute();
  
  const appliedVersions = new Set(appliedMigrations.map(m => m.version));
  
  // Run pending migrations
  for (const migration of migrations) {
    if (!appliedVersions.has(migration.version)) {
      await migration.up(db);
      await db
        .insertInto('migrations')
        .values({ version: migration.version })
        .execute();
      console.log(`Migration ${migration.version} applied`);
    }
  }
}

Features

• Full TypeScript support with type-safe queries
• Support for all SQLite features available in Expo SQLite
• Transaction support
• Schema builder
• Query builder with support for joins, aggregations, etc.
• Compatible with Kysely's plugin system
• Debug logging for development
• SQLite pragma configuration
• Custom connection initialization
• Optimized single-row queries using getFirstAsync
• Prepared statements for parameterized queries (automatic performance optimization)
• Streaming query results for memory-efficient processing of large datasets
• Binary data support with Uint8Array
• Cross-platform support (iOS, Android, Web, macOS, Windows)
• Database change listeners

Debugging

Enable debug mode to see all SQL queries and their execution times:

const db = new Kysely<Database>({
  dialect: new ExpoSQLiteDialect({
    database: 'myapp.db',
    debug: true, // or __DEV__ for development only
  }),
});

Debug output includes:

• SQL query text
• Query parameters
• Execution time in milliseconds
• Whether the query used prepared statements (for parameterized queries)

Performance Tips

1. Use WAL mode for better concurrency (iOS/Android only):

pragmas: {
  journal_mode: 'WAL', // Note: wa-sqlite (web) doesn't support WAL mode
}

2. Enable foreign keys for data integrity:

pragmas: {
  foreign_keys: 1,
}

3. Optimize synchronous mode for better performance:

pragmas: {
  synchronous: 'NORMAL', // Faster than FULL, still safe
}

4. Use indexes for frequently queried columns:

await db.schema
  .createIndex('idx_users_email')
  .on('users')
  .column('email')
  .execute();

5. Prepared statements are automatic - The dialect automatically uses prepared statements for all parameterized queries, providing better performance for repeated queries with different values.

Platform Support

This dialect supports all platforms that Expo SQLite supports:

• ✅ iOS
• ✅ Android
• ✅ macOS (Expo SDK 46+)
• ✅ Windows (Expo SDK 51+)
• ✅ Web (Experimental)

Web Support

Expo SQLite has experimental web support. To use it in your web app:

// The dialect works the same on web
const db = new Kysely<Database>({
  dialect: new ExpoSQLiteDialect({
    database: 'myapp.db',
  }),
});

// Note: On web, the database is stored in IndexedDB

Requirements

• Expo SDK 48+ (for Expo SQLite v13+)
• React Native or Web
• Kysely 0.24.0+

Feature Requirements

Some features require specific Expo SQLite versions:

• Change listeners: Works on all platforms
• WAL mode: iOS/Android only (not supported in wa-sqlite/web)

License

MIT

Example App

A complete example Expo app demonstrating the usage of this dialect is available in the example/ directory. To run it:

cd example
npm install
npm start

The example app includes:

• Database setup and table creation
• CRUD operations (Create, Read, Update, Delete)
• Complex queries with joins
• Transaction examples
• TypeScript integration

TODO / Future Features

The following features are available in Expo SQLite but not yet implemented in this dialect:

• Exclusive Transactions - withExclusiveTransactionAsync() for uninterrupted transactions

These features would require deeper integration with Kysely's architecture or significant API changes.

Contributing

Contributions are welcome! Please feel free to submit a Pull Request.

Acknowledgments

This dialect is inspired by the knex-expo-sqlite-dialect project and adapted for use with Kysely.