Here's a spicy take: SQLite might be the best database choice for your next SaaS project. Yes, really. The same database that powers your browser h...
For further actions, you may consider blocking this person and/or reporting abuse
Having separate servers for the application and the database is mainly because security concerns and different server requirement needs. It has less to do with what flavour of database you choose.
Also Sqlite has no build-in authentication. This is a potential security risk.
Sqlite is certainly a good sql database, and it is great the nice parts are getting promoted.
Just be aware it is a tool like any other, it comes with flaws too.
Potentially, in the sense that if you don't give your database file the correct ownership and permissions on disk, it could be read by other users on the machine. Or if you let your machine accept incoming connections that can read files on disk, and can access the SQLite database file, they can potentially read your data.
But these 'security risks' are at a level above SQLite itself–you can have the same security risks whether you're using SQLite or not, and you should be locking down file permissions and network connections on production machines anyway. Calling this a risk with SQLite is calling being on a leaky boat and pointing out that there's a leaky bucket in the boat.
You are right about the file permissions. That is just the first step in the sqlite security plan. But it is not the security risk I'm hinting at.
Because it is not needed to authenticate, there is no way of tracking who corrupted the data if that happens.
Most database systems also come with permissions. This improves the security even more.
I saw there is encryption for the file and there is an authentication extension. So it is possible to get to almost the same level as other databases. But when people are promoting it as an one on one replacement, many who read those posts are going to be missing out on that information.
This is how I see it: since SQLlite is a library and not a DB like MySQL or Postgres, you don't need the same type of authentication.
With a traditional DB, you have to take care of the actual files that keep the data plus the users of the DB itself (which rarely are also the users of your application).
In the picture, on the left the use of a traditional DB. You have to worry about user authentication, to avoid somebody could access someone else's data through your applications, and DB Authentication, to avoid that somebody could connect to your DB instance and get the data.
With SQLite your application is the one controlling data access so all the security logic has to be implemented there, not in SQLite.
The issue on keeping the access to the data files themselves private is common to both, of course.
Why wouldn't you need the same authentication, security, when you are storing the same data in both databases.
Most applications have web content data, and that is what I call low level security data.
And on the other hand there is high level security data, for example with e-commerce; prices, user data, orders, payments.
For the low level security data I think Sqlite is fine. For the other data type I want that extra layer of security "client/server" databases offer out of the box.
But who is going to set up two databases from the start? So in production the more secure database is a better option, even to store the low level security data.
Maybe it is time to consider a multi database setup as the default?
In the post:
This is called out in its own section. If people are going to read the post and then ignore this specific warning, they have bigger problems than security.
That is a confusing paragraph, because it mixes application users and permissions and database users and permissions. Their tasks are different.
From the tone it could be interpreted as database users and permissions are not needed at all.
They're not needed at all, because the database is a file and it is opened by a single user–the application that uses it. In fact database users and permissions fundamentally don't make sense in SQLite–it's a library running in the same process as the application itself; it's not a separate server that is called by clients. You should read up a bit more on SQLite because you are fundamentally misunderstanding how it works.
So you are saying that a password protected zipfile with a document in it, sqlite, is as secure as a password protected zipfile with a password protected document, other sql databases. Because the document is a pdf instead of word.
My main concern in this thread is data security. I just can't wrap my head around the fact you are reducing security measures.
From a security standpoint it is a best practice to run different applications, web application and database in this case, with different OS users. This reduces the risks when one user is compromised.
Having a database user that needs to log in to read and manipulate data adds an extra layer of security.
And this is what you want to remove from all your data, because of the database application you prefer? This is the tone of the post, replace the "heavy" database with a fast and easy one.
As I mentioned before I see the benefit for certain data, but not for all.
It feels like good decisions have been made in the past. But the reasoning behind the decisions is forgotten, and that is why mistakes from the past are going to be repeated.
If the application server's user is compromised, even with a separate database server running with a different user, it already gives the attacker access to the database, because they can just log in as the application server user and get the credentials to log in to the database. Your extra security layer is not actually adding any extra security.
Looking at it another way, you are effectively saying that an application server can never use local files on disk as any kind of data store for security reasons. This is obviously an absurd argument.
Sure it is not a complex security measure to circumvent, but it is a bump in the road attackers need to overcome to get to the data. And that time span could be enough to change the password and kill current connections.
Now you are just twisting my words. It is ok for public data, as I mentioned before.
Realistically, that's going to be milliseconds. Attackers are not manually rooting around after logging in, they are just running scripts that automate all the attacks for them. If they already gained login access, you have much bigger problems to worry about than how many more steps it takes to the database.
It is generally understood that an application's data store is part of its persistence layer; it does not typically refer to publicly available data.
Just like the counter actions are scripted upon attacker detection. Did you really think it would be a manual response?
Your security model relies on the database server somehow being more impregnable against attackers than the application server (ie attackers would try to get the data by compromising the latter and then indirectly access the former, rather than just directly accessing the former). The question that naturally arises then is that if the database server can be made so secure, why can't the application server itself? They are both just computers, there's no magic here.
Imho, this specific model doesn't make sense. It's basically security theatre. Obviously, database servers themselves having some kind of access control mechanism makes sense because they need to be accessed securely from external systems. SQLite files don't.
The application server is the public facing server, so that is the obvious attack vector.
When you rob a bank it is easier to go in the front door than tunneling into the vault.
You don't see a bank that is displaying their gold bars in the public area.
The database server is more secure because it is not public. And yet it still has database user accounts, on top of things like IP whitelisting.
I'm sure you have done, and know, all the things I mentioned. And you call it security theater for the purpose of wanting to use a sql database flavor that provides less security features as a one on one replacement.
A scenario where I would feel comfortable to put all data in sqlite is an internal service, in a micro services configuration.
I'm not against using sqlite in production, just be smart about it
damn, the amount of hassle i could’ve skipped with simpler setups like this kinda messes with me tbh - you think most people pick the bigger tools just outta habit or is it low-key fear of missing out on features they’ll never use?
For me personally it's the first.
For some random reasons, first database I encountered was MySQL (it was probably WAMP at the time), and since then it is what I would default to (well, MariaDB mostly these days). If I start a new project, I will go for tools I'm comfortable with, as I don't want to spend time on solving issues related to tools, not the app itself. So I need to be pushed a bit to something new:
Last few years, I'm trying to actively push for "as simple solution as possible", strongly influenced by mindset of "not being able to scale from the defaults (in my case mostly Rails+SQLite) is a great problem to have (as it indicates you have very successful product/bussiness)". So I don't worry about that
we need sqlheavy
thats postgres for you
this is extremely impressive, i’ve had so many pointless arguments about sqlite and this lays it all out in a way that actually makes sense
you think the “lite” in sqlite scares people into overthinking their needs
There are at least three reasons, not mentioned in your post, to still use a database server:
Hardware costs do not scale linearly and it can be more expensive to run a single large node instead of many small nodes. But yes, distributed systems are complex and that too has a high cost maybe more so than a large node. However…
As hard as they tried to make servers redundant with raid disk arrays and redundant power systems etc. Hardware will fail. Disks used to fail every couple years and ssd performance degrades quickly under high use and databases are hard on storage systems and require replacement frequently.
However, its not as painful as it used to be. Cloud providers virtualize nearly everything. Database servers are attached to storage systems where disks are swapped frequently without any impact to database performance. Even if the entire machine fails the cloud provider will move the VM to new hardware automatically within minutes and all you see is an unexpected server reboot.
For full redundancy however you need a second server running in another building with different network switches and power generators cooling systems etc. AWS and other vendors provide multiple data centers and availability zones because individual zones do periodically experience hardware and other failures that result in the unavailability of resources in that zone. Many organizations do choose not to implement multi-az deployments or do so incorrectly and when an AWS zone goes down a lot of the internet goes down with it.
If you are running on a cloud provider they will often resolve the issue in a few hours and waiting it out may or may not be an acceptable trade off, but if you need to credit customers when you fail to meet SLAs don't expect any credits from AWS. Even still 99% availability translates to bit over 7 hours per month so if hardware was the only concern you are probably fine. However...
Many software upgrades will require restarting services and sometimes the entire OS. Even a small team can easily push several changes per day and if each of these changes requires a bit of downtime users are going to notice. Furthermore some of these deployments are going to fail and require a rollbacks.
The more teams you have developing on a shared monolith the more daily deployments you will have and the riskier they will be. Monoliths bring their own development challenges, developers can no longer understand the entire code base breaking parts of the code they don't understand, and local builds often take several minutes (some monoliths even take hours to build) drastically slowing the development feedback loop.
Database servers also need updated but far less than application code. Using symlinks or docker containers today you can perform application updates in place within a few seconds, but for OS or database upgrades the safest option is still a blue/green deployment where an entirely new server is deployed with the new software, validated, and then the dns/load balancers updated to the new servers. If your data is hosted on the old server this becomes massively more complicated. You will need to stop writes to the old server and copy the database to the new server before you can switch traffic to the new server. If your database is large this can take a painfully long time from several minutes to several hours. If you deploy your database on a different node you can deploy new application nodes without replicating your database for each deployment.
That said I mostly agree with you. If your organization is a single team and you have no SLAs a monolithic application on a single server is a great idea. Simplicity is undervalued and you should try to run a monolith on a single server as long as you can.
However, if you have a SLA or multiple teams doing regular deployments, you will need a database server. And running a database server will allow you to: scale horizontally, tolerate ate hardware failures, preform blue green deployments, and deploy micro services instead of monoliths.
Honestly, so many teams overbuild their infra instead of starting with something like SQLite – simplicity wins big early on.
Anyone here actually run into real limits with SQLite before needing to switch?
I agree heavily with this take. I have projects and apps that work off SQLite and are very performative. I use Fly.io also and have not looked into the LiteFS feature yet, I will have to check this out.
Great post. I agree that more people should give SQLite a shot. I just want to point out two things that I feel should be clarified.
Expensify does use a single SQLite database for their production system (or at least did the last time they talked about it publicly), but the key here is that they built a distributed replication system on top of it to handle multiple writers: Bedrock bedrockdb.com/
The other thing I want to point out is that SQLite is great when the service is reading/writing an SQLite database on the local disk, but it's too risky when it's reading/writing an SQLite file on a network mount, like NFS. On most cloud-based systems, NFS is what's available. Local disk is not available. Of course that's not true on traditional-style rented VMs–those do have local disks. So if that's what you're using, you are fine.
I use SQLite as my blog database. It's good, stable, and cheap.
The fun part is I run my blog on AWS serverless, and some people may know serverless doesn't provide permanent storage, so where do I put my SQLite file?
I use NFS to store my SQLite file, serverless will mount NFS first, then dealing with requests.
The best thing of this stack is I don't have to maintain any part of it. I don't have VM, and I don't use RDS (very expensive!).
So... I ❤️ SQLite.
Great write-up 👏
In many cases, you can get away with using Durable Objects + WebSockets.
We also built a live support app and it was much simpler to code and manage than Firebase Firestore.
There are other limitations:
That being said SQLite is powerful but it not a PostgreSQL replacement. It is a different kind of beast that needs different set to shackles and tools to tame!
About database security.
How often do you get a one-to-one mapping between the entries in the "users" table (application users) and the database "logins"?
If your application uses social logins, do you find them in your database logins list?
In fact, I'm almost certain that, in most cases, the application only uses one of the database logins, and it's associated with the application not any particular user.
Imagine you need to do database migration with the multi-tenant pattern 🤔
Lots of folks run their apps on containers in ephemeral hosting environments. That makes the scenario for hosting sqllite tricky because if you do it wrong, you could lose all your data. It would be nice to see a section about that and techniques for dealing with it (like persistent volumes)
Depends on the scenario. One example, while working on a PoC I had the need to use a semi-durable function in Azure but didn't want to pay for it. I setup my sqlite database in blob storage and mounted it as a drive to my regular old function at startup. It was only meant to be single user demo and didn't need to be fast and if worked like a charm. The concepts are really the same for any service. Create persistent storage, put your database(s) there, and mount them to your VM or container.
Nice post ! Thanks for sharing
sudo apt-get install postgresis a good option too. both great. less maintenance with SQLite.Well said, most people compare SQLite with other databases like Postgres without knowing SQLite is file based and Postgre is server based. When I switched from SQLite to PostgreSQL (which I had used in my earlier project), the setup felt overwhelming — configuring the connection string, setting up user roles, and managing the server was a whole different beast.
This is pretty compelling but if you’re running your database on the same computer instance as your app, won’t you need to backup your database and restore it every deployment? That would also mean, if you’re doing a blue green deployment, there will be the potential for a race condition where the current deployment receives one or more writes after the database was backed up to be copied to the next deployment. If you want to ensure no data loss, you’ll need to bring your service down for a short period of time.
Thanks to litestream.io/ this is a non-issue. You can cheaply replicate and back up your SQLite databases into cloud storage in near real-time.
Looks great bro
Wat a simple take
It's for those reasons that Django picked it as default db.
How do you stream changes from postgres to a sqlite "replica"?
Great article. Sometimes simplicity is the best answer.
Very cool article with the important points outlined.
Thanks for this.
It does kind of flip things a little when thinking about architecture... Which is probably why there is such resistance?
I agree all the way to the end
There used to be this thing called SQL compact. That was my favorite thing before SQL lite. Very little to change
What about redundancy / failover? What happens when the VM the application is running on goes down?
Very good App!Recommended!
Interesting take What do you think people get wrong about SQLite.
Bold of you to assume i don't think the same way as you. Therefore your claim that EVERYONE is wrong, is in fact, wrong