Scripting SQLite database creation

From SwinBrain

SQLite includes a command line tool for creating and administering SQLite databases. This tool is very handy for scripting database creation and for populating SQLite databases with test data.

Installing SQLite

In this article we will assume that you have downloaded and installed the sqlite3 command-line tool. (See 'Downloading & Installing SQLite' for instructions on how to do this.)

You should before proceeding, be able to run the sqlite3 tool from your command line. If you installed this into a new folder or directory make sure that it is in your 'path' environment variable (see Setting Environment Variables How To).

Using SQLite

Type sqlite3 mydatabase.sqlite at the command line to create an SQLite database in a file named "mydatabase.sqlite". The sqlite3 tool should now present you with a prompt where you can execute SQL statements against your newly created database.

If you want to script these actions you run sqlite3 as shown below, by passing your commands in via an input file.

sqlite3 -init <inputfile> mydatabase.sqlite

The input file ("<inputfile>") is just a text file that contains the SQL statements that you want executed.

To script database creation you need an input file that contains SQL to create, and populate the databases tables. The script shown below executes the statements contained within CreateTables.sql and PopulateTestData.sql.

.read CreateTables.sql

.read PopulateTestData.sql

If the above script is called CreateDB.sql you would run it using sqlite3 as shown below.

sqlite3 -init CreateDB.sql mydb.sqlite

This would create (or use if it already exists) a database in a file named mydb.sqlite, and run the contents of the CreateDB.sql file against it. This in turn would cause the 'CreateTables.sql' and 'PopulateTestData.sql' scripts to be run.

Summary

In this article we used the command-line 'sqlite3' tool to create an SQLite database, and run an SQL script to populate it.