Experimenting SQLite in a WordPress Plugin

SQLite & WordPress

Lately I’ve been reading the source code of all types of WordPress plugins, and noticed that there are either no SQLite plugins for the current version of WordPress or the SQLite plugins are only compatible with older WordPress version.

According to my own experience, using SQLite in a WordPress plugin has the following benefits:

  • Separate plugin data and settings from the main MySQL database which stores WordPress core data in order to maintain the size and speed of the core database.
  • Eliminate the need for extra code which used to remove the plugin-generated records from the MySQL database, which doesn’t usually work in my opinion.
  • SQLite database file typically resides in the plugin folder. When removing the plugin, the database removed as well. No more useless data generated by the plugin left behind inside the MySQL database.

Most of the benefits above are for end users. However, for the plugin developers like us, there are a few downsides for using SQLite:

  • Since WordPress is a MySQL-based application, all of its build-in functions are specifically made for the MySQL operations. It means that we need to use either PDO (PHP Data Objects) or native SQLite functions in PHP.
  • We need to manually create the database tables for every aspects of the plugin, such as its settings and custom fields.
  • We also need to manually apply and check for the saved plugin settings.
  • SQLite doesn’t have any helpful utilities similar to phpMyAdmin. Although there is SQLiteManager, I don’t find it useful as the SQLite database file needs to be at the same directory as the manager. Imagine that we just need to change one record inside a SQLite database. We need to move the whole file to the manager folder, open the browser, navigate to the SQLiteManager admin page, find and update this one record, and then move the file back to the original folder. If We are debugging database errors, this could become very unproductive.
  • We should familiar with the command line since SQLite doesn’t have any good GUI interface as stated above.

If you have been relying on the build-in WordPress functions to develop database-dependent plugins without knowing anything about PHP and SQL languages, it’s time to learn these two languages.

In the next post, I will write a “proof of concept” plugin to show what you can do with SQLite-based WordPress plugins.