Skip to content
LiveStore Logo LiveStore (0.4.0-dev.14)

SQL Queries

Query builder

Section titled “Query builder”

LiveStore also provides a small query builder for the most common queries. The query builder automatically derives the appropriate result schema internally.

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


const 
const table: State.SQLite.TableDef<State.SQLite.SqliteTableDefForInput<"my_table", {
    readonly id: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: true;
        autoIncrement: false;
    };
    readonly name: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: false;
        autoIncrement: false;
    };
}>, State.SQLite.WithDefaults<{
    readonly id: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: true;
        autoIncrement: false;
    };
    readonly name: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: false;
        autoIncrement: false;
    };
}>, Schema<...>>
table = 
import State
State.
import SQLite
SQLite.
function table<"my_table", {
    readonly id: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: true;
        autoIncrement: false;
    };
    readonly name: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: false;
        autoIncrement: false;
    };
}, Partial<{
    indexes: Index[];
}>>(args: {
    name: "my_table";
    columns: {
        readonly id: {
            columnType: "text";
            schema: Schema<string, string, never>;
            default: None<never>;
            nullable: false;
            primaryKey: true;
            autoIncrement: false;
        };
        readonly name: {
            columnType: "text";
            schema: Schema<string, string, never>;
            default: None<never>;
            nullable: false;
            primaryKey: false;
            autoIncrement: false;
        };
    };
} & Partial<...>): State.SQLite.TableDef<...> (+2 overloads)
Creates a SQLite table definition from columns or an Effect Schema.


This function supports two main ways to define a table:


    

  1. Using explicit column definitions
    2. 

  2. Using an Effect Schema (either the name property needs to be provided or the schema needs to have a title/identifier)
    3. 



// Using explicit columns
const usersTable = State.SQLite.table({
  name: 'users',
  columns: {
    id: State.SQLite.text({ primaryKey: true }),
    name: State.SQLite.text({ nullable: false }),
    email: State.SQLite.text({ nullable: false }),
    age: State.SQLite.integer({ nullable: true }),
  },
})


// Using Effect Schema with annotations
import { Schema } from '@livestore/utils/effect'


const UserSchema = Schema.Struct({
  id: Schema.Int.pipe(State.SQLite.withPrimaryKey).pipe(State.SQLite.withAutoIncrement),
  email: Schema.String.pipe(State.SQLite.withUnique),
  name: Schema.String,
  active: Schema.Boolean.pipe(State.SQLite.withDefault(true)),
  createdAt: Schema.optional(Schema.Date),
})


// Option 1: With explicit name
const usersTable = State.SQLite.table({
  name: 'users',
  schema: UserSchema,
})


// Option 2: With name from schema annotation (title or identifier)
const AnnotatedUserSchema = UserSchema.annotations({ title: 'users' })
const usersTable2 = State.SQLite.table({
  schema: AnnotatedUserSchema,
})


// Adding indexes
const PostSchema = Schema.Struct({
  id: Schema.String.pipe(State.SQLite.withPrimaryKey),
  title: Schema.String,
  authorId: Schema.String,
  createdAt: Schema.Date,
}).annotations({ identifier: 'posts' })


const postsTable = State.SQLite.table({
  schema: PostSchema,
  indexes: [
    { name: 'idx_posts_author', columns: ['authorId'] },
    { name: 'idx_posts_created', columns: ['createdAt'], isUnique: false },
  ],
})
table({
  
name: "my_table"
name: 'my_table',
  
columns: {
    readonly id: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: true;
        autoIncrement: false;
    };
    readonly name: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: false;
        autoIncrement: false;
    };
}
columns: {
    
id: {
    columnType: "text";
    schema: Schema<string, string, never>;
    default: None<never>;
    nullable: false;
    primaryKey: true;
    autoIncrement: false;
}
id: 
import State
State.
import SQLite
SQLite.
const text: <string, string, false, typeof NoDefault, true, false>(args: {
    schema?: Schema<string, string, never>;
    default?: typeof NoDefault;
    nullable?: false;
    primaryKey?: true;
    autoIncrement?: false;
}) => {
    columnType: "text";
    schema: Schema<string, string, never>;
    default: None<never>;
    nullable: false;
    primaryKey: true;
    autoIncrement: false;
} (+1 overload)
text({ 
primaryKey?: true
primaryKey: true }),
    
name: {
    columnType: "text";
    schema: Schema<string, string, never>;
    default: None<never>;
    nullable: false;
    primaryKey: false;
    autoIncrement: false;
}
name: 
import State
State.
import SQLite
SQLite.
const text: () => {
    columnType: "text";
    schema: Schema<string, string, never>;
    default: None<never>;
    nullable: false;
    primaryKey: false;
    autoIncrement: false;
} (+1 overload)
text(),
  },
})


