3 ms·

I was agreeing with this article until: > Consider a fast single-file database like SQLite Sure, it's nice if your application is optimized enough that it cou

I was agreeing with this article until: > Consider a fast single-file database like SQLite Sure, it's nice if your application is optimized enough that it could run on a single server. But it seems to me that actually tying it to a single server, with local storage on that same server, in production, is irresponsible. I sure wouldn't want to have to explain why the application went down, and will come back running a possibly out-of-date DB backup, if that single server suddenly disappears. SQLite may be faster and simpler, but to sleep well at night, there's no substitute for stateless application servers in front of a managed database.

zozbot234 6y ago
SQLite is not a substitute for a proper database; it's a substitute for fopen().
- bob1029 6y ago
  I strongly disagree with this in practice. I understand the philosophical motivations to encourage deeper thought regarding the engineering, but we have been using SQLite as our only datastore for almost 5 years now without a single DB-related incident. We probably dont even spend 30 minutes per month screwing with SQL/database-related concerns. I will say that our strategy probably would not work at a larger technology organization. It is more advanced and requires disciplined, motivated engineers to keep it on rails. In retrospect, we were probably only able to get away with this because we were willing to accept a ridiculous amount of up-front risk. We got lucky that it played out so well. Now that we are on the other side of that journey, I can assure everyone that the new world is incredible (and much more stable). To manage scalability and maintenance, we run multiple SQLite databases - one per logical type of persistent business entity (i.e. Users.db, Sessions.db, Customers.db, etc). This allows for us to manage schema versioning for each type independent of any others. We have ~25 types that each get their own separate DB. Our migrator is a simple for-loop, but somehow our approach seems even more elegant than Entity Framework because we don't need special unicorn tables to track migration metadata - see: pragma user_version. The part that requires discipline is that we have no hard referential integrity constraints. This is where developers have to make the right choices when designing related entities & data stores. We do not rely on the database/ORM to clean up our modeling for us. Our backup strategy is to snapshot the entire VM. The biggest motivation for having your application fit on a single box is that you can synchronously snapshot the whole system with a single click. This is far simpler than maintaining a completely separate SQL server instance and worrying about all of the added complexity of backing up 2 (or more) machines. We have yet to encounter a customer who did not have the ability and willingness to use this strategy. If your business application can run on 1 server (and is forecast to do so forever) and you have a RTO/RPO that permits using VM snapshots as backup/restore, then I would strongly recommend considering this type of approach from an engineering perspective (assuming you have the team/skills for it). Thinking more broadly, since we have committed to this idea of the datastore living on the application server, we could hypothetically build up clustering at the application-level by adding multiple application nodes. This would probably be better for us anyways, because we really only have 2-3 entities that we absolutely must have synchronously replicated across all nodes. A heavy-handed SQL Server cluster approach is way overkill when we can just swap to GUID keys on our sessions/state and pull consensus to update important settings, transactions or permissions. And at the broadest scope, I still feel like most developers vastly underestimate just how fast a computer can do things in a well-optimized domain. SQLite is substantially faster than SQL Server in the single-node case. You will never get lower latency than by having your database running in the same process as your business application. Latency is the biggest devil when it comes to dealing with transaction throughput. If I can get a user request out the door in 100 microseconds vs 5 milliseconds, it makes a shitload of difference when I am pushing thousands of these per second.
- IshKebab 6y ago
  Why though? SQLite is rock solid, has write ahead logs, transactions etc. You can presumably do backup at the file system layer via RAID/ZFS/whatever. The main issue with SQLite is it's insanely loose type checking. Column types are completely ignored, as are foreign key constraints by default. Not a good way to build a robust system. But if your schema is pretty simple and you don't anticipate much data I don't see a problem with using SQLite.

zozbot234 6y ago

SQLite is not a substitute for a proper database; it's a substitute for fopen().

bob1029 6y ago
I strongly disagree with this in practice. I understand the philosophical motivations to encourage deeper thought regarding the engineering, but we have been using SQLite as our only datastore for almost 5 years now without a single DB-related incident. We probably dont even spend 30 minutes per month screwing with SQL/database-related concerns. I will say that our strategy probably would not work at a larger technology organization. It is more advanced and requires disciplined, motivated engineers to keep it on rails. In retrospect, we were probably only able to get away with this because we were willing to accept a ridiculous amount of up-front risk. We got lucky that it played out so well. Now that we are on the other side of that journey, I can assure everyone that the new world is incredible (and much more stable). To manage scalability and maintenance, we run multiple SQLite databases - one per logical type of persistent business entity (i.e. Users.db, Sessions.db, Customers.db, etc). This allows for us to manage schema versioning for each type independent of any others. We have ~25 types that each get their own separate DB. Our migrator is a simple for-loop, but somehow our approach seems even more elegant than Entity Framework because we don't need special unicorn tables to track migration metadata - see: pragma user_version. The part that requires discipline is that we have no hard referential integrity constraints. This is where developers have to make the right choices when designing related entities & data stores. We do not rely on the database/ORM to clean up our modeling for us. Our backup strategy is to snapshot the entire VM. The biggest motivation for having your application fit on a single box is that you can synchronously snapshot the whole system with a single click. This is far simpler than maintaining a completely separate SQL server instance and worrying about all of the added complexity of backing up 2 (or more) machines. We have yet to encounter a customer who did not have the ability and willingness to use this strategy. If your business application can run on 1 server (and is forecast to do so forever) and you have a RTO/RPO that permits using VM snapshots as backup/restore, then I would strongly recommend considering this type of approach from an engineering perspective (assuming you have the team/skills for it). Thinking more broadly, since we have committed to this idea of the datastore living on the application server, we could hypothetically build up clustering at the application-level by adding multiple application nodes. This would probably be better for us anyways, because we really only have 2-3 entities that we absolutely must have synchronously replicated across all nodes. A heavy-handed SQL Server cluster approach is way overkill when we can just swap to GUID keys on our sessions/state and pull consensus to update important settings, transactions or permissions. And at the broadest scope, I still feel like most developers vastly underestimate just how fast a computer can do things in a well-optimized domain. SQLite is substantially faster than SQL Server in the single-node case. You will never get lower latency than by having your database running in the same process as your business application. Latency is the biggest devil when it comes to dealing with transaction throughput. If I can get a user request out the door in 100 microseconds vs 5 milliseconds, it makes a shitload of difference when I am pushing thousands of these per second.
IshKebab 6y ago
Why though? SQLite is rock solid, has write ahead logs, transactions etc. You can presumably do backup at the file system layer via RAID/ZFS/whatever. The main issue with SQLite is it's insanely loose type checking. Column types are completely ignored, as are foreign key constraints by default. Not a good way to build a robust system. But if your schema is pretty simple and you don't anticipate much data I don't see a problem with using SQLite.