So there is a recent push on using SQLite in production, but there's also little talks around the pros and cons of doing so. This article will try to cover the pros and cons of using SQLite in production.

To start, there's some value in asking ChatGTP what they think about using SQLite in production. So, the below information is from ChatGPT and it has some interesting points. However, some of these points are flawed and I don't agree with them. So, this is another example where you must take everything with a grain of salt when it comes to generative AI content.

My responses will be in italics next to the points made by ChatGPT.

Pros of Using SQLite in Production:

Simplicity and Maintenance: SQLite requires no separate server process or system to operate. Its serverless nature makes it easy to set up, backup, and maintain. Keep in mind that this is requiring you to install the sqlite service on the server that will be using the database. This isn't a big deal as the sqlite3 binary is about 4MB. Though, it doesn't run as a service so it is simple to manage.

Portability: All the data in an SQLite database is stored in a single file, making it extremely easy to transfer, copy, or backup. This is true. However, you may want to be careful in situations where you could run out of disk space. Make sure that you have proper monitoring in place on the server. Also, while I've never confirmed that this could be an issue, but this is also putting all of your eggs in one basket. If there was ever an issue with the file being corrupted then it could be a total loss. Again, I've never seen this happen, but I also don't use SQLite enough to have experienced this.

Low Resource Footprint: It’s lightweight and requires minimal memory, making it suitable for devices with limited resources like IoT devices or mobile applications. The IoT devices and mobile applications is where I have seen this the most. While a default Ruby on Rails application does come with SQLite as the default database engine, typically it isn't seen in a production setting.

Consistent Performance: For applications with a moderate load and where the read operations far outnumber the write operations, SQLite can deliver consistent and fast performance. There's no network hops to worry about. Optimized SQL queries are often not the slow point of the application. Even with network latency it's often a tiny amount such that you won't notice. If we're talking about a huge application that's service millions of requests per second then these network hops could add up with a traditional database.

ACID Compliant: SQLite transactions are ACID compliant, ensuring data integrity even after system crashes or power failures. Atomic, consistent, isolated, and durable (ACID). It's true that SQLite has come a long way in terms of integrity and reliability of handling the data. However, with system crashes or power failures, database corruptions can occur especially on systems where RAID systems are configured improperly. If the RAID cache is set to write-back, you get better performance but could end up in a situation where there is data loss or corruption.

Cross-Platform: SQLite is cross-platform and can be used on all major operating systems, including Windows, Linux, and macOS. In terms of deploying a Ruby on Rails application, this is kind of a moot point as we will typically not change the platform the application is hosted on.

Free and Open-Source: SQLite is free to use and is released under the Public Domain license, which means it can be used for any purpose, including commercial applications. The same could be said for MySQL (or MariaDB) and PostgreSQL. MySQL is GPL licensed which probably doesn't make a difference if you're hosting your own application and not distributing it.

No Configuration: SQLite doesn’t require any configuration or setup, making it easy to use for beginners. Yes, properly configuring a MySQL or PostgreSQL instance can be difficult and should be done properly for optimal performance. However, using a managed service with AWS, GCP, Digital Ocean, Azure, etc. will often have a decent configuration that you don't need to worry about.

No Dependencies: SQLite doesn’t have any dependencies, which means it can be used in any programming language or framework. While MySQL and PostgreSQL have dependencies and you'll need to install some libraries on the server, they're well documented steps and usually not a big deal.

Cons of Using SQLite in Production:

Concurrency Issues: SQLite is not ideal for applications with a high level of concurrent write operations. It locks the entire database during a write operation, which can cause a bottleneck. If we're working on a single threaded application since we're talking about smaller applications without too much traffic, this will not really be an issue. However, it does bring up a good point for any kind of background workers that occur asynchronous. Even though SQLite does a good job at handling transactions, it is easy to get ourselves into situations of Deadlocks when background jobs are trying to write to the database at the same time.

Not Suited for Large Databases: While SQLite supports database files up to 140 TB, it’s generally not the best choice for very large datasets due to potential performance issues. Most databases will never reach 140TB. But this does not talk to file system limits. This is really a moot point since modern file systems exceed SQLite's limits, but if you're file system was improperly set (to something like Fat32), then you could easily reach the 4GB limit. Most likely, if your database was even getting to a fraction of this size, you'll have other issues to be concerned with; system resources, performance issues, etc.

Limited Built-in Features: Unlike more robust RDBMS like PostgreSQL or MySQL, SQLite doesn’t have a wide range of built-in tools or support for stored procedures, right out of the box. It supports the important ones like JSON data types. Typically, we don't use stored procedures in Ruby on Rails applications since the ActiveRecord ORM is powerful enough in most cases.

Limited Security: SQLite doesn’t have built-in support for user management or access control, making it less secure than other RDBMS. While this can be a critical aspect in some scenarios, the Ruby on Rails applications will likely not need this level of access control. However, it does speak to the vulnerability of the database as it will be much easier to make a copy of if the system is ever breached. In a traditional deployment, there's still nothing that could really stop the attacker if they gained access to the machine, but it does offer some levels of obfuscation if they don't know about how Rails handles credentials. But, the reality is that if a bad actor has gained access to your machine, all bets are already off and you're in a very bad situation.

No Client-Server Model: SQLite doesn’t have a client-server model, which means it can’t be accessed by multiple users at the same time. This is going to be a big issue if High Availability is a concern. Typically, we will have a load balancer as the SSL termination point and it will route the traffic to one of the available web servers. This is a deal breaker in many situations as using SQLite in a production setting could mean that you have downtime during deployments. You lose the ability for rolling deployments and have basically put all eggs in one basket. Depending on your deployment strategy, it could also mean that you're having to maintain a server instead of being able to manually or automatically provision and scale up new web servers.

Conclusion

So, there's some pros and cons to using SQLite in production. I think that the pros are valid, but the cons must also be taken into consideration. The recent push for using SQLite in production isn't necessarily a bad direction, but shouldn't be blindly followed. There's a lot of factors that go into deciding what database engine to use. I think that SQLite is a great choice for smaller applications that don't have a lot of traffic. It is also a good choice when architecture simplicity is a concern. Just make sure that you're weighing all of your options and understand the consequences and benefits of your choices. There's no one size fits all solution in this case. Also keep in mind that a lot of platforms will have a free tier or a cheap tier for managed database engines. You can get a lot of value out of these services and it will be much easier to scale up in the future if you need to. I think that the biggest concern is the lack of High Availability This is a deal breaker for most applications that are going to be deployed in a production setting. However, for non-revenue generating applications or applications that are not mission critical, SQLite could be a good choice.