Pages

Tuesday, July 29, 2014

Differences between SQLite and MySQL

Today I'm going to talk about 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.

SQLite:
  • 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
MySQL:
  • 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.