Using SQLite databases on different machines as a single virtual database

You can use separate SQLite database on different machines as a single virtual database by using the attach command in SQLite.

I read about the technique here: http://souptonuts.sourceforge.net/readme_sqlite_tutorial.html

For this example let’s use the simplest possible database. A contact database containing nothing but email addresses.

1. Create SQL script for a simple contacts database

File createcontacts.sql:

CREATE TABLE contacts (
id INTEGER PRIMARY KEY,
email VARCHAR(64));

2. Create two databases

Create the databases:

$ sqlite3 contacts1.db < createcontacts.sql
$ sqlite3 contacts2.db < createcontacts.sql

Notice: I'm using SQLite on Mac OS X, which comes preinstalled with /usr/bin/sqlite3.

Insert one contact in each database:

$ sqlite3 contacts1.db "insert into contacts (email) values ('john@example.com');"
$ sqlite3 contacts2.db "insert into contacts (email) values ('jane@example.com');"

The are now two databases, each with a row of data:

$ ls
contacts1.db	contacts1.sql	contacts2.db	contacts2.sql
$ sqlite3 contacts1.db 'select * from contacts;'
1|john@example.com
$ sqlite3 contacts2.db 'select * from contacts;'
1|jane@example.com

Next, we'll combine the databases using the SQLite command attach.

3. Create a single virtual database

First, enter the sqlite3 console:

$ sqlite3 
SQLite version 3.6.12
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> 

Then, attach the two databases:

sqlite> attach database 'contacts1.db' as c1;
sqlite> attach database 'contacts2.db' as c2;

Check that it went well with the .databases command:

sqlite> .databases
seq  name             file                                                      
---  ---------------  ----------------------------------------------------------
0    main                                                                       
2    c1               /Users/kostas/Documents/Dev/SqliteExp/attach/contacts1.db 
3    c2               /Users/kostas/Documents/Dev/SqliteExp/attach/contacts2.db 

Finally, select email addresses simultaneously from contacts1.db and contacts2.db:

sqlite> select 'c1',* from c1.contacts union select 'c2',* from c2.contacts;
c1|1|john@example.com
c1|2|janet@example.com
c1|3|joe@example.com
c2|1|jane@example.com

This demonstrates how one can select rows from multiple databases at once, using the attach command in SQLite. Imagine that the databases are actually on separate machines and accessed via e.g. NFS mounts.

Next, I'll look at spatial data in SQLite.

Leave a Reply