Recursive AI

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.

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

Terminal
dotnet add package RecursiveAI.OneLite

Requires .NET 10 (net10.0); .NET 9 and earlier aren’t supported.

C#
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, 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

C#
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:

C#
using (var list = connection.Prepare(
    "PRAGMA database_list"))
    while (list.Step())
        Console.WriteLine(list.GetString(1));
Text
main
customers

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 covers attachment, schema lookup and commit behavior. SQLite’s own ATTACH reference 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, 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.

All onelite recipes