Today I got asked from a friend about the differences between SQLite and MySQL and in which cases it is better to use the first or the latter one. You can’t really compare these two, I will try to explain why this is the case and when it is better to use the first or the latter.
SQLite is a single-file based database which is useful for testing and for embedding in applications. This means that all the information is stored in a single file on a file system and you use a SQLite library to open this file to read and write your data.
MySQL is a database server which got different forms to store its data (engines) and will server the data to request comming from applications who connect to it.
- Fast setup. You only need to include a library into your app.
- Embedded. No need to think about connections
- Easy Testing. Many frameworks for example Rails use SQLite as testing database
- No scaling
- No users and permissions. Everyone who can access the file can access all data.
- No performance improvements. Sqlite doesn’t cache queries, optimizes selects, etc
- Scaling. MySQL can easily grow if the application using it needs more. It can even run in a cluster environment
- Better options for performance tuning. MySQL can be configured to fit to the requierements of your application
- Fit for a production database
- User permissions. You can create permisions for different actions on databases, etc.