The question of when SQLite should be used and when MySQl should be used has come up again and again. To know when and where it is appropriate to use either SQLite or MySQL databases management systems, it is first and foremost important to know what the difference is between them, if at all there is any difference.

In a nutshell, SQLite is an Open Source library that implements a self-contained transactional SQL database engine which requires no server and works on little or no configuration. MySQL on the other hand is also and Open Source Relational Database Management System.

Diving right into the task of answering this question, I have provided an itemized list of some the things SQLite is capable of doing well and I have compared the same with MySQL

SQLite is:

• easy to set up and in many cases no configuration or installation is necessary
• great enough to use for databases you would only need on a temporary basis or for test purposes
• not suitable where user management is needed as SQLite uses the file systems permissions so there is no way you will be able to use SQL statements such as GRANT and REVOKE.
• suitable for using in embedded applications and installations and embedding into applications themselves
• great for rapid development
• lacking in performance measurement features
• not suitable where concurrency transactions on the databases is required
• not good for large scale databases as SQLite stores the database in a single file and this can fall under the restrictions of the operating system where SQLite is not capable of splitting the data across volumes.
• not suitable for use in any situation where a Client/Server Architecture is needed
• suitable for using on small to medium website. These are websites with average 100K or less hits per day.
• Not readily scalable. Altering tables is not permitted in SQLite except for adding columns and renaming tables
• suitable in replacement of Ad-hoc file storage commonly where applications access files using fopen(), fread() and fwrite().
• Is not suited in a situation where Stored procedures are needed and where certain types of joins are needed

Sqlite vs MySQL

MySQL is:

• far more difficult to set up and configuration of users is a must
• good for creating temporary databases as well as for test purposes. This would only be practical if you have the MySQL database server and client already set up
• quite suited for managing users and their permissions
• not suitable for embedding in some hardware as you would still need the server component of the database. MySQL though is suitable for embedding into application using provided libmysql libraries
• great for rapid development in some situations
• perfect for concurrency transactions on the data and is well suited for multi-user environment
• great for large scale production applications which scale even over clustered database configurations
• perfect for using in a Client/Server Architecture set up
• suitable for use on small, medium and large scale websites taking in billions of hits a day
• highly scalable as far as the MySQL data and tables go and can be manipulated any time the MySQL DBA. This scaling capabilities transcends disks, physical servers and location
• not intended to replace fopen(), fread() and fwrite(). MySQL manages its own data files and not the operating system
• fully compatible with stored procedures, triggers, view and other operations common with other major Relational Database Management Systems. MySQL only provides these features with selected storage engines

With these tips, I sure hope they are a competent guide to shove you in the right direction when the choice come between choosing between SQLite or MySQL for your next project.