Let’s make WordPress officially support SQLite

WordPress can be (and is) used for any kind of site, regardless of size or complexity. Some common use-cases include:

  • Single-page landing sites
  • Simple company sites with just a few pages. These sites are usually rarely updated and are essentially static sites with an administration aspect.
  • Simple, single-user blogs
  • Complex news sites
  • e-Commerce sites
  • Full-fledged CMS solutions

Part of the success of WordPress is because it is extendable, hookable, and can be used and tweaked to accomplish almost any task on the web.

However, one aspect of WordPress has never changed, regardless of the increase in WP’s use cases and popularity: The database. WordPress requires MySQL/MariaDB to be installed on a site. MySQL is arguably only optimal for some of the scenarios: The “mid-tier” range of site types.

Large sites usually implement custom database stacks depending on their specific needs, so are beyond the scope of this proposal.

On the lower end of the spectrum, there are small and simple sites. These are numerous and consist of all the blogs, company pages, and sites that don’t have thousands of users or thousands of posts, etc. These websites don’t always need the complexities of a MySQL/MariaDB database. The requirement of a dedicated MySQL server increases their hosting cost and the complexity of installation. On lower-end servers, it also decreases performance since the same “box” needs to cater to both a PHP and a MySQL/MariaDB server.

The ideal scenario

Ideally, WordPress would allow us to choose the type of database during installation. That could be done using an installation guide, or a simple constant in wp-config.php. To accomplish that, WordPress would need to have a database-abstraction layer. This is not an innovative or radical idea in the CMS space; Drupal has had a solid database-abstraction layer for more than a decade. Laravel, Symfony, and others also include ORMs that allow using multiple database types.

Building a database abstraction layer for WordPress would be a colossal task – though it might be one that, at some point in the future, we may have to undertake to ensure the project’s continued evolution and longevity.

A middle ground

As a middle ground, we could implement a solution for the bottom tier: small to medium sites and blogs. These sites don’t necessarily need a full-fledged MySQL database.

SQLite seems to be the perfect fit:

  • It is the most widely used database worldwide
  • It is cross-platform and can run on any device
  • It is included by default on all PHP installations (unless explicitly disabled)
  • WordPress’s minimum requirements would be a simple PHP server, without the need for a separate database server. 
  • SQLite support enables lower hosting costs, decreases energy consumption, and lowers performance costs on lower-end servers.

Implementing SQLite in WordPress Core

Using SQLite in WordPress is, at this point, simple; There are implementations out there that have been around and evolving for more than 8 years. They have been thoroughly tested and proved to work seamlessly. These implementations are drop-in wp-content/db.php files that users can add to their installation; they are not hard to use. 

However, most people are not aware of them. They are not aware that they have the option to buy cheaper hosting sans-mysql and then install WordPress using an SQLite database. Nor should they have to know about it… After all, they just want a simple company site or a blog. 

WordPress could officially support SQLite by including one of the existing SQLite implementations in Core. We would need to ensure that it is properly tested and supported, and additionally, raise awareness and expose the option to users.

Why should this be in Core and not a plugin? 

Picking a database type is something that should happen when a site is first installed. It is not something that should be done after the fact because that would require migrating data from one database to another, which can often be complex. 

WordPress includes the MySQL implementation in Core, so if we support SQLite then that implementation should live alongside it. 

Data migration can (and should) be in a plugin to facilitate migrations for existing sites should they wish to do that, but the database engine itself belongs in Core. 

That will ensure that the implementation is properly supported, properly tested, and WordPress will be able to benefit, as detailed in the following section.

What would the benefits of SQLite be?

Officially supporting SQLite in WordPress could have many benefits. Some notable ones would include:

  • Increased performance on lower-end servers and environments.
  • Potential for WordPress growth in markets where we did not have access due to the system’s requirements.
  • Potential for growth in the hosting market using installation “scenarios”.
  • Reduced energy consumption – increased sustainability for the WordPress project.
  • Further WordPress’s mission to “democratize publishing” for everyone.
  • Easier to contribute to WordPress – download the files and run the built-in PHP server without any other setup required.
  • Easier to use automated tests suite.
  • Sites can be “portable” and self-contained.

Next steps

