[11.x] [policy] Set the SQLite minimum requirement to at least 3.45

Updating title, tags and version number based on recent announcement at https://www.drupal.org/about/core/blog/new-drupal-core-branching-scheme-... →

The recent and current versions for Debian and Ubuntu:

Ubuntu 22.04 LTS: 3.37.2
Ubuntu 23.04:     3.40.1
Debian 11:        3.34.1
Debian 12:        3.40.1

From https://repology.org/project/sqlite/versions

DDEV is on 3.34.1
Lando is on 3.34.1

SQLite 3.26.0 was released December 2018, and it looks like most platforms have at least moved to 3.34 ...

RHEL 9 was released a year ago (May 10, 2022) and should be stable enough by now, so that those who need SQLite 3.34 have the option of upgrading, I would think.

Lets go for the newest SQLite version that is available 6 months before the release of the major version. See: 🌱 [Policy] How to select the minimum required database versions Active .

Having 3.38 as minimal will allow common operator syntax for JSON https://sqlite.org/json1.html#jptr which is common for pgsql/mysql as well

PHP 8.2 can stay on minimally supported version but 8.3 better to upgrade to latest to detect incompatibilities earlier

Given we're going to support Drupal 10 until c.2026, I think we should not be constrained by RHEL here, especially since they're aggressively disrupting downstream repositories these days. If people are determined to use older RHEL versions, they could also stay on Drupal 10 a bit longer. It's not clear (or at least, not to me), when RHEL 10 will come out. Because we're going to support Drupal 11 longer too, we're stuck with the minimum decided here for quite a long time.

3.38 or higher appears to be supported by nearly every distro except RHEL.

3.42 was only released in the middle of this year but is also making its way around.
.
If we go with @daffie's idea that would mean 3.44 but that only came out last month, so filtering into distros could take a while longer - i.e. if distros are in pre-release now, it's probably too late for them to update so would only make it into their next release.

I would lean towards 3.42 here - the release date is similar-ish to PostgreSQL 16, so it's likely that distros that support one as a minimum will support the other, and both will have had a bit of time to filter through more before Drupal 11 comes out.

When even Jeff Geerling gets angry, then you know it's bad, and Red Hat's decisions should be given less weight.

Brad Jones is working on JSON storage project and the developers from SQLite have added support for JSONB in SQLite. There is no release from SQLite with support for JSONB. If have asked him on Slack what his opinion on this.
If it is not important to him than we should go with SQLite 3.42. I did not see anything important in SQLite 3.43 or 3.44 that is interesting for Drupal.

Thanks @daffie for pointing me to this. Re: #10, the SQLite development announcement on JSONB states:

...if you modify your application to start storing JSONB instead of text JSON, you might see a 3-times performance improvement, at least for the JSON-intensive operations.

As of now there isn't a release of SQLite that includes JSONB, however it will be released sometime before D11 is tagged:

The current plan is to release the JSONB enhancement in the next major release of SQLite - version 3.45.0. That will probably occur in a month or two.

That affects what we can support in Drupal, however we also need to consider what distros ship. It would be great for JSON data types in D11 to support JSONB if the underlying SQLite library does, but we'll still need to support creating JSON data columns as JSON (not B) until the floor is raised, which sounds like that would be a D12 thing. There would also be the question of an upgrade path for D11 sites that want this enhancement. (We might need to do something new/interesting/novel to ship an upgrade that only runs when the underlying system updates? Not sure if there's an example of that, now.)

All that said, SQLite is uncommon in production. It would be great to just bump the minimum to 3.45.0, however that sounds unlikely given the concern over distros. If we can get around that easily enough, then great. Otherwise no this is not a blocker for anything re: JSON data types in core, however we should change over to it when we can.

Sqlite is moving fast and has biggest (very probably) usage in a wild, also it will be unlikely used on distros a-la redhat as bundled in php.
OTOH a lot of apps building/compiling own version

