Databases with SQLite

Database 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.

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

Database Deletion

What if you want to delete the whole database? For whatever reason you have, deleting a whole database in SQLite is quite simple - delete the file.

Yes, an SQLite database (all tables and records) are held in 1 file, whether you're on a Mac, Windows, or Linux computer.

So all you need to do is delete that one file. Typically it will have an extension of '.db'. And you don't need to run the CL command - just use your usual file manager.

Table Creation

The information we determined was essential 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 a table you didn't create:

CREATE TABLE sqlite_sequence(name,seq);

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

Table Deletion

Once you've created a table and started adding records, you might find you've made some errors and want to start over.

Perhaps the table was just a test for some conditions and now you don't need it. Don't 'delete' it - DROP it.

drop table fubar;

.output .dump .read

These are 3 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