// Read queries
const table: State.SQLite.TableDef<State.SQLite.SqliteTableDefForInput<"my_table", {
    readonly id: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: true;
        autoIncrement: false;
    };
    readonly name: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: false;
        autoIncrement: false;
    };
}>, State.SQLite.WithDefaults<{
    readonly id: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: true;
        autoIncrement: false;
    };
    readonly name: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: false;
        autoIncrement: false;
    };
}>, Schema<...>>
table.
select: <"name">(pluckColumn: "name") => QueryBuilder<readonly string[], State.SQLite.TableDefBase<State.SQLite.SqliteTableDefForInput<"my_table", {
    readonly id: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: true;
        autoIncrement: false;
    };
    readonly name: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: false;
        autoIncrement: false;
    };
}>, State.SQLite.WithDefaults<{
    readonly id: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: true;
        autoIncrement: false;
    };
    readonly name: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: false;
        autoIncrement: false;
    };
}>>, "select" | ... 2 more ... | "row"> (+1 overload)
Selects and plucks a single column
select('name')
const table: State.SQLite.TableDef<State.SQLite.SqliteTableDefForInput<"my_table", {
    readonly id: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: true;
        autoIncrement: false;
    };
    readonly name: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: false;
        autoIncrement: false;
    };
}>, State.SQLite.WithDefaults<{
    readonly id: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: true;
        autoIncrement: false;
    };
    readonly name: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: false;
        autoIncrement: false;
    };
}>, Schema<...>>
table.
where: <"name">(col: "name", op: QueryBuilder<TResult, TTableDef extends State.SQLite.TableDefBase, TWithout extends QueryBuilder.ApiFeature = never>.WhereOps, value: string) => QueryBuilder<readonly {
    readonly id: string;
    readonly name: string;
}[], State.SQLite.TableDefBase<State.SQLite.SqliteTableDefForInput<"my_table", {
    readonly id: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: true;
        autoIncrement: false;
    };
    readonly name: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: false;
        autoIncrement: false;
    };
}>, State.SQLite.WithDefaults<...>>, "select" | "row"> (+2 overloads)
where('name', '=', 'Alice')
const table: State.SQLite.TableDef<State.SQLite.SqliteTableDefForInput<"my_table", {
    readonly id: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: true;
        autoIncrement: false;
    };
    readonly name: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: false;
        autoIncrement: false;
    };
}>, State.SQLite.WithDefaults<{
    readonly id: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: true;
        autoIncrement: false;
    };
    readonly name: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: false;
        autoIncrement: false;
    };
}>, Schema<...>>
table.
where: (params: Partial<{
    readonly id: string | {
        op: QueryBuilder<TResult, TTableDef extends State.SQLite.TableDefBase, TWithout extends QueryBuilder.ApiFeature = never>.WhereOps.SingleValue;
        value: string;
    } | {
        op: QueryBuilder.WhereOps.MultiValue;
        value: readonly string[];
    } | undefined;
    readonly name: string | {
        op: QueryBuilder.WhereOps.SingleValue;
        value: string;
    } | {
        op: QueryBuilder.WhereOps.MultiValue;
        value: readonly string[];
    } | undefined;
}>) => QueryBuilder<...> (+2 overloads)
where({ 
name?: string | {
    op: QueryBuilder.WhereOps.SingleValue;
    value: string;
} | {
    op: QueryBuilder.WhereOps.MultiValue;
    value: readonly string[];
} | undefined
name: 'Alice' })
const table: State.SQLite.TableDef<State.SQLite.SqliteTableDefForInput<"my_table", {
    readonly id: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: true;
        autoIncrement: false;
    };
    readonly name: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: false;
        autoIncrement: false;
    };
}>, State.SQLite.WithDefaults<{
    readonly id: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: true;
        autoIncrement: false;
    };
    readonly name: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: false;
        autoIncrement: false;
    };
}>, Schema<...>>
table.
orderBy: <"name">(col: "name", direction: "asc" | "desc") => QueryBuilder<readonly {
    readonly id: string;
    readonly name: string;
}[], State.SQLite.TableDefBase<State.SQLite.SqliteTableDefForInput<"my_table", {
    readonly id: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: true;
        autoIncrement: false;
    };
    readonly name: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: false;
        autoIncrement: false;
    };
}>, State.SQLite.WithDefaults<{
    readonly id: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: true;
        autoIncrement: false;
    };
    readonly name: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: false;
        autoIncrement: false;
    };
}>>, "onConflict" | "returning"> (+1 overload)
orderBy('name', 'desc').
offset: (offset: number) => QueryBuilder<readonly {
    readonly id: string;
    readonly name: string;
}[], State.SQLite.TableDefBase<State.SQLite.SqliteTableDefForInput<"my_table", {
    readonly id: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: true;
        autoIncrement: false;
    };
    readonly name: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: false;
        autoIncrement: false;
    };
}>, State.SQLite.WithDefaults<{
    readonly id: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: true;
        autoIncrement: false;
    };
    readonly name: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: false;
        autoIncrement: false;
    };
}>>, "orderBy" | ... 3 more ... | "row">
Example:


db.todos.offset(10)
offset(10).
limit: (limit: number) => QueryBuilder<readonly {
    readonly id: string;
    readonly name: string;
}[], State.SQLite.TableDefBase<State.SQLite.SqliteTableDefForInput<"my_table", {
    readonly id: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: true;
        autoIncrement: false;
    };
    readonly name: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: false;
        autoIncrement: false;
    };
}>, State.SQLite.WithDefaults<{
    readonly id: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: true;
        autoIncrement: false;
    };
    readonly name: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: false;
        autoIncrement: false;
    };
}>>, "orderBy" | ... 5 more ... | "row">
Example:


db.todos.limit(10)
limit(10)
const table: State.SQLite.TableDef<State.SQLite.SqliteTableDefForInput<"my_table", {
    readonly id: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: true;
        autoIncrement: false;
    };
    readonly name: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: false;
        autoIncrement: false;
    };
}>, State.SQLite.WithDefaults<{
    readonly id: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: true;
        autoIncrement: false;
    };
    readonly name: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: false;
        autoIncrement: false;
    };
}>, Schema<...>>
table.
count: () => QueryBuilder<number, State.SQLite.TableDefBase<State.SQLite.SqliteTableDefForInput<"my_table", {
    readonly id: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: true;
        autoIncrement: false;
    };
    readonly name: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: false;
        autoIncrement: false;
    };
}>, State.SQLite.WithDefaults<{
    readonly id: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: true;
        autoIncrement: false;
    };
    readonly name: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: false;
        autoIncrement: false;
    };
}>>, "select" | ... 7 more ... | "row">
Example:


db.todos.count()
db.todos.count().where('completed', true)
count().
where: <"name">(col: "name", op: QueryBuilder<TResult, TTableDef extends State.SQLite.TableDefBase, TWithout extends QueryBuilder.ApiFeature = never>.WhereOps, value: string) => QueryBuilder<number, State.SQLite.TableDefBase<State.SQLite.SqliteTableDefForInput<"my_table", {
    readonly id: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: true;
        autoIncrement: false;
    };
    readonly name: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: false;
        autoIncrement: false;
    };
}>, State.SQLite.WithDefaults<...>>, "select" | ... 7 more ... | "row"> (+2 overloads)
where('name', 'LIKE', '%Ali%')


// Write queries
const table: State.SQLite.TableDef<State.SQLite.SqliteTableDefForInput<"my_table", {
    readonly id: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: true;
        autoIncrement: false;
    };
    readonly name: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: false;
        autoIncrement: false;
    };
}>, State.SQLite.WithDefaults<{
    readonly id: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: true;
        autoIncrement: false;
    };
    readonly name: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: false;
        autoIncrement: false;
    };
}>, Schema<...>>
table.
insert: (values: {
    readonly name: string;
    readonly id: string;
}) => QueryBuilder<readonly {
    readonly id: string;
    readonly name: string;
}[], State.SQLite.TableDefBase<State.SQLite.SqliteTableDefForInput<"my_table", {
    readonly id: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: true;
        autoIncrement: false;
    };
    readonly name: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: false;
        autoIncrement: false;
    };
}>, State.SQLite.WithDefaults<{
    readonly id: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: true;
        autoIncrement: false;
    };
    readonly name: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: false;
        autoIncrement: false;
    };
}>>, "select" | ... 6 more ... | "row">
Insert a new row into the table


