SQLite State Schema

LiveStore provides a schema definition language for defining your database tables and mutation definitions using explicit column configurations. LiveStore automatically migrates your database schema when you change your schema definitions.

Alternative Approach: You can also define tables using Effect Schema with annotations for type-safe schema definitions.

Example

schema.ts

import { Events, makeSchema, Schema, SessionIdSymbol, State } from '@livestore/livestore'

// You can model your state as SQLite tables (https://docs.livestore.dev/reference/state/sqlite-schema)
export const tables = {
  todos: State.SQLite.table({
    name: 'todos',
    columns: {
      id: State.SQLite.text({ primaryKey: true }),
      text: State.SQLite.text({ default: '' }),
      completed: State.SQLite.boolean({ default: false }),
      deletedAt: State.SQLite.integer({ nullable: true, schema: Schema.DateFromNumber }),
    },
  }),
  // Client documents can be used for local-only state (e.g. form inputs)
  uiState: State.SQLite.clientDocument({
    name: 'uiState',
    schema: Schema.Struct({ newTodoText: Schema.String, filter: Schema.Literal('all', 'active', 'completed') }),
    default: { id: SessionIdSymbol, value: { newTodoText: '', filter: 'all' } },
  }),
}

// Events describe data changes (https://docs.livestore.dev/reference/events)
export 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 }),
  }),
  todoUncompleted: Events.synced({
    name: 'v1.TodoUncompleted',
    schema: Schema.Struct({ id: Schema.String }),
  }),
  todoDeleted: Events.synced({
    name: 'v1.TodoDeleted',
    schema: Schema.Struct({ id: Schema.String, deletedAt: Schema.Date }),
  }),
  todoClearedCompleted: Events.synced({
    name: 'v1.TodoClearedCompleted',
    schema: Schema.Struct({ deletedAt: Schema.Date }),
  }),
  uiStateSet: tables.uiState.set,
}

// Materializers are used to map events to state (https://docs.livestore.dev/reference/state/materializers)
const materializers = State.SQLite.materializers(events, {
  'v1.TodoCreated': ({ id, text }) => tables.todos.insert({ id, text, completed: false }),
  'v1.TodoCompleted': ({ id }) => tables.todos.update({ completed: true }).where({ id }),
  'v1.TodoUncompleted': ({ id }) => tables.todos.update({ completed: false }).where({ id }),
  'v1.TodoDeleted': ({ id, deletedAt }) => tables.todos.update({ deletedAt }).where({ id }),
  'v1.TodoClearedCompleted': ({ deletedAt }) => tables.todos.update({ deletedAt }).where({ completed: true }),
})

const state = State.SQLite.makeState({ tables, materializers })

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

Defining Tables

Define SQLite tables using explicit column definitions:

export const userTable = State.SQLite.table({
  name: 'users',
  columns: {
    id: State.SQLite.text({ primaryKey: true }),
    email: State.SQLite.text({ unique: true }),
    name: State.SQLite.text(),
    age: State.SQLite.integer({ default: 0 }),
    isActive: State.SQLite.boolean({ default: true }),
    metadata: State.SQLite.json({ nullable: true }),
  }
})

Column Types

You can use these column types when defining tables:

Core SQLite column types

• State.SQLite.text: A text field, returns string.
• State.SQLite.integer: An integer field, returns number.
• State.SQLite.real: A real field (floating point number), returns number.
• State.SQLite.blob: A blob field (binary data), returns Uint8Array.

Higher level column types

• State.SQLite.boolean: An integer field that stores 0 for false and 1 for true and returns a boolean.
• State.SQLite.json: A text field that stores a stringified JSON object and returns a decoded JSON value.
• State.SQLite.datetime: A text field that stores dates as ISO 8601 strings and returns a Date.
• State.SQLite.datetimeInteger: A integer field that stores dates as the number of milliseconds since the epoch and returns a Date.

Custom column schemas

You can also provide a custom schema for a column which is used to automatically encode and decode the column value.

Example: JSON-encoded struct

import { State, Schema } from '@livestore/livestore'

export const UserMetadata = Schema.Struct({
  petName: Schema.String,
  favoriteColor: Schema.Literal('red', 'blue', 'green'),
 })

export const userTable = State.SQLite.table({
  name: 'user',
  columns: {
    id: State.SQLite.text({ primaryKey: true }),
    name: State.SQLite.text(),
    metadata: State.SQLite.json({ schema: UserMetadata }),
  }
})

