I’m trying to implement a small server for an iOS app that I have. This server won’t have write operations from the outside. It will create its own data and will store it, ideally, in an SQLite database. Few records, really small size. However, several thousands users will use it. I plan to cache the results, again, in SQLite database if possible.
In the book we can read the following: “SQLite is a simple, file-based relational database system. It is designed to be embedded into an application and is useful for single-process applications such as iOS applications. It relies on file locks to maintain database integrity, so it’s not suitable for write-intensive applications. This also means it can’t be used across servers. It is, however, a good database for both testing and prototyping applications.”
My question: does it mean that we shouldn’t use SQLite in a production app? Even in an easy scenario like the one I’m describing?
Honestly, my life would be much easier (and cheaper) if I could use SQLite instead of MySQL or PostgreSQL for my app…
So there are a few issues with SQLite. First off, where are you going to host your app? If you’re planning to use something like Heroku or Vapor Cloud, then you’re going to be out of luck. These hosting providers don’t have persistent storage and your app could be restarted at any time. If this were to happen then you would lost your database.
The other issue is that SQLite can’t handle concurrent connections (since it’s file based), so you can only have one connection at a time. If you’re prototyping it doesn’t matter - if you’re expecting several thousand users then that is going to cause problems.
Finally, if you are expecting to have a large number of users, SQLite doesn’t scale. With a traditional database, you can scale your application server to have multiple instances (say 10 instances of your Vapor app behind a load balancer) all connected to a single database. If you’re using SQLite which lives on disk, this obviously isn’t possible.
My advise would be to avoid using SQLite if possible. If however you only have a few records in the database, then both Heroku and Vapor Cloud 2 provide free databases for a limited number of rows. this may help reduce the cost.
Hope that helps!
PS there’s a chapter in the book on caching you may find useful!