Example:


db.todos.insert({ id: '123', text: 'Buy milk', status: 'active' })
insert({ 
id: string
id: '123', 
name: string
name: 'Bob' })
const table: State.SQLite.TableDef<State.SQLite.SqliteTableDefForInput<"my_table", {
    readonly id: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: true;
        autoIncrement: false;
    };
    readonly name: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: false;
        autoIncrement: false;
    };
}>, State.SQLite.WithDefaults<{
    readonly id: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: true;
        autoIncrement: false;
    };
    readonly name: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: false;
        autoIncrement: false;
    };
}>, Schema<...>>
table.
update: (values: Partial<{
    readonly name: string;
    readonly id: string;
}>) => QueryBuilder<readonly {
    readonly id: string;
    readonly name: string;
}[], State.SQLite.TableDefBase<State.SQLite.SqliteTableDefForInput<"my_table", {
    readonly id: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: true;
        autoIncrement: false;
    };
    readonly name: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: false;
        autoIncrement: false;
    };
}>, State.SQLite.WithDefaults<{
    readonly id: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: true;
        autoIncrement: false;
    };
    readonly name: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: false;
        autoIncrement: false;
    };
}>>, "select" | ... 6 more ... | "row">
Update rows in the table that match the where clause


Example:


db.todos.update({ status: 'completed' }).where({ id: '123' })
update({ 
name?: string
name: 'Alice' }).
where: (params: Partial<{
    readonly id: string | {
        op: QueryBuilder<TResult, TTableDef extends State.SQLite.TableDefBase, TWithout extends QueryBuilder.ApiFeature = never>.WhereOps.SingleValue;
        value: string;
    } | {
        op: QueryBuilder.WhereOps.MultiValue;
        value: readonly string[];
    } | undefined;
    readonly name: string | {
        op: QueryBuilder.WhereOps.SingleValue;
        value: string;
    } | {
        op: QueryBuilder.WhereOps.MultiValue;
        value: readonly string[];
    } | undefined;
}>) => QueryBuilder<...> (+2 overloads)
where({ 
id?: string | {
    op: QueryBuilder.WhereOps.SingleValue;
    value: string;
} | {
    op: QueryBuilder.WhereOps.MultiValue;
    value: readonly string[];
} | undefined
id: '123' })
const table: State.SQLite.TableDef<State.SQLite.SqliteTableDefForInput<"my_table", {
    readonly id: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: true;
        autoIncrement: false;
    };
    readonly name: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: false;
        autoIncrement: false;
    };
}>, State.SQLite.WithDefaults<{
    readonly id: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: true;
        autoIncrement: false;
    };
    readonly name: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: false;
        autoIncrement: false;
    };
}>, Schema<...>>
table.
delete: () => QueryBuilder<readonly {
    readonly id: string;
    readonly name: string;
}[], State.SQLite.TableDefBase<State.SQLite.SqliteTableDefForInput<"my_table", {
    readonly id: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: true;
        autoIncrement: false;
    };
    readonly name: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: false;
        autoIncrement: false;
    };
}>, State.SQLite.WithDefaults<{
    readonly id: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: true;
        autoIncrement: false;
    };
    readonly name: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: false;
        autoIncrement: false;
    };
}>>, "select" | ... 6 more ... | "row">
Delete rows from the table that match the where clause


Example:


db.todos.delete().where({ status: 'completed' })


Note that it's generally recommended to do soft-deletes for synced apps.
delete().
where: (params: Partial<{
    readonly id: string | {
        op: QueryBuilder<TResult, TTableDef extends State.SQLite.TableDefBase, TWithout extends QueryBuilder.ApiFeature = never>.WhereOps.SingleValue;
        value: string;
    } | {
        op: QueryBuilder.WhereOps.MultiValue;
        value: readonly string[];
    } | undefined;
    readonly name: string | {
        op: QueryBuilder.WhereOps.SingleValue;
        value: string;
    } | {
        op: QueryBuilder.WhereOps.MultiValue;
        value: readonly string[];
    } | undefined;
}>) => QueryBuilder<...> (+2 overloads)
where({ 
id?: string | {
    op: QueryBuilder.WhereOps.SingleValue;
    value: string;
} | {
    op: QueryBuilder.WhereOps.MultiValue;
    value: readonly string[];
} | undefined
id: '123' })


