Make query builder pluggable to support third-party SQLite drivers like Drizzle

Currently LiveStore has its own SQLite driver (query builder), making it difficult to integrate with existing third-party SQLite drivers like Drizzle, Kysely, or Knex. This creates friction for developers who want to use LiveStore's reactive capabilities with their preferred database tooling.

Tasks

1. Investigate requirements for SQLite driver compatibility

• Analyze constraints: synchronous SQL generation, table tracking for reactivity
• Document the interface requirements for external SQLite drivers
• Identify which SQLite drivers are compatible

2. Design integration API for external SQLite drivers

• Create converter functions that translate external queries to LiveStore-compatible formats
• Design schema bridge utilities to map between LiveStore and external schemas
• Ensure proper table dependency tracking for reactive queries

3. Support client-document table creation

• Driver DSL should provide convenient methods for creating client-document tables
• Enable local-only state management patterns similar to existing LiveStore patterns
• Maintain compatibility with LiveStore's client-side data patterns
• Support typed document schemas with default values

4. Provide schema integration with Effect Schema preference

• SQLite drivers should preferably use Effect Schema as the primary type system for consistency
• Leverage Effect Schema's runtime validation, transformation, and type inference capabilities
• If not using Effect Schema, drivers must provide bi-directional schemas for encoding and decoding query results
• Ensure type-safe serialization/deserialization of complex data types (JSON, dates, etc.)
• Handle nullable fields, default values, and type coercion properly
• Enable seamless interop between driver schemas and LiveStore's internal type system

Proposed API Design

Note: This API design is currently a draft and will likely need further refinement during implementation.

Driver Interface Types

// Core SQLite driver interface
interface SQLiteDriver<TExternalTable = unknown, TExternalQuery = unknown> {
  // Convert external table definitions to LiveStore-compatible format
  fromExternalTable(externalTable: TExternalTable): TableDef.Any
  
  // Create materializers that can handle external queries
  materializers<TEvents extends Record<string, EventDef.AnyWithoutFn>>(
    events: TEvents,
    handlers: Record<string, (payload: any) => TExternalQuery>
  ): Record<string, Materializer<any>>
  
  // Create client documents
  clientDocument<TSchema extends Schema.Schema<any>>(config: {
    name: string
    schema: TSchema
    default: Schema.Schema.Type<TSchema>
  }): ClientDocument<TSchema>
  
  // Create state from external tables and materializers
  makeState(config: {
    tables: Record<string, TExternalTable>
    materializers: Record<string, Materializer<any>>
  }): InternalState
}

// External query requirement - must be able to generate SQL synchronously
interface ExternalQuery {
  toSQL(): {
    sql: string
    params: unknown[]
  }
}

// Driver factory function signature
declare function createSQLiteDriver<TTable, TQuery extends ExternalQuery>(): SQLiteDriver<TTable, TQuery>

Usage Example

import { drizzle } from 'drizzle-orm/sqlite-core'
import { text, integer, sqliteTable } from 'drizzle-orm/sqlite-core'
import { eq } from 'drizzle-orm'
import { createDrizzleSQLiteDriver } from '@livestore/drizzle-sqlite-driver'
import { Events, makeSchema } from '@livestore/livestore'

// Define Drizzle schema
const todos = sqliteTable('todos', {
  id: text('id').primaryKey(),
  text: text('text').notNull().default(''),
  completed: integer('completed', { mode: 'boolean' }).notNull().default(false),
  deletedAt: integer('deletedAt', { mode: 'timestamp' }),
})

const users = sqliteTable('users', {
  id: text('id').primaryKey(),
  name: text('name').notNull(),
  email: text('email').notNull(),
})

// Create SQLite driver
const driver = createDrizzleSQLiteDriver()

// Define events
const events = {
  todoCreated: Events.synced({
    name: 'v1.TodoCreated',
    schema: Schema.Struct({ id: Schema.String, text: Schema.String }),
  }),
  todoCompleted: Events.synced({
    name: 'v1.TodoCompleted', 
    schema: Schema.Struct({ id: Schema.String }),
  }),
}

// Materializers using driver helper and Drizzle syntax
const materializers = driver.materializers(events, {
  'v1.TodoCreated': ({ id, text }) => 
    drizzle.insert(todos).values({ id, text, completed: false }),
  
  'v1.TodoCompleted': ({ id }) => 
    drizzle.update(todos).set({ completed: true }).where(eq(todos.id, id)),
    
  'v1.TodoDeleted': ({ id }) =>
    drizzle.update(todos).set({ deletedAt: new Date() }).where(eq(todos.id, id)),
})

// Client-document support through driver DSL
const uiState = driver.clientDocument({
  name: 'uiState',
  schema: Schema.Struct({ 
    filter: Schema.Literal('all', 'active', 'completed'),
    selectedId: Schema.NullOr(Schema.String)
  }),
  default: { filter: 'all', selectedId: null }
})

// Create state using driver with raw Drizzle tables
const state = driver.makeState({ tables: { users, todos }, materializers })

// Create schema using makeSchema
export const schema = makeSchema({ events, state })

Goal

Enable developers to use their preferred SQLite driver while maintaining LiveStore's reactive capabilities, type safety, and performance characteristics.