> ## Documentation Index
> Fetch the complete documentation index at: https://docs.archil.com/llms.txt
> Use this file to discover all available pages before exploring further.

# SQLite

> SQLite databases on your Archil disk

The SQLite SDK allows you to create and manage SQLite databases on your Archil disk. It provides a simple interface for executing SQL queries and managing database files, enabling persistence of multi-tenant structured data for your applications.

Agents can use the SQLite SDK to store and retrieve structured data in a familiar SQL format, the ability to perform complex queries during long-running sessions and retrieve relevant context on-demand.

```bash theme={null}
npm install disk @archildata/sqlite
```

## Create and manage SQLite databases

To create a new SQLite database on your Archil disk, use the `createDatabase` function and pass in the disk and the name of the database file. You can then use the `getDatabase` function to retrieve the database later.

```ts theme={null}
import * as archil from "disk";
import { createDatabase, getDatabase } from "@archildata/sqlite";

const disk = archil.getDisk(process.env.ARCHIL_DISK_ID!);
const db = await createDatabase(disk, "apps/app-1/main.sqlite");
// ...
// get it back later
const db = await getDatabase(disk, "apps/app-1/main.sqlite");
```

`createDatabase` will create a new SQLite database on the disk if it doesn't exist, or return the existing database if it does. `getDatabase` will return an existing database.

## Execute SQL queries

You can execute SQL queries against each database using the `read` and `write` methods. The `read` method is used for queries that return data, while the `write` method is used for queries that modify data.

```ts theme={null}
import * as archil from "disk";
import { createDatabase, getDatabase } from "@archildata/sqlite";

const disk = await archil.getDisk(process.env.ARCHIL_DISK_ID!);
const db = await createDatabase(disk, "apps/app-1/main.sqlite");

// initialize DB
await db.write`CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT, email TEXT)`.run();

// create user
const username = "John Doe";
const email = "john@example.com";
await db.write`INSERT INTO users (name, email) VALUES (${username}, ${email})`.run();

// read users
const users = await db.read`SELECT * FROM users`.all();
```

<Info>
  Why is there a difference between `read` and `write`? `write` queries must mount the disk with a delegation to ensure it has exclusive write access to the database file, while `read` queries can be executed without a delegation in parallel.
</Info>

You can execute multiple queries in a single transaction using the `transaction` method. This ensures that either all queries succeed or none of them are applied.

```ts theme={null}
const [johnResult, janeResult] = await db.transaction([
  db.write`INSERT INTO users (name, email) VALUES ('John Doe', 'john@example.com')`.run(),
  db.write`INSERT INTO users (name, email) VALUES ('Jane Doe', 'jane@example.com')`.run()
]);
```