Why SQLite succeeded as a database (2016)

by Tomte • 6 years ago

589 281 6 years ago

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• 6 years ago

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.

jbob20002• 6 years ago

TL;DR because it's simple.

umvi7• 6 years ago

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

jbob20002• 6 years ago

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.

solarkraft• 6 years ago

It's simple to use.

scottlamb4• 6 years ago

I mean, reading about how they test it[1] leads me to believe it is not simple.

They are unusually thorough in their testing. I see that as a good thing, not a sign it isn't (relatively) simple. In fact, I don't think any other database engine is tested as thoroughly. I can't even imagine how much effort it'd take to do so for Oracle or even PostgreSQL, except that it'd be prohibitive.

gwd• 6 years ago

I'd say there are a load of reasons, all of which combine together to make it unbeatable:

* Simple to embed into you application (no setting up a separate database, &c).

* Fantastically backwards compatible

* Public domain (so easy to comply with the license)

* High quality: Reasonably fast, reasonably efficient, reasonable feature support.

The lack of any of these would add significant drag. It's difficult to imagine any other project catching up and displacing it at this point.

bob10296• 6 years ago

I personally cannot see any near term replacement for what SQLite offers. In my world, SQLite became the way in which I now manipulate a scope of structured data on a single logical disk. I mostly view it as a very reliable and friendly layer on top of basic file IO which also happens to be compatible with virtually every computer on earth. I can copy a SQLite database out of an embedded microcontroller environment and directly open it on my x86_64 workstation, edit some values, and then copy it back down without any fears of compatibility issues.

thrower1232• 6 years ago

I do wonder how much of the time that SQLite is used, that it wouldn't be even simpler to just dump things to an XML file.

lurker458• 6 years ago

Here are some considerations why: https://danluu.com/file-consistency/

danso4• 6 years ago

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• 6 years ago

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

cheez• 6 years ago

And I thought it was S-Q-lite. S-Q-L-ite makes sense though. Most consistent.

larrydag• 6 years ago

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

buckminster• 6 years ago

Sequel-lite here. I know that makes no sense phonetically.

sweeneyrod• 6 years ago

No love for "seekwelitay"?

agumonkey• 6 years ago

and it does sound like a mineral which is quite in line with how I see sqlite.. pretty, timeless and rock solid

big_chungus2• 6 years ago

I think there's a case for disputing the official pronunciation because it's based off an "official" pronunciation of SQL. I've heard it ess-queue-elle and sequel, but personally side with sequel. It was originally called “Structured English Query Language”, abbreviated and pronounced "SEQUEL"; later shortened to "SQL" but same pronunciation. So I figure sequel-ite is a perfectly legitimate pronunciation.

danso4• 6 years ago

That's how I do it. I understand the etymological reasons for "S-Q-L-ite", but SQLite is just too ubiquitous in my everyday work for me to speak 4 syllables when referring to it.

gavinray• 6 years ago

Last month I discover NGINX is not pronounced "en-jinx", and today I discover SQLite is not pronounced "sequel-light".

2020 is turning out to be pretty terrible guys.

eropple• 6 years ago

Conscientiousness and empathy are rare commodities in software development. It's difficult for somebody to get out of their own head and consider a problem fresh, and it's not always personally rewarding to do so. It's also expensive, if you want to back it up with user testing.

You see it, even, in ostensibly public-consumption open-source projects, where the goal is clearly not merely to scratch one's own itch but to market the project for uptake for this reason or that. I feel like SQLite is a great example of what happens when a team is able to foster the kind of empathy to really understand how the user wants it to be.

BlueTemplar3• 6 years ago

Well, to be fair, it sounds like that it at least started out as a project where the programmer and the user were the same person?

barkingcat• 6 years ago

I pronounce it "ess que lite"

hazebooth• 6 years ago

I'll be honest and say that in my head, I think 's-q-l lite'

krn• 6 years ago

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.

The same could probably be said about Redis and Salvatore Sanfilippo.

remmargorp64• 6 years ago

I'm still just gonna keep calling it "sequel light".

syspec• 6 years ago

What’s the episode number?

anitil2• 6 years ago

One of the many reasons I love sqlite and redis are because of the attitude of the developers. Sqlite has just an uncanny amount of testing, I wonder if it's the most formally tested piece of user software (outside of formally proven software and the space/medical industry I suppose).

