# Turso

> How to use Reylo with Turso. Queries on Turso Cloud through the libSQL client, migrations on Turso Cloud, the local file the type check reads, libSQL's older SQLite, libSQL's vectors, and Turso's own engine.

Reylo on Turso Cloud and Turso's own engine

Turso Cloud runs libSQL, a fork of SQLite, so everything on the [SQLite](https://reylo.dev/sqlite) page holds for it.

| | |
| --- | --- |
| Dialect | SQLite |
| The type check reads | A local SQLite file with your schema, never a `libsql://` URL |
| Turso Cloud | `@libsql/client`, with `.run()` |
| Turso's own engine | `@tursodatabase/database`, with `.run()`, and `.runSync()` on its compat `Database` |
| Migrations | [`reylo migrate`](https://reylo.dev/migrate/docs), on Turso Cloud through its `libsql://` URL |
| SQLite versions | Reylo's 3.53, and the SQLite of each driver your `package.json` lists: 3.45.1 for `@libsql/client` |
| libSQL's own SQL | Vector columns and indexes, the vector functions, `vector_top_k` and `alter table … alter column` ([Vectors](https://reylo.dev/turso#vectors)) |

**View the schema behind the examples**

```sql
create table users (
  id integer primary key,
  name text not null,
  email text,
  settings text not null default '{}'
) strict;

create table docs (
  id integer primary key,
  title text not null,
  emb F32_BLOB(3)
);
create index docs_emb on docs (libsql_vector_idx(emb));
```

## Turso Cloud

Pass an `@libsql/client` client, made with your database's URL and an auth token, or a transaction taken from it:

`main.ts`:

```ts
import { createClient } from "@libsql/client";
import addUser from "./add_user.sql";
import findUser from "./find_user.sql";

const db = createClient({
  url: process.env.TURSO_DATABASE_URL!, // libsql://…
  authToken: process.env.TURSO_AUTH_TOKEN,
});

const user = await findUser.run(db, { id: 1 });

// both inserts land, or neither
const tx = await db.transaction("write");
await addUser.run(tx, { name: "Ada" });
await addUser.run(tx, { name: "Grace" });
await tx.commit();
```

`find_user.sql`:

```sql
select id, name, email from users where id = :id
```

`add_user.sql`:

```sql
insert into users (name) values (:name)
```

- The client answers through a promise, so use `.run()`, not `.runSync()`.
- With the default `intMode`, libSQL throws a `RangeError` on an integer past 2^53. Set `intMode: "bigint"` to get it exact.
- A Drizzle database over `@libsql/client` works too ([Drizzle](https://reylo.dev/sqlite#drizzle)).

## Local database

Make a local SQLite file from a dump of your Turso database:

```sh
turso db shell app .dump > dump.sql
sqlite3 dev.db < dump.sql
```

Or run your migrations into a new file:

```sh
for file in migrations/*.sql; do sqlite3 dev.db < "$file"; done
```

Point [`direct_url`](https://reylo.dev/configuration#direct_url) at it:

`.reylo.yaml`:

```yaml
# the path starts at package.json
direct_url: file:./dev.db
```

- A `DATABASE_URL` that holds your `libsql://` URL fails the type check, which reads only `file:` and `sqlite:` URLs for SQLite. A `direct_url` wins over it.
- In development, your app can open the same file: `createClient({ url: "file:dev.db" })` needs no auth token.
- In CI, run your migrations into the file before the type check ([Deployment](https://reylo.dev/deployment#sqlite)). The job needs no Turso token.

## Migrations

Run [Reylo Migrate](https://reylo.dev/migrate/docs) on Turso Cloud with your database's URL, its auth token in `authToken`:

```sh
npx reylo migrate deploy --url "libsql://app-acme.turso.io?authToken=$TURSO_AUTH_TOKEN"
```

To make a token, run `turso db tokens create app`. Keep `direct_url` on your local file, so the type check and `reylo migrate dev` stay on it, and pass `--url` to reach Turso Cloud:

`.github/workflows/deploy.yml`:

```yaml
steps:
  # ...
  - run: npx reylo migrate deploy --url "$TURSO_DATABASE_URL?authToken=$TURSO_AUTH_TOKEN"
    env:
      TURSO_DATABASE_URL: ${{ secrets.TURSO_DATABASE_URL }}
      TURSO_AUTH_TOKEN: ${{ secrets.TURSO_AUTH_TOKEN }}
```

- Every `reylo migrate` and `reylo db` command runs on Turso Cloud, and on any libSQL server: a `libsql://`, `https://` or `http://` URL, as `@libsql/client` takes it. Add `?tls=0` to a `libsql://` URL for a server without TLS.
- From code, pass the same URL: `migrate({ url })` ([API](https://reylo.dev/migrate/docs#api)).
- Shadow databases are in memory, on libSQL's own engine as Turso Cloud runs it: SQLite 3.47.0, libSQL's SQL, and the functions Turso Cloud adds, such as `floor`, `percentile`, `uuid4` and `regexp_like`.
- Migrations can use libSQL's own SQL, such as a vector index (`libsql_vector_idx`) or `alter table … alter column`. When your migrations or `schema.sql` do, `reylo migrate dev` and every other command open your local file on libSQL's engine too.
- Each migration runs in one transaction with its history row. Two deploys on one machine take turns; two on different machines at once don't, and one of them fails, changing nothing.

## SQLite version

The SQLite in `@libsql/client` is 3.45.1, older than Reylo's 3.53. With `@libsql/client` in your `package.json`, the type check also runs each query on it:

`package.json`:

```json
{
  "dependencies": {
    "@libsql/client": "^0.18.0"
  }
}
```

A query it refuses is a type error, with its own message:

```text
queries/user_settings.sql(1,12): error reylo6: @libsql/client runs SQLite 3.45.1, which refuses this statement: no such function: json_pretty
```

It refuses `json_pretty`, which came in SQLite 3.46, and `iif` with two arguments, which came in 3.48. [Functions](https://reylo.dev/sqlite#functions) has the rest.

## Vectors

Reylo types libSQL's vector SQL. Declare a column as a vector, such as `F32_BLOB(3)`, index it with `libsql_vector_idx`, and search it with `vector_top_k`:

`main.ts`:

```ts
import { createClient } from "@libsql/client";
import addDoc from "./add_doc.sql";
import nearestDocs from "./nearest_docs.sql";

const db = createClient({ url: "file:dev.db" });

await addDoc.run(db, { title: "Ada", embedding: "[0.1,0.2,0.3]" });
const docs = await nearestDocs.run(db, { q: "[0.1,0.2,0.3]" });
```

`nearest_docs.sql`:

```sql
select docs.id, docs.title
from vector_top_k('docs_emb', vector32(:q), 5) as top
join docs on docs.id = top.id
```

`add_doc.sql`:

```sql
insert into docs (title, emb) values (:title, vector32(:embedding))
```

- A vector parameter takes the vector's text, as `'[0.1,0.2,0.3]'`. The count of `vector_top_k` takes an integer.
- Each row of `vector_top_k` is the `id` of a row of the indexed table: its rowid, or the primary key of a `WITHOUT ROWID` table.
- A distance, from `vector_distance_cos` or `vector_distance_l2`, is `number | null`, since it's `NULL` where it can't be computed, as for a vector of zeros.
- A vector function fails on a `NULL` vector, where most SQLite functions return `NULL`. Leave rows without one out with `where emb is not null`.
- A vector column is typed as a `BLOB` column outside a `STRICT` table, since libSQL refuses vector types in a `STRICT` table. `.run()` returns a vector as a `Uint8Array`, and `vector_extract(emb)` returns its text.
- A driver whose SQLite has no vectors, such as `better-sqlite3`, refuses these queries in the type check. Turso's own engine can't open a schema with a vector index, so the type check doesn't run queries on it then.
- `alter table … alter column` type-checks as a statement that returns nothing.

## Turso's own engine

Turso's own engine, `@tursodatabase/database`, runs in your process on a database file. Pass the database `connect()` resolves to, or the transaction `.transactionAsync()` hands its callback:

`main.ts`:

```ts
import { connect } from "@tursodatabase/database";
import { Database } from "@tursodatabase/database/compat";
import addUser from "./add_user.sql";
import findUser from "./find_user.sql";

const db = await connect("dev.db");
const user = await findUser.run(db, { id: 1 });

// both inserts land, or neither
await db.transactionAsync(async (tx) => {
  await addUser.run(tx, { name: "Ada" });
  await addUser.run(tx, { name: "Grace" });
})();

// synchronous
const compat = new Database("dev.db");
const sameUser = findUser.runSync(compat, { id: 1 });
```

- The `Database` of `@tursodatabase/database/compat` is synchronous, so `.runSync()` works on it, inside its `db.transaction(fn)` as well.
- The type check reads the same file, through `direct_url` as above.
- With `@tursodatabase/database` in your `package.json`, the type check also runs each query on Turso's engine, unless your schema has an FTS5 table or a vector index, which the engine can't open.
- From 0.8 on, its errors carry no code. With [`.try()`](https://reylo.dev/typescript#try), `code` is `undefined` and only `message` names the failure.
- An integer past 2^53 comes back exact.
