CSE 344: Brief Guide to SQLite

SQLite is a lightweight relational database management system (RDBMS). It's a great way to get started learning SQL because it's so easy to install and run, compared with other RDBMSes.

If you haven't already, please follow the software setup instructions to install SQLite.

In a pinch, you can also run SQLite in your browser using sqlime.org. But this is a bit of an unholy hack, so it's not recommended for real work. Note that sqlime.org does not implement the shell interface of SQLite, but only the SQL parts! Any command that starts with a . such as .help will not work.

SQLite Resources

There are many useful "dot commands" in the SQLite shell:

Use .help to get a list of SQLite shell commands.
Use .tables to get a list of table names.
Use .schema to print the schema of the whole database as a bunch of CREATE TABLE statements.
Use .mode box to print query results nicely.
Use .headers on to show column names on query results.

There are also many useful pages of official documentation

Brief tour

Start the SQLite shell. (The details depend on your operating system. See the installation instructions.)

Create a table called books with columns title and year_published:

CREATE TABLE books(
  title TEXT,
  year_published INT
);

Run the .tables command. You should see books listed.

Add some rows to the table:

INSERT INTO books
  VALUES
    ('Database Systems: The Complete Book', 2008),
    ('Dune', 1965);

List all books:

SELECT *
FROM books;

By default, SQLite prints each row on one line with fields separated by a | character:

Database Systems: The Complete Book|2008
Dune|1965

For a nicer appearance, say .mode ascii and re-run the SELECT statement above to get:

+-------------------------------------+----------------+
|                title                | year_published |
+-------------------------------------+----------------+
| Database Systems: The Complete Book | 2008           |
| Dune                                | 1965           |
+-------------------------------------+----------------+

(Other modes to try: .mode box and .mode column)

What makes SQLite different from other RDBMSes

Some of the items listed below are good things about SQLite, some not so good (in our opinion), and others are sometimes good and sometimes bad! (See also the list of distinctive features of SQLite and the list of quirks.)

Most RDBMSes run as a server program that you contact over some kind of network protocol. SQLite is a software library that client programs call directly.
Most RDBMSes enforce the declared types of columns. SQLite does not.
Most RDBMSes enforce foreign key constraints by default. SQLite does not.
Most RDBMSes store the data in a database across many complex files. A SQLite database is a single normal file.
Most RDBMSes do not allow unaggregated columns in the select clause of an aggregate query. SQLite does, and returns an arbitrary value.
Most RDBMSes make it hard to compute argmin in SQL. In SQLite, you can do that by mentioning an unaggregated column in an aggregate min() query. The unaggregated column will be taken from a row that achieves the min() value.
Most RDBMSes do not allow NULL in a PRIMARY KEY column. SQLite does (except in INTEGER PRIMARY KEYs (but does in INT PRIMARY KEY, confusingly!)!)

Our advice for using SQLite beyond this course

SQLite has many quirks, but also several advantages. If you choose to use it for a project after this course, here is some real-world advice. (You do not necessarily need to follow this advice on the homework.)

A general piece of advice: Learn the database system you are using.
- Some people argue that you should write only SQL-standard-compliant queries that will work on any conforming implementation. This sounds nice in theory, but in practice, there are no conforming implementations. And there are many problems with the SQL standard that make it annoying to program against. Many RDBMSes have specific improvements that go beyond the standard. You should learn these extensions and use them.
So if you are going to use SQLite for a project, learn SQLite. Learn its quirks. Take advantage of its conveniences that go beyond the standard and beyond what other DBMSes give you. Learn to avoid its pitfalls.

Each person will develop there own personal taste for which quirks of SQLite are worth taking advantage of and which are worth steering clear of. Here is some advice on what has worked well for us in the past when using SQLite:

Enforce foreign keys by enabling PRAGMA foreign_keys = ON at the beginning of every connection (supported in SQLite since 2009).
Learn about ROWIDs and the special meaning of INTEGER PRIMARY KEY in SQLite. Every table with a primary key should either have an INTEGER PRIMARY KEY (declared exactly like that, and not INT PRIMARY KEY!) or else the table should be declared WITHOUT ROWID (supported in SQLite since 2013).
Learn the datatypes supported by SQLite. Only declare columns with the types that are actually supported: INTEGER, REAL, TEXT, and BLOB.
Declare every table STRICT (supported in SQLite since 2021). This means that column types will actually be enforced. Note that intermediate query results that are not stored in a STRICT table can still be dynamically typed. And it's important to understand that the physical storage of all tables, including STRICT tables, is dynamically typed. But in our experience, this helps prevent bugs in applications.
Learn about SQLite's treatment of bare columns in aggregate queries. Be aware that this makes it harder to find mistakes in GROUP BY queries. But take advantage of it to compute argmin/argmax much more easily in SQLite than in standard SQL.
Learn and use UPSERT where appropriate.
Learn about the query optimizer and get a mental model for what will be fast.
Learn about the file format and get a mental model for how data is stored by SQLite.