const table: State.SQLite.TableDef<State.SQLite.SqliteTableDefForInput<"my_table", {
    readonly id: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: true;
        autoIncrement: false;
    };
    readonly name: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: false;
        autoIncrement: false;
    };
}>, State.SQLite.WithDefaults<{
    readonly id: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: true;
        autoIncrement: false;
    };
    readonly name: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: false;
        autoIncrement: false;
    };
}>, Schema<...>>
table.
insert: (values: {
    readonly name: string;
    readonly id: string;
}) => QueryBuilder<readonly {
    readonly id: string;
    readonly name: string;
}[], State.SQLite.TableDefBase<State.SQLite.SqliteTableDefForInput<"my_table", {
    readonly id: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: true;
        autoIncrement: false;
    };
    readonly name: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: false;
        autoIncrement: false;
    };
}>, State.SQLite.WithDefaults<{
    readonly id: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: true;
        autoIncrement: false;
    };
    readonly name: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: false;
        autoIncrement: false;
    };
}>>, "select" | ... 6 more ... | "row">
Insert a new row into the table


Example:


db.todos.insert({ id: '123', text: 'Buy milk', status: 'active' })
insert({ 
id: string
id: '123', 
name: string
name: 'Charlie' }).
onConflict: <"id">(target: "id", action: "ignore" | "replace") => QueryBuilder<readonly {
    readonly id: string;
    readonly name: string;
}[], State.SQLite.TableDefBase<State.SQLite.SqliteTableDefForInput<"my_table", {
    readonly id: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: true;
        autoIncrement: false;
    };
    readonly name: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: false;
        autoIncrement: false;
    };
}>, State.SQLite.WithDefaults<{
    readonly id: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: true;
        autoIncrement: false;
    };
    readonly name: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: false;
        autoIncrement: false;
    };
}>>, "select" | ... 6 more ... | "row"> (+1 overload)
onConflict('id', 'replace')
const table: State.SQLite.TableDef<State.SQLite.SqliteTableDefForInput<"my_table", {
    readonly id: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: true;
        autoIncrement: false;
    };
    readonly name: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: false;
        autoIncrement: false;
    };
}>, State.SQLite.WithDefaults<{
    readonly id: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: true;
        autoIncrement: false;
    };
    readonly name: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: false;
        autoIncrement: false;
    };
}>, Schema<...>>
table.
insert: (values: {
    readonly name: string;
    readonly id: string;
}) => QueryBuilder<readonly {
    readonly id: string;
    readonly name: string;
}[], State.SQLite.TableDefBase<State.SQLite.SqliteTableDefForInput<"my_table", {
    readonly id: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: true;
        autoIncrement: false;
    };
    readonly name: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: false;
        autoIncrement: false;
    };
}>, State.SQLite.WithDefaults<{
    readonly id: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: true;
        autoIncrement: false;
    };
    readonly name: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: false;
        autoIncrement: false;
    };
}>>, "select" | ... 6 more ... | "row">
Insert a new row into the table


Example:


db.todos.insert({ id: '123', text: 'Buy milk', status: 'active' })
insert({ 
id: string
id: '456', 
name: string
name: 'Diana' }).
onConflict: <"id">(target: "id", action: "update", updateValues: Partial<{
    readonly name: string;
    readonly id: string;
}>) => QueryBuilder<readonly {
    readonly id: string;
    readonly name: string;
}[], State.SQLite.TableDefBase<State.SQLite.SqliteTableDefForInput<"my_table", {
    readonly id: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: true;
        autoIncrement: false;
    };
    readonly name: {
        columnType: "text";
        schema: Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: false;
        autoIncrement: false;
    };
}>, State.SQLite.WithDefaults<...>>, "select" | ... 6 more ... | "row"> (+1 overload)
onConflict('id', 'update', { 
name?: string
name: 'Diana Updated' })

Raw SQL queries

Section titled “Raw SQL queries”