And Salvatore (redis) has a way of simplifying issues that gives me a great deal of confidence in the decisions he makes and the code he writes. I really like the code and reasoning behind his 'linenoise' library in particular

cjfd3• 6 years ago

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.

acdanger• 6 years ago

I've never had an issue altering or deleting columns in SQLite. Grant it, I've used both Sequelize and SQLAlchemy as ORMs.

But if you're using SQLite for simplicity there's a good chance you're not interested writing raw SQL statements either.

The_Colonel2• 6 years ago

Yeah, I'm using SQLite for simplicity and exactly because of that I want raw SQL instead of some leaky complex ORM abstractions.

oefrha2• 6 years ago

Pretty sure SQLA the ORM has nothing to do with schema migration, which is usually handled with alembic, the sister project. Not sure about Sequelize.

Now, there’s nothing magical about alembic’s handling of renaming or deleting columns for SQLite; it has to recreate the table just like what you would do if you’re hand rolling the SQL, so it has the same problems.

Details: https://alembic.sqlalchemy.org/en/latest/batch.html

danso4• 6 years ago

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

(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)

cjfd3• 6 years ago

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

dana321• 6 years ago

Yeah, its a pity it doesn't have the ability to do anything more than alter table statements that add to a schema, having to rename the table, create a new schema and copy its contents from the old table can be quite a tricky and troublesome thing to do if you've got a live system running with users on it.

janvdberg2• 6 years ago

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?

jerodsanto3• 6 years ago

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/

janvdberg2• 6 years ago

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

me551ah• 6 years ago

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• 6 years ago

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• 6 years ago

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!

jopsen• 6 years ago

How do you ensure persistence in production?

With postgres I just buy a managed setup from heroku, GCP or AWS. Backup is automatic, and there is a follower to takeover if the leader does/crashes.

It's easy to backup an sqlite snapshot, but you can't do that after every operation.

Curious, how do people solve this?

hasperdi• 6 years ago

Actually, it was about to become standards. Some browsers implemented it, but then they decided not to go ahead as all browsers will have the same SQLite implementation.

atishay811• 6 years ago

It is weird in the hindsight as with just Webkit(which is most of blink still) and Gecko, there are just two engines left. And when Gecko goes down (which is losing market share), this would seem even more stupid. SQL is a standard. They could have standardized on it rather than an implementation.

joshdance• 6 years ago

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.

kmfrk• 6 years ago

Do you have any good articles or posts you can think of for managing the migration, in case there're some lifesavers out there.

rocmcd• 6 years ago

Not an article, but I've used pgloader for this purpose in the past:

https://pgloader.readthedocs.io/en/latest/

Great tool, I can't recommend it enough.

throwaway55554• 6 years ago

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.

kirstenbirgit• 6 years ago

You still have to migrate the data. I faced the same kind of dilemma, but with MySQL, and kind of noped out when I got to stuff like [0].

[0] https://stackoverflow.com/a/87531/1210797

jtdev• 6 years ago

Ha! This selling point is just one in a miserable litany of poor reasons to use an ORM - you absolutely cannot simply switch between databases without doing some work to ensure that the data is migrated correctly and the SQL statements translate properly.

jimbokun• 6 years ago

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

imtringued5• 6 years ago

An ORM does indeed force you to write lowest common denominator code but I wouldn't rely on that.

jessermeyer2• 6 years ago

Did you use WAL?

fyfy18• 6 years ago

For anyone wondering why: I was working on a hobby project a few years ago that had to do a lot of inserts in a SQLite DB. Performance was ok, but it wasn't great either. Turning on WAL greatly sped up performance. In both cases just a single thread was writing to the database.

With the WAL turned off (the default), when you start a transaction SQLite will duplicate the data into a new file, change the original file, and delete the backup file when the transaction is committed. With WAL turned on it will write each change to a journal file, and merge this into the main file periodically (by default when the change file grows to 1000 entries) - so only a single write is needed.

rhombocombus• 6 years ago

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• 6 years ago

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.

bob10296• 6 years ago

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).

api• 6 years ago

I came here to say this. It succeeded because it's an extraordinarily high quality piece of code!

