Database API
Complete reference for SQLite database operations in your extension.
Overview
The Database API provides full SQLite access with automatic table namespacing, migrations, and optional Drizzle ORM integration.
Table Name Helper
// getTableName(name: string): string
// Get the full prefixed table name
const tableName = client.getTableName('users')
// Returns: "a1b2c3d4__my-extension__users"
// Use in queries
const users = await client.query(`SELECT * FROM ${tableName}`)
Query Methods
query()
// query<T>(sql: string, params?: unknown[]): Promise<T[]>
// Executes a SELECT query and returns rows
const tableName = client.getTableName('users')
const users = await client.query<User>(`SELECT * FROM ${tableName}`)
// With parameters
const user = await client.query<User>(
`SELECT * FROM ${tableName} WHERE id = ?`,
[userId]
)
// With multiple conditions
const filtered = await client.query<User>(
`SELECT * FROM ${tableName} WHERE role = ? AND active = ?`,
['admin', true]
)
select()
// select<T>(sql: string, params?: unknown[]): Promise<T[]>
// Alias for query() - use whichever you prefer
const postsTable = client.getTableName('posts')
const posts = await client.select<Post>(
`SELECT * FROM ${postsTable} WHERE author_id = ? ORDER BY created_at DESC`,
[authorId]
)
execute()
// execute(sql: string, params?: unknown[]): Promise<ExecuteResult>
// Executes INSERT, UPDATE, DELETE, CREATE, ALTER statements
const tableName = client.getTableName('users')
// CREATE TABLE
await client.execute(`
CREATE TABLE IF NOT EXISTS ${tableName} (
id TEXT PRIMARY KEY,
name TEXT NOT NULL,
email TEXT UNIQUE
)
`)
// INSERT
const result = await client.execute(
`INSERT INTO ${tableName} (id, name, email) VALUES (?, ?, ?)`,
[crypto.randomUUID(), 'John Doe', 'john@example.com']
)
console.log('Last insert ID:', result.lastInsertId)
console.log('Rows affected:', result.rowsAffected)
// UPDATE
await client.execute(
`UPDATE ${tableName} SET name = ? WHERE id = ?`,
['Jane Doe', userId]
)
// DELETE
await client.execute(`DELETE FROM ${tableName} WHERE id = ?`, [userId])
Always use parameterized queries (?) to prevent SQL injection.
Helper Methods
These convenience methods simplify common operations:
insert()
// insert(table: string, data: Record<string, unknown>): Promise<number>
// Helper for inserting a record, returns last insert ID
const tableName = client.getTableName('users')
const id = await client.insert(tableName, {
id: crypto.randomUUID(),
name: 'John Doe',
email: 'john@example.com',
created_at: new Date().toISOString()
})
update()
// update(table: string, data: Record<string, unknown>,
// where: string, whereParams?: unknown[]): Promise<number>
// Helper for updating records, returns rows affected
const tableName = client.getTableName('users')
const rowsAffected = await client.update(
tableName,
{ name: 'Jane Doe', updated_at: new Date().toISOString() },
'id = ?',
[userId]
)
// Multiple conditions
await client.update(
tableName,
{ active: false },
'role = ? AND last_login < ?',
['guest', thirtyDaysAgo]
)
delete()
// delete(table: string, where: string, whereParams?: unknown[]): Promise<number>
// Helper for deleting records, returns rows deleted
const tableName = client.getTableName('users')
const sessionsTable = client.getTableName('sessions')
const rowsDeleted = await client.delete(tableName, 'id = ?', [userId])
// Delete multiple
await client.delete(sessionsTable, 'expires_at < ?', [Date.now()])
count()
// count(table: string, where?: string, whereParams?: unknown[]): Promise<number>
// Count records in a table
const tableName = client.getTableName('users')
const totalUsers = await client.count(tableName)
const activeUsers = await client.count(
tableName,
'active = ?',
[true]
)
Transactions
Execute multiple statements atomically:
// transaction(statements: string[]): Promise<void>
// Execute multiple statements in a transaction
const ordersTable = client.getTableName('orders')
const inventoryTable = client.getTableName('inventory')
const orderItemsTable = client.getTableName('order_items')
await client.transaction([
`INSERT INTO ${ordersTable} (id, user_id, total) VALUES ('${orderId}', '${userId}', ${total})`,
`UPDATE ${inventoryTable} SET quantity = quantity - ${qty} WHERE product_id = '${productId}'`,
`INSERT INTO ${orderItemsTable} (order_id, product_id, quantity) VALUES ('${orderId}', '${productId}', ${qty})`
])
If any statement fails, all changes are rolled back.
Migrations
Register and run database migrations. Migrations are tracked and only run once:
// registerMigrationsAsync(version: string, migrations: Migration[]): Promise<MigrationResult>
// Register and run database migrations
interface Migration {
name: string
sql: string
}
const tableName = client.getTableName('users')
const result = await client.registerMigrationsAsync('1.0.0', [
{
name: '001_create_users',
sql: `
CREATE TABLE IF NOT EXISTS ${tableName} (
id TEXT PRIMARY KEY,
name TEXT NOT NULL
)
`
},
{
name: '002_add_email',
sql: `ALTER TABLE ${tableName} ADD COLUMN email TEXT`
}
])
console.log('Applied:', result.appliedCount)
console.log('Already applied:', result.alreadyAppliedCount)
Drizzle ORM
For type-safe database access, use the built-in Drizzle ORM integration:
import { sqliteTable, text, integer } from 'drizzle-orm/sqlite-core'
import { eq, and, desc } from 'drizzle-orm'
// Define schema
const users = sqliteTable('users', {
id: text('id').primaryKey(),
name: text('name').notNull(),
email: text('email').unique(),
role: text('role').default('user'),
createdAt: text('created_at').default('CURRENT_TIMESTAMP'),
})
// Initialize Drizzle
const db = client.initializeDatabase({ users })
// Type-safe queries
const allUsers = await db.select().from(users)
const admins = await db
.select()
.from(users)
.where(eq(users.role, 'admin'))
.orderBy(desc(users.createdAt))
// Insert
await db.insert(users).values({
id: crypto.randomUUID(),
name: 'John',
email: 'john@example.com'
})
// Update
await db
.update(users)
.set({ role: 'admin' })
.where(eq(users.id, userId))
// Delete
await db.delete(users).where(eq(users.id, userId))
Type Definitions
TypeScript interfaces for database operations:
interface ExecuteResult {
rowsAffected: number
lastInsertId?: number
}
interface Migration {
name: string
sql: string
}
interface MigrationResult {
appliedCount: number
alreadyAppliedCount: number
appliedMigrations: string[]
}
interface DatabaseTableInfo {
name: string
columns: DatabaseColumnInfo[]
}
interface DatabaseColumnInfo {
name: string
type: string // 'TEXT', 'INTEGER', 'REAL', 'BLOB'
notNull: boolean
defaultValue?: unknown
primaryKey: boolean
}