---
title: "Open an existing SQLite .db file in C#"
url: https://recursiveai.org/onelite/cookbook/open-existing-sqlite-database-csharp/
description: "Open an existing SQLite .db file in C# with OneLite and query it: a database made by the sqlite3 shell, switched to WAL mode, checked with OneLite’s own PRAGMA integrity_check, then written and read back in sqlite3."
version: "0.2.0-preview.2"
---

# Open an existing SQLite .db file in C#

Pass the path to `Database.OpenAsync` and query it. OneLite reads the SQLite file format directly, so a database made by the `sqlite3` shell, another app or another language opens as it is. `Database.OpenAsync` opens it in WAL mode, the happy-path default, and that switch stays with the file ([why](#why-wal)).

```shell
dotnet new console -o OpenShopDb && cd OpenShopDb
dotnet add package RecursiveAI.OneLite --version 0.2.0-preview.2
```

Make a database with the `sqlite3` shell (3.53.4, from sqlite.org), or use a `.db` file you already have:

```shell
sqlite3 shop.db "create table products
    (id integer primary key, name text not null, price real);
  insert into products (name, price)
    values ('Pen', 1.5), ('Notebook', 4.25), ('Ink', 12);" "pragma journal_mode"
```

```text
delete
```

`delete` is SQLite’s default, a rollback-journal mode.

Put this in `Program.cs` and run `dotnet run`:

```csharp
using RecursiveAI.OneLite;

// OpenAsync opens in WAL mode, so it switches this rollback-journal file to WAL.
await using var db = await Database.OpenAsync("shop.db");
Console.WriteLine(await db.ScalarAsync<string>($"pragma journal_mode=wal")); // says so

Console.WriteLine(await db.ScalarAsync<string>($"pragma integrity_check"));
foreach (Product p in await db.QueryAsync<Product>(
             $"select id, name, price from products order by price"))
    Console.WriteLine($"{p.Id} {p.Name,-9} {p.Price,6:0.00}");

// Write to it as well: one row
await db.ExecuteAsync(
    $"insert into products (name, price) values ({"Stapler"}, {7.8})");

[OneLiteRow]
public record Product(long Id, string Name, double Price);
```

```text
wal
ok
1 Pen         1.50
2 Notebook    4.25
3 Ink        12.00
```

OneLite’s own `PRAGMA integrity_check` checks the file `sqlite3` wrote and returns `ok`, and the query reads its rows. Records map by constructor parameter, matched to columns by name, ignoring case.

## Why WAL

- **It is the happy-path default.** In the words of [the happy-path docs](https://recursiveai.org/onelite/docs/happy-path/), `Database.OpenAsync` and `Database.OpenDefault` “open (creating) a database with write-ahead logging, `foreign_keys=ON` and a 5 s busy timeout”. `pragma journal_mode=wal` states it and prints `wal`. `Database.Open` and the ADO.NET provider keep SQLite’s rollback-journal default.
- **The switch is permanent.** From the same page: “opening an existing rollback-journal file with `OpenAsync`/`OpenDefault` switches that file to WAL for good (it is a property of the file, as `PRAGMA journal_mode=WAL` is in SQLite), so other programs that open it, `sqlite3` included, see a WAL database and need write access to the directory for the `-wal` and `-shm` files; pass `DatabaseOptions` to keep the file’s journal mode.” `sqlite3` 3.53.4 reads and writes it as usual, as the check below shows. SQLite older than 3.7.0 can’t open a WAL file at all.
- **Two more files while it is open.** In WAL mode the database keeps `shop.db-wal` and `shop.db-shm` beside `shop.db` while a connection has it open. They are part of the database: copy or move all three together, or close every connection first.

## Check it with sqlite3

Back in `sqlite3`, the row OneLite wrote is there, the file is in WAL mode and it checks clean:

```shell
sqlite3 shop.db "select * from products" "pragma journal_mode" "pragma integrity_check"
```

```text
1|Pen|1.5
2|Notebook|4.25
3|Ink|12.0
4|Stapler|7.8
wal
ok
```

## Limits

- A preview: APIs may change between previews.
- Native extensions (`load_extension`) are not supported. OneLite has FTS3/4/5, R*Tree, `dbstat` and `vec0`; in the [compatibility](https://recursiveai.org/onelite/docs/compatibility/) page’s words, “Geopoly, sessions and changesets, `carray` and the shell’s miscellaneous extensions are not implemented.”
- `vec0` is OneLite’s own, not sqlite-vec: in the words of [docs/vec.md](https://recursiveai.org/onelite/docs/vec/), “A `vec0` table written by OneLite can’t be used by sqlite-vec, and one written by sqlite-vec can’t be used by OneLite.”
- Encrypted (SQLCipher) files need their key: see [encryption](https://recursiveai.org/onelite/docs/encryption/).
- Sharing the file live with `sqlite3` processes was measured in WAL mode (“10,000 interleaved commits, consistent both ways”).

[Getting started](https://recursiveai.org/onelite/docs/getting-started/) · [Compatibility](https://recursiveai.org/onelite/docs/compatibility/) · [All recipes](https://recursiveai.org/onelite/cookbook/)