TE
TechEcho
Home24h TopNewestBestAskShowJobs
GitHubTwitter
Home

TechEcho

A tech news platform built with Next.js, providing global tech news and discussions.

GitHubTwitter

Home

HomeNewestBestAskShowJobs

Resources

HackerNews APIOriginal HackerNewsNext.js

© 2025 TechEcho. All rights reserved.

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

391 pointsby iio7over 2 years ago

43 comments

irskepover 2 years ago
This sentiment pops up regularly on HN, and I&#x27;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&#x27;re comfortable spinning up public web servers.<p>If you want to use a PaaS to deploy an app, because you don&#x27;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&#x27;s what&#x27;s supported. (Of course, you&#x27;ll then end up paying $15+&#x2F;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 &quot;distributed&quot; anything, in theory.<p>I took a shot at productionizing SQLite here as an experiment: <a href="https:&#x2F;&#x2F;cheapo.onrender.com&#x2F;" rel="nofollow">https:&#x2F;&#x2F;cheapo.onrender.com&#x2F;</a> But I&#x27;m not sure I did it right, because I don&#x27;t have much experience working below the level of a PaaS. I&#x27;m an application developer and I <i>do not want</i> to become a release engineer. I resent even having to learn Docker. :-)<p>Anyway, if somebody wants to nitpick my Flask+SQLite deployment, I&#x27;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&#x2F;Render configs would apply just as well to Node, Ruby, etc. <a href="https:&#x2F;&#x2F;github.com&#x2F;irskep&#x2F;cheapo_website">https:&#x2F;&#x2F;github.com&#x2F;irskep&#x2F;cheapo_website</a><p>Edit: Somebody got mad at the Docker bit. I tossed it in for effect, but I promise you I don&#x27;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?
评论 #34813487 未加载
评论 #34813389 未加载
评论 #34813241 未加载
评论 #34815837 未加载
评论 #34813207 未加载
评论 #34814305 未加载
评论 #34817082 未加载
评论 #34813287 未加载
评论 #34813506 未加载
评论 #34814792 未加载
评论 #34814469 未加载
评论 #34817370 未加载
评论 #34814858 未加载
评论 #34821671 未加载
评论 #34813808 未加载
adamkfover 2 years ago
&gt;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.<p>This caveat covers &quot;most cases&quot;. If there&#x27;s only a single machine, then any data stored is not durable.<p>Additionally, to my knowledge SQLite doesn&#x27;t have a solution for durability other than asynchronous replication. Arguably, most applications can tolerate this, but I&#x27;d rather just use MySQL with semi-sync replication, and not have to think through all of the edge cases about data loss.
评论 #34813033 未加载
评论 #34815150 未加载
评论 #34813460 未加载
评论 #34813305 未加载
评论 #34812953 未加载
评论 #34813688 未加载
deathclassicover 2 years ago
I like sqlite as much as the next guy but it&#x27;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.
评论 #34813227 未加载
评论 #34813088 未加载
评论 #34813067 未加载
评论 #34813261 未加载
评论 #34813052 未加载
评论 #34813043 未加载
评论 #34814403 未加载
评论 #34817496 未加载
评论 #34813084 未加载
ZephyrBluover 2 years ago
From a technical perspective, almost every tool is more than what you need. There is a lot of mature software that does amazing things.<p>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.<p>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.<p>Or perhaps there are specific Postgres plugins that enable your use case, or a specific Postgres feature like n-gram search (I don&#x27;t know if SQLite supports that), etc.<p>Technical maximalism (&quot;it does all the things!&quot;) is great for marketing, but a poor way to choose the appropriate technology for your application.
irjustinover 2 years ago
&gt; 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.<p>That&#x27;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&#x27;ll reach for the tool they already know.<p>Sure, I get the argument that SQLLite fits &quot;more exactly&quot; for many many applications because the sheer number that never move off of one machine, but it has this hard ceiling of &quot;what if I need more&quot; and well &quot;I could just use this other tool that goes the full distance, in case one day... oh and I already use it at work.&quot;<p>That&#x27;s why SQLLite excels for embedded applications. It fits perfectly. There is no &quot;what if&quot; and the performance is astounding esp in low power.
评论 #34815311 未加载
jxfover 2 years ago
&gt; 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.<p>Am I misunderstanding this or is this not the vast, vast majority of all cases?
评论 #34813421 未加载
bravuraover 2 years ago
I would <i>love</i> 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.<p>Is there a &quot;using SQLite for a multi-threaded webapp for dummies&quot; package that does all the config I need so I can just drop it in and go and not tune anything?<p>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?
评论 #34813298 未加载
评论 #34814054 未加载
评论 #34813327 未加载
bob1029over 2 years ago
SQLite does it all if you look closely enough. Even for performance it can turn out to be the best option.<p>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).<p>Millions of transactions per second doesn&#x27;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.<p>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.
评论 #34813934 未加载
0xbadcafebeeover 2 years ago
I agree. Most uses of databases definitely don&#x27;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.<p>There could not be any features of any other database that SQLite might not have, or that an application might need, or want.<p>We definitely should not, like, read a book on databases, or read the manual of another database, or something else crazy like that. There&#x27;s no reason we might learn about other databases. They are just &quot;shiny stuff&quot;, meaning, there&#x27;s something going on with them that I can&#x27;t see, because of all the glare.<p>Honestly, the existence of all those other databases, and database models, and the billions of dollars spent on them, is a fluke, probably. It&#x27;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&#x27;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 <i>isn&#x27;t</i> in SQLite.<p>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.<p>I don&#x27;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&#x27;ll ever need in most cases.
评论 #34817618 未加载
评论 #34815408 未加载
评论 #34814486 未加载
评论 #34833358 未加载
posharmaover 2 years ago
I love sqlite but can&#x27;t hold myself from saying: another day, another sqlite post on HN :-)
评论 #34812840 未加载
margorczynskiover 2 years ago
Yeah, it works when it works. Just that in many cases you&#x27;ll run into scenarios with it when it completely doesn&#x27;t or is missing something crucial and we&#x27;ll get another prodigal son story about going back to Postgres.
评论 #34813491 未加载
评论 #34815416 未加载
mythzover 2 years ago
Also worth mentioning Fly.io&#x27;s work on LiteStream [1] and LiteFS [2] giving SQLite important S3 DR&#x2F;reliability &amp; multi-node replication and scalability - opening SQLite up to even more use-cases.<p>We&#x27;re making use of this ourselves in <a href="https:&#x2F;&#x2F;blazordiffusion.com" rel="nofollow">https:&#x2F;&#x2F;blazordiffusion.com</a> which runs entirely on SQLite, using Litestream to replicate it to Cloudflare&#x27;s R2 object storage which is running on a single Hetzner US Cloud VM at €13 &#x2F;mo.<p>As we believe SQLite + Litestream is a very cost effective solution that can support a large number of App&#x27;s data requirements we&#x27;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&#x27;s looking to do something similar, the GitHub Actions + Docker compose configuration that enable this are being maintained at <a href="https:&#x2F;&#x2F;github.com&#x2F;ServiceStack&#x2F;mix&#x2F;tree&#x2F;master&#x2F;actions">https:&#x2F;&#x2F;github.com&#x2F;ServiceStack&#x2F;mix&#x2F;tree&#x2F;master&#x2F;actions</a><p>[1] <a href="https:&#x2F;&#x2F;litestream.io" rel="nofollow">https:&#x2F;&#x2F;litestream.io</a><p>[2] <a href="https:&#x2F;&#x2F;fly.io&#x2F;blog&#x2F;introducing-litefs&#x2F;">https:&#x2F;&#x2F;fly.io&#x2F;blog&#x2F;introducing-litefs&#x2F;</a><p>[3] <a href="https:&#x2F;&#x2F;docs.servicestack.net&#x2F;ormlite&#x2F;litestream" rel="nofollow">https:&#x2F;&#x2F;docs.servicestack.net&#x2F;ormlite&#x2F;litestream</a>
bikamonkiover 2 years ago
I have similar arguments for &quot;Firebase is the only database you will ever need in most cases&quot; for web apps, be it that you need real-time capabilities or not.<p>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?
user3939382over 2 years ago
I’ve been learning the idiosyncrasies of MySQL since 2003, I doubt whatever benefit SQLite has outweighs 20 years of experience.
onlypositiveover 2 years ago
&gt; 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.<p>Is this with nodejs or something single threaded as the webserver?<p>I would kind of assume you&#x27;d run into issues with something like PHP.
评论 #34813484 未加载
评论 #34813055 未加载
评论 #34812826 未加载
评论 #34812828 未加载
zzzeekover 2 years ago
and if you need to migrate your database schema in any non-trivial way.....well then you&#x27;re on your own. for anything beyond adding a column to a table, you&#x27;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&#x27;s not fun to maintain).<p>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&#x27;t have to maintain.
评论 #34815475 未加载
erulabsover 2 years ago
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.<p>Sure it’s all <i>insane</i> 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.<p>The simplicity is not the API. The simplicity is having one API.
评论 #34814437 未加载
评论 #34815516 未加载
ripdogover 2 years ago
I have wondered why Synapse, the most feature-complete Matrix homeserver, so vehemently recommends against use of SQLite as it&#x27;s backing db. They say that the performance is insufficient and it&#x27;s only appropiate for testing purposes.<p>That would make sense if you assume that Synapse is only going to be used in instances with hundreds&#x2F;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?
评论 #34813531 未加载
评论 #34813590 未加载
xenatorover 2 years ago
A spoon is the only instrument you will ever need in most cases.
xwdvover 2 years ago
Why learn SQLite when you could just learn Postgres and have a database that is virtually guaranteed to be enough in almost all cases?
评论 #34813249 未加载
评论 #34813387 未加载
评论 #34814604 未加载
评论 #34813773 未加载
评论 #34815604 未加载
评论 #34813933 未加载
评论 #34813412 未加载
sireatover 2 years ago
I am ashamed to admit I&#x27;ve run into a case where SQlite was the wrong choice.<p>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.<p>So I did the simplest and cheapest thing that would work.<p>I setup sync.com folder on all machines (think dropbox&#x2F; onedrive) to write logs to the same log.db file across all machines.<p>This worked great at first (I could analyze the db remotely).<p>However you see the potential problem here...<p>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).<p>Now I am faced with merging 20 conflicting log.db files.<p>In theory I should have used a server based SQL database. Or perhaps I should have just lived with 20 different log.db files.<p>In my defense there was only SQlite API so I would have had to write some middleware to transfer to another DB.
评论 #34821755 未加载
dangover 2 years ago
Related:<p><i>SQLite the only database you will ever need in most cases</i> - <a href="https:&#x2F;&#x2F;news.ycombinator.com&#x2F;item?id=26816954" rel="nofollow">https:&#x2F;&#x2F;news.ycombinator.com&#x2F;item?id=26816954</a> - April 2021 (370 comments)
joshspankitover 2 years ago
I’ve argued this before and I’ll argue it here now:<p>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”.<p>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).<p>Computers are “stupid fast” now that we’ve gotten off platters.
评论 #34814109 未加载
评论 #34814101 未加载
评论 #34814506 未加载
评论 #34814118 未加载
hammockover 2 years ago
I used to use SQL Server on a PC to deal with tables over 1 million rows. I&#x27;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.
评论 #34814591 未加载
评论 #34813407 未加载
评论 #34813473 未加载
Dig1tover 2 years ago
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 :(<p>I wish SQLite had GIS support built in.
SnowHill9902over 2 years ago
The only database you’ll need 60% of the time every time.
评论 #34815170 未加载
revskillover 2 years ago
In memory SQlite for unit testing is about 45 miles&#x2F;h, which is the fastest db engine i&#x27;ve used, like a cheetah.
osigurdsonover 2 years ago
SQLite is great if you just have one process accessing the db, other wise it is a dumb choice.
albert_eover 2 years ago
AWS has a habit of taking a open source project and creating a &quot;managed service&quot; offering of it.<p>Is it possible to offer SQLite as a managed &#x2F; serverless offering?<p>A light weight and cheap relational data store that we just consumer using an API
评论 #34815449 未加载
评论 #34818138 未加载
umviover 2 years ago
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?
评论 #34814174 未加载
评论 #34830158 未加载
评论 #34815576 未加载
lekeover 2 years ago
As someone who uses free hosting for personal projects, I thought I couldn&#x27;t use SQLite because it wasn&#x27;t advertised as being available. I&#x27;m beginning to find this isn&#x27;t true.
freilanzerover 2 years ago
I&#x27;m using DuckDB atm instead of SQLite: <a href="https:&#x2F;&#x2F;duckdb.org&#x2F;" rel="nofollow">https:&#x2F;&#x2F;duckdb.org&#x2F;</a><p>For data science purposes, it seems to be quite interesting.
nikeeeover 2 years ago
I really like SQLite and I use it a lot. There are only two things that are missing to make it near-perfect:<p>- A type for Instants &#x2F; time handling<p>- being strict with types. No inserts of ints into a string column
评论 #34815587 未加载
OrvalWintermuteover 2 years ago
&gt; 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.<p>Disagree.<p>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.<p>Various customizations &#x2F; extensions &#x2F; versions of PG<p>There are security versions e.g. <a href="https:&#x2F;&#x2F;www.crunchydata.com&#x2F;products&#x2F;hardened-postgres" rel="nofollow">https:&#x2F;&#x2F;www.crunchydata.com&#x2F;products&#x2F;hardened-postgres</a><p>Columnar &#x2F; high performance Parallelized extensions e.g. <a href="https:&#x2F;&#x2F;www.citusdata.com&#x2F;product" rel="nofollow">https:&#x2F;&#x2F;www.citusdata.com&#x2F;product</a><p>General Purpose &#x2F; Oracle transitions e.g. <a href="https:&#x2F;&#x2F;www.citusdata.com&#x2F;product" rel="nofollow">https:&#x2F;&#x2F;www.citusdata.com&#x2F;product</a><p>Yandex even has an embedded Postgres <a href="https:&#x2F;&#x2F;github.com&#x2F;yandex-qatools&#x2F;postgresql-embedded">https:&#x2F;&#x2F;github.com&#x2F;yandex-qatools&#x2F;postgresql-embedded</a><p>If you&#x27;d like to see a full list of features see <a href="https:&#x2F;&#x2F;www.postgresql.org&#x2F;about&#x2F;featurematrix&#x2F;" rel="nofollow">https:&#x2F;&#x2F;www.postgresql.org&#x2F;about&#x2F;featurematrix&#x2F;</a><p>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 <a href="https:&#x2F;&#x2F;www.postgresql.org&#x2F;support&#x2F;professional_support&#x2F;" rel="nofollow">https:&#x2F;&#x2F;www.postgresql.org&#x2F;support&#x2F;professional_support&#x2F;</a> and willing to help out on Libera <a href="https:&#x2F;&#x2F;www.postgresql.org&#x2F;about&#x2F;news&#x2F;migration-of-postgresql-irc-channels-2216&#x2F;" rel="nofollow">https:&#x2F;&#x2F;www.postgresql.org&#x2F;about&#x2F;news&#x2F;migration-of-postgresq...</a>
评论 #34814553 未加载
ReflectedImageover 2 years ago
Might as well be using MongoDB if you are going to use SQLite in prod.<p><a href="https:&#x2F;&#x2F;youtube.com&#x2F;watch?v=b2F-DItXtZs">https:&#x2F;&#x2F;youtube.com&#x2F;watch?v=b2F-DItXtZs</a>
jameshartover 2 years ago
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.<p>It was neat and quick and easy to get running right on your regular windows desktop.<p>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.<p>Since that was the only database we needed, what does SQLite bring to the table?
ilrwbwrkhvover 2 years ago
Pocketbase which uses sqlite is a total game changer. I am slowly moving away from Supabase to Pocketbase.
评论 #34831657 未加载
vouaobrasilover 2 years ago
Except for people running Wordpress on shared webservers. Then MySQL is the only database you can ever use.
sammy2255over 2 years ago
What if I want things to connect to it?
leraxover 2 years ago
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
评论 #34814923 未加载
tmpfileover 2 years ago
I wish sqlite made their terminal interface a bit more robust or emulated psql’s interface. Simple things like \d tablename would be great.
评论 #34815590 未加载
thedudeabides5over 2 years ago
We should teach SQL in high school
评论 #34813351 未加载
kris-novaover 2 years ago
SQLite — works 80% of the time — every time.
评论 #34813075 未加载
评论 #34813777 未加载