The 'lite' is even a bit of a misnomer. Properly tuned I've seen 1TB+ SQLite databases work just fine. It doesn't scale horizontally with CPU cores as well as a "real" database but it can perform very well with large data sets if there's only one or two writers.

StreamBright• 6 years ago

What is the underlying storage structure that scales to TB+?

nbevans2• 6 years ago

There is actually no "lite" word in SQLite. Think of it as SQL-ite. The "ite" is a deliberate connotation to rocks/geology i.e. granite, magnetite, etc. Make of that what you will. This is documented BTW on the SQLite docs.

inferiorhuman2• 6 years ago

one connection per logical operation sucks for SQLite

How is this typically handled? Using OS file locking primitives?

zahrc• 6 years ago

Yes

https://www.sqlite.org/faq.html#q5

jessermeyer2• 6 years ago

I know SQLite's SQL query engine is implemented as a VM. Are there ways of optimizing repeated queries, similar to compiling a query to avoid the VM churn? Perhaps more importantly, have you ever been disappointed by the query engine's performance?

nbevans2• 6 years ago

You can "prepare" a SQL statement and use it multiple times. I.e. you can use the same VM for a statement many times.

kccqzy2• 6 years ago

You can put each query and the result of preparing that query into an LRU cache (or even just a static pointer in C if your application only ever deals with a single connection). Then you can reuse these without preparing them over and over.

zzo38computer4• 6 years ago

You can prepare a query to use multiple times (if you are using the C interface, at least; as far as I know the command-line interface does not support this), as another comment mentions. You can also prefix a SQL statement by EXPLAIN to view the VDBE codes. But, I see that sometimes the VDBE code is not as good as it should be.

miohtama4• 6 years ago

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

webpaymentsguy• 6 years ago

You probably already heard of storing amount in cents as an integer, but it's a pattern worth mentioning if it fits your use case.

BlueTemplar3• 6 years ago

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

massysett2• 6 years ago

In addition to the other workarounds suggested, you can store the numbers in two columns - one for the coefficient and one for the power of ten, negated. For instance 2.50 becomes 250 and 2, meaning 250 x 10 ^ -2. You can then do math at the application level, or even in SQLite with user-defined functions or even with recursive CTEs if you’re a bit of a masochist.

bob10296• 6 years ago

You know its strange - We work with an incredible number of decimal facts (financial data), but we find that this has zero impact on our SQLite usage because we have policy where extremely complex business entities that are prone to rapid change are serialized to JSON and stored as BLOB columns. Within this JSON serialized data is where virtually all of our decimal values reside. Obviously, its not a solution for any arbitrary case, but you can usually find workarounds in interesting places further up the stack. We use only 3 data types in SQLite - BINARY, TEXT and INTEGER.

manigandham4• 6 years ago

