2 Sample Databases for SQLite

If you need a sample database to run some quick tests in SQLite, here are a couple of options.

1. The Chinook Database

The Chinook database was created as an alternative to the Northwind database. It represents a digital media store, including tables for artists, albums, media tracks, invoices and customers.

The Chinook database is available on GitHub. It’s available for various DBMSs including MySQL, SQL Server, SQL Server Compact, PostgreSQL, Oracle, DB2, and of course, SQLite.

Install the Chinook Database

You can install the Chinook database in SQLite by running the SQL script available on GitHub. It’s quite a large script, so you might find it easier to run it from a file.

First, save the Chinook_Sqlite.sql script to a folder/directory on your computer. That’s a direct link to the script on GitHub.

Now create a database called Chinook. You can do this by connecting to SQLite with the following command:

sqlite3 Chinook.db

Now you can run the script. To run it from the file, use the following command:

.read Chinook_Sqlite.sql

This assumes that you’re in the same directory as the file, and that the file is called Chinook_Sqlite.sql. If not, you’ll need to use the full path to the file, along with whatever you’ve called the file.

Running this script creates the database tables and populates them with data.

Once the script has finished running, you can verify that it created the database by selecting some data from a table. For example, you could run the following:

SELECT * FROM Artist LIMIT 10;

Result:

1|AC/DC
2|Accept
3|Aerosmith
4|Alanis Morissette
5|Alice In Chains
6|Antônio Carlos Jobim
7|Apocalyptica
8|Audioslave
9|BackBeat
10|Billy Cobham

2. The Northwind Database

You can do exactly the same thing with the Northwind database. In fact, the Chinook database was created as a newer alternative to the Northwind database.

The Northwind database has been available for SQL Server and Microsoft Access for years. It was provided as a tutorial database for managing small business customers, orders, inventory, purchasing, suppliers, shipping, and employees. 

Github has a version of the Microsoft Access 2000 Northwind sample database, re-engineered for SQLite3.

So, similar to the previous example, save the SQL script available on GitHub as Northwind_Sqlite.sql.

Now connect to SQLite and create a database called Northwind:

sqlite3 Northwind.db

Now you can run the script:

.read Northwind_Sqlite.sql

This will create the tables and populate them, etc. You’ll probably see the data flash by as the script as it’s running.

In any case, once it’s finished running, you can run a quick check by selecting some data. For example:

SELECT * FROM "Order Details" LIMIT 10;

Result:

10248|11|14|12|0.0
10248|42|9.8|10|0.0
10248|72|34.8|5|0.0
10249|14|18.6|9|0.0
10249|51|42.4|40|0.0
10250|41|7.7|10|0.0
10250|51|42.4|35|0.15
10250|65|16.8|15|0.15
10251|22|16.8|6|0.05
10251|57|15.6|15|0.05