SQLite in Production - Master Course

SQLite is Enough video here:    • SQLite is enough  
------
SQLite is perfect for production of most websites
And here I’ll show how to run SQLite in production
It is short video
as there are not much to learn
with sqlite
We’ll also see why it is possible
and why you want to do so

Let’s get to it

Why?
Why would you use SQLite in production?
In short:

Simple and easy
That makes it robust
Extremely fast as there’s no server
If you’re not convinced
then checkout my video called “SQLite is enough”
You can find that in the description

Configuration
SQLite is a so called zero-configuration database
and that’s mostly true

Now, I’d like to highlight 3 configurations that you should probably do

1. journal mode
You probably want to configure SQLite to use
the journal mode WAL = Write Ahead Log

You do this by

PRAGMA journal_mode = wal;

and you only need to do this once
What this does is it sacrifices a little bit of atomicity for a shit tonnes performance.
I always put this on.

I also heard there’s a new wal2
but I have no experience with this
But you should probably take a look at that

2. Foreigns keys
In Sqlite3 the foreign keys are not enforced by default
You can do this by using

PRAGMA foreign_keys = ON;

This have a small effect on performance,
but you should probably do it.
Now I haven’t done it,
and I already shot myself in the foot a few times because of
I will do that in the next project

3. Strict table
Sqlite forgives you if you do weird shit
Such as inserting string into a int
Now that’s usually not a good idea
and since version 3.37
you can now make a table strict by adding prepending “strict” when creating a table

CREATE TABLE Goleko(age INTEGER) STRICT;

Now I haven’t done this as well
and again
I shot myself in the foot a few times
So I’ll probably do this in my next projects

Intersting opportunities as files
Your sqlite database is just a file
and it gives interesting opportunities to play around

Let’s say you make a system that have have organizations
Then you could have a database for each organization.

Futuremore, you could distribute those databases to an region
Making it easier to be GDPR compliant and improve latency for the users

You can also very easily just copy the prod db to testing environments

Indices / indexes
Indexes are some of the most important things in databases
not only in traditional databases,
but also in sqlite.
So, if your database is slow
checkout how to do indexes
as they will greatly improve performance

Performance consideration
You don’t need to worry that much about performance of SQLite databases
For most cases,
if you put write ahead logging on it is good enough

However, if you’re in a write heavy environment you might need to do
connection pooling as writes are serialized.
Now, this is the champagne problem,
and so be happy when you are gifted that problem

Example PocketBase uses SQLite and boast a 10k connection on just a 4 euro Hetnzer machine
That’s enough for 95% of the websites out there.

So don’t worry about it until it becomes a problem

Backup Cavets
Backing up a sqlite database is extremely simple.

Now, you can just copy the file of the database
but in some rare cases that might corrupt some data
It is better to run the

.dump

you can do this by having a cronjob just doing

`sqlite3 yourdatabase.db “.dump" mybackup.sql`

There’s also newer solution such as litestream
but again I like to be conservative
and dumping works well.