Some cool SQLite things

Strict tables

For those of you who don't know, SQLite is like a dynamically-typed language. There's no restrictions on what you can and can't store inside of a column.

That means if you have a INTEGER column in your database table, there's nothing stopping you from storing a string inside of it.

That is where strict tables come into play. You can enable "strict mode" when creating your table by appending the strict keyword to the table definition.

CREATE TABLE users (
  id      INTEGER PRIMARY KEY,
  name    TEXT,
  email   TEXT,
  age     REAL
) strict;

Now any inserts will be type checked, preventing invalid types of data being inserted into your columns.

.import

Ever needed to import a CSV file into your database but didn't want to bother writing an import script to do it? SQLite has got you covered!

If you open your database in the SQLite command-line interface, you can use the .import command to import a CSV file directly into a database table.

.import --csv /Users/ryan/Downloads/users.csv users

This will import the users.csv file into the users table.

Extensions

Lots of database engines have some sort of extension API that lets you add new functionality to the database without hand-rolling stuff.

SQLite is no exception to this. All you need to do is find the extension you want to use and download the object file for your operating system.

If you don't want to mess around with downloading files, there's an excellent package manager called sqlpkg that takes care of the boring stuff: https://github.com/nalgeon/sqlpkg-cli.

If you're using the command-line interface for SQLite, you can load the extension using the .load command.

.load /Users/ryan/Downloads/uuid
select uuid4();

If you want to find an extension to do something, try searching through sqlpkg.org!

Bytecode!?

Most SQL engines convert your SQL statements into some sort of internal representation. It's normally a tree of nodes that describe what you're doing and the SQL engine uses the tree to perform operations.

SQLite is actually different in this regard. Instead of using a tree to process your statements, it instead converts everything into a flatter sequence of instructions, bytecodes.

This is very similar to how interpreted programming languages like PHP, JavaScript, Ruby etc are executed.

You can inspect the bytecode that is generated by prepending EXPLAIN to your SQL statements in the command-line interface!

explain select * from users;

addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     8     0                    0   Start at 8
1     OpenRead       0     2     0     2              0   root=2 iDb=0; users
2     Rewind         0     7     0                    0
3       Rowid          0     1     0                    0   r[1]=users.rowid
4       Column         0     1     2                    0   r[2]= cursor 0 column 1
5       ResultRow      1     2     0                    0   output=r[1..2]
6     Next           0     3     0                    1
7     Halt           0     0     0                    0
8     Transaction    0     0     1     0              1   usesStmtJournal=0
9     Goto           0     1     0                    0