Seed Sqlite with Data

Here's how to seed sqlite with data.

What to Git Commit

Sqlite is a self-contained database. There's a file that you can put right in your repo.

But should you commit your .sqlite database? It'd probably be better not to.

Instead of committing tables, sequences or constraints, it'd be better to commit .sql files for database migrations.

Instead of committing data rows in your tables, it'd be better to commit something in plaintext format for your data, likea .csv file.

This will make it better to track changes to structure in .sql migration files and changes to data in the .csv files.

Create CSV

Create a plaintext .csv file. The first row has column headers. These should correspond to column names in your sqlite table.

Use the first row, usually column headers in a csv file, as the column names in a table

Import CSV

The good news is that sqlite can import a .csv file, no problem.

First, connect to the database:

sqlite3 myproject_dev.db

Then turn on import ability:

.mode csv

Then import your .csv file, using the absolute file path, and append the name of the table you'd like to insert into (here, stuff):

.import /home/jaketrent/dev/myproject/stuff.csv stuff

Problems with Constraints

But for most tables, this is likely to give us some problems:

/home/jaketrent/dev/stuff.csv:1: expected 6 columns but found 4 - filling the rest with NULL
/home/jaketrent/dev/stuff.csv:1: INSERT failed: datatype mismatch
/home/jaketrent/dev/stuff.csv:2: expected 6 columns but found 4 - filling the rest with NULL
/home/jaketrent/dev/stuff.csv:2: INSERT failed: datatype mismatch
/home/jaketrent/dev/stuff.csv:3: expected 6 columns but found 4 - filling the rest with NULL

What about primary key autoincrement? Or default current_timestamp? Or column types of integer?

If we look at our table, we have to deal with all those things:

sqlite> .schema stuff
CREATE TABLE stuff
( id integer primary key autoincrement
, name varchar(200)
, details text
, date_created timestamp default current_timestamp);

But we don't want to save ids or timestamps to our .csv.

Insert into Temp Table

Our solution is to save to temporary table (here, stuff_temp):

.import /home/jaketrent/dev/myproject/stuff.csv stuff_temp;

It'll go in just fine. The .import will create the table. All the columns will be type text.

Then you can run an insert sql statement that will take care of the autoincrement and default:

insert into stuff (name,details) select * from stuff_temp;

It's there now. Prove it:

select * from stuff;

And finally, clean up the temp table:

drop table stuff_temp;

Doesn't it feel good to plant a seed?