How to Show Databases in SQLite Using the .databases Command

SQLite is an embedded relational database engine that is a popular choice for local/client storage in applications due to its lightweight nature and efficient performance. A key capability SQLite provides is the ability to attach and access multiple databases within a single database connection.

The .databases command in the sqlite3 shell provides a simple way to display details on databases currently attached to the active database connection. This allows you to easily see what databases are available and switch between them.

In this in-depth guide, we will cover everything you need to know about using the .databases command including:

• Technical overview of attaching databases in SQLite
• Usage examples for attaching multiple database files
• Performance and optimization considerations
• Alternative methods for querying attached databases
• Practical applications for multi-database access

We will also provide expert best practices for working with multiple SQLite databases efficiently and securely.

How SQLite Attaches Database Files

Before covering the .databases command usage it is important to understand the technical details of how SQLite is able to integrate multiple database files through attachments.

When you attach an additional database file using a statement like:

ATTACH DATABASE ‘secondary.db‘ AS ‘secondary‘;

SQLite performs the following key steps behind the scenes:

1. SQLite creates a temporary database file containing empty tables based on secondary.db schema
2. Data is copied from secondary.db to populate this temporary database
3. The temporary database is attached to the connection
4. The schema (table/index definitions) are integrated

This allows queries to access secondary database objects through the single database connection transparently.

The additional database file itself remains unchanged on disk. The temporary copy allows SQLite to prevent changes to the original file. If the schema of secondary.db changes externally, re-attachment handles integrating those changes.

When detaching a database with DETACH DATABASE, SQLite simply discards the in-memory temporary database.

This differs from database systems like MySQL and PostgreSQL where databases instances establish direct connections between each other for cross-database queries. SQLite attachments keep database files separate for administrative isolation and flexibility.

Now that we‘ve covered the basics of how SQLite attachments work behind the scenes, let‘s see how to use the .databases command…

Displaying Attached Databases with .databases

The .databases command outputs a table listing the attached databases:

sqlite> .databases
seq   name             file
---  ---------------  --------------------------
0    main             /home/user/main.db
2    secondary        /home/user/secondary.db

The key details shown for each attached database are:

• seq – Integer sequence number assigned to each attached database (starting from 0)
• name – The database alias used when attaching the database
• file – The underlying disk file for the database

This provides valuable insight into what databases are currently accessible and how they are aliased.

To demonstrate usage, first we will attach a database file using an alias:

ATTACH DATABASE ‘/var/data/users.db‘ AS ‘users‘;

We can confirm users.db is attached by running .databases again:

sqlite> .databases  
seq   name             file
---  ---------------  -------------------------- 
0    main             /home/user/main.db                      
2    secondary        /home/user/secondary.db
3    users            /var/data/users.db

The users database now appears in the list of attached databases. This also shows the absolute file path SQLite is using to access the underlying content for that database.

You can now write SQL queries to access users database objects through the users alias, for example:

SELECT * FROM users.user_accounts; 

This makes it very convenient to work with multiple databases through SQLite attachments.

Next we cover some best practices for attaching database…

Attach Database Guidelines and Best Practices

When attaching and accessing multiple databases through SQLite there are several guidelines worth following:

Use Aliases

It is considered best practice to always specify a custom alias when attaching additional database using the AS ‘alias_name‘ syntax as shown above.

The reasons to use a database alias are:

• Avoids namespace collisions between identically named objects (e.g tables) in attached databases
• The alias namespace isolates each database
• Queries require less changes if the underlying file path changes
• Easier to understand what data is coming from which database

In contrast, attaching databases by file path only causes trouble:

ATTACH DATABASE ‘/var/other.db‘ AS ‘/var/other.db‘;

This exposes tables through their full names making queries verbose:

SELECT * FROM ‘/var/other.db‘.table1

The absolute file path also risks breaking if the file location changes. Use of database aliases is preferred in most cases.

Mind Database Locking and Contention

Due to SQLite‘s use of reader/writer locking on the entire database file, contention issues can occur in parallel workloads as multiple connections attempt to acquire write locks.

Attaching extraneous databases increases lock pressure which can negatively impact performance. For example, if queries are only needed against users, there is no reason to keep secondary attached – it creates extra lock overhead.

A best practice is to limit attachments only to databases currently needed and detach them when done.

For example, only keep active databases attached during the session:

ATTACH ‘users.db‘ as ‘users‘; 
...
INSERT INTO users.logins (user, access_time) VALUES (‘jwick‘, ‘09:17am‘);
... 
DETACH DATABASE ‘users‘;

This avoids unnecessary lock conflicts between the live databases.

Of course in read-only situations attachments pose lower threat since writes cannot occur. But locking considerations should always be evaluated with multiple writable live attachments.

Use Memory Databases to Avoid File Locking

File-based locking limitations can be bypassed using memory-backed database attachments:

ATTACH DATABASE ‘:memory:‘ AS memdb;

In-memory databases enable efficient high-performance parallel inserts and complex queries without file lock restrictions.