Use a 64-bit integer and store the value as microdollars (or whatever main currency you're using). Simple, fast, correct, and compatible everywhere.

porker• 6 years ago

There is a SQLite decimal extension, unfortunately still under development and not finished: https://chiselapp.com/user/lifepillar/repository/sqlite3deci...

to11mtm3• 6 years ago

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.

Look into Linq2Db [1] (Disclaimer, I contribute to the project a little bit.)

It's the perfect in-between for if you want LINQ like abstractions but don't want the overhead of an ORM, it's more like Dapper than, say, EF or NHibernate.

Best of both worlds: I can quickly prototype a design using SQLite, and then flip it over to SQL Server/Postgres by changing a config string.

EF Core is somewhat improved from EF in some ways (It's faster, often as fast as Linq2Db or Raw SQL) but has moved backwards in others (lots of stuff still doesn't work right, more importantly things that -used- to work in EF6). IMO EF Core tries to do too much; It tries to give the same API to interface with both Relational and KV stores, and results in not being great at either.

[1] https://github.com/linq2db/linq2db/

bob10296• 6 years ago

We exclusively use Dapper for all database access. I have zero problems with a few string constants in my services that contain the requisite SQL statements for managing my business entities against the database. It's really nice having such a consolidated context where your SQLite implementations are just single partial classes tacked onto the services you are providing persistence for.

That is one other point - SQLite has enabled us to do some really deep consolidation of our codebase. We generally use one SQLite database per business entity or abstraction. Users.db, Logs.db, Sessions.db, Settings.db, etc. It allows for a truly 100% independent vertical stack (aside from coupling against business models) for purposes of developing various pieces of business functionality. This is, in my view, is the perfect ideal of 'microservice' architecture. One big folder called services, one class per 'microservice', one SQLite database per class, one collection of mappers per class, etc. I honestly feel like we have settled on the perfect grain for our codebase structure (at least in C#/.NET Core land). The benefit in our world is that instead of HTTP-based RPC, we are doing direct method invocation throughout. We also use Blazor in order to extend this concept as close to the screen as technically feasible.

SNACKeR992• 6 years ago

Thanks for reminding me about this, just taking a quick look at it now. Quick question: do you know why it uses methods like db.InsertWithInt32Identity(product) rather than db.InsertWithIdentity<int>(product) or similar?

GordonS• 6 years ago

I've been using SQLite for something like 20 years - how did I not know about `user_version` until now?!

I've always added a `schema_version` table with a single row containing a single column with the version, so thanks for letting me know about this!

scottlamb4• 6 years ago

I didn't know about user_version or application_id either until just now. I think I'll start setting application_id. Probably not registering it in the SQLite repository - there's no procedure to do that at the top of the file and it looks like almost no one does. I'll just pick a random 32-bit int and assume I don't collide.

I don't think I'll use user_version though. I prefer my current solution: a version table with multiple rows. One for initial installation and one for each schema upgrade with timestamps and the software version that performed the operation. The current version is just the max. If my migration procedure goes badly, I have some clue what happened.

If my application supported schema downgrades, I'd want to have a separate operation_id and schema_version; the max operation_id would win rather than the max schema_version.

plq• 6 years ago

We also do something very similar but our version table has two columns: module_name and version. Instead of doing central schema management like in a usual monolith, Every module manages its own schema.

zmj• 6 years ago

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.

Agreed. I wrote a performance-oriented lightweight ORM on top of SQLite calls: https://github.com/zmj/sqlite-fast . Its focus is minimizing allocations - treating SQLite as something closer to an in-memory store than a network API.

OrgNet• 6 years ago

Doesnt sqlite fail fast when many users commit at once?

samatman3• 6 years ago

There's a level of write-heaviness after which SQLite, even in WAL mode, is the wrong choice.

But one can get pretty far with this design. Thousands of writes per second from a few dozen writers, no problem. Millions and hundreds, I'd use something else for that.

mrighele• 6 years ago

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).

It seems to me that in that case you cannot use transactions though, or am I wrong ?

easytiger• 6 years ago

You can use transactions. They determine batch persistence of the data rather than persisting every insert.

You can just batch them up by

    mod N inserts or Z milliseconds

if you have enough data If you don't particularly care you can disable syncing too.

felixge• 6 years ago

I love SQLite, but I'd be curious how you were able to saturate an NVMe drive with it. Were you doing the inserts in large batches?

Saturating an NVMe drive requires writing with very large block sizes or very high queue depths.

bob10296• 6 years ago

Inserting rows with ~500KB blob columns (JSON data) on a single connection w/ WAL enabled & pragma synchronous == NORMAL or OFF.

I will admit that it would be very difficult to saturate a disk that can write on the order of gigabytes per second if you are trying to insert rows that are only a few KB in size (at least without batching these into larger entities or requests).

NicoJuicy• 6 years ago

For programming, the use-case for EF or expression-tree queries is the specification pattern.

Although you are correct that it doesn't handle complex queries very fast ( the ORM), there is definitely a valid motivation to use/enforce it.

Complex queries are mostly handled through something like elastic search or search engine ( azure), the goal is to keep queries simple and if the code is loosely coupled, it's easier to do this.

hharnisch2• 6 years ago

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

There are 3 developers who maintain the project 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

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

dekhn2• 6 years ago

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

hharnisch2• 6 years ago

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.

SQLite• 6 years ago

Just to be clear: We do not have any support contracts with the US military, nor any other US government agency, nor any other government entity, either inside or outside the US. Not that we would turn down such work if it were available, it is just that is has never come up.

otoburb• 6 years ago

They address this directly under the section entitled "Open-Source, not Open-Contribution":

SQLite is open-source, meaning that you can make as many copies of it as you want and do whatever you want with those copies, without limitation. But SQLite is not open-contribution. In order to keep SQLite in the public domain and ensure that the code does not become contaminated with proprietary or licensed content, the project does not accept patches from unknown persons.

In other words, the reasoning is that since the code is released to the public domain, they want to ensure they can continue doing so without encumbering or confusing future releases with tainted contributions. Quite admirable.

thrower1232• 6 years ago

It's unfortunate that they bowed to pressure and removed their original monastic code of conduct.

oefrha2• 6 years ago

Being open source and being open to contributions are pretty much orthogonal. SQLite itself is every bit open source.

This isn’t even a particularly strange arrangement for open source. See The Cathedral and the Bazaar.

jjeaff• 6 years ago

Huh? The page you linked clearly says "Open Source".

So even if those 3 developers disappear tomorrow, you can fork the source code and compile and maintain your own.

https://www.sqlite.org/src/doc/trunk/README.md

And it's already been sustained for 20 years meaning it has outlasted the great majority of software projects out there.

greenshackle2• 6 years ago

It's pretty normal for open source projects to refuse contributions from anyone who hasn't signed a CLA or something similar. The alternative is a legal nightmare.

outworlder• 6 years ago

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.

combatentropy• 6 years ago

"SQLite does not compete with client/server databases. SQLite competes with fopen()." --- https://www.sqlite.org/whentouse.html

jimis• 6 years ago

(including features like undo)

Can you please elaborate on this?

g82918• 6 years ago

Transaction rollback is probably what he meant. If that is a familiar topic to you the relation to undo should make sense and his point should be clear. If that doesn't make sense, look up ACID, Transactions, and Migrations in the context of a database.

hinkley2• 6 years ago

I had a different interpretation. It's not uncommon in commercial code to use a sort of copy-on-write behavior where every edit creates a new row and marks the other one as dead (for instance, filling in or blanking out a column to mark it defunct). You leave an audit trail behind.

Which, with a little bit of work, can be used to revert to earlier versions.

kalleboo• 6 years ago

Even Apple, home of not-invented-here, built their "Core Data" object storage API (which is extremely prevalent throughout iOS and macOS apps) using SQLite as a backing store.

flukus• 6 years ago

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.

imtringued5• 6 years ago

Why are you comparing schemas to file formats? Those configuration files still have their own user defined schemas.

gwbas1c• 6 years ago

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.

ric2b• 6 years ago

And you lose the ability to quickly change settings in a text editor

There are plenty of SQL CLI/GUI clients that support SQLite.

But if you need end users to be able to change some configurations sure, move those to standalone files and parse them.

int_19h3• 6 years ago

You don't lose the ability to have multiple or tiered settings files - there's nothing preventing you from using as many SQLite database files as you want.

The other points are valid, but not everything can be shoehorned into JSON or XML. Sometimes the data size is too large for the read-everything-into-memory approach. Sometimes you need to deal with a lot of binary blobs. And so on.

crmrc114• 6 years ago

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!

shadowgovt• 6 years ago

I don't know how they seeded the transcript set (looks like this one landed in 2017), but nowadays, transcripts are accepted via GitHub.

https://github.com/thechangelog/transcripts

cxr2• 6 years ago

Asked and answered here:

https://news.ycombinator.com/item?id=15251977

jerodsanto3• 6 years ago

Yeah except that is a different podcast...

jerodsanto3• 6 years ago

Yes, we pay a human to transcribe each episode and then we open source the markdown on GitHub for community collaboration/improvement.

everybodyknows• 6 years ago

... 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.

GnarfGnarf• 6 years ago

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.

vbezhenar• 6 years ago

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.

zachwill• 6 years ago

I actually prefer SQLite's take on type affinity: https://www.sqlite.org/datatype3.html

senderista5• 6 years ago

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.

v642• 6 years ago

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.

scottlamb4• 6 years ago

SQLite's quirks page [1] actually suggests the same thing:

In retrospect, perhaps it would have been better if SQLite had merely implemented an ANY datatype so that developers could explicitly state when they wanted to use flexible typing, rather than making flexible typing the default. But that is not something that can be changed now without breaking the millions of applications and trillions of database files that already use SQLite's flexible typing feature.

I wish they had done this in the first place. I wish even now that they implemented a pragma to enforce types (and introduce the ANY type for folks who explicitly want it). But SQLite has enough good things going for it that I'm happy to use it in spite of this problem.

[1] https://www.sqlite.org/quirks.html

mark-r2• 6 years ago

It's absolutely intuitive for people used to programming languages that aren't strongly typed, like Python.

rankam• 6 years ago

Just FYI Python is strongly typed https://stackoverflow.com/questions/11328920/is-python-stron...

massysett2• 6 years ago

All you have to do is put a column constraint on there. CREATE TABLE t ( intcol CHECK (typeof(intcol) = ‘integer’));

kccqzy2• 6 years ago

Can't you just add CHECK constraints then?

hinkley2• 6 years ago

I finished the podcast a couple hours ago. It was pretty clear to me that Hipp has the perspective of a person working on small teams, where most of the changes to the system either don't affect you or are highly visible.

This is not a man who fears the existential dread of discovering, just after you've stated loudly that some part of the system could not possibly break that way, that some other developer has pulled the rug out from under you when you weren't looking.

The odds that I add the string "52" to a column I know damn well has integers is pretty low, but non-zero. And I'm gonna transpose some fields eventually. The odds of any particular coworker doing the same is some multiple of that. But q^n across my entire team, across months and years, across new hires and people switching into and out of the team? We are so deep into Murphy's Law territory that it's almost a certainty.

It's a fool's errand to try to prevent absolutely every one of those scenarios happening. All I ask is that we have a way to discover them early and cheaply.

mrzacsmith• 6 years ago

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.

jokoon• 6 years ago

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

JTbane• 6 years ago

It does one thing and does it well.

bgdkbtv• 6 years ago

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.

jjice• 6 years ago

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• 6 years ago

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.

miguelmota2• 6 years ago

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• 6 years ago

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.

miguelmota2• 6 years ago

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.

to11mtm3• 6 years ago

Depends.

The Micro-Orm I use in C# manages to work around this well enough; the only thing SQLite is really hard to work with is Time since there's no real structure defined for it.

oftenwrong• 6 years ago

Yes, I often see Java developers using an embedded H2 database [1] for development and test, and a "real" database in production, and there are always unexpected issues of false-positive tests and functionality due to this. Terrible anti-pattern. I am glad "Dev/prod parity" is recognised in The Twelve-Factor App manifesto [2].

I do what you suggest: run dev/test against a real PostgreSQL (of the same version) running in a container. In fact, I take this further and run builds against an actual PostgreSQL, since I like to generate code based on the DB schema using jOOQ [3].

The build proceeds like so:

1. Spin up a fresh PostgreSQL container

2. Run my schema migrations against it

3. Run the jOOQ code generator against it

4. Compile code

This provides some additional guarantees, including:

1. My queries are fully type-safe from my code down to the database schema level

2. My schema migrations can be run against an empty database

[1] https://www.h2database.com/html/main.html

[2] https://12factor.net/dev-prod-parity

[3] https://www.jooq.org/doc/3.13/manual/code-generation/

rogerthis• 6 years ago

Benedict rule.

ksharifbd• 6 years ago

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

manigandham4• 6 years ago

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

manigandham4• 6 years ago

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

rurban• 6 years ago

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...

sriku2• 6 years ago

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.

tomaskafka• 6 years ago

knowing that I can't accidentally bungle up the history. I'm always nervous about that when I use git

I just read a great insight yesterday:

Understanding there’s no harm to be done eases things a lot. It encourages experimenting, which is one of the most effective ways to learn new things and to get familiar with them. Imagine this: you start rebase, mess things up, hit Ctrl+Z several times and try again. Good news is, we can implement Undo/Redo given just ordinary Git implementation. Git already stores all the possible states our repo was in. All Undo/Redo will have to do is to shuffle some branch pointers here and there.

https://tonsky.me/blog/reinventing-git-interface/

int_19h3• 6 years ago

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.

sriku2• 6 years ago

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.

zubspace• 6 years ago

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/ [2] https://stackoverflow.com/a/24670334/998987

bump64• 6 years ago

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.

SigmundA• 6 years ago

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.

imtringued5• 6 years ago

h2db for java

djhworld• 6 years ago

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

qwerty456127• 6 years ago

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.

Why SQLite succeeded as a database (2016)

Related Stories