SQLite is certainly the world's most used database overall... however not for production web sites and I don't think Drupal. Fair enough points though. I am generally-speaking in favor of moving faster and not getting too hung up on distributions, especially as a lot of hosting is distribution-decoupled and people can increasingly roll their own container images, etc. But this is also not something I care that much about. All things being equal I'd love to bump the requirement sooner rather than later so we don't have to do so much work later in providing an upgrade path to JSONB and just supporting it up-front.

The biggest problem with 3.45.0 is that we aim to set requirements six months before the first release window for the major version, and it doesn't exist yet. If it was already out, running on gitlabci or with a pending MR to add the container support, but not in distros yet, then we'd be able to require it and only need to discuss the distro aspect, but I don't think it's possible with the current situation - unless it all happens in the next week or so but that would be extremely tight.

@catch Would it be possible to go for SQLite 3.42 for now. And when SQLite 3.45 comes out, check if it is possible to upgrade the mimimum required version to SQLite 3.45. If for some reason it is not possible, than no. If it is, than change it to SQLite 3.45. It that something we could do?

We can definitely set 3.42 now and open a follow-up to consider 3.45 at the last minute (probably end of January is the last point where we can say 'six months') yeah.

+1 for SQLite 3.42

Update the issue summary and the title.

Debian and Ubuntu both have releases scheduled prior to Drupal 11's earliest release date which will support sqlite 3.42.

This is correct for Ubuntu, but I don't think it is for Debian. Debian's latest stable release is Debian 12, released in June 2023, which has SQLite 3.40. Debian has not yet announced a release date for Debian 13, but they typically follow a ~2 year cycle, so mid-2025 is likely. So if we want Drupal 11 with sqlite to work on the latest release of Debian that's available when 11.0 is released, we should set the minimum to 3.40. However, if there's nothing we need in 3.40, I'd suggest lowering it to 3.39, which is the version in MacOS Ventura. If there is something we need in 3.40, then it's okay to set above Ventura's minimum since MacOS Sonoma has 3.43, but some people are slow to update their MacOS (including if they're on old hardware), so if there's no difference to us between 3.40 and 3.39, then we might as well be nice to them.

The argument for 3.42 over 3.40 is json5 support, but I don't envision us having any use case in core for json5. Especially if MySQL doesn't support json5 yet (does it?). json5 is great for human-readable (e.g., adding comments) json, but what would be the use case for us needing to send json5 into the db? Also, sqlite doesn't even retain the json5 format (e.g., comments would be stripped out before storing into the db file).

I think we should not be constrained by RHEL here

I can accept this for core, but I don't think it's good to drop RHEL support entirely. In other words, if Drupal 11 requires anything more than SQLite 3.26, I will create a contrib module with a sqlite driver that works on 3.26. It makes sense for core's sqlite driver to use the -> and ->> operators (which requires 3.38), but I don't expect there to be any problem with making contrib's sqlite driver change those to the equivalent json functions that are available in 3.26.

It would be great to just bump the minimum to 3.45.0

I agree that core being able to use 3.45's jsonb_*() functions would be nice. Given that I'll create a contrib sqlite driver for old sqlite versions, there might be a pathway to us choosing to be this aggressive with core's requirement. I think the key question here is whether 3.45 will make it into Ubuntu 24.04. If SQLite releases 3.45 end of January and Ubuntu's feature freeze is end of February, this is possible, but not guaranteed, especially since I think Ubuntu's process is that it would need to land in Debian's trunk first.

I think the other question is whether Drupal 11.0 will be released in June or July, because the June window means a beta end of March (before Ubuntu 24.04's release) and the July window means a beta end of April (just after Ubuntu's release).