Schema migrations

Migration strategies:

• auto: Automatically migrate the database to the newest schema and rematerializes the state from the eventlog.
• manual: Manually migrate the database to the newest schema.

Client documents

• Meant for convenience
• Client-only
• Goal: Similar ease of use as React.useState
• When schema changes in a non-backwards compatible way, previous events are dropped and the state is reset
  • Don’t use client documents for sensitive data which must not be lost
• Implies
  • Table with id and value columns
  • ${MyTable}Set event + materializer (which are auto-registered)

Basic usage

import { Schema, SessionIdSymbol, State } from '@livestore/livestore'

// Define a client document for simple UI state
export const uiState = State.SQLite.clientDocument({
  name: 'UiState',
  schema: Schema.Struct({
    newTodoText: Schema.String,
    filter: Schema.Literal('all', 'active', 'completed'),
  }),
  // Using a default id lets you omit the id in get/set calls
  default: { id: SessionIdSymbol, value: { newTodoText: '', filter: 'all' } },
})

// Get value (uses default id)
const value = store.query(uiState.get())

// Update value (partial updates are merged for Struct schemas by default)
store.commit(uiState.set({ newTodoText: 'Buy milk' }))

// In React
const [state, setState] = store.useClientDocument(uiState)
setState({ filter: 'active' })

KV-style client document

Sometimes you want a simple key-value store for arbitrary values without partial merging. You can model this by using Schema.Any as the value schema. With Schema.Any, updates fully replace the stored value (no partial merge semantics).

import { Schema, SessionIdSymbol, State } from '@livestore/livestore'

export const kv = State.SQLite.clientDocument({
  name: 'Kv',
  // Any JSON-serializable value; updates will fully replace the value
  schema: Schema.Any,
  // Choose an appropriate default value
  default: { value: null },
})

// Usage with the store API

// Get value
const value = store.query(kv.get('some-id'))

// Set value
store.commit(kv.set({ some: 'payload' }, 'some-id'))
store.commit(kv.set(123, 'some-id'))

// Usage with React
const [value, setValue] = store.useClientDocument(kv, 'some-id')
setValue('hello')
setValue({ b: 2 }) // replaces the previous value

Column Types

You can use these column types:

Core SQLite column types

• State.SQLite.text: A text field, returns string.
• State.SQLite.integer: An integer field, returns number.
• State.SQLite.real: A real field (floating point number), returns number.
• State.SQLite.blob: A blob field (binary data), returns Uint8Array.

Higher level column types

• State.SQLite.boolean: An integer field that stores 0 for false and 1 for true and returns a boolean.
• State.SQLite.json: A text field that stores a stringified JSON object and returns a decoded JSON value.
• State.SQLite.datetime: A text field that stores dates as ISO 8601 strings and returns a Date.
• State.SQLite.datetimeInteger: A integer field that stores dates as the number of milliseconds since the epoch and returns a Date.

Custom column schemas

You can also provide a custom schema for a column which is used to automatically encode and decode the column value.

Example: JSON-encoded struct

import { State, Schema } from '@livestore/livestore'

export const UserMetadata = Schema.Struct({
  petName: Schema.String,
  favoriteColor: Schema.Literal('red', 'blue', 'green'),
 })

export const userTable = State.SQLite.table({
  name: 'user',
  columns: {
    id: State.SQLite.text({ primaryKey: true }),
    name: State.SQLite.text(),
    metadata: State.SQLite.json({ schema: UserMetadata }),
  }
})

Best Practices

Column Configuration

• Use appropriate SQLite column types for your data (text, integer, real, blob)
• Set primaryKey: true for primary key columns
• Use nullable: true for columns that can contain NULL values
• Provide meaningful default values where appropriate
• Use unique: true for columns that should have unique constraints

Schema Design

• Choose column types that match your data requirements
• Use custom schemas with State.SQLite.json() for complex data structures
• Group related table definitions in the same module
• Use descriptive table and column names

General Practices

• It’s usually recommend to not distinguish between app state vs app data but rather keep all state in LiveStore.
  • This means you’ll rarely use React.useState when using LiveStore
• In some cases for “fast changing values” it can make sense to keep a version of a state value outside of LiveStore with a reactive setter for React and a debounced setter for LiveStore to avoid excessive LiveStore mutations. Cases where this can make sense can include:
  • Text input / rich text editing
  • Scroll position tracking, resize events, move/drag events
  • …