from Hacker News

SQLite the only database you will ever need in most cases (2021)

by iio7 on 2/15/23, 11:31 PM with 374 comments

• by irskep on 2/16/23, 12:26 AM

  This sentiment pops up regularly on HN, and I've seen at least one article per month for the past few months, but the trouble is, none of them seem to help you actually deploy it. They assume you're comfortable spinning up public web servers.

  If you want to use a PaaS to deploy an app, because you don't want to spend your time learning to be a sysadmin, then all the tutorials are going to put you on the Postgres path, because that's what's supported. (Of course, you'll then end up paying $15+/mo for Postgres, which is hilarious for most hobby projects storing 50MB of data.) But in reality, you could just scale vertically on one machine and be completely fine. No need for "distributed" anything, in theory.

  I took a shot at productionizing SQLite here as an experiment: https://cheapo.onrender.com/ But I'm not sure I did it right, because I don't have much experience working below the level of a PaaS. I'm an application developer and I do not want to become a release engineer. I resent even having to learn Docker. :-)

  Anyway, if somebody wants to nitpick my Flask+SQLite deployment, I'd really appreciate it, because it seems really silly that people have to keep writing this stuff from scratch when 90% of hobby sites have the exact same needs. And the Fly.io/Render configs would apply just as well to Node, Ruby, etc. https://github.com/irskep/cheapo_website

  Edit: Somebody got mad at the Docker bit. I tossed it in for effect, but I promise you I don't hate learning new skills. I wish people would recognize that weekend coding projects should be fun, and sometimes that means avoiding certain kinds of things that a person experiences as difficult or frustrating. Arguing on the internet sucks, who knew?

• by adamkf on 2/16/23, 12:07 AM

  >The only time you need to consider a client-server setup is: Where you have multiple physical machines accessing the same database server over a network. In this setup you have a shared database between multiple clients.

  This caveat covers "most cases". If there's only a single machine, then any data stored is not durable.

  Additionally, to my knowledge SQLite doesn't have a solution for durability other than asynchronous replication. Arguably, most applications can tolerate this, but I'd rather just use MySQL with semi-sync replication, and not have to think through all of the edge cases about data loss.

• by deathclassic on 2/16/23, 12:07 AM

  I like sqlite as much as the next guy but it's built-in datatypes are limited. Things like arrays, UUIDs, geometry stuff, JSON, etc. Sure you can store more advanced stuff as blobs or text but then you have to mess around with deserializing it in the host language and you lose the ability to query it directly in the db engine.

