# Persistence with SQLite

> SqliteDb / SqliteStmt / SqliteCursor — CRUD, transactions, bulk writes and encryption.

Source: https://crossfly.dev/docs/data-and-services/storage-sqlite — CrossFly documentation. Full docs as one file: https://crossfly.dev/llms-full.txt

# Persistence with SQLite

The **canonical** way to persist structured data in CrossFly is the `dev.crossfly.sqlite` module —
a thin layer over the native **cfsqlite** lib (SQLite3MultipleCiphers). It replaces the old
`dev.crossfly.sql` (JDBC).

> **Run the database off the paint thread** and return to the UI with `Component.onUI(...)` (see
> [Background work](/docs/data-and-services/background-work)). `SqliteDb`, `SqliteStmt` and `SqliteCursor` are all
> `AutoCloseable` → use **try-with-resources**.

## Open / close
```java
try (SqliteDb db = SqliteDb.open("app.db")) {
   db.exec("CREATE TABLE IF NOT EXISTS cliente(id INTEGER PRIMARY KEY, nome TEXT, idade INTEGER)");
   // … use db …
}   // closes on its own
```
Factories: `SqliteDb.open(String)`, `open(String, int flags)`, `open(String, byte[] key)` (encrypted),
`openMemory()`.

## Parameterized CRUD
```java
// INSERT/UPDATE/DELETE → execute(sql, params...) returns the number of affected rows
db.execute("INSERT INTO cliente(nome, idade) VALUES(?, ?)", "Ana", 30);
db.execute("UPDATE cliente SET idade=? WHERE id=?", 31, 1);

// SELECT → query(sql, params...) returns a cursor
try (SqliteCursor c = db.query("SELECT id, nome, idade FROM cliente WHERE idade > ?", 18)) {
   while (c.next()) {
      long id    = c.getLong(0);      // ⚠️ cursor getters are 0-based
      String nome= c.getString(1);
      int idade  = c.getInt(2);
   }
}

// single value:
long total = db.queryLong("SELECT COUNT(*) FROM cliente", 0);
String nome = db.queryString("SELECT nome FROM cliente WHERE id=?", 1);
```

## ⚠️ The index gotcha: bind 1-based, getters 0-based
| Operation | Base | Example |
|---|---|---|
| parameter **bind** (`SqliteStmt`) | **1-based** | `stmt.bindString(1, nome)` |
| column **getter** (`SqliteCursor`) | **0-based** | `cursor.getString(0)` |

It is the module's most common mistake — the `?` markers start at **1**, the result columns at **0**.

## Transactions and bulk writes
```java
db.transaction(() -> {                      // automatic begin/commit (rollback on exception)
   db.execute("INSERT INTO cliente(nome) VALUES(?)", "A");
   db.execute("INSERT INTO cliente(nome) VALUES(?)", "B");
});

// manual begin/commit/rollback also exist:
db.begin(); /* … */ db.commit();

// bulk write (statement reused internally):
List<Object[]> linhas = List.of(new Object[]{"A", 1}, new Object[]{"B", 2});
db.bulkInsert("INSERT INTO cliente(nome, idade) VALUES(?, ?)", linhas);
```

## Reusable prepared statement
```java
try (SqliteStmt st = db.prepare("INSERT INTO cliente(nome, idade) VALUES(?, ?)")) {
   for (Cliente c : lista) {
      st.bindAll(c.nome, c.idade);   // or bindString(1, ...).bindInt(2, ...)
      st.step();
      st.reset();                    // reuses the statement
   }
}
```

## Optional encryption
```java
byte[] chave = obterChave();                       // your key (byte[])
try (SqliteDb db = SqliteDb.open("secreto.db", chave)) {   // ChaCha20-Poly1305 cipher by default
   // … encrypted database …
   db.rekey(novaChave);                            // changes the key (or removes it by passing another)
}
```
No key = plain SQLite, **zero overhead**. Supported ciphers include `CHACHA20` (default),
`SQLCIPHER`, `AES256CBC`, etc. (`Sqlite.Cipher`).

## Key/value table (replaces the old lightweight persistence)
Lightweight persistence (`Settings.appSettings`) has been **removed**. For flags/theme/last login, a
key/value table does the job:
```java
db.exec("CREATE TABLE IF NOT EXISTS kv(k TEXT PRIMARY KEY, v TEXT)");
db.execute("INSERT OR REPLACE INTO kv(k, v) VALUES(?, ?)", "tema", "escuro");
String tema = db.queryString("SELECT v FROM kv WHERE k=?", "tema");
```
See [Files and preferences](/docs/data-and-services/files-and-preferences).

## Best practices
- Always use **try-with-resources** (db, stmt, cursor) — avoids leaking native handles.
- Batch writes → `transaction(...)`/`bulkInsert(...)` (orders of magnitude faster).
- Database **off the paint thread**; results back with `onUI(...)`.

## Common mistakes
- Swapping the bases: `?` is **1-based**, cursor column is **0-based**.
- Running queries on the paint thread and freezing the UI.
- Forgetting to close cursor/statement (leaks a native resource).

## See also
- [State management](/docs/data-and-services/state-management) · [Background work](/docs/data-and-services/background-work)