Databases with SQLite

DB Creation

Once we've decided what information needs to be in our database, it's now quite easy to make one.

Bear in mind we're going to be using the information from the previous pages, so you may want to have that available as well as the Terminal window.

  • open a Terminal window
  • cd to the directory we made to hold your database/s: 
    cd ~/MyDB
  • and enter the following 
    sqlite3 MyBooks.db
SQLite version 3.49.1 2025-02-18 13:38:58
Enter ".help" for usage hints.
sqlite>

    SQLite creates a database in the directory you are currently in, unless the database exists already.
    Thus it is imperative you be in the directory containing your database, or use it on the command line. 
    sqlite3 ~/MyDB/MyBooks.db

    This does a few things. It creates a file named 'MyBooks.db' in your current directory (MyDB), or the one you specified. It also makes a 'connection' to that database, so any SQL commands you issue affect that database.

    It also presents the SQLite prompt, waiting for you to enter commands: 

    sqlite>

    At this point we have only CREATED the database - it has no structure and is empty at this point.

    Note the leading . in front of the .help command. There are several dozen dot commands in SQLite, as in many other command-line applications.

    These are NOT SQL statements, but affect the operation of the SQLite command interpreter - what you are running right now in a bash shell.

    Two dot commands you should know right now are

  • how to quit
    .q
  • get help about commands
    .help

    Table Creation

    The necessary information we determined was needed in the previous page, can now be put to use.

    First we will create the authors table:

    In Terminal, in your 'MyDB' directory, enter the following to open the database file we just made: 

    sqlite3 MyBooks.db
SQLite version 3.49.1 2025-02-18 13:38:58
Enter ".help" for usage hints.
sqlite>

    Table creation is the only place in sqlite we can create primary and foreign keys.

    Now we need to create the tables which will hold the data. Enter 

    create table authors (
id integer primary key autoincrement,
author text not null,
authorID integer
);

    Now we create the subjects table: 

    create table subjects (
id integer primary key autoincrement,
authorID integer,
titleID integer,
subject text
);

    Finally, the titles table: 

    create table titles (
id integer primary key autoincrement,
title text not null,
titleID integer,
authorID integer,
pubYear integer,
ISBN text,
format text,
filename text,
notes text
);


    Congratulations! You've just created your first SQLite database!

    To see what you've just created, here's another important dot command you can use: 

    .schema
CREATE TABLE authors (
id integer primary key autoincrement,
author text not null,
authorID integer
);
CREATE TABLE sqlite_sequence(name,seq);
CREATE TABLE subjects (
id integer primary key autoincrement,
authorID integer,
titleID integer,
subject text
);
CREATE TABLE titles (
id integer primary key autoincrement,
title text not null,
titleID integer,
authorID integer,
pubYear integer,
ISBN text,
format text,
filename text,
notes text
);
sqlite>

    You can also view the structure of a particular table: 

    .schema titles
create table titles (
id integer primary key autoincrement,
title text not null,
titleID integer,
authorID integer,
pubYear integer,
ISBN text,
format text,
filename text,
notes text
);

    Being observant, you will notice the line:

    CREATE TABLE sqlite_sequence(name,seq);
    which we (you) did NOT enter.

    This is an internal table used by SQLite, so don't mess with it

    .output .dump .read

    Three more dot commands you need to know. By itself dump will display the whole database to the monitor - giving the commands needed to recreate it if necessary. If you give it the name of a table, only that table will be displayed.

    As a simple 'backup' system, this is more useful sending it to a file using the .output command first: 
    .output Books_Format.sql
.dump formats
.exit

    This will create the file 'Books_Format.sql' in your current directory with the necessary commands to recreate that table.

    PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS "formats" (
	"id"	INTEGER NOT NULL,
	"authorid"	INTEGER NOT NULL,
	"titleid"	INTEGER NOT NULL,
	"format"	TEXT NOT NULL,
	PRIMARY KEY("id" AUTOINCREMENT)
);
INSERT INTO formats VALUES(1,295,100,'epub');
INSERT INTO formats VALUES(2,181,101,'epub');
INSERT INTO formats VALUES(3,166,102,'pdf');
INSERT INTO formats VALUES(4,193,103,'pdf');
INSERT INTO formats VALUES(5,209,104,'pdf');
INSERT INTO formats VALUES(6,286,105,'pdf');
.
.
.

    To recreate that table use the .read dot command: 

    .read Books_Format.sql
.schema
CREATE TABLE IF NOT EXISTS "formats" (
	"id"	INTEGER NOT NULL,
	"authorid"	INTEGER NOT NULL,
	"titleid"	INTEGER NOT NULL,
	"format"	TEXT NOT NULL,
	PRIMARY KEY("id" AUTOINCREMENT)
);
CREATE TABLE sqlite_sequence(name,seq);
select count(*) from formats;
┌──────────┐
│ count(*) │
├──────────┤
│ 446      │
└──────────┘

    The .sql file is ASCII (text) so you can read it with your editor.

    That's an awful lot to digest. Take a break if you need. We're going to be populating the database next.

    DB Population