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

``bash
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

$3

`typescript
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({
dialect: new ExpoSQLiteDialect({
database: 'myapp.db', // Database file name
}),
});

// With additional options
const db = new Kysely({
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({
dialect: new ExpoSQLiteDialect({
database: 'myapp.db',
enableChangeListener: true, // Enable reactive queries
finalizeUnusedStatementsBeforeClosing: true, // Clean up prepared statements
}),
});
`

$3

`typescript
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();
}
`

$3

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

$3

`typescript
// 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;
}
`

$3

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

return result;
}
`

$3

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

return result;
}
`




$3

Expo SQLite supports binary data through Uint8Array:

`typescript
// 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');
`

$3

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

$3

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

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

// 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', 'john@example.com']);
} finally {
await stmt.finalizeAsync();
}

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

await directDb.closeAsync();
`

$3

`typescript
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 };
});
}
`

$3

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

`typescript
// 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

$3

`typescript
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({
dialect: new ExpoSQLiteDialect({
database: 'myapp.db',
}),
});

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

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

setUsers(userList);
}

loadUsers();
}, []);

return (
data={users}
keyExtractor={(item) => item.id.toString()}
renderItem={({ item }) => (

{item.name}
{item.email}

)}
/>
);
}
`

$3

Enable reactive queries by listening to database changes:

`typescript
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({ 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([]);
const [driver, setDriver] = useState(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({ 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 ;
}
`

Migrations

For migrations, you can create a simple migration system:

`typescript
interface Migration {
version: number;
up: (db: Kysely) => Promise;
}

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) {
// 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:

`typescript
const db = new Kysely({
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):
`typescript
pragmas: {
journal_mode: 'WAL', // Note: wa-sqlite (web) doesn't support WAL mode
}
`

2. Enable foreign keys for data integrity:
`typescript
pragmas: {
foreign_keys: 1,
}
`

3. Optimize synchronous mode for better performance:
`typescript
pragmas: {
synchronous: 'NORMAL', // Faster than FULL, still safe
}
`

4. Use indexes for frequently queried columns:
`typescript
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)

$3

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

`typescript
// The dialect works the same on web
const db = new Kysely({
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+

$3

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:

`bash
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.