# SQLite

> Embedded SQLite from JavaScript and TypeScript with `elide:sqlite`: open a file or in-memory database, prepared statements, parameters, rows and transactions.

`elide:sqlite` is an embedded SQLite database with a synchronous API. It is built in: there is nothing to install and no
server to run.

```javascript
// sqlite.mjs
import { Database } from "elide:sqlite";

const db = new Database("app.db", { create: true });
db.exec("CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT NOT NULL, age INTEGER)");

const insert = db.prepare("INSERT INTO users (name, age) VALUES (?, ?)");
insert.run("Ada", 36);
db.prepare("INSERT INTO users (name, age) VALUES ($name, $age)").run({ $name: "Alan", $age: 41 });

const addAll = db.transaction((people) => {
  for (const p of people) insert.run(p.name, p.age);
  return people.length;
});
console.log(addAll([{ name: "Grace", age: 85 }, { name: "Edsger", age: 72 }]));

console.log(db.prepare("SELECT name, age FROM users WHERE age > ?").all(40));
console.log(db.prepare("SELECT name FROM users WHERE id = ?").get(1));
console.log(db.prepare("SELECT name, age FROM users ORDER BY age LIMIT 2").values());
console.log(db.query("SELECT count(*) AS n FROM users").get());
db.close();
```

```bash
elide run sqlite.mjs
```

```text
2
(3)[{name: "Alan", age: 41}, {name: "Grace", age: 85}, {name: "Edsger", age: 72}]
{name: "Ada"}
(2)[["Ada", 36], ["Alan", 41]]
{n: 4}
```

## Open a database

| Call | Opens |
| --- | --- |
| `new Database()`, `new Database(":memory:")` | A new in-memory database |
| `new Database("app.db", { create: true })` | `app.db`, created if missing |
| `new Database("app.db")` | An existing `app.db`; a missing file throws `Database file does not exist or is not readable` |
| `new Database("app.db", { readonly: true })` | An existing file; writes throw `[SQLITE_READONLY] Attempt to write a readonly database` |

## `Database`

| Member | Does |
| --- | --- |
| `exec(sql)` | Runs one statement; returns nothing. Text after the first statement is ignored. |
| `prepare(sql)` | Returns a statement. |
| `query(sql)` | Same as `prepare`. |
| `transaction(fn)` | Returns a function that runs `fn` in a transaction: it commits when `fn` returns and rolls back when `fn` throws. `fn` must be synchronous. `.deferred()`, `.immediate()` and `.exclusive()` return the same function with that lock mode. |
| `serialize()` | The database as an array of bytes. |
| `loadExtension(path)` | Loads a SQLite extension. |
| `close()` | Closes the database; later calls throw `Database is closed`. |

## Statements

| Member | Returns |
| --- | --- |
| `all(...params)` | Every row, as objects keyed by column name |
| `get(...params)` | The first row, or `null` |
| `values(...params)` | Every row, as arrays |
| `run(...params)` | Nothing; use `SELECT last_insert_rowid()` or `changes()` for counts |
| `bind(...params)` | The statement, with its parameters set |

Parameters are positional (`?`, `?1`) or named (`$name`, passed as `{ $name: value }`). A `NULL` column reads as
`undefined`.

## `node:sqlite` and `bun:sqlite`

`node:sqlite` and `bun:sqlite` export the same `Database` class as `elide:sqlite`. Code written for Node's `DatabaseSync` does not run:
`DatabaseSync` is not exported, and `run()` does not return `{ changes, lastInsertRowid }`.

## Samples

- [Embedded SQLite](https://elide.help/docs/code-samples#embedded-sqlite)
- [SQLite transactions](https://elide.help/docs/code-samples#sqlite-transactions)
- [Databases](https://elide.help/docs/api-database): the other drivers, and SQLite from Python and the JVM.