The contents are volatile so this suits temporary scratchpad purposes rather than durable storage. Attaching this type of fast in-memory database alongside disk-based ones provides a performance boost for analytical queries without disrupting production content.

Make Databases Read-Only When Possible

Another technique that avoids locking trouble is to open database files read-only using ATTACH ‘somedb‘ AS readonly:

ATTACH DATABASE ‘accounts.sqlite‘ AS accounts;
ATTACH DATABASE ‘reporting.sqlite‘ AS readonly;

The first is opened read/write while the second read-only. This avoids writers blocking readers access.

Permissive scenarios that allow offline analytics or backups to run without impacting systems are thus enabled.

Example: Attaching a CSV File as a Database

In addition to normal SQLite database files, SQLite has the ability to attach virtual tables that expose other data sources like CSV files as database tables.

For instance, we can take a CSV containing product data and attach it as a new products database:

ATTACH DATABASE ‘products.csv‘ AS csv TYPE csv;

SELECT * FROM csv.products;

This provides easy SQL access over CSV data.

Under the covers SQLite implements a csv virtual table module that parses the file. This is just one example of many available virtual table adapters from JSON to cloud data.

Attaching specialized tables like this introduces new data sources. The .databases command tracks them like any other database.

Digging Into Attached Database Metadata

While .databases shows high-level details on attached databases, we can gather more metadata using SQLite‘s sqlite_master table which tracks all schema objects.

Querying sqlite_master against an attached database reveals low-level info:

SELECT * FROM users.sqlite_master;

This would print metadata on the users database tables, indexes, triggers and other objects.

We can even join schema queries across multiple attached databases:

SELECT m1.name, m2.name
  FROM main.sqlite_master AS m1 
  JOIN users.sqlite_master AS m2;

This performs a comparative analysis of schema contents across attached databases.

Digging into sqlite_master is helpful for in-depth debugging of database configuration issues across attachments.

Alternative Approaches for Querying SQLite Databases

Although multi-database access is made convenient through SQLite‘s attach/detach architecture, some situations may call for alternative approaches.

Multiple Connections Queries

In extremely high performance parallel applications, maintaining isolated database connections rather than sharing attachments can make sense.

For example a dashboard system might sustain dedicated database connections:

Connection 1 -> users.db 
Connection 2 -> posts.db
Connection 3 -> analytics.db

This divides resources across connections avoiding lock contention between the live databases. Performance improvements from parallelism may justify the extra programming complexity.

The tradeoff is duplicating schema metadata queries across the disjoint connections unlike unified attachments.

Export/Import Batch ETL

For offline analytics pipelines that aggregate persisted SQLite data, Extract-Transform-Load (ETL) batch workflows help limit impact to production systems.

An incremental ETL pipeline would:

1. Export datasets from live databases to files
2. Apply calculations/filters to build derivative datasets
3. Bulk import final tables/reports into a separate analytics database

By keeping databases fully isolated and using file staging, production systems avoid both locking disruptions and bloat from analytic content.

However this requires extensive SQL scripting compared to attached database querying.

Real-World Usage of Multiple SQLite Databases

There are a variety of compelling use cases where attaching multiple SQLite databases provides benefits:

Mobile Applications

Due to SQLite‘s small footprint it has become the preferred local storage for most mobile platforms.

A common pattern seen in mobile apps is logical separation of user generated content from structural tables:

main.sqlite - User photos, notes, settings 
static.sqlite - Read-only application metadata 

Attachments give convenient access while maintaining isolation between dynamic user content and static app tables.

Web Browsers

Major web browsers like Firefox also take advantage of SQLite for internal data storage of bookmarks, cookies, and browsing history.

As the datasets have differing access patterns, attaching databases helps segregate hot content from analytics data queried less frequently. This improves user performance.

Embedded Devices

With internet-connected embedded systems on the rise, SQLite is a perfect fit for storing sensor data streams and device logs.

A modular approach attaching individual databases for each subsystem component has good properties. Database attachments prevent messy consolidation while enabling rich cross-database reporting.

As shown in the examples above, SQLite‘s flexible database architecture has become invaluable to single-user software across many domains.

The .databases command provides helpful visibility as more databases get attached to an application.

Conclusion: Working Efficiently with Multiple SQLite Databases

The .databases command offers simple yet important visibility into the status of attachments and aliases in a SQLite session. Leveraging attachments to access multiple isolated on-disk database files simultaneously provides modular flexibility.

However, care should be taken to detach unneeded databases avoiding unnecessary lock contention – especially for write-intensive workloads. Additionally, in-memory and read-only database attachments can eliminate locking bottlenecks in high performance parallel usage.

Alternative methods to attachments may be appropriate depending on the application architecture including isolated connections or scheduled ETL pipelines.

As SQLite continues growth as a preferred method for compact local storage on devices and clients, the techniques covered in this guide will help developers and DBAs make the most effective use of SQLite‘s multi-database capabilities.

Proper database design considering concurrency limitations and performance characteristcs allows systems to scale efficiently. Relying on the .databases command for monitoring helps track runtime attachment configurations between SQLite database files.