We use a SQLite hybrid with JSON blobs (stored as regular TEXT columns) as the principal transactional store for our clients' business system state. The simplicity of this approach (which is definitely not for everyone) is what made it feasible for our small development team to manage the project as far as we have. If we were still using a big-boy SQL solution like PostgreSQL or SQL Server and managing all of the concerns specific to these dependencies (multiplied by the number of environments we are responsible for deploying these to), we probably would have been forced to close up shop by now.
Being able to deploy a single binary which brings every dependency (including the database) it needs along for the ride proved to be a lifesaver for us. The process before this involved spending an entire day getting SQL Server configured relative to the service accounts, and whatever ridiculous quirks may exist in each clients' specific AD. Today, it is literally a 30 second file extract, sc create, and it's running. We go from RDP into a blank box to a working production system within 5 minutes. This is also how we've been able to completely sidestep the docker question, since no one could justify the added complexity once we got the equation down to: a single binary folder + a bare-ass Windows Server 2016/19 box = good to go. This is also enabled by .NET Core's Self-Contained Deployments, so SQLite isn't a free lunch all on its own.
Again, the above is not for everyone. We are responsible for maintaining production services running across many client sites, and needed a solution that could scale not only in terms of our clients' requirements, but also in terms of our abilities to manage all of these environments all at once. Managing a hosted SQL service requires direct access to each of these environments which has tremendous overhead for us due to the email back-and-forth remote access game that has to be played each time.
If you are in an environment where its just a single instance of the production/test/qa/development stack, I would absolutely push for a hosted SQL option like PostgreSQL/MySQL/SQL Server. In these cases you have a single (or 4) points of touch, and it is very reasonable and consistent and all within your control. If you find yourself in a situation similar to mine, I would still advocate for the hosted SQL by default unless you are a very brave soul and understand the risks of going a bit off the beaten path. Disclaimer aside, there really are some interesting treasures out here. If you can afford to waste a month, try doing your entire persistence layer in SQLite with JSON serialized business models where appropriate. Assuming you budgeted for complete failure, worst case you walk away with a new perspective. Better case, you end up in the position I find myself in now.
Just don't forget to set PRAGMA journal_mode=WAL; if you decide to go on the adventure. It makes orders of magnitude difference in concurrent write throughput (SQL Server was never this fast for us).
We use a SQLite hybrid with JSON blobs (stored as regular TEXT columns) as the principal transactional store for our clients' business system state. The simplicity of this approach (which is definitely not for everyone) is what made it feasible for our small development team to manage the project as far as we have. If we were still using a big-boy SQL solution like PostgreSQL or SQL Server and managing all of the concerns specific to these dependencies (multiplied by the number of environments we are responsible for deploying these to), we probably would have been forced to close up shop by now.
Being able to deploy a single binary which brings every dependency (including the database) it needs along for the ride proved to be a lifesaver for us. The process before this involved spending an entire day getting SQL Server configured relative to the service accounts, and whatever ridiculous quirks may exist in each clients' specific AD. Today, it is literally a 30 second file extract, sc create, and it's running. We go from RDP into a blank box to a working production system within 5 minutes. This is also how we've been able to completely sidestep the docker question, since no one could justify the added complexity once we got the equation down to: a single binary folder + a bare-ass Windows Server 2016/19 box = good to go. This is also enabled by .NET Core's Self-Contained Deployments, so SQLite isn't a free lunch all on its own.
Again, the above is not for everyone. We are responsible for maintaining production services running across many client sites, and needed a solution that could scale not only in terms of our clients' requirements, but also in terms of our abilities to manage all of these environments all at once. Managing a hosted SQL service requires direct access to each of these environments which has tremendous overhead for us due to the email back-and-forth remote access game that has to be played each time.
If you are in an environment where its just a single instance of the production/test/qa/development stack, I would absolutely push for a hosted SQL option like PostgreSQL/MySQL/SQL Server. In these cases you have a single (or 4) points of touch, and it is very reasonable and consistent and all within your control. If you find yourself in a situation similar to mine, I would still advocate for the hosted SQL by default unless you are a very brave soul and understand the risks of going a bit off the beaten path. Disclaimer aside, there really are some interesting treasures out here. If you can afford to waste a month, try doing your entire persistence layer in SQLite with JSON serialized business models where appropriate. Assuming you budgeted for complete failure, worst case you walk away with a new perspective. Better case, you end up in the position I find myself in now.
Just don't forget to set PRAGMA journal_mode=WAL; if you decide to go on the adventure. It makes orders of magnitude difference in concurrent write throughput (SQL Server was never this fast for us).