• by ZephyrBlu on 2/16/23, 12:14 AM

  From a technical perspective, almost every tool is more than what you need. There is a lot of mature software that does amazing things.

  Whether the tool fits with your architecture and specific use case is much more important than whether it does the job. You can make most tools work for most use cases, but it might not be a natural fit.

  For example, an in-memory database is probably not conducive with a serverless environment and you would prefer to either host your own DB server or use a serverless DB.

  Or perhaps there are specific Postgres plugins that enable your use case, or a specific Postgres feature like n-gram search (I don't know if SQLite supports that), etc.

  Technical maximalism ("it does all the things!") is great for marketing, but a poor way to choose the appropriate technology for your application.

• by irjustin on 2/16/23, 1:59 AM

  > If your application software runs on the same physical machine as the database, which is what most small to medium sized web applications does, then you probably only need SQLite.

  That's the adoption problem. It has a hard ceiling. Besides embedded, most engineers use a different DB professionally - dare I say pg or mysql? And because of that they'll reach for the tool they already know.

  Sure, I get the argument that SQLLite fits "more exactly" for many many applications because the sheer number that never move off of one machine, but it has this hard ceiling of "what if I need more" and well "I could just use this other tool that goes the full distance, in case one day... oh and I already use it at work."

  That's why SQLLite excels for embedded applications. It fits perfectly. There is no "what if" and the performance is astounding esp in low power.

• by jxf on 2/16/23, 12:24 AM

  > The only time you need to consider a client-server setup is: Where you have multiple physical machines accessing the same database server over a network. In this setup you have a shared database between multiple clients.

  Am I misunderstanding this or is this not the vast, vast majority of all cases?

• by bravura on 2/16/23, 12:32 AM

  I would love to use SQLite for all my Django webapps that have only several simultaneous users, but this article suggests there are too many footguns for me to be able to do that.

  Is there a "using SQLite for a multi-threaded webapp for dummies" package that does all the config I need so I can just drop it in and go and not tune anything?

  Paging fly.io founders etc! If I have a persistent volume can my fly.io apps use SQLite? What are other good micro-hosting options?

• by bob1029 on 2/16/23, 1:22 AM

  SQLite does it all if you look closely enough. Even for performance it can turn out to be the best option.

  If you dare combine (properly-configured) SQLite with a local NVMe disk, you will find yourself well beyond what many hosted solutions can provide (clustered or otherwise). To be clear - the biggest reason for this is the incredible latency reduction, not raw IO bandwidth or disk IOPS (although this helps massively too).

  Millions of transactions per second doesn't mean much when there exist no dependencies between them. The figure I am more concerned with is serial transactions per second. 1 logical thread blocking on every command.

  SQLite is the only database engine I have ever used that can reliably satisfy queries in timeframes that are more conveniently measured in microseconds rather than milliseconds.

• by 0xbadcafebee on 2/16/23, 2:25 AM

  I agree. Most uses of databases definitely don't need to grow larger than, say, a single filesystem, or a single application, or a single host, or a single network, or a single geographical region, or a single customer, or a single organization, or a single global network of customers in organizations in regions on networks on hosts on applications on filesystems.

  There could not be any features of any other database that SQLite might not have, or that an application might need, or want.

  We definitely should not, like, read a book on databases, or read the manual of another database, or something else crazy like that. There's no reason we might learn about other databases. They are just "shiny stuff", meaning, there's something going on with them that I can't see, because of all the glare.

  Honestly, the existence of all those other databases, and database models, and the billions of dollars spent on them, is a fluke, probably. It's unlikely you will ever in your life see or work on an application that needs a database other than SQLite. Because the only applications you will ever work on won't ever run on more than one virtual host, or be used by more than one application. And definitely you will probably not need a feature that isn't in SQLite.

  SQLite is very fast, it is very simple, it is very well written, and it has a lot of tests. Therefore we can conclude that you should never look at or learn about another database, because considering the previously states information, we know that no other database could possibly be desired or needed.

  I don't know a lot about databases. And, granted, I only just found out about SQLite. But I am quite sure I am correct that SQLite is the only one you'll ever need in most cases.

• by posharma on 2/15/23, 11:54 PM

  I love sqlite but can't hold myself from saying: another day, another sqlite post on HN :-)

• by margorczynski on 2/16/23, 12:19 AM

  Yeah, it works when it works. Just that in many cases you'll run into scenarios with it when it completely doesn't or is missing something crucial and we'll get another prodigal son story about going back to Postgres.

• by mythz on 2/16/23, 1:18 AM

  Also worth mentioning Fly.io's work on LiteStream [1] and LiteFS [2] giving SQLite important S3 DR/reliability & multi-node replication and scalability - opening SQLite up to even more use-cases.

  We're making use of this ourselves in https://blazordiffusion.com which runs entirely on SQLite, using Litestream to replicate it to Cloudflare's R2 object storage which is running on a single Hetzner US Cloud VM at €13 /mo.

  As we believe SQLite + Litestream is a very cost effective solution that can support a large number of App's data requirements we've added first-class support to add SQLite + Litestream support in our .NET Project templates [3] which uses GitHub Actions to run Docker compose App deployments along with setting up Litestream replication to AWS S3, Azure Blob Storage and SFTP in a sidecar container that also includes support running DB Migrations on Server with Rollback on failure. If anyone's looking to do something similar, the GitHub Actions + Docker compose configuration that enable this are being maintained at https://github.com/ServiceStack/mix/tree/master/actions

  [1] https://litestream.io

  [2] https://fly.io/blog/introducing-litefs/

  [3] https://docs.servicestack.net/ormlite/litestream

• by bikamonki on 2/16/23, 2:06 AM

  I have similar arguments for "Firebase is the only database you will ever need in most cases" for web apps, be it that you need real-time capabilities or not.

  I can also confidently say: a static HTML landing page is the only website you will ever need in most cases. I suffer every time I see a one-page site, hardly ever updated, built with Wordpress. Why hire an 18-wheeler to deliver a pizza?

• by user3939382 on 2/16/23, 12:50 AM

  I’ve been learning the idiosyncrasies of MySQL since 2003, I doubt whatever benefit SQLite has outweighs 20 years of experience.

• by onlypositive on 2/15/23, 11:56 PM

  > I have run SQLite as a web application database with thousands concurrent writes every second, coming from different HTTP requests, without any delays or issues.

  Is this with nodejs or something single threaded as the webserver?

  I would kind of assume you'd run into issues with something like PHP.

• by zzzeek on 2/16/23, 2:55 AM

  and if you need to migrate your database schema in any non-trivial way.....well then you're on your own. for anything beyond adding a column to a table, you'll have to copy the whole table to a new one with the structure you want, drop the old table, then rename your new table, carrying along all the foreign key constraints and other constraints while you do so. Or use a tool which does this (I write one such tool and it's not fun to maintain).

  if SQLite allowed for custom commands, at least there could be ALTER commands that run this process behind the scenes, which the SQLite developers wouldn't have to maintain.

• by erulabs on 2/16/23, 12:38 AM

  See I’ve been using Vitess on Kubernetes for even personal projects and I gotta say I love that I can run, for 10 bucks a month on Linode, the same tools that I know by experience I can scale to a multi-billion dollar valuation worth of customers. Heck I even run it in development on my laptop thanks to Skaffold.

  Sure it’s all insane overkill - but I use Linux for the same reasons - I want one API that I can use everywhere, from my toaster to my spaceship, from hobby to enterprise.

  The simplicity is not the API. The simplicity is having one API.

• by ripdog on 2/16/23, 12:54 AM

  I have wondered why Synapse, the most feature-complete Matrix homeserver, so vehemently recommends against use of SQLite as it's backing db. They say that the performance is insufficient and it's only appropiate for testing purposes.

  That would make sense if you assume that Synapse is only going to be used in instances with hundreds/thousands of users, but plenty of people host their own instances for themselves only. Surely SQLite would be plenty for single-user instances, or family instances?

• by xenator on 2/16/23, 12:53 AM

  A spoon is the only instrument you will ever need in most cases.

• by xwdv on 2/16/23, 12:35 AM

  Why learn SQLite when you could just learn Postgres and have a database that is virtually guaranteed to be enough in almost all cases?

• by sireat on 2/16/23, 4:53 PM

  I am ashamed to admit I've run into a case where SQlite was the wrong choice.

  I have a cluster of 20 Windows 7,8,10,11 machines spread across multiple sites that I wanted to run some log analytics. This was for some custom software running on all 20 machines which had a SQlite API.

  So I did the simplest and cheapest thing that would work.

  I setup sync.com folder on all machines (think dropbox/ onedrive) to write logs to the same log.db file across all machines.

  This worked great at first (I could analyze the db remotely).

  However you see the potential problem here...

  Logging was maybe a few writes an hour PER MACHINE but inevitably you start getting conflicts (since sync takes a 5-30 seconds to actually sync).

  Now I am faced with merging 20 conflicting log.db files.

  In theory I should have used a server based SQL database. Or perhaps I should have just lived with 20 different log.db files.

  In my defense there was only SQlite API so I would have had to write some middleware to transfer to another DB.

• by dang on 2/16/23, 3:03 AM

  Related:

  SQLite the only database you will ever need in most cases - https://news.ycombinator.com/item?id=26816954 - April 2021 (370 comments)

• by joshspankit on 2/16/23, 2:13 AM

  I’ve argued this before and I’ll argue it here now:

  Modern computers are fast enough that in many cases “the only database you will ever need” can be files on the filesystem. For example “1 row = 1 file”.

  It brings additional benefits as well: for low-write applications you can use git to get a history (+transactions if you store them in the log), backups are super easy, replication is trivial. For higher-write applications it gets more complex but you can still plan and implement most of the traditional DB scaling techniques (and even implement them one at a time as you go grow).

  Computers are “stupid fast” now that we’ve gotten off platters.

• by hammock on 2/16/23, 12:41 AM

  I used to use SQL Server on a PC to deal with tables over 1 million rows. I'm on a Mac now... can I use SQL Lite? What is my best option? I am not a coder, I just know enough how to query SQL databases.

• by Dig1t on 2/16/23, 2:58 AM

  This post inspired me to switch one of my projects to SQLite, but then I remembered that it uses PostGIS, and the ORM I use does not support the SQLite GIS types :(

  I wish SQLite had GIS support built in.

• by SnowHill9902 on 2/16/23, 2:44 AM

  The only database you’ll need 60% of the time every time.

• by revskill on 2/16/23, 12:55 AM

  In memory SQlite for unit testing is about 45 miles/h, which is the fastest db engine i've used, like a cheetah.

• by osigurdson on 2/16/23, 2:28 AM

  SQLite is great if you just have one process accessing the db, other wise it is a dumb choice.

• by albert_e on 2/16/23, 4:56 AM

  AWS has a habit of taking a open source project and creating a "managed service" offering of it.

  Is it possible to offer SQLite as a managed / serverless offering?

  A light weight and cheap relational data store that we just consumer using an API

• by umvi on 2/16/23, 2:15 AM

  Everytime I try to use SQLite I run into db locking issues where I seemingly have to try to run my query in a retry loop. Am I doing something wrong or does SQLite just not play nice in multi threaded contexts?

• by leke on 2/16/23, 6:25 AM

  As someone who uses free hosting for personal projects, I thought I couldn't use SQLite because it wasn't advertised as being available. I'm beginning to find this isn't true.

• by freilanzer on 2/16/23, 10:46 AM

  I'm using DuckDB atm instead of SQLite: https://duckdb.org/

  For data science purposes, it seems to be quite interesting.

• by nikeee on 2/16/23, 2:35 AM

  I really like SQLite and I use it a lot. There are only two things that are missing to make it near-perfect:

  - A type for Instants / time handling

  - being strict with types. No inserts of ints into a string column

• by OrvalWintermute on 2/16/23, 2:25 AM

  > In contrast to many other database management systems, SQLite is not a client-server database engine, but you actually very rarely need that. If your application software runs on the same physical machine as the database, which is what most small to medium sized web applications does, then you probably only need SQLite.

  Disagree.

  If you think about it from an attack surface perspective, there are numerous advantages to isolating the database. There are performance, availability, sharding, and columnar options out there also that may better meet the use-case (just to name a few). I have ran Postgres on endpoints when developing with performance akin to SQLite. Further, there are numerous ways in which to increase performance, availability, or to pursue some of the more customized versions of Postgres depending on use-case. One of the times I used Postgres was with Oracle DBAs, and they found the transition pretty simple.

  Various customizations / extensions / versions of PG

  There are security versions e.g. https://www.crunchydata.com/products/hardened-postgres

  Columnar / high performance Parallelized extensions e.g. https://www.citusdata.com/product

  General Purpose / Oracle transitions e.g. https://www.citusdata.com/product

  Yandex even has an embedded Postgres https://github.com/yandex-qatools/postgresql-embedded

  If you'd like to see a full list of features see https://www.postgresql.org/about/featurematrix/

  More than this though, PG has a really excellent community with a large amount of talented folks, available both individually and through OSS oriented companies https://www.postgresql.org/support/professional_support/ and willing to help out on Libera https://www.postgresql.org/about/news/migration-of-postgresq...

• by ReflectedImage on 2/16/23, 1:14 AM

  Might as well be using MongoDB if you are going to use SQLite in prod.

  https://youtube.com/watch?v=b2F-DItXtZs

• by jameshart on 2/16/23, 1:00 AM

  Back in 1999 or so, before SQLite was a thing, I used to throw little ASP websites together with an MS access .mdb file as the backend connected up through ODBC.

  It was neat and quick and easy to get running right on your regular windows desktop.

  By the criteria of this article, that was apparently the only database I ever needed. It could handle the multiple reads and occasional write of a small scale website. Backing it up consisted of copying the file. It was accessed through a simple standard library (ODBC) and supported SQL.

  Since that was the only database we needed, what does SQLite bring to the table?

• by ilrwbwrkhv on 2/16/23, 3:00 AM

  Pocketbase which uses sqlite is a total game changer. I am slowly moving away from Supabase to Pocketbase.

• by vouaobrasil on 2/16/23, 12:41 AM

  Except for people running Wordpress on shared webservers. Then MySQL is the only database you can ever use.

• by sammy2255 on 2/16/23, 1:31 AM

  What if I want things to connect to it?

• by lerax on 2/16/23, 2:04 AM

  saying the obvious for the new comers, sometimes is really necessary in that generation of master of overengineers and not only that, but with pretty useless and unecessary abstractions

• by tmpfile on 2/16/23, 3:07 AM

  I wish sqlite made their terminal interface a bit more robust or emulated psql’s interface. Simple things like \d tablename would be great.

• by thedudeabides5 on 2/16/23, 12:38 AM

  We should teach SQL in high school

• by kris-nova on 2/16/23, 12:11 AM

  SQLite — works 80% of the time — every time.