---
title: "Join two SQLite databases in C# with ATTACH"
url: https://recursiveai.org/onelite/cookbook/join-attached-sqlite-databases/
description: "Attach a second SQLite database in C#, join schema-qualified tables and detach it with OneLite. Includes two tiny sample databases and runnable code."
version: "1.0.0"
---

# Join two SQLite databases in C# with ATTACH

**OneLite** is a free, open-source SQLite-compatible database for .NET from Recursive AI. MIT or Apache-2.0, no native dependencies.

[NuGet](https://www.nuget.org/packages/RecursiveAI.OneLite) · [GitHub](https://github.com/recursive-ai-oss/onelite-csharp) · [Docs](https://recursiveai.org/onelite/docs/) · [Download example (.zip)](https://recursiveai.org/onelite/cookbook/join-attached-sqlite-databases/join-attached-sqlite-databases.zip)

`ATTACH` lets one query join tables from two database files. The download includes both sample files.

```shell
dotnet add package RecursiveAI.OneLite
```

Requires [.NET 10](https://dotnet.microsoft.com/download/dotnet/10.0) (net10.0); .NET 9 and earlier aren’t supported.

```csharp
using RecursiveAI.OneLite;

// Both files are in the download; the full
// program below also creates them.
using var db = Database.Open("orders.db");
using var connection = db.Connect();
using (var attach = connection.Prepare(
    "ATTACH ? AS customers"))
{
    attach.Bind(1, "customers.db");
    attach.Step();
}
using (var query = connection.Prepare("""
    SELECT c.name, o.total
    FROM main.orders AS o
    JOIN customers.people AS c
      ON c.id = o.customer_id
    ORDER BY o.id
    """))
    while (query.Step())
        Console.WriteLine($"{query.GetString(0)}: " +
            $"{query.GetInt64(1)}");
using var detach = connection.Prepare(
    "DETACH customers");
detach.Step();
```

```text
Maya: 25
Leo: 40
```

Unzip [the example](https://recursiveai.org/onelite/cookbook/join-attached-sqlite-databases/join-attached-sqlite-databases.zip), then `dotnet run`.

`orders.db` contains orders `(id, customer_id, total)` of `(1, 7, 25)` and `(2, 8, 40)`. `customers.db` contains people `(id, name)` of `(7, Maya)` and `(8, Leo)`. The full program recreates these sample rows on each run. Finish or dispose the query before detaching: a database still in a transaction on this connection fails to detach with `database NAME is locked`.

## The full program

```csharp
using RecursiveAI.OneLite;

Create("orders.db", """
    CREATE TABLE IF NOT EXISTS orders(
      id INTEGER PRIMARY KEY, customer_id, total);
    DELETE FROM orders;
    INSERT INTO orders VALUES (1, 7, 25), (2, 8, 40);
    """);
Create("customers.db", """
    CREATE TABLE IF NOT EXISTS people(
      id INTEGER PRIMARY KEY, name);
    DELETE FROM people;
    INSERT INTO people VALUES (7, 'Maya'), (8, 'Leo');
    """);

using var db = Database.Open("orders.db");
using var connection = db.Connect();
using (var attach = connection.Prepare(
    "ATTACH ? AS customers"))
{
    attach.Bind(1, "customers.db");
    attach.Step();
}
using (var query = connection.Prepare("""
    SELECT c.name, o.total
    FROM main.orders AS o
    JOIN customers.people AS c
      ON c.id = o.customer_id
    ORDER BY o.id
    """))
    while (query.Step())
        Console.WriteLine($"{query.GetString(0)}: " +
            $"{query.GetInt64(1)}");
using var detach = connection.Prepare(
    "DETACH customers");
detach.Step();

static void Create(string path, string sql)
{
    using var db = Database.OpenDefault(path);
    db.ExecuteScript(sql);
}
```

`dotnet run` prints:

```text
Maya: 25
Leo: 40
```

## Variations

To inspect attached schema names, insert this before `DETACH` in the full program:

```csharp
using (var list = connection.Prepare(
    "PRAGMA database_list"))
    while (list.Step())
        Console.WriteLine(list.GetString(1));
```

```text
main
customers
```

## Related recipes

- [SQLite transactions](https://recursiveai.org/onelite/cookbook/sqlite-transactions-csharp/)
- [Back up a SQLite database](https://recursiveai.org/onelite/cookbook/backup-sqlite-database/)

## The short answer

Open the first file, execute `ATTACH ? AS customers` with the second file path bound as a parameter, then join `main.orders` to `customers.people`. Both tables must be visible to the same connection. Dispose the query and execute `DETACH customers` when you are done.

An attached schema is a connection-local name, not a separate server or a copied database. The [OneLite ATTACH documentation](https://recursiveai.org/onelite/docs/attach/) covers attachment, schema lookup and commit behavior. SQLite’s own [ATTACH reference](https://sqlite.org/lang_attach.html) explains the SQL model.

## How do I join tables from two SQLite databases?

Attach the second file under a schema name, then qualify each table with its schema. In the example, `main` means the file opened by `Database.Open`, and `customers` means the file attached by the prepared statement. Table aliases `o` and `c` shorten column references; they do not replace the attached schema name.

The join condition links `customer_id` in an order to `id` in a person row. That is application data, not a relationship created by ATTACH. Attaching two files does not discover matching keys or merge records for you. Use an inner join when only matched rows belong in the result; a left join is appropriate when orders without matching customer rows must remain visible.

Binding the file path avoids turning a path containing quotes into SQL syntax. The example’s schema name is fixed SQL. Keep schema and table identifiers under application control instead of building them from untrusted input.

## Does ATTACH merge two SQLite databases into one?

No. ATTACH adds access to another file; it does not import its tables into `main`. DETACH removes that access from the connection without deleting the attached file. The two sample files remain separate after the program runs.

If the requirement is a permanent combined file, that is a data-copy task: choose destination tables, map their columns and resolve key collisions before inserting source rows. A join is a query over existing rows. Keeping those operations separate avoids accidentally treating a report query as a migration.

## Can I use multiple SQLite databases on one connection?

OneLite supports up to ten attached databases in addition to `main` and `temp`. Use distinct schema names. `main` is reserved for the initial database; `temp` is the temporary database. The `PRAGMA database_list` variation shows the active schema names on this connection.

Qualify table names even when a name is currently unique. OneLite looks up an unqualified name in `temp`, then `main`, then attached databases in slot order. An added table can therefore change which object an unqualified query reaches. `customers.people` records the intended file directly in the SQL.

Attachment also does not make two independent connections share a query context. Run ATTACH and the join through the same `Connection` object, as the example does.

## Are transactions across attached SQLite databases atomic?

The read-only join above does not change either database. For writes to multiple files, journal and synchronization settings matter. OneLite documents a super-journal commit when multiple changed databases are all in rollback-journal mode with `synchronous` above OFF.

A database using WAL, `synchronous=OFF`, or `journal_mode=MEMORY/OFF` commits on its own. Do not assume that one transaction automatically gives an all-files crash-atomic update with those settings. SQLite likewise documents [cross-file atomicity conditions](https://sqlite.org/lang_attach.html), including the main-database and WAL restrictions.

`Database.OpenDefault` enables WAL, so the sample uses it only while seeding each file independently. Choose and verify the settings for every participating database before designing a multi-file write workflow. A successful two-file SELECT is not evidence of cross-file write atomicity.
