Every once in a while I send hand picked things I've learned. Kind of like your filter to the tech internet. No spam, I promise!
Share
SQLite Is the ULTIMATE Choice For 99% of Projects
Published 24 days ago • 3 min read
SQLite Is the ULTIMATE Choice For 99% of Projects
When you need to crack a nut, you grab a nutcracker, not a sledgehammer.
So why, when it comes to databases, do so many of us immediately reach for a heavy, complex solution, just because we “feel” it’s right?
too much?
This simple question changes how you look at building software. The default solution for most developers is to spin up a dedicated database server like MySQL, Postgres, or a NoSQL option. This means dealing with separate running processes, connection strings, network latency, and separate resource consumption. This approach works, but for the vast majority of projects, it’s total overkill. You end up dragging a metaphorical sledgehammer around everywhere, not only your local environment now needs to run another container, or a background process but every other developer or pre-prod environment.
The better solution is to use the tool that’s already in your pocket: SQLite. It’s easy to dismiss it as a toy for learning SQL, but the reality is that it’s the most widely deployed piece of software on the planet, running silently inside every iPhone, Android device, and Mac computers. Its power lies in its simplicity - your entire database is a single file.
it's on my iPad! (keep reading to figure our the `-wal` feature)
There’s no server to run, no user accounts to manage, and no complex setup. This makes it incredibly easy to work with locally, back up (just copy the file…), and share. And don’t mistake simplicity for weakness: SQLite is fully ACID compliant (Atomic, Consistent, Isolated, and Durable), offering the same reliability guarantees you’d expect from its heavyweight cousins.
It's not all sunshine and rainbows
But the fact that SQLite runs from a single file is both its power, but also its biggest obstacle. This makes concurrent writes a problem for many large scale deployments. So, how do you put this into action and overcome the one hurdle that holds people back? The common fear is that a single file can’t handle high production loads with many users reading and writing at once. The game changer solution is a feature called Write-Ahead Logging (WAL). You can enable it with a single command:
PRAGMA journal_mode=WAL;.
Instead of locking the entire database for every change, WAL writes new changes to a separate, temporary log file first (see the image above on my ipad, using temp WAL files while updating book store metadata). This allows multiple read operations to happen at the same time as a write operation, dramatically improving concurrency and making your application feel significantly faster. If you need to push it even further, a modern fork called libSQL is a fork of SQLite that aims to solve the concurrency limitations of sqlite. It offers a distributed version of SQLite that can be run from anywhere, including... 🥁 serverless functions! This means thousand of concurrent connections using the same DB, but in a distributed architecture. It effectively gives you the simplicity of SQLite with the scalability of a massive database system.
For your next project, before you reach for that sledgehammer, take a serious look at the powerful, elegant nutcracker that is SQLite ❤️.
After writing this post, and releasing a video around it, I found that the same team responsible for libSQL had also reached a V0.1 of their Rust rewrite of SQLite, called Turso. While I haven't had the pleasure of testing it out yet, I'm definitely planning on doing that soon. Let me know if you have! Thank you for reading. Feel free to reply directly with any question or feedback. Have a great weekend!
I Used Heroku Open Source Alternatives So You Don't Have To This issue is brought to you by: Enjoy fast, secure and reliable web hosting with Hostinger. Use "devopstoolbox" at checkout of extra discounts on op! Start hosting with Hostinger today! I recently went down the rabbit hole of self-hosted application platforms. Just 5 weeks ago I made a video covering Coolify end 2 end.Once that got released (or maybe, because of that?) I started seeing many similar, open source, self-hostable...
I've Been Using AWS Wrong for YEARS... For years, my approach to AWS felt like a battle. As a DevOps engineer and later and architect, building infra always involved a tedious process of carefully building templates and structure, reviewing, deploying, testing and iterating over and over. I’d either spend hours clicking through the console or writing endless infrastructure code, always feeling like I was one misconfiguration away from a headache. It turns out, I was making it much harder than...
You've been lied to about self hosting... This issue is brought to you by: Auth0, my auth provider for the last 6 years. Join their free virtual dev_day on June 18th to learn how to secure AI agents and applications. Save your free spot That title might sound a bit aggressive, but this isn't about hating on hosting platforms. It's about loving the freedom, control, and cost-savings that come from owning your deployment process, without giving up the slick, easy experience we all love. And...