LiveStore supports arbitrary SQL queries on top of SQLite. In order for LiveStore to handle the query results correctly, you need to provide the result schema.

import { 
const queryDb: {
    <TResultSchema, TResult = TResultSchema>(queryInput: QueryInputRaw<TResultSchema, ReadonlyArray<any>> | QueryBuilder<TResultSchema, any, any>, options?: {
        map?: (rows: TResultSchema) => TResult;
        label?: string;
        deps?: DepKey;
    }): LiveQueryDef<TResult>;
    <TResultSchema, TResult = TResultSchema>(queryInput: ((get: GetAtomResult) => QueryInputRaw<TResultSchema, ReadonlyArray<any>>) | ((get: GetAtomResult) => QueryBuilder<TResultSchema, any, any>), options?: {
        map?: (rows: TResultSchema) => TResult;
        label?: string;
        deps?: DepKey;
    }): LiveQueryDef<TResult>;
}
NOTE queryDb is only supposed to read data. Don't use it to insert/update/delete data but use events instead.


When using contextual data when constructing the query, please make sure to include it in the deps option.
@example 
const todos$ = queryDb(tables.todos.where({ complete: true }))
@example 
// Group-by raw SQL query
const colorCounts$ = queryDb({
  query: sql`SELECT color, COUNT(*) as count FROM todos WHERE complete = ? GROUP BY color`,
  schema: Schema.Array(Schema.Struct({
    color: Schema.String,
    count: Schema.Number,
  })),
  bindValues: [1],
})
@example 
// Using contextual data when constructing the query
const makeFilteredQuery = (filter: string) =>
  queryDb(tables.todos.where({ title: { op: 'like', value: filter } }), { deps: [filter] })


const filteredTodos$ = makeFilteredQuery('buy coffee')
queryDb, 
import Schema
Schema, 
import State
State, 
const sql: (template: TemplateStringsArray, ...args: unknown[]) => string
This is a tag function for tagged literals.
it lets us get syntax highlighting on SQL queries in VSCode, but
doesn't do anything at runtime.
Code copied from: https://esdiscuss.org/topic/string-identity-template-tag
sql } from '@livestore/livestore'


  
name: "my_table"
name: 'my_table',
  
columns: {
    readonly id: {
        columnType: "text";
        schema: Schema.Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: true;
        autoIncrement: false;
    };
    readonly name: {
        columnType: "text";
        schema: Schema.Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: false;
        autoIncrement: false;
    };
}
columns: {
    
id: {
    columnType: "text";
    schema: Schema.Schema<string, string, never>;
    default: None<never>;
    nullable: false;
    primaryKey: true;
    autoIncrement: false;
}
id: 
import State
State.
import SQLite
SQLite.
const text: <string, string, false, typeof NoDefault, true, false>(args: {
    schema?: Schema.Schema<string, string, never>;
    default?: typeof NoDefault;
    nullable?: false;
    primaryKey?: true;
    autoIncrement?: false;
}) => {
    columnType: "text";
    schema: Schema.Schema<string, string, never>;
    default: None<never>;
    nullable: false;
    primaryKey: true;
    autoIncrement: false;
} (+1 overload)
text({ 
primaryKey?: true
primaryKey: true }),
    
name: {
    columnType: "text";
    schema: Schema.Schema<string, string, never>;
    default: None<never>;
    nullable: false;
    primaryKey: false;
    autoIncrement: false;
}
name: 
import State
State.
import SQLite
SQLite.
const text: () => {
    columnType: "text";
    schema: Schema.Schema<string, string, never>;
    default: None<never>;
    nullable: false;
    primaryKey: false;
    autoIncrement: false;
} (+1 overload)
text(),
  },
})


const 
const filtered$: LiveQueryDef<readonly {
    readonly id: string;
    readonly name: string;
}[], "def">
filtered$ = 
queryDb<readonly {
    readonly id: string;
    readonly name: string;
}[], readonly {
    readonly id: string;
    readonly name: string;
}[]>(queryInput: QueryInputRaw<readonly {
    readonly id: string;
    readonly name: string;
}[], readonly any[]> | QueryBuilder<readonly {
    readonly id: string;
    readonly name: string;
}[], any, any>, options?: {
    map?: (rows: readonly {
        readonly id: string;
        readonly name: string;
    }[]) => readonly {
        readonly id: string;
        readonly name: string;
    }[];
    label?: string;
    deps?: DepKey;
} | undefined): LiveQueryDef<readonly {
    readonly id: string;
    readonly name: string;
}[], "def"> (+1 overload)
NOTE queryDb is only supposed to read data. Don't use it to insert/update/delete data but use events instead.


