Why you should consider using SQLite
SQLite might not be suitable for large databases. But most databases aren't actually that large.
SQLite doesn't require a database server. This is excellent for portability. It's also very lightweight, requires minimal setup and supports many of the SQL commands you may be familiar with.
When it should be used
SQLite is an excellent database for:
- Software (all OSes)
- Mobile Apps (all OSes)
- Small/Medium Websites
Software and Mobile Apps are a prime example of when SQLite could be the perfect option. No need for building your own custom solution or using a database that requires a server (such as MySQL or SQL Server).
A lot of sources state that SQLite works without issues with 100,000 hits/day. This seams reasonable and equates to just over one hit every second.
EllinghamDev will run tests in the future on SQLite Performance
- SQLite is in the public domain, so it's FREE for any purpose (http://www.sqlite.org/copyright.html)
- It requires no server software
- No configuration
- SQLite is small and can be embedded within the software
- Can handle databases upto 2TB (unconfirmed but more might be possible)
- Supported almost everywhere, with implementation is a majority of languages
- Not all of the SQL 92 Standard implemented (https://www.sqlite.org/omitted.html)
- Not very good at many simultaneous writes (not a problem for most software or small websites)
- Not great memory performance for larger databases (anything over 1GiB or 2GiB)
- Not great with column types - limited types and not enforced
- No support for failover and load balancing/replication (at least, built in).
SQLite is supported in many different programming and scripting languages. It's small size and portability make it excellent for Software and Mobile Apps.
SQLite is a relational database. It's not suited for file storage and non-relational database. Always consider what data you will be storing and the best options.
How we use it
We use SQLite in Mobile Apps and Software we have developed. We don't use it for our web services or websites because of the lack of proper replication and failover support. And we have a dedicated database instances at our disposal...