
A Minimalist Guide to SQLite - craigkerstiens
http://tech.marksblogg.com/sqlite3-tutorial-and-guide.html
======
krallin
I love SQLite (and use it in a large number of places in our application
stack!), but I feel that it suffers from a case of bad defaults. To name a
few:

\- Foreign key checking (and cascading deletion for that matter) are turned
off by default. You need to enable them using `PRAGMA foreign_keys = ON;`.

\- There are practically no downsides (and a number of upsides) to using the
WAL journalling mode (at least for "use a local database and store it on the
disk" use cases). The main one being that reads will conflict with writes if
you don't enable it! (which is a problem in a multi-threaded environment)
Unfortunately, that's another feature you must remember to enable: `PRAGMA
journal_mode = WAL;` (for obvious reasons, this one "stays enabled" after you
turn it on).

\- Full auto-vacuum cannot be enabled after you start writing to the database
unless you enabled incremental auto-vacuum. If you're unsure, it's a good idea
to enable incremental auto-vacuum to keep that option open. But, here again,
that's not the default: you need `PRAGMA auto_vacuum = INCREMENTAL`.

This tends to be explicitly problematic if you have to perform one or more ad-
hoc queries using the SQLite command line on an app's database, and forget to
apply the relevant PRAGMAs! I wish there was a way to add "default" PRAGMAs on
a sqlite database file to avoid this.

(note: some of these defaults are configurable when compiling sqlite from
scratch, but if you're dynamically linking with an OS-provided instance of the
library, you can't really do that).

~~~
crest
The disadvantage of enabling WAL is that your database just turned into two
files and you have to recover the database to read it. Recovery is fast but it
requires write access.

------
echlebek
SQLite is one of the best pieces of software I have used in my career as a
developer. It is performant, reliable, simple and consistent. There is a
reason sqlite3 is deployed in so many places.

~~~
hasenj
I think it's not used widely enough yet. For example, 99% of websites could
benefit from using SQLite instead of MySql or (god forbid) PostgreSQL.

I mean Postgres is a fine piece of software but if your website gets 500
visits a day, you don't need Postgres; just use SQLite.

~~~
flatline
Sqlite is great but it is a poor substitute for a "real" database where you
need any serious constraint enforcement at the database level. Yes, you can
enable RI, but it's off by default. There is no field length constraint on
text values, no native datetime, boolean, or guid types. You can't
structurally alter tables without building a new one and copying the old.

If you actually need any of these things you can enforce them in code, by
convention, but you're better off installing Postgres.

Also writes are serialized. That one really can't be worked around.

~~~
zackelan
> Sqlite is great but it is a poor substitute for a "real" database

This is true, and explicitly acknowledged by the author:

> SQLite does not compete with client/server databases. SQLite competes with
> fopen().

[https://sqlite.org/whentouse.html](https://sqlite.org/whentouse.html)

If you're deciding between Postgres and SQLite, in most cases you're doing it
wrong (there are a few exceptions, such as serving a low-traffic almost-
entirely-read dynamic website can be accomplished easily with either). Usually
you want to choose between SQLite and "anything else that involves writing
directly to the local filesystem".

~~~
mwexler
I would disagree. The proper question is: "do I need a structured RDBMS or
not?" Then if yes, which should I use? Just saying "SQLite!" starts to sound
like the Regex quote from 1997: [https://blog.codinghorror.com/regular-
expressions-now-you-ha...](https://blog.codinghorror.com/regular-expressions-
now-you-have-two-problems/) A SQL system can do many wonderous things, but
there's a wide range of pain and benefit across the different systems.

Think about requirements first, then tech/implementation approach.

But yeah, like everyone else, I often just start with SQLite anyway!

------
ilitirit
Sqlite is great as data store for storing JSON. We receive realtime data from
different sources in JSON format. I dump the raw data into MongoDB, but it's
still not very easy to query. This is where Sqlite comes in handy. I import
the data straight into Sqlite from MongoDB which then enables me to run SQL
queries using the JSON1 extension. Currently I'm using it to store data that
drive feed simulators. We can extract the data using queries like:

    
    
      SELECT json_extract(RawData, '$.sourceId') SourceId
      , json_extract(RawData, '$.messageId') MessageId
      , json_extract(RawData, '$.message') Message
      , json_extract(RawData, '$.message.parties[0].name') Party
      , json_extract(RawData, '$.timestamp') Timestamp
      FROM RawFeed
      JOIN Source
        ON SourceId = Source.Id
      WHERE Source.Name = 'Foo Company'
      ORDER BY Timestamp

~~~
zepolen
Don't get me wrong, I was hating mongo before it was cool to do so, but your
example is one place which makes zero sense, just learn to write a mongo query
already or use SQL straight up.

------
microtherion
One of my favorite sqlite features is the vtable mechanism:
[https://sqlite.org/vtab.html](https://sqlite.org/vtab.html)

It makes it possible to expose a custom, application specific database format
as a sqlite table, and suddenly you can run SQL queries on your data!

~~~
masklinn
Postgres also has that concept, though calls it Foreign Data Wrappers:
[https://wiki.postgresql.org/wiki/Foreign_data_wrappers](https://wiki.postgresql.org/wiki/Foreign_data_wrappers)

And it goes beyond single tables e.g. PG-Strom uses FDW to implement GPGPU
scans, joins, aggregations & projections (and provides a pg/CUDA while at it)

------
CJefferson
I feel this article misses out on "why". I tend to store stuff as csv or json,
then slurp it into python to operate on it. It's not clear what benefit
putting your csv into sqlite gets you, from this article.

~~~
Avshalom
An sqlite db file is probably as light as your json or csv AND better
formed/typed

~~~
hk__2
It’s easier to compare it to CSV than JSON, because the former is tabular
while the latter can have deep imbrications that aren’t well represented in
SQL(ite).

~~~
zackelan
I'd argue that the more large/complicated/nested the JSON structure, the more
it would benefit from using a "real" database instead of a JSON file sitting
on disk. If not SQLite due to document-relational mismatch, then LevelDB,
RocksDB, or one of the other embedded key-value stores.

Of course, SQLite can also emulate a key-value store quite well, with a table
with 'key' and 'value' columns.

~~~
mythrwy
Also the JSON1 extension.

[https://www.sqlite.org/json1.html](https://www.sqlite.org/json1.html)

------
hardwaresofton
[https://www.sqlite.org/whentouse.html](https://www.sqlite.org/whentouse.html)

Once you're ready get yourself a cup of the SQLite koolaid

------
danso
Very nice! I'm in the middle of writing a book based on SQLite (the overall
topic is data analysis with SQL, but SQLite is the medium) while I'm teaching
it to students. I used to teach MySQL but it was just so goddamned hard to get
it configured correctly on people's computers (I always hated when work had to
be done on pre-configured computers lab rather than my own laptop), nevermind
the server/client/daemon aspects of MySQL and other variants. While SQLite
lacks polished (and free) GUIs, it's easier to explain on a sysops level (with
sqlite files being similar to XLS files) without burying students in
irrelevant tech details on the way to learning the power of SQL queries.

~~~
Something1234
Have you tried sqlitebrowser? It's fantastic, I use it a lot.

[http://sqlitebrowser.org](http://sqlitebrowser.org)

~~~
danso
Yep, that's what I use. Because it is open-source and cross-platform -- not to
mention, pretty high quality -- it has significantly increased the
attractiveness of teaching SQLite. Previously, me and other SQLite teachers I
knew would point students to the SQLite Manager plugin for Firefox, which is
also free and open source, but a bit clunkier: [https://addons.mozilla.org/en-
US/firefox/addon/sqlite-manage...](https://addons.mozilla.org/en-
US/firefox/addon/sqlite-manager/)

The DB Browser (they changed it from sqlitebrowser at the request of sqlite
IIRC) also has in my experience, a very helpful and responsive dev team:
[https://github.com/sqlitebrowser/sqlitebrowser/issues](https://github.com/sqlitebrowser/sqlitebrowser/issues)

~~~
justinclift
> ... also has in my experience, a very helpful and responsive dev team.

Thanks. We definitely try. :D

------
placebo
> _It 's used in systems as important as the Airbus A350 so it comes as no
> surprise the tests for SQLite 3 are aviation-grade_

I stumbled upon SQLite around 2001 and have been using and admiring this gem
of a software since that time, but hearing that it is aviation-grade is
definitely a surprise to me...

~~~
FridgeSeal
From what I've heard it has one of the most complete and comprehensive test
suites of any database. There's tests that simulate sudden power loss as well
which is pretty neat.

------
mistercow
> Data locality can be greatly improved by storing a SQLite 3 database in
> memory instead of on disk

My understanding (although I can no longer find the page in the sqlite3 docs)
was that because of caching, using :memory: is unlikely to make much
difference in practice.

~~~
beagle3
For reading, for a small database, after the cache is warm, that's likely
true. In all other cases, there is no avoiding disk access - which, on
spinning rust, is slow.

------
jokoon
Only thing that I miss in a database software is some sort of spatial indexing
implementation. I wish sqlite would have one by default.

~~~
CaptainDecisive
The SpatiaLite extension ([https://www.gaia-
gis.it/fossil/libspatialite/index](https://www.gaia-
gis.it/fossil/libspatialite/index)) handles spatial data and includes
indexing. It's solid and we've been using it in production for years. The only
real downside I've experienced is that the SpatiaLite-gui browser is a bit
flaky.

------
sigzero
What does "aviation-grade" mean in a software context?

~~~
otterpro
It means that software is robust enough to be safely deployed on airplanes or
vehicles so that safety is not compromised. Imagine if there were some bugs in
the software that could potentially endanger safety of plane full of people.

------
vram22
I wonder why the Firebird database is not mentioned more often in such
threads. It is a somewhat lightweight RDBMS that still has many advanced
features that SQLite may not (by design, of course). It may be positioned
somewhere between SQLite and heavyweight databases like MySQL, PostgreSQL,
Oracle, DB2, etc. I've used Firebird, though only lightly, with Python. It
also has different (lighter/heavier) versions for different needs.

[https://en.wikipedia.org/wiki/Firebird_(database_server)](https://en.wikipedia.org/wiki/Firebird_\(database_server\))

[https://firebirdsql.org/](https://firebirdsql.org/)

A few excerpts from this page:

Get to know Firebird in 2 minutes:

[https://www.firebirdnews.org/docs/fb2min.html](https://www.firebirdnews.org/docs/fb2min.html)

[

Firebird is derived from Borland InterBase 6.0 source code. It is open source
and has no dual license. Whether you need it for commercial or open source
applications, it is totally FREE!

Firebird technology has been in use for 20 years, which makes it a very mature
and stable product.

Don’t be fooled by the installer size! Firebird is a fully featured and
powerful RDBMS. It can handle databases from just a few KB to many Gigabytes
with good performance and almost free of maintenance!

Below is a list of some of the Firebird’s major features:

Full support of Stored Procedures and Triggers

Full ACID compliant transactions

Referential Integrity

Multi Generational Architecture

Very small footprint

Fully featured internal language for Stored Procedures and Triggers (PSQL)
Support for External Functions (UDFs)

Little or no need for specialized DBAs

Almost no configuration needed - just install and start using!

Big community and lots of places where you can get free and good support

Optional single file embedded version - great to create CDROM catalogs, single
user or evaluation versions of applications

Dozens of third party tools, including GUI administrative tools, replication
tools, etc.

Careful writes - fast recovery, no need for transaction logs!

Many ways to access your database: native/API, dbExpress drivers, ODBC, OLEDB,
.Net provider, JDBC native type 4 driver, Python module, PHP, Perl, etc.

Native support for all major operating systems, including Windows, Linux,
Solaris, MacOS, HP-UX and FreeBSD.

Incremental Backups

64bits builds available

Full cursor implementation in PSQL

Monitoring tables

Connection and Transaction Triggers

Temporary Tables

TraceAPI - know what happens in your server

]

Disclaimer: I'm not associated with them in any way. Just have used the
product a bit, and think it is somewhat good, based on that.

~~~
vram22
Also, from:

[https://www.firebirdsql.org/en/features/](https://www.firebirdsql.org/en/features/)

[

Firebird is free for commercial and educational usage: no license fees,
installation or activation restrictions. No double licensing - Firebird
license is based on Mozilla Public License.

The mix of high performance, small footprint, supreme scalability, silent and
simple installation and 100% royalty-free deployment make Firebird a highly
attractive choice for all types of software developers and vendors.

It is used by approximately 1 million of software developers worldwide.

]