When using contextual data when constructing the query, please make sure to include it in the deps option.
@example 
const todos$ = queryDb(tables.todos.where({ complete: true }))
@example 
// Group-by raw SQL query
const colorCounts$ = queryDb({
  query: sql`SELECT color, COUNT(*) as count FROM todos WHERE complete = ? GROUP BY color`,
  schema: Schema.Array(Schema.Struct({
    color: Schema.String,
    count: Schema.Number,
  })),
  bindValues: [1],
})
@example 
// Using contextual data when constructing the query
const makeFilteredQuery = (filter: string) =>
  queryDb(tables.todos.where({ title: { op: 'like', value: filter } }), { deps: [filter] })


const filteredTodos$ = makeFilteredQuery('buy coffee')
queryDb({
  
query: string
query: 
const sql: (template: TemplateStringsArray, ...args: unknown[]) => string
This is a tag function for tagged literals.
it lets us get syntax highlighting on SQL queries in VSCode, but
doesn't do anything at runtime.
Code copied from: https://esdiscuss.org/topic/string-identity-template-tag
sql`select * from my_table where name = 'Alice'`,
  
schema: Schema.Schema<readonly {
    readonly id: string;
    readonly name: string;
}[], readonly any[], never>
schema: 
import Schema
Schema.
Array<Schema.Schema<{
    readonly id: string;
    readonly name: string;
}, {
    readonly id: string;
    readonly name: string;
}, never>>(value: Schema.Schema<{
    readonly id: string;
    readonly name: string;
}, {
    readonly id: string;
    readonly name: string;
}, never>): Schema.Array$<Schema.Schema<{
    readonly id: string;
    readonly name: string;
}, {
    readonly id: string;
    readonly name: string;
}, never>>
export Array
@since ― 3.10.0
Array(
const table: State.SQLite.TableDef<State.SQLite.SqliteTableDefForInput<"my_table", {
    readonly id: {
        columnType: "text";
        schema: Schema.Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: true;
        autoIncrement: false;
    };
    readonly name: {
        columnType: "text";
        schema: Schema.Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: false;
        autoIncrement: false;
    };
}>, State.SQLite.WithDefaults<{
    readonly id: {
        columnType: "text";
        schema: Schema.Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: true;
        autoIncrement: false;
    };
    readonly name: {
        columnType: "text";
        schema: Schema.Schema<string, string, never>;
        default: None<never>;
        nullable: false;
        primaryKey: false;
        autoIncrement: false;
    };
}>, Schema.Schema<...>>
table.
rowSchema: Schema.Schema<{
    readonly id: string;
    readonly name: string;
}, {
    readonly id: string;
    readonly name: string;
}, never>
rowSchema),
})


const 
const count$: LiveQueryDef<number, "def">
count$ = 
queryDb<number, number>(queryInput: QueryInputRaw<number, readonly any[]> | QueryBuilder<number, any, any>, options?: {
    map?: (rows: number) => number;
    label?: string;
    deps?: DepKey;
} | undefined): LiveQueryDef<number, "def"> (+1 overload)
NOTE queryDb is only supposed to read data. Don't use it to insert/update/delete data but use events instead.


When using contextual data when constructing the query, please make sure to include it in the deps option.
@example 
const todos$ = queryDb(tables.todos.where({ complete: true }))
@example 
// Group-by raw SQL query
const colorCounts$ = queryDb({
  query: sql`SELECT color, COUNT(*) as count FROM todos WHERE complete = ? GROUP BY color`,
  schema: Schema.Array(Schema.Struct({
    color: Schema.String,
    count: Schema.Number,
  })),
  bindValues: [1],
})
@example 
// Using contextual data when constructing the query
const makeFilteredQuery = (filter: string) =>
  queryDb(tables.todos.where({ title: { op: 'like', value: filter } }), { deps: [filter] })


