[11.x] [policy] Treat SQLite as a dev (rather than prod) requirement and allow it to be raised in Drupal minors

RHEL 8 is on 3.26. RHEL 9 will probably be released in May, but they're already in beta, so their beta is probably higher than 3.26 but lower than 3.38, and I don't know if they plan to update it from whatever version is in their beta.

Debian 11 was already released in 2021 so wouldn't have 3.38, and Debian 12 might not be released until 2023.

MacOS wouldn't get the new SQLite version until the next MacOS release, which typically happens around Sept/Oct/Nov, which would be after an August Drupal release, if that's when we release Drupal 10.

I wonder if instead if we should keep it at 3.26 for Drupal 10.0, but make a policy that we can raise the SQLite requirement in Drupal minors?

How much do we think we'll really need the -> and ->> operators? 3.26 and older versions have the json_extract() function which is almost the same as those operators. It's not exactly the same in that the operators are explicit about whether what's returned is a JSON type or a primitive type, whereas json_extract() decides what to return based on whether the extracted value is an array/object or a scalar. But do we foresee that really being a barrier to us being able to add JSON capabilities to our database API and being able to implement that API in the sqlite driver in a way that works for the quirks of json_extract()?

Thanks, @daffie. I updated the issue title and summary accordingly. Is this okay or would you suggest additional changes to it?

Just a minor change from "plan" to "would like" :)

It looks like the support isn't there in distros to just raise the requirement outright yet, and we already told people we'd finalised the requirements, so raising in 10.0.0 as a platform requirement change seems out of the question.

The idea of making it a development dependency is interesting. I think if we did, we could raise the requirement in a minor (although should only do so when it's actually going to block something, since it's not something we ship with core).

We haven't done this before (define a database driver is development only) so it'd be somewhat new ground.

I haven't heard of someone running a production website on sqlite, maybe someone has but it'd have to be very low traffic if so.

I do think there might be people out there using Drupal as a static site generator on sqlite - since sqlite isn't serving any requests in that case except in the build stage. But it's still a Drupal install that they need to maintain even if it's local, not a throwaway dev install.

Defining sqlite as a dev dependency would discourage inexperienced site owners from using it in production, and that might be good as a usability/signposting improvement too.

I kind of think that changing it from a production dependency to a dev dependency in itself sort of counts as a change to the platform requirements (in fact, it's an even bigger change than increasing the minimum required version, because we'd be dropping production support for an entire database driver), so I'm not totally comfortable with making that change this late in Drupal 10 development.

We could discuss deprecating it as a production-supported database driver for Drupal 11, which would give us more time to gather feedback on usecases that might be disrupted, but that still would not allow the 3.38 requirement increase in Drupal 10.

Overall, I'd also generally prefer that our database version requirements be dictated by available support and security coverage in the ecosystem, rather than by database features.

Thanks!

To be fair, you can totally run a regular blog with decent traffic on Drupal with SQLite (not saying you should.) I wouldn't just call it just a development dependency. What if it's used as a secondary database in a Drupal site?

I think it would just be best to give more flexibility in bumping SQLite requirements during a major version of Drupal.

Moving this to the D11 platform requirements discussions then. Thanks!

Per #8, this means we won't be able to use SQLite 3.38 features in Drupal 10. I think that's ok, because there's two problems with the -> and ->> operators anyway:

• They don't work the same way on MySQL and PostgreSQL. On MySQL, the right side of the operand is a json pointer (e.g., "$.a.b.c"). On PostgreSQL, it's a single-level property (e.g., "a"). SQLite 3.38 is compatible with both, but regardless of that, we wouldn't be able to write any queries with those operators that would work with both MySQL and PostgreSQL.
• Those operators are not defined in a SQL standard. Whereas, there are equivalent functions that are. Therefore, calling code that wants to work with more than one database should use the functions and not the operators.

The functions defined by the SQL 2016 standard aren't implemented the same way across MySQL, PostgreSQL, and SQLite either. For that, I opened #3268266: Add an API for calling SQL functions that database drivers can override, and with that API, I think we can make all the core database drivers translate from the standard-defined function to the database-specific function, even for SQLite 3.26.

