I faced a small challenge this weekend in migrating a database from MySQL to SQLite. I’ll explain why shortly. The challenge was that all instructions for exporting from MySQL and importing into SQLite seemed to fail. Large blocks of HTML content in the database were not escaped properly for insert into SQLite. Escaped single quotes from MySQL, for example Bird\'s, were interpreted as ending quotes for field content, completely mucking up the SQLite import. I tried for a couple of hours, using different pieces of advice from online forums, including exporting the MySQL tables as CSV files importing into SQLite in CSV mode. No luck.
Eventually, I decided to hell with it, and simply wrote a script to query all records from each table, then loop through the results and insert them into each of the SQLite tables using prepared statements. Worked perfectly. Bit of brute force, but it restored my sanity. You can look at it, or grab it here if like:
https://github.com/pagerange/mysql2sqlite
And to answer the question: “why was I migrating from MySQL to SQLite?” Well, the client’s site went down a couple of days ago with an apparent database failure. When I logged into the remote server, it was clear that the MySQL daemon was not running, and restarting it resulted in an error message. According to the MySQL server logs, the failure was a result of an ‘unattended update’ where the MySQL daemon failed to restart afterward. I’m not sure how that came to pass, since I do all my own updates.
I eventually got it up and running by reverting to the earlier version of MySQL, but it made me reconsider the use of MySQL for this client. The site is fairly small, and 99.9% of requests are database reads, not writes. The only DB writes that take place are when the admin updates content, maybe a few times a month. MySQL is actually overkill for this particular site. I use SQLite in development mode, because it’s easy to use and the DB files are quite portable. According to the SQLite website, SQLite can easily handle light to moderate use, which covers 99% of all Websites using a database. One of the other conditions is that the database works primarily in read mode, which was definitely the case with my client.
From the SQLite website – When to use SQLite: “SQLite works great as the database engine for most low to medium traffic websites (which is to say, most websites). The amount of web traffic that SQLite can handle depends on how heavily the website uses its database. Generally speaking, any site that gets fewer than 100K hits/day should work fine with SQLite. The 100K hits/day figure is a conservative estimate, not a hard upper bound. SQLite has been demonstrated to work with 10 times that amount of traffic.”
Anyway, migration complete. No changes required to the code other than to the DBConnect file. I’ll keep an eye on it, but for now I’m chalking this up as a win for SQLite.