But, let's assume hypothetically that 3.45 makes it into Ubuntu 24.04 and Drupal 11's beta is right after Ubuntu's release. Would it then be reasonable to say that core only needs to support that, and people on the non-latest Ubuntu or on any other OS (including MacOS until a new version of that is released later in 2024) need to install the contrib driver? How would this affect the quick-start command (e.g., could we add a check in that script that tells people if their sqlite is too old and give them the composer command they need to run to install the contrib driver)?

Finally, if any of the work in ✨ [PP-3] Add "json" as core data type to Schema and Database API Postponed and related issues actually wants to use sqlite 3.45 syntax (jsonb functions) or even 3.38 syntax (-> and ->>) operators, are we then saying that that work will only be committed to 11.0 and not to 10.3?

In summary, my opinion is that we should choose either 3.39 or 3.45 (or the former now and consider the latter later), but that anything in between the two doesn't make as much sense (unless there's important features between those two other than json5).

The biggest problem with 3.45.0 is that we aim to set requirements six months before the first release window for the major version

I forget if we documented our "set platform requirements 6 months before major release" policy, but if so, then FWIW, I think we should change that policy to explicitly mention SQLite as an exception to that (not just for 11.0 but ongoing), and allow SQLite's minimum to float up until beta. While I think SQLite is a fantastic db, and I would actually love to see more Drupal sites using it in production, especially if https://sqlite.org/hctree/doc/hctree/doc/hctree/index.html ever makes it beyond its currently experimental state, I don't think there's currently enough Drupal sites using SQLite in production to warrant needing 6 months prior to major release notice.

Re: this question

if any of the work in ✨ [PP-3] Add "json" as core data type to Schema and Database API Postponed and related issues actually wants to use sqlite 3.45 syntax (jsonb functions) or even 3.38 syntax (-> and ->>) operators, are we then saying that that work will only be committed to 11.0 and not to 10.3?

I have explained elsewhere that we actually don't need support for the jptr syntax because: it's just a shorthand convention for functions that are available in all our supported versions of SQLite, so we can just call the functions and therefore we are abstracting out the query syntax for userland code from the underlying drivers. Users will get an experience similar to ->> but they don't send that operator to SQLite directly. See #3343634-23: [PP-1] Add "json" as core data type to Schema and Database API → for the details.

Also seems that similar conversations were had at ✨ [11.x] [policy] Treat SQLite as a dev (rather than prod) requirement and allow it to be raised in Drupal minors Closed: won't fix .

I forget if we documented our "set platform requirements 6 months before major release" policy,

I don't think so, and I think there's some flexibility built in - we've never actually done it a proper six months before - it's mainly intended to give hosting platforms time to prepare, and that's not really an issue for sqlite so I think it's reasonable to exclude it.

I agree that core being able to use 3.45's jsonb_*() functions would be nice. Given that I'll create a contrib sqlite driver for old sqlite versions, there might be a pathway to us choosing to be this aggressive with core's requirement.

@effulgentsia: That would be great!

Finally, if any of the work in #3343634: [PP-1] Add "json" as core data type to Schema and Database API and related issues actually wants to use sqlite 3.45 syntax (jsonb functions) or even 3.38 syntax (-> and ->>) operators, are we then saying that that work will only be committed to 11.0 and not to 10.3?

I would rather go for a great solution that is only D11+ instead of a lesser option that can be committed to 10.2 or 10.3.

I don't think so, and I think there's some flexibility built in - we've never actually done it a proper six months before - it's mainly intended to give hosting platforms time to prepare, and that's not really an issue for sqlite so I think it's reasonable to exclude it.

I agree with @catch. Back to RTBC.

Setting the title to what I think is RTBC here per #19.

typo fix

Re: #24, I would like to reiterate that jptr syntax is not used (for a few reasons) nor is a blocker for ✨ [PP-3] Add "json" as core data type to Schema and Database API Postponed . The question then is only if we delay JSON data types for D11 to be able to introduce SQLite support requiring JSONB or if we accomodate both the current text-backed JSON field and JSONB depending on the version used. I would love to see this get into D10 because it will be LTS and I think there are some amazing things that can be built on top of it, and I'd hate to see it be D11 only based on a SQLite dependency question that affects few production installs.

