SQLite

An “embedded” RDBMS. Runs in the same process as your program, not as a separate server.

Fast and small.

Install SQLite in your codespace

./start-project sqlite

cd projects/sqlite

./install-sqlite.sh

Run sqlite

sqlite3 db.db

This creates a file called db.db which contains your database. It starts out empty.

Create a table

For instance

sqlite> create table pets (name, type, weight);

Put some data into the table

sqlite> insert into pets values ("Fido", "dog", 35);
sqlite> insert into pets values ("Fluffy", "Cat", 12);

Select the data

sqlite> select * from pets;

Figure out how to load a TSV or CSV of data into your table

TSV = Tab-separated values

CSV = Comma-separated values

Define your tables in a file

I usually make schema.sql with my CREATE TABLE DDL. Then:

sqlite3 db.db < schema.sql

Idempotency

If doing something repeatedly gives you the same result as doing it once, it’s idempotent.

In schema.sql you may want to write your create table like this:

CREATE TABLE IF NOT EXISTS pets (name, type, weight);

That way you can reload schema.sql and not get errors and not wipe out existing tables.