Kysely dialect for Expo SQLite
npm install kysely-expo-sqlite-dialectA Kysely dialect for Expo SQLite, allowing you to use the powerful Kysely query builder with SQLite databases in React Native/Expo applications.
``bash`
npm install kysely-expo-sqlite-dialect kysely expo-sqliteor
yarn add kysely-expo-sqlite-dialect kysely expo-sqliteor
pnpm add kysely-expo-sqlite-dialect kysely expo-sqlite
`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
}),
});
`
`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();
}
`
`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;
}
`
`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;
}
`
`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;
}
`typescript`
async function deleteUser(id: number) {
const result = await db
.deleteFrom('users')
.where('id', '=', id)
.execute();
return result;
}
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');
`
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() internallyrunAsync()
- INSERT/UPDATE/DELETE → Uses internally getEachAsync()
- Streaming queries → Uses for efficient row-by-row processing
- No hidden optimizations - The dialect doesn't try to be smart about query execution
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();
`
`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 };
});
}
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
`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 (
keyExtractor={(item) => item.id.toString()}
renderItem={({ item }) => (
)}
/>
);
}
`
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
// 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
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
// 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
}
`
For migrations, you can create a simple migration system:
`typescript
interface Migration {
version: number;
up: (db: Kysely
}
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);`
}
}
}
- 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 getFirstAsyncUint8Array
- Prepared statements for parameterized queries (automatic performance optimization)
- Streaming query results for memory-efficient processing of large datasets
- Binary data support with
- Cross-platform support (iOS, Android, Web, macOS, Windows)
- Database change listeners
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)
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.
This dialect supports all platforms that Expo SQLite supports:
- ✅ iOS
- ✅ Android
- ✅ macOS (Expo SDK 46+)
- ✅ Windows (Expo SDK 51+)
- ✅ Web (Experimental)
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
`
- Expo SDK 48+ (for Expo SQLite v13+)
- React Native or Web
- Kysely 0.24.0+
Some features require specific Expo SQLite versions:
- Change listeners: Works on all platforms
- WAL mode: iOS/Android only (not supported in wa-sqlite/web)
MIT
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
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.
Contributions are welcome! Please feel free to submit a Pull Request.
This dialect is inspired by the knex-expo-sqlite-dialect project and adapted for use with Kysely.