I'm sure there's been discussion (but I can't find the issue for it) of changing the install process so it installs immediately to an SQLite DB without requiring DB credentials, thus streamlining the Drupal installation prcess, and then later on you move the DB over to something heavier-duty as an admin task.

Would that clash with this issue's proposed changes?

@joachim There's some discussion in #1226380: Generate minimal mocked or SQLite environment and use to install Drupal in full environment and #1530756: [meta] Use a proper kernel for the installer.

If we we made sqlite a hard requirement of every install, we'd have to change all the requirements again in the major release that made the switch. This issue definitely goes against doing that since we'd then have to ensure sqlite requirements can be met on every target environment, but it'd be a new, different requirements change - just one in the opposite direction.

If we make the installer a self-contained app with no need for a database connection, then we'd not need to require sqlite for it.

We've made progress towards either of those scenarios being possible since those issues were opened (driver agnostic database dumps, less in system_schema/user_schema), but both are still extremely far off.

Now that database drivers are modules, is it possible to get some stats as to how many users are actually using SQLite (and Postgres)?

Ooh yes, we can ask the DA for those.

To provide some background on this issue from a site developer perspective:

I've been trying to get a Drupal 10 site onto my customer's existing CloudLinux 7 (RHEL) hosting that they wish to reuse. I'm likely going to have to move the site I developed on SQLite to MySQL, because CL7 comes with SQLite 3.7.17. There's a faint chance it can be upgraded to 3.26 (that CloudLinux 8 uses), but it may only provide a second install of SQLite that's not the system library, which may be hard to use. For Drupal 9 you could use PHP 7.3's bundled SQLite, but that's no longer supported in Drupal 10, making this a more pressing issue.

SQLite is often not updated to new versions by distros as frequently as it "should" be, due to its position as a low-level library rather than a standalone service, making it infeasible for large web hosting platforms to offer multiple "stable" versions of it in the way they do for PHP, MySQL or PostgreSQL. At the same time because its DB essentially just a file, without a persistent server process, it can be very useful for simple deployments. People are going to expect to be able to move an SQLite DB with the rest of the files and for it to just work.

I have communicated to my customer that this is essentially an outdated platform issue, but this bug would make it an issue for far more "modern" web hosting platforms. Realistically, RHEL 8 will be in use and fully supported for the expected lifetime of Drupal 10, while RHEL 9 only has 3.34 and will be around for another decade at least. It'd be more palatable to raise the requirements for specific add-on modules.

I haven't heard of someone running a production website on sqlite, maybe someone has but it'd have to be very low traffic if so.

#16 provides at least one example :) Thank you, @GreenReaper, for that.

I recently learned about LiteFS. Once that's a bit farther along (in particular, once it adds the S3 replication feature that's on its roadmap), I could see myself wanting to use SQLite on low-traffic sites in production if I had any (which I currently don't, so that's theoretical).

I'm tempted to close this as won't fix, and to consider SQLite as a completely legitimate database choice for certain use cases. Any objections to that?

The only thing in the issue summary is:

Newer versions of SQLite will be useful during Drupal 10's lifetime. For example, SQLite 3.38 (released in Feb. 2022) adds support for the -> and ->> operators that will be useful to us when we add a JSON querying API to our database API, which we would like to do in Drupal 10 minors.

However:
- we haven't got any JSON query support into core yet, sqlite isn't the blocker to adding it.
- we could still raise the sqlite requirement in major releases when needed to support features like that.

So for me I think I'd support marking this won't fix.

It's more rhel issue, at cloud times outdated server platforms can use docker, containerd, k3s and even systemd-nspawn

+1 to won't fix

OTOH it make sense to define policy about predictable upgrades for sqlite, asI got it making a minor release nearly every 2 months

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

No objections to marking this "won't fix" (meaning, we won't raise SQLite requirements in Drupal minors) since #17, so doing so. #3346338: [11.x] [policy] Set the SQLite minimum requirement to at least 3.45 is the issue for deciding on what the SQLite minimum should be for Drupal 11.

I think we need to reopen it to add support of JSON which is required since 10.0, maybe "recommended version" can help

SQLite 3.26's JSON support is already pretty good. What specific features from later versions are needed? 3.38 has -> and ->> operators, but why do we need those?

Crediting discussion participants.