
Why SQLite succeeded as a database (2016) - Tomte
https://changelog.com/podcast/201
======
bob1029
SQLite is a wonderful database. We use it in production many times over for
all of our clients. Not having to worry about whatever arbitrary SQL Server
installation is available in a particular environment has saved us so much
time and frustration. Combine SQLite with .NET Self-Contained Deployments
means that we (our automated tools) now copy our software distribution zip to
target, extract to path, run executable as administrator and walk away.
Without SQLite we could not do this.

Migrations are also hilariously easy with SQLite if you use the user_version
pragma. We have a monotonically-incrementing number to indicate each version.
Our migrator first queries this upon startup and runs all the migrations that
exist between current user_version and whatever our highest sequence # is
(defined as a constant in code). Some might argue that you should just use EF
(or whatever language-specific ORM), but I prefer the level of control and the
substantial performance boost you get with raw SQL and a simple migrator
approach. I cannot speak for EF Core, but EF6 performance was absolutely
abysmal in some more complex cases.

I would also say that performance might surprise you if you turn on WAL. I
have saturated NVMe disks inserting lots of data into SQLite. The trick is to
use a single connection and let SQLite handle the serialization for you,
rather than try to manage multiple connections to the same file in code (i.e.
one connection per logical operation sucks for SQLite).

~~~
miohtama
My only grievance, and a blocker, is that SQLite does not support decimal
numbers, making it less practical to work with finance related data.

~~~
BlueTemplar
Wait, there are finance applications that don't forbid floating point numbers
??

~~~
fowl2
I'd like you to meet my friend "Excel"...

~~~
BlueTemplar
I mean for professional applications, not amateur stuff.

~~~
Dylan16807
Excel is used for very very expensive professional tasks.

------
danso
I remember listening to this when it was recorded and I still remember details
from it years later – probably my favorite Changelog podcast ever. It's
obviously very foolish to make software choices based on how much you like its
creator as a person. But Richard Hipp comes off as a guy so likable that
goshdarnit, I hope his little project succeeds. Jokes aside, though, I don't
think it's entirely coincidence that SQLite, something that is so good and
reliable, was made by someone who seems so conscientious and thoughtful.

Though I have to admit, I was and am still disappointed to learn that the
official way to pronounce SQLite is "S-Q-L-ite", i.e. "like a mineral".

~~~
scohesc
I thought it was always pronounced "sequel-ite" personally

~~~
larrydag
MySQL is the same way. A lot of people say My-sequel but I think its supposed
to be my-ess-que-ell

~~~
WorldMaker
PostgreSQL is the fun one out because they officially say it is Postgres-Q-L.

The "it's always S-Q-L and never `sequel`" is IBM's fault and an early
trademark infringement issue in computing. (IBM was told it couldn't call it
"SEQUEL" by an aircraft company.)

~~~
big_chungus
I typically go with just "postgres" for phonetic pronunciation. Not sure how
else it would be done; "postgres sequel?"

~~~
jdfellow
I've heard Postgres pronounced as if it were French. "Postgree."

~~~
andrewflnr
I've heard that, but assumed it came from splitting up the name as
"Postgre"+"SQL"; if you leave off the SQL part you get a word ending in E, and
"postgreh" can't be right, can it? :)

~~~
WorldMaker
Which is why I've also heard developers that either assumed the 'g' was silent
or a transposition problem and you get "poster SQL", "postreg SQL", or worst
of all "posgret SQL".

Somewhere, I believe in an HN comment, I saw a Postgres developer say that one
of the biggest regrets of the project naming was capitalizing that "shared S".

------
outworlder
One thing that I wish more people realized, especially ones developing native
apps, is this: unless you really need an specific file format for your
application, don't design your own. Just use SQLite. You get so much out of it
(including features like undo), as well as a huge toolkit.

~~~
flukus
And you lose the ability to quickly change settings in a text editor, to have
multiple settings files, to have tiered (system, local, project) settings
files, to version them in source control, etc. Instead of learning ini, josn
and xml you'll have to learn whatever schema project x uses and there will be
more schemas than file formats. Plus SQLite might be fast, but it's still a
lot more overhead than parsing an ini file.

~~~
gwbas1c
> unless you really need an specific file format for your application

It's all about choosing the right tool for the job.