Released https://www.sqlite.org/releaselog/3_45_0.html

Re #28, cool! Glad they released that ahead of their initial projection for end of Jan. If someone knows Ubuntu's process (e.g., if it needs to land in Debian trunk first) and can open an issue in the correct queue (Ubuntu's or Debian's) making the case for why they should try to get this in before Ubuntu 24.04's feature freeze, that would help! With only 6 weeks till Ubuntu's feature freeze, we shouldn't assume that this particular SQLite upgrade is on their radar or something they're prioritizing without someone filing an issue in their queue.

This issue has not landed yet and SQLite 3.45 has been released. Lets set the minimum required version to SQLite 3.45.

Back to NR for the Core release managers.

I think #29 is the main issue here. Has anyone reached out upstream on this yet? If not I'm happy to poke around a bit tomorrow and see if I can't find the right answer/people to link in.

@bradjones1 wonder if you had any update?

Looks like Ubuntu got it in already: https://packages.ubuntu.com/source/noble/sqlite3.

I think the next step here then is to not make things more difficult than necessary for developers/testers of 11.x prior to when Ubunutu 24.04 and the next MacOS are released. I'll try to find some time in the next week or two to create a sqlite contrib module and make sure that it's as easy as `composer require` and that things like the quickstart command and running tests work smoothly.

#33 affects the timing of when we commit the change in the 11.x branch, but back to RTBC as far as the policy itself is concerned.

We'll need sqlite 3.45 on the test bots prior to commit too.

@catch do you mean to create another docker image using Ubuntu/noble?

@andypost no idea, just that we can't commit a new requirement without being able to run tests on gitlab.

create another docker image using Ubuntu/noble?

Right, the goal is to be able to run Drupal 11 tests on SQLite 3.45 while still also being able to run Drupal 10 tests on SQLite 3.26. I think one way to achieve that would be with an Ubuntu Noble docker image. Not sure if we should just go with that or if it makes sense to consider any alternative ways of getting SQLite 3.45 onto any of our other existing docker images.

Debian trixy also has it now
https://packages.debian.org/trixie/sqlite3

Guess the way ahead only is to add another pho83-edge image as related and another one are stuck #3404082: Adopt (semver) versioning for DrupalCi images →

Debian trixie would be fine in the short term, but I don't think its release is any time soon, so it will drift to later SQLite versions when they're released. I think we'll want to keep testing on SQLite 3.45 for the lifetime of Drupal 11, so either Ubuntu Noble or a way to pin SQLite to the desired version regardless of the distro's default would be preferable I think.

Pushed "dev" image via #3419616: Create PHP 8.3 image containing sqlite 3.45 →

$ docker run --rm drupalci/php-8.3-apache-ubuntu:dev php --ri pdo_sqlite

pdo_sqlite

PDO Driver for SQLite 3.x => enabled
SQLite Library => 3.45.0

Started pipeline https://git.drupalcode.org/issue/drupal-3346338/-/pipelines/89186

So all green https://git.drupalcode.org/issue/drupal-3346338/-/pipelines/89188

I have updated Database server requirements →

Since this is a policy issue and it has been decided. I am going to move the implementation to a new issue, 📌 Add testing wtih SQLite 3.45 Fixed .

@andypost, I copied the diff to the new issue.

andypost → closed merge request !6478

I opened #3421175: Update INSTALL.txt and friends with Drupal 11 platform requirements → to do when 11.x is ready for it.

Automatically closed - issue fixed for 2 weeks with no activity.

Just a note here about SQLite 3.45 compatibility. The OP says

> Debian and Ubuntu both have releases scheduled prior to Drupal 11's earliest release date which will support SQLite 3.45.

