Working with Multiple Databases, Transactions, and SQLite Internals

Hello, I'm Maneshwar. I'm working on FreeDevTools online currently building **one place for all dev tools, cheat codes, and TLDRs* — a free, open-source hub where developers can quickly find and use tools without any hassle of searching all over the internet.*

In the previous post, I looked at how SQLite simplifies execution with sqlite3_exec and how it behaves under concurrent, multithreaded access.
That discussion highlighted SQLite’s balance between convenience and correctness.

Today’s learning moves one level deeper. Instead of focusing on how statements are executed, the focus is on what SQLite can manage at once multiple databases, transactional boundaries, and internal metadata that SQLite itself maintains.

Working with Multiple Databases in SQLite

SQLite allows a single library connection to work with multiple database files simultaneously. This is done using the ATTACH DATABASE command.

At first glance, this can be confusing, because the application only sees one connection handle, yet multiple databases are involved.

Attaching an Additional Database

A typical workflow looks like this:

1. Open the main database (e.g., MyDB)
2. Attach another database file (e.g., MyDBExtn)
3. Access tables from both databases in a single SQL statement
4. Close the connection

Once attached, tables from the second database are accessed using a database alias as a prefix.

For example:

sqlite3_open("MyDB", &db);  /* Open a database named MyDB */
sqlite3_exec(db, "attach database MyDBExtn as DB1", 0, 0, 0);
sqlite3_exec(db, "select * from Students S, DB1.Courses C where S.sid = C.sid", callback, 0, 0);
sqlite3_close(db);

• Students → table in MyDB
• DB1.Courses → table in MyDBExtn

This allows joins across databases as if they were part of the same logical schema.

lovestaco@i3nux-mint:~/pers/sqlite/multiple_databases$ ./multiple_databases 
SID = 1
name = Math
SID = 1

SID = 1
name = Science
SID = 1

SID = 2
name = History
SID = 2

Library Connection vs. Database Connection

This is a subtle but important concept.

• Library connection: The handle the application holds (sqlite3 *db)
• Database connections: Internal connections SQLite opens one per database file

A single library connection may internally manage:

• One database connection (simple case)
• Two, three, or more database connections (when using ATTACH)

From the application’s perspective, everything happens through a single handle, but internally SQLite manages multiple database files safely and transparently.

This is why the term database connection can be misleading in SQLite.

Working with Transactions

After understanding how SQLite can span multiple databases, the next logical step is ensuring consistency across them. This is where transactions come in.

A transaction groups multiple SQL statements into one atomic unit of work.

Transaction Lifecycle in SQLite

A typical transaction follows this sequence:

1. BEGIN
2. Execute one or more SQL statements
3. COMMIT or ROLLBACK

sqlite3_open("MyDB", &db);  /* Open a database named MyDB */
sqlite3_exec(db, "attach database MyDBExtn as DB1", 0, 0, 0);
sqlite3_exec(db, "begin", 0, 0, 0);
sqlite3_exec(db, "insert into Students values(2000)", 0, 0, 0);
sqlite3_exec(db, "insert into DB1.Courses values('SQLite Database', 2000)", 0, 0, 0);
sqlite3_exec(db, "commit", 0, 0, 0);

If all statements succeed, the transaction is committed.
If any statement fails, the transaction can be rolled back, undoing all changes made within it.

lovestaco@i3nux-mint:~/pers/sqlite/transactions$ ./transactions 
Verifying inserted data:
SID = 2000
name = SQLite Database
SID = 2000

SID = 2000
name = SQLite Database
SID = 2000

Transactions Across Multiple Databases

SQLite allows a transaction to span multiple attached databases.

In practice:

• One insert can be made into Students
• Another insert can be made into Courses in an attached database
• Both inserts succeed or fail together

This ensures logical consistency, even when data is distributed across database files.

SQLite also allows executing multiple SQL statements in a single sqlite3_exec call, which makes transactional batching concise and expressive.

Why Transactions Matter

Without transactions:

• Partial updates can leave the database in an inconsistent state
• Failures midway through execution are difficult to recover from

With transactions:

• Atomicity is guaranteed
• Either everything succeeds or nothing does

This aligns directly with SQLite’s ACID guarantees.

Working with the SQLite Catalog

Every database system needs metadata information about tables, indexes, views, and triggers. SQLite stores this metadata in catalog tables.

The sqlite_master Table

Each SQLite database contains a master catalog named:

sqlite_master

This table stores schema information such as:

• Table definitions
• Index definitions
• Views
• Triggers

You can query it like a regular table:

select * from sqlite_master;

lovestaco@i3nux-mint:~/pers/sqlite/multiple_databases$ sqlite3 MyDBExtn "select * from sqlite_master;"
table|Courses|Courses|2|CREATE TABLE Courses(name TEXT, SID INTEGER)

However:

• You cannot modify it directly
• You cannot drop it
• SQLite maintains it internally

Reserved Catalog Names

All catalog tables:

• Begin with the prefix sqlite_
• Are reserved for internal use

Applications are not allowed to create objects with names starting with this prefix, regardless of letter case. This protects SQLite’s internal structures from accidental corruption.

Using the sqlite3 Executable

So far, all examples have used SQLite as a library embedded in applications. SQLite can also be built and used as a standalone command-line utility, commonly called sqlite3.

This tool allows:

• Interactive SQL execution
• Schema inspection
• Data import/export
• Database dumping

It also supports dot commands, which are SQLite-specific utilities prefixed with ..

Examples include:

• .help → list available commands
• .dump → export the entire database
• .tables → list tables

Although powerful, this utility is mainly a developer and debugging tool rather than a production interface.

lovestaco@i3nux-mint:~/pers/sqlite/transactions$ sqlite3 MyDB ".schema"
CREATE TABLE Students(SID INTEGER PRIMARY KEY);
lovestaco@i3nux-mint:~/pers/sqlite/transactions$ sqlite3
SQLite version 3.37.2 2022-01-06 13:25:41
Connected to a transient in-memory database.
sqlite> .help
.archive ...             Manage SQL archives
.auth ON|OFF             Show authorizer callbacks
.backup ?DB? FILE        Backup DB (default "main") to FILE
.bail on|off             Stop after hitting an error.  Default OFF
.binary on|off           Turn binary output on or off.  Default OFF
.cd DIRECTORY            Change the working directory to DIRECTORY

Closing Thoughts

Today’s learning highlights SQLite’s:

• One library connection can manage multiple database files
• Transactions provide atomicity across those databases
• SQLite maintains its own internal catalogs to track schema safely
• The standalone sqlite3 tool offers powerful inspection and debugging capabilities

Together, these features show that SQLite is not just a lightweight database, it is a fully capable relational engine with carefully designed abstractions.

Next, I’ll dive deeper into transactional support, concurrency control, and database recovery, where SQLite’s internal design choices really start to shine.

My experiments and hands-on executions related to SQLite will live here: lovestaco/sqlite

References:

SQLite Database System: Design and Implementation. N.p.: Sibsankar Haldar, (n.d.).

👉 Check out: FreeDevTools

Any feedback or contributors are welcome!

It’s online, open-source, and ready for anyone to use.

⭐ Star it on GitHub: freedevtools