There's great reasons to use xml files over SQLite, but that mostly comes down
to needing a file to be human readable versus file size.

In the product that I work on, I moved very small tables out to xml files
because it's easier to inspect them in a text editor instead of expecting non-
developers to figure out a database. These files are also small enough and are
written infrequently enough that any performance difference between xml and
SQLite is negligible.

The vast majority of data is in SQLite. An older version of the product would
always write out a giant xml file, but when the xml got big, performance
suffered. In this case, SQLite is the correct choice because it can make a
small change to the database without needing to rewrite the entire file.

------
vbezhenar
While everyone's praising SQLite, I want to note that SQLite type system is
absolutely horrible. When you can store string in the integer column, you
don't have a type system. And it's absolutely not intuitive for people having
experience with other databases.

~~~
senderista
I agree, if you want dynamically typed columns then just define a separate Any
or Variant type. I don’t see the point of schema if it’s not enforced.

~~~
v64
Hipp talks about that in the interview a little bit:

> SQLite really started life as a Tcl extension, Tcl being the programming
> language, the Tcl/Tk. The project I was working was working on was written
> in Tcl/Tk and so SQLite began as a Tcl extension and as a scripting
> language, like Perl or Python, where you can put any type of value you want
> in a variable. So a variable might hold a string, a number, a byte array or
> whatever. So I made SQLite the same way, where just because you’ve declared
> a column of a table to be text doesn’t mean you can’t put integer in there,
> or just because you declared a column in the table to be a short int, why
> not put a 2-megabyte blob there? So what? It’ll do that.

> Or if you have a column that’s declared integer and you try to put text in
> it, it looks like an integer and it can be converted without loss. It will
> convert and store it as an integer. But if you try and put a blob into a
> short int or something, there’s no way to convert that, so it just stores
> the original and it gives flexibility there. And this is useful in a lot of
> cases, because sometimes you just have a miscellaneous column in a table
> that you might need to store lots of different things in. And in traditional
> database systems you actually have to have multiple columns, one for each
> possible data type, whereas in SQLite you put it all in one column. So it
> works well.

> And for that matter, with SQLite you don’t have to give the column a type at
> all. You can just say, CreateTable T1 (a,b,c) and then you’ve got a table
> with three columns named a, b and c and you put whatever you want there.

> Well, it flows directly out of the scripting language traditions. You don’t
> declare types for variables in Tcl; you didn’t used to do it in Python, I
> guess you can do it some, now. You don’t do it in JavaScript… You just say
> it’s a var.

~~~
nine_k
This apparently has roots in some ancient practices (Tcl is not exactly
young). In 21th century, we likely don't need to save a few bytes and reuse a
variable, and can afford to preserve our sanity instead. Well, we can even
afford a built-in ACID-compliant SQL database!..

------
cjfd
Sure, it is simple and that is why people use it.

But I have also run into hard problems using it. Deleting and renaming columns
is not supported natively. On the internet one can find procedures to do that
but they generally do not take foreign key references into account. Getting
this right is a hard problem. In the end we had a function that could be used
to rename or delete a column even without switching off foreign key checking
but it was not simple and it was not the first iteration of said function and
earlier iterations caused lots of subtle problems, like a column having a
default value or not depending on whether the database was a new database or
was created using schema updates.

Use at your own peril.

