blog, Website Design

Connecting Two SQLite Databases

I was working on a website that used SQLite databases and found that when multiple read/writes were happening to the database there were times when it errored out and the website broke.

The code was written to get information from a table in the database, sweeten the data and then write it to another table in the same database.  This function was written on a loop and ran over and over all day long.

It seemed to me that there was a more efficient way of getting the data out of the database and into another one.  That way the data can be duplicated and each database can take multiple connections without interfering with each other functioning properly.

SQLite3 has a great query statement for doing this: ATTACH DATABASE.

Following is an example of how easy it is to use this feature of SQLite in PHP.

I opened my first database as usual:

$db = new SQLite3('../db/inventory.db');

Then I made a connection to the second database and gave it an alias:

$r = $db->query("ATTACH database '../../big-data/db/inventory.db' AS bigdata");

At this point the statement to query the first connection and write the data to the bigdata (attached database) was easy:

$stmt = $db->prepare("INSERT OR IGNORE INTO bigdata.items (`itemId`, `itemColor`) SELECT DISTINCT `itemId`, `itemColor ` FROM main.itemsNew");

And then execute the prepared statement:

$query = $stmt->execute();