But that's definitely not true. Debian's next release (13 Trixie) is more than a year away, and current Debian 12 Bookworm has sqlite3 version 3.40.1. Trixie will in fact have 3.45 or later, but it's most likely Summer 2025. See https://en.wikipedia.org/wiki/Debian_version_history

(Ubuntu 24.04 does in fact have sqlite3 3.45.1)

This is a bit of a struggle for DDEV, because the ddev-webserver in v1.23.0 just upgraded to Debian 12 Bookworm, and likely won't get to Trixie for more than a year (certainly it will be after Trixie is released). It's never a stability-builder to mix packages from different distro versions, so I'm reluctant to put 3.45 in ddev-webserver by default, at least using the mixed-debian-version technique.

* DDEV issue: https://github.com/ddev/ddev/issues/6110
* ddev-drupal-core-dev workaround: https://github.com/justafish/ddev-drupal-core-dev/pull/23#discussion_r15...
*joachim-n/drupal-core-development-project - also pending

Change record added.

The main reason to choose sqlite 3.45 was jsonb support, to avoid having to write an sqlite-specific upgrade path for any json tables created on < 3.45 (since we don't have any in core now, but we might in 11.x, so starting off with them all being in jsonb avoids that).

I'm wondering if we can do some kind of kludge, like allow installs on 3.4.0, but add an extra hook_requirements() to nudge a 3.45 update in sqlite module (similar to what we do for old PHP versions). That would hopefully only be annoying on production sites then and allow ddev/tests to work on the older versions without workarounds. We could also potentially even prevent json tables being created on < 3.45 when that feature is added.

@rfay Is it a possibility to move SQLite support in DDEV from default included to must be added with an addon command? Just like for Oracle and MongoDB.

Changed the status by accident.

The change record looks good to me.

FYI for Drupal 11 only, DDEV will pull the Debian Trixie Sqlite3 package in v1.23.1, see https://github.com/ddev/ddev/pull/6137

I do think the 3.45 requirement will cause a lot of pain for the next year while operating systems catch up. People who are deployed on any stable distro other than Ubuntu 24.04 will find pain.

But as we all know... it's not used mostly except for testing. At least I think the installation and dburl are the only places you get complaints?

Per #33, I still plan on making a sqlite326 (or better name if anyone has ideas) contrib module for D11; I just haven't had the time for it these last few months. If someone wants to beat me to it, go for it! I agree that a 3.45 requirement will be inconvenient for enough people to warrant making the contrib module for them.

Updated the IS with the correct info for Ubuntu and Debian

I just rebased ✨ [PP-3] Add "json" as core data type to Schema and Database API Postponed which would be the first use of JSON/JSONB in core with SQLite. Right now it uses JSON, but if it were decided to require 3.45 we could update that to JSONB very easily.

I'm of the personal belief that because 1) SQLite is uncommon in production environments and 2) if you _are_ using it in production, you are likely already in expert mode and 3) the only issue here would be if you are both using SQLite in production _and_ implementing this awesome new functionality. That means you are likely qualified to navigate this issue without being tripped up by an inflexible hosting environment.

Making a bold decision on a minimum version (with whatever warnings we feel appropriate) would help us avoid a weird upgrade path and get this functionality into core for 11.0.

BTW https://git.drupalcode.org/project/drupal/-/commit/10466dba9d7322ed55165... already raised the requirement to 3.45 two weeks ago and that has been released in alpha1. I think if there are really good reasons, we can roll this back, but I agree sqlite is mainly used for testing locally.

Anecdotally my homebrew based PHP setup on Mac is PHP 8.3.6 currently with sqlite 3.45.3, and this is the default I got when I asked homebrew to install/update PHP.

Marking this back to fixed, then, since it was NW for the CR it appears.

I think the CR ( https://www.drupal.org/node/3444548 → ) is ready to be published, but deferring to release managers to decide when to do that.