~~~
danso
Not disagreeing with your overall point about SQLite's feature set, but IIRC,
`ALTER TABLE RENAME` was added in 2018, for version 3.25.0:
[https://www.sqlite.org/releaselog/3_25_0.html](https://www.sqlite.org/releaselog/3_25_0.html)

(I chime in only to remark that I myself am constantly surprised that SQLite
continues to add these kinds of features – e.g. WINDOW functions were also in
3.25 – long after I had accepted their absence as a fact of life)

~~~
cjfd
Ah, that is interesting. My experiences were before 2018 and I had not kept an
eye on it, so I did not know this.

~~~
cjfd
Ow, but looking at the docs they still do not seem to support delete yet.

------
sriku
While on SQLite, Hipp's fossil-scm is also another great piece of work. My
personal favourite to start projects since I can keep code, docs and issues
all in a single .. SQLite file! fossil is also just a single executable with
no other dependencies and comes with a built-in web server complete with user
management if you want to expose to a small group.

The main and real reason I love fossil is that I'm always at peace knowing
that I can't accidentally bungle up the history. I'm always nervous about that
when I use git .. still.

~~~
int_19h
You can still bungle history with badly authored commits, though. This is
exactly why I like Git - no need to think about how messy the history is while
working on something, I can create lots of small commits tweaking things as I
go, and then clean it all up into larger commits representing _logical_ groups
of changes, with proper description etc.

~~~
sriku
I'm fine with my commit flow and commenting, which perhaps helps. I do use
orphaned dirty branches. It helps that fossil's tags are reusable, unlike
git's.

For me, there've been cases where I've worked on an angle, only to discard it
for another approach .. and much later wanting to revisit that angle because
it was a good idea. If I discard history to "clean it up", such a resurrection
becomes impossible.

------
joshdance
We used SQLite at my company to allow users to write SQL queries against their
db. When we hit the limit of it, we had to switch to Postgres. That migration
is quite difficult and I wish we had used Postgres from the start. 20/20
hindsight but that was my first thought.

~~~
throwaway55554
Were you using an ORM? I ask because most people use database switching as a
selling point for using an ORM. I'm rather indifferent on the matter, but I'm
curious.

~~~
jimbokun
As an alternative to an ORM, there is another great abstraction layer that
works across a large number of databases. It's called SQL!

~~~
imtringued
Until you realize that not even booleans are standardized between SQL
dialects...

------
zubspace
I'm glad something like SQLite exists. It's awesome if you want to store data
in your applications without falling back to a full SQL database. I am also
thankful for those client applications like "DB Browser for SQLite" [1]. It
nearly provides the same functionality like SQL Management Studio, which is
quite an achievement.

The only gripe I have with SQLite is with full text search FTS5. The last time
I tried to store and retrieve text with associated metadata from another table
was really confusing. It kinda worked in the end, but then I also realized
that you can't do a contains-query on your text, just starts-with and ends-
with [2]. This is a major flaw in my book. So many times I just want to dump
tons of text into a table and want to query single words naturally, but I
can't do that without resorting to hacks or unoptimized LIKE queries. And if I
need to take into account typos or similarity, I'd have no idea how to do that
with SQLite. Does someone have experience regarding this?

[1] [https://sqlitebrowser.org/](https://sqlitebrowser.org/) [2]
[https://stackoverflow.com/a/24670334/998987](https://stackoverflow.com/a/24670334/998987)

------
me551ah
SQLite is a brilliant lightweight database which is available everywhere. All
mobile OSs support it and it's a breeze to run on even embedded Linux. I just
wish web browsers would also support it and make webSQL a standard, it would
make programming for the web so much easier.

~~~
3fe9a03ccd14ca5
Once I discovered SQLite, I never went back. It’s so easy. I’m still shocked
when I see docker compose files that spin up entire MySQL dbs when a simple
SQLite connector would be so much easier!

~~~
williamdclt
Whether it is in staging, in dev or in tests, use the same DB engine as in
prod. It _will_ save your butt sometimes.

Also, my docker-compose for a Postgres container is literally:

    
    
        services:
          db:
            image: postgres:10
            ports:
              - "5432:5432"
    

"so much easier" seems very hyperbolic. Not denying that SQLite is an amazing
piece of software though!

------
miguelmota
Something I see some developers do which I don't recommend is using SQLite for
testing environments when they're using PostgreSQL in production. SQLite3
doesn't support a lot of types that PostgreSQL does and it doesn't support
things like full outer joins. Save the headaches of debugging in production
and spin up a docker PostgreSQL instance since testing environments should
emulate production environments as much as possible.

~~~
ebg13
> _and it doesn 't support things like full outer joins_

This at least is possible to fake. But in the era of premade docker packages
for postgres, I do wonder why the practice you describe persists.

~~~
miguelmota
I know some people with a certain generation of Macbook or macOS version have
had bad luck running docker which causes their fan to go nuts and resources
get drained so they prefer to avoid docker if possible.

------
everybodyknows
>... they realized that if this is a critical part of their infrastructure,
they needed to make sure my business was sustainable.

>So companies which are typically large companies that really depend on SQLite
as part of their product, they just pay us an annual fee.

>... we are a 100% engineering shop.

An intriguing option for anyone sitting on an original infrastructure
solution, and looking to turn it into a livelihood, with quality of life as
first priority.

------
AdmiralAsshat
The interview transcript is much appreciated! I hate it when random two-hour
podcast has a 15 minute interview I want to listen to, and have to go hunting
for it.

~~~
rtkwe
Which is a shame because most podcast apps have chaptering systems where
particular interviews and segments can be easily bookmarked. Even without that
feature show notes can include links and timestamps to particular segments too
but many shows don't bother because it's extra work.

------
rhombocombus
I came to SQLite recently having used big iron enterprise tools for my
analytical work previously (namely Oracle), and wow, what a lovely elegant
tool. It isn't just super useful for web and embedded applications, it is
quite a powerful and convenient analytical tool for quickly ingesting huge
flat files and querying them.

------
Animats
Because they took reliability seriously.

If the people who did the original Macintosh "resource fork" updater had been
serious about maintaining consistency of the data structure, that approach
would have lasted longer. That was a really good idea, badly implemented
because they had to cram it into 128KB and use a floppy. Unfortunately, it
wasn't rewritten when the Mac line got more memory and hard disks.

Somebody had to get the open source world past using text files for
everything.

------
bgdkbtv
AFAIK nomadlist.com by @levelsio is using SQLite in production. He has a bunch
of data, plenty of users and his site is very fast. Proof that you can use
SQLite in production on a successful project.

------
crmrc114
Serious question, how in the heck did the podcasters get their transcript? Did
they pay someone to edit a machine translation? That is one of the coolest
things I have never seen for any podcast!

~~~
cxr
Asked and answered here:

[https://news.ycombinator.com/item?id=15251977](https://news.ycombinator.com/item?id=15251977)

~~~
jerodsanto
Yeah except that is a different podcast...

~~~
cxr
... except it's the same answer, only their response is more detailed.

------
mrzacsmith
I found this fascinating, as a student we are taught to use SQLite and then
told to use PostgreSQL for production. I am told that a large amount of mobile
native apps use SQLite. Does anyone have any stats on this? Seems like a nice
choice, but the searches I have done are all over the place.

------
janvdberg
Meta: this podcast's RSS feed only goes back so far (2018). So I have no way
to add/listen to this episode in my podcast app?

~~~
jerodsanto
We used paged feeds[1] for our RSS, but not all podcast clients support it. I
might end up going back to a full feed, but remove the show notes, etc from
older episodes to keep the file size smaller...

[1]: [https://podlove.org/paged-feeds/](https://podlove.org/paged-feeds/)

~~~
janvdberg
Ok that makes sense. But I use Overcast, so how can I listen to it?

~~~
dueyfinster
I use Huffduffer[1] either by searching for someone who has added it
already[2] or adding it directly with details and a link to the mp3 myself.
Then add your huffduff feed to Overcast (can add based on tags also).

[1]: [https://huffduffer.com/](https://huffduffer.com/) [2]:
[https://huffduffer.com/neaj/543363](https://huffduffer.com/neaj/543363)

------
jjice
I've worked on a few internal tools for the company I intern at and I've
always been told to use Postgres. Don't get me wrong, I love Postgres, but for
an internal application that won't get any significant concurrent interaction,
Sqlite is the way to go in my opinion. Maybe I'll try to mention it at the
next meeting.

~~~
mekster
And you'll be told their production is using PostgreSQL.

Having identical environment makes life easier. I never run anything on my Mac
despite many Unix daemons being available but either run it remotely on Linux
or locally via Linux VM with same distro.

------
djhworld
Is there a database file format that supports columnar storage query able via
SQL? For OLAP style queries.

I've tried SQLite for this usecase but the query falls down when querying
millions of rows.

I know there is parquet file format but I'm wondering if there is like a SQL
engine that can sit on top of something like that similar to SQLite

------
manigandham
Here's the MP3 file for those who can't stand 1x playback speed:
[https://cdn.changelog.com/uploads/podcast/201/the-
changelog-...](https://cdn.changelog.com/uploads/podcast/201/the-
changelog-201.mp3)

------
SigmundA
Is there really any other file based SQL alternative anymore?

Back in the day on Windows you would use the Jet (MSAccess) db for this
purpose or maybe dBase before that in DOS. Many apps that need to store data
would use them as it did not need a server process just a library.

I guess you have some NoSql options like LevelDB, but its really nice to have
a full ACID SQL engine running in process.

I always thought it would be great if PostgreSQL could separate into a library
for the DB with a server that uses the lib or you could use the lib directly
in your process but I would imagine the process model is baked heavily into
the DB engine.

~~~
imtringued
h2db for java

------
jokoon
Don't forget about SpatiaLite! Having a GIS-capable lightweight database,
without relying on a typical DB system is pretty nice too.

------
GnarfGnarf
My app had been running on CodeBase (dBase clone from Sequiter) for twenty-
five years. When we converted to macOS, CodeBase wouldn't compile for 64-bit.
Panicked, I turned to SQLite. Miraculously, we were able to switch in a couple
of weeks. It is indeed a pleasure to work with. Especially with tools like DB
Browser to poke around with.

------
bump64
We are currently working on IoT solution where we deploy many distributed
computing units with leaf devices and SQLite works flawlessly to keep local
state on the remote devices. There may be other solutions but this was a no-
brainer for us and so far we haven't had any major issues with it.

------
qwerty456127
Obviously because it's awesome in so many ways. The only thing I miss when
using SQLite are implementations in high-level programming languages. In many
cases (e.g. with .Net WinForms) using a native library is a pain while the
speed native code provides is not always necessary.

------
manigandham
I also recommend this great video walkthrough of SQLite from Richard Hipp:
[https://www.youtube.com/watch?v=Jib2AmRb_rk](https://www.youtube.com/watch?v=Jib2AmRb_rk)

------
rogerthis
Benedict rule.

------
JTbane
It does one thing and does it well.

------
ksharifbd
I use SQLite for demo projects and it is very cool!

------
jbob2000
TL;DR because it's simple.

~~~
umvi
Is it simple, though? I mean, reading about how they test it[1] leads me to
believe it is not simple. Maybe it's simple compared to Oracle, and maybe
_using it_ is simple, but the actual implementation is definitely _not_
simple.

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

~~~
jbob2000
I don't care about the implementation and I congratulate the developers on
abstracting all of that complexity away from me. That's what good software
does.

~~~
umvi
I misinterpreted your comment. I thought you were saying SQLite was successful
because SQlite itself (i.e. the implementation) was simple, not because it was
simple to use.

~~~
wpietri
For what it's worth, I use "simple" for talking about the complexity of the
thing, and "easy" for the user experience. Quite frequently easy things have
to be complex because the cognitive load gets shifted into the machinery.

E.g., compared with a Model T, modern cars are much easier but much more
complex because the car itself understands things like adjusting the ignition
timing and safely getting the motor turning, things that a Model T user had to
understand if they wanted to avoid getting a broken arm before they got out of
the driveway: [https://www.fordmodelt.net/how-to-drive-ford-
model-t.htm](https://www.fordmodelt.net/how-to-drive-ford-model-t.htm)

------
rurban
I find it remarkable that it succeeded, despite its complete and utter lack of
security. It's trivial to overwrite its metatables to create rop chains via
select, it's default fts hook is insecure. It's basically a hack.
[https://research.checkpoint.com/2019/select-
code_execution-f...](https://research.checkpoint.com/2019/select-
code_execution-from-using-sqlite/)

------
hharnisch
For all the great things about SQLite there are some concerning things around
the project.

First off, even though the source code is public domain, you can't contribute
since it is closed source:
[https://sqlite.org/copyright.html](https://sqlite.org/copyright.html)

There are 3 developers who maintain the project
[https://www.sqlite.org/crew.html](https://www.sqlite.org/crew.html) and
operate under a "code of ethics" that used to be called their "code of
conduct"
[https://sqlite.org/codeofconduct.html](https://sqlite.org/codeofconduct.html)

While it succeeded in getting widely adopted I have trouble believing that
this is sustainable.

~~~
dekhn
they have contracts to support the US military for many decades into the
future. Hard to be more sustainable than that.

~~~
hharnisch
There are 3 people on the planet who can make changes to it and one person who
can work on their custom made source control system. Single points of failure
are not sustainable.

~~~
Carpetsmoker
Well, it's been popular for 20 years, so that sounds fairly sustainable to me.
A lot of projects with many more contributors have come and gone in that time
period.

Either way, that doesn't make it "closed source" like you said in the other
comment.

