# SQL queries

## Query builder

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


## `reference/state/sql-queries/query-builder.ts`

```ts filename="reference/state/sql-queries/query-builder.ts"
import { Schema } from 'effect'

import { State } from '@livestore/livestore'

const table = State.SQLite.table({
  name: 'my_table',
  columns: {
    id: State.SQLite.text({ primaryKey: true }),
    name: State.SQLite.text(),
    tags: State.SQLite.json({ schema: Schema.Array(Schema.String), default: [] }),
  },
})

// Read queries
table.select('name')
table.where('name', '=', 'Alice')
table.where({ name: 'Alice' })
table.orderBy('name', 'desc').offset(10).limit(10)
table.count().where('name', 'LIKE', '%Ali%')

// JSON array containment queries
// NOTE: These use SQLite's json_each() which cannot be indexed
table.where({ tags: { op: 'JSON_CONTAINS', value: 'important' } })
table.where({ tags: { op: 'JSON_NOT_CONTAINS', value: 'archived' } })

// Write queries
table.insert({ id: '123', name: 'Bob' })
table.update({ name: 'Alice' }).where({ id: '123' })
table.delete().where({ id: '123' })

// Upserts (insert or update on conflict)
table.insert({ id: '123', name: 'Charlie' }).onConflict('id', 'replace')
table.insert({ id: '456', name: 'Diana' }).onConflict('id', 'update', { name: 'Diana Updated' })
```

## 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.


## `reference/state/sql-queries/raw-sql.ts`

```ts filename="reference/state/sql-queries/raw-sql.ts"
/** biome-ignore-all lint/correctness/noUnusedVariables: docs snippet keeps reactive references */
// ---cut---
import { queryDb, Schema, State, sql } from '@livestore/livestore'

const table = State.SQLite.table({
  name: 'my_table',
  columns: {
    id: State.SQLite.text({ primaryKey: true }),
    name: State.SQLite.text(),
  },
})

const filtered$ = queryDb({
  query: sql`select * from my_table where name = 'Alice'`,
  schema: Schema.Array(table.rowSchema),
})

const count$ = queryDb({
  query: sql`select count(*) as count from my_table`,
  schema: Schema.Struct({ count: Schema.Number }).pipe(Schema.pluck('count'), Schema.Array, Schema.headOrElse()),
})
```

## JSON array containment

For JSON array columns, you can use `JSON_CONTAINS` and `JSON_NOT_CONTAINS` operators to check if an array contains (or doesn't contain) a specific value:

```ts
// Find items with a specific tag
table.where({ tags: { op: 'JSON_CONTAINS', value: 'important' } })

// Find items without a specific tag
table.where({ tags: { op: 'JSON_NOT_CONTAINS', value: 'archived' } })
```

:::caution[Performance consideration]
These operators use SQLite's `json_each()` table-valued function which **cannot be indexed** and requires a full table scan. For large tables with frequent lookups, consider denormalizing the data into a separate indexed table.
:::

## 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.