const filteredTodos$ = makeFilteredQuery('buy coffee')
queryDb({
  
query: string
query: 
const sql: (template: TemplateStringsArray, ...args: unknown[]) => string
This is a tag function for tagged literals.
it lets us get syntax highlighting on SQL queries in VSCode, but
doesn't do anything at runtime.
Code copied from: https://esdiscuss.org/topic/string-identity-template-tag
sql`select count(*) as count from my_table`,
  
schema: Schema.Schema<number, readonly any[], never>
schema: 
import Schema
Schema.
function Struct<{
    count: typeof Schema.Number;
}>(fields: {
    count: typeof Schema.Number;
}): Schema.Struct<{
    count: typeof Schema.Number;
}> (+1 overload)
@since ― 3.10.0
Struct({ 
count: typeof Schema.Number
count: 
import Schema
Schema.
class Number
export Number
@since ― 3.10.0
Number }).
Pipeable.pipe<Schema.Struct<{
    count: typeof Schema.Number;
}>, Schema.SchemaClass<number, {
    readonly count: number;
}, never>, Schema.Array$<Schema.SchemaClass<number, {
    readonly count: number;
}, never>>, Schema.transform<Schema.Array$<Schema.SchemaClass<number, {
    readonly count: number;
}, never>>, Schema.SchemaClass<number, number, never>>>(this: Schema.Struct<...>, ab: (_: Schema.Struct<{
    count: typeof Schema.Number;
}>) => Schema.SchemaClass<number, {
    readonly count: number;
}, never>, bc: (_: Schema.SchemaClass<...>) => Schema.Array$<...>, cd: (_: Schema.Array$<...>) => Schema.transform<...>): Schema.transform<...> (+21 overloads)
pipe(
import Schema
Schema.
const pluck: <{
    readonly count: number;
}, {
    readonly count: number;
}, "count">(key: "count") => <R>(schema: Schema.Schema<{
    readonly count: number;
}, {
    readonly count: number;
}, R>) => Schema.SchemaClass<number, {
    readonly count: number;
}, R> (+1 overload)
Given a schema Schema<A, I, R> and a key key: K, this function extracts a specific field from the A type,
producing a new schema that represents a transformation from the { readonly [key]: I[K] } type to A[K].
@example 
import * as Schema from "effect/Schema"


// ---------------------------------------------
// use case: pull out a single field from a
// struct through a transformation
// ---------------------------------------------


const mytable = Schema.Struct({
  column1: Schema.NumberFromString,
  column2: Schema.Number
})


// const pullOutColumn: S.Schema<number, {
//     readonly column1: string;
// }, never>
const pullOutColumn = mytable.pipe(Schema.pluck("column1"))


console.log(Schema.decodeUnknownEither(Schema.Array(pullOutColumn))([{ column1: "1", column2: 100 }, { column1: "2", column2: 300 }]))
// Output: { _id: 'Either', _tag: 'Right', right: [ 1, 2 ] }
@since ― 3.10.0
pluck('count'), 
import Schema
Schema.
const Array: <Value extends Schema.Schema.Any>(value: Value) => Schema.Array$<Value>
export Array
@since ― 3.10.0
Array, 
import Schema
Schema.
const headOrElse: <Schema.Array$<Schema.SchemaClass<number, {
    readonly count: number;
}, never>>, readonly number[]>(fallback?: LazyArg<number> | undefined) => (self: Schema.Array$<Schema.SchemaClass<number, {
    readonly count: number;
}, never>> & Schema.Schema<readonly number[], readonly {
    readonly count: number;
}[], never>) => Schema.transform<Schema.Array$<Schema.SchemaClass<number, {
    readonly count: number;
}, never>>, Schema.SchemaClass<number, number, never>> (+1 overload)
Retrieves the first element of a ReadonlyArray.


If the array is empty, it returns the fallback argument if provided; otherwise, it fails.
@since ― 3.10.0
headOrElse()),
})

Best Practices

Section titled “Best Practices”
  • Query results should be treated as immutable/read-only
  • For queries which could return many rows, it’s recommended to paginate the results
    • Usually both via paginated/virtualized rendering as well as paginated queries
      • You’ll get best query performance by using a WHERE clause over an indexed column combined with a LIMIT clause. Avoid OFFSET as it can be slow on large tables
  • For very large/complex queries, it can also make sense to implement incremental view maintenance (IVM) for your queries
    • You can for example do this by have a separate table which is a materialized version of your query results which you update manually (and ideally incrementally) as the underlying data changes.