The next steps would need to be discussed in the comments section of this proposal. If there is consensus to implement SQLite in WordPress Core, an outline of the next steps would look something like this:

  • Create the necessary Trac tickets
  • Decide how the database type would be defined. The most simple scenario would be a DATABASE_TYPE constant in wp-config.php, allowing users to choose if their new site would use a MySQL or SQLite database, but there can be other solutions that come up during a later discussion.
  • Port an SQLite implementation to WordPress Core, applying the necessary changes like coding standards, in-code documentation, migrate tests, etc.
  • Test WordPress Core functionality with SQLite
  • Outreach to plugin developers for testing.

The subject of database abstractions and using SQLite was discussed at length during WCEU 2022, in an unofficial capacity, in the hallways. This post is a distilled culmination of these conversations to bring the discussion to the broader community for serious consideration.

Props @zieladam, @mamaduka, @jonoaldersonwp, @tweetythierry, @SergeyBiryukov, @joostdevalk, @dingo_d, @desmith, @crixu, @williampatton, @costdev, @adamsilverstein, @JavierCasares, @robinwpdeveloper, @aaemnnosttv, @jessibelle for reviewing and contributing to this proposal.

#database, #install, #performance, #sustainability

","commentTrashedActions":"","mentions":[],"mentionContext":"","commentCreated":"1662964973","hasChildren":false,"userLogin":"jb510","userNicename":"jb510"},{"type":"comment","id":"43695","postID":"98400","postTitleRaw":"Letu2019s make WordPress officially support SQLite","cssClasses":"comment byuser comment-author-aristath bypostauthor even depth-2","parentID":"43694","contentRaw":"
What are the limitations of running WP on SQLite? Why not use it for all sites?
nnOne of the drawbacks would be that SQLite wouldn't perform as well in sites that have multiple authors for example. If multiple authors are editing posts on a site and updating them at the same time, then their save requests would run one after the other instead of simultaneously - so there would be a small delay (probably milliseconds, but still a delay).nSQLite is a great tool, but it's not a silver bullet - just like MySQL. MySQL is and should continue to be the default because if you have aspirations to be the next big thing, then MySQL can scale better. If on the other hand you just want a blog, a company site with your about page and a contact form to have an online presence, or any type of small site (which is arguably the majority of sites on WordPress) then SQLite is all you'll ever need and it will perform a lot better.nn
Will there be a conversion tool built into WordPress to move a between the two DB options?
nNo. If you want to migrate from MySQL database A to B (both MySQL databases), WordPress doesn't include tools to migrate that data. SQLite is no different... Data migration doesn't belong in Core and should be in a plugin. It's very possible to migrate data between databases, and doing it in a plugin (or WP-CLI) is not a hard task. But it's not a task that should be part of Core...nn
How much hosting out there doesnu2019t actually support/include MySQL by default today?
nIt's not so much a question of support... it's more a question of fine-tuning.nIf all I can afford is a low-end shared host for my blog, then that server is currently splitting its performance between MySQL & PHP. Most crashes I've witnessed in cheap shared hosts are database-related, and I'm sure a lot of people have similar experiences.nIf, on the other hand, my WordPress site is using SQLite, then the server can be better configured. All its resources get dedicated to PHP since SQLite is included in there. The host can provide better performance for the same box, by fine-tuning it for PHP only. By removing MySQL from the equation, the same site on the same server can perform better.nn
Seems those u201csmallu201d sites might easily benefit from being static sites, would it make sense to look into static flat file sites generators as part of this?n
nnThat's probably where aggressive caching comes in; there are a few plugins out there that do just that.","contentFiltered":"

What are the limitations of running WP on SQLite? Why not use it for all sites?

n

One of the drawbacks would be that SQLite wouldnu2019t perform as well in sites that have multiple authors for example. If multiple authors are editing posts on a site and updating them at the same time, then their save requests would run one after the other instead of simultaneously u2013 so there would be a small delay (probably milliseconds, but still a delay).
nSQLite is a great tool, but itu2019s not a silver bullet u2013 just like MySQL. MySQL is and should continue to be the default because if you have aspirations to be the next big thing, then MySQL can scale better. If on the other hand you just want a blog, a company site with your about page and a contact form to have an online presence, or any type of small site (which is arguably the majority of sites on WordPress) then SQLite is all youu2019ll ever need and it will perform a lot better.

n

Will there be a conversion tool built into WordPress to move a between the two DB options?

n

No. If you want to migrate from MySQL database A to B (both MySQL databases), WordPress doesnu2019t include tools to migrate that data. SQLite is no differentu2026 Data migration doesnu2019t belong in Core and should be in a plugin. Itu2019s very possible to migrate data between databases, and doing it in a plugin (or WP-CLI) is not a hard task. But itu2019s not a task that should be part of Coreu2026

n

How much hosting out there doesnu2019t actually support/include MySQL by default today?

n

Itu2019s not so much a question of supportu2026 itu2019s more a question of fine-tuning.
nIf all I can afford is a low-end shared host for my blog, then that server is currently splitting its performance between MySQL & PHP. Most crashes Iu2019ve witnessed in cheap shared hosts are database-related, and Iu2019m sure a lot of people have similar experiences.
nIf, on the other hand, my WordPress site is using SQLite, then the server can be better configured. All its resources get dedicated to PHP since SQLite is included in there. The host can provide better performance for the same box, by fine-tuning it for PHP only. By removing MySQL from the equation, the same site on the same server can perform better.

n

Seems those u201csmallu201d sites might easily benefit from being static sites, would it make sense to look into static flat file sites generators as part of this?n

n

Thatu2019s probably where aggressive caching comes in; there are a few plugins out there that do just that.

n","permalink":"https://make.wordpress.org/core/2022/09/12/lets-make-wordpress-officially-support-sqlite/#comment-43695","unixtime":1662967194,"loginRedirectURL":"https://login.wordpress.org/?redirect_to=https%3A%2F%2Fmake.wordpress.org%2Fcore%2F2022%2F09%2F12%2Flets-make-wordpress-officially-support-sqlite%2F%23comment-43695&locale=en_US","approved":true,"isTrashed":false,"prevDeleted":"","editURL":null,"depth":2,"commentDropdownActions":"","commentFooterActions":"","commentTrashedActions":"","mentions":[],"mentionContext":"","commentCreated":"1662967194","hasChildren":false,"userLogin":"aristath","userNicename":"aristath"},{"type":"comment","id":"43696","postID":"98400","postTitleRaw":"Letu2019s make WordPress officially support SQLite","cssClasses":"comment byuser comment-author-ste18k odd alt thread-odd thread-alt depth-1","parentID":"0","contentRaw":"I really like the ideanI just see a side effect: some people will tend to buy the cheapest shared hosting offer (the one with SQLite) and then will complain because their site with WooCommerce and other tens of plugins will not work :-) but it will be up to us (hosting providers) to indicate which is the best product for each custormer...","contentFiltered":"

I really like the idea
nI just see a side effect: some people will tend to buy the cheapest shared hosting offer (the one with SQLite) and then will complain because their site with WooCommerce and other tens of plugins will not work ud83dude42 but it will be up to us (hosting providers) to indicate which is the best product for each custormeru2026

n","permalink":"https://make.wordpress.org/core/2022/09/12/lets-make-wordpress-officially-support-sqlite/#comment-43696","unixtime":1662968538,"loginRedirectURL":"https://login.wordpress.org/?redirect_to=https%3A%2F%2Fmake.wordpress.org%2Fcore%2F2022%2F09%2F12%2Flets-make-wordpress-officially-support-sqlite%2F%23comment-43696&locale=en_US","approved":true,"isTrashed":false,"prevDeleted":"","editURL":null,"depth":1,"commentDropdownActions":"","commentFooterActions":"","commentTrashedActions":"","mentions":[],"mentionContext":"","commentCreated":"1662968538","hasChildren":false,"userLogin":"ste18k","userNicename":"ste18k"}],"postFormat":"standard","postMeta":{"isSticky":false},"postTerms":{"category":[{"label":"General","count":2025,"link":"https://make.wordpress.org/core/category/general/"}],"post_tag":[{"label":"database","count":5,"link":"https://make.wordpress.org/core/tag/database/"},{"label":"install","count":4,"link":"https://make.wordpress.org/core/tag/install/"},{"label":"performance","count":114,"link":"https://make.wordpress.org/core/tag/performance/"},{"label":"sustainability","count":2,"link":"https://make.wordpress.org/core/tag/sustainability/"}],"post_format":[]},"pluginData":[],"isPage":false,"mentions":["zieladam","mamaduka","jonoaldersonwp","tweetythierry","sergeybiryukov","joostdevalk","dingo_d","desmith","crixu","williampatton","costdev","adamsilverstein","javiercasares","robinwpdeveloper","aaemnnosttv","jessibelle"],"mentionContext":"","isTrashed":false,"userLogin":"aristath","userNicename":"aristath"}]

Leave a Reply

Your email address will not be published. Required fields are marked *