Can you clarify Database Agnostic feature cited in the readme?

[peter-lockhart-pub]

I was evaluating using this over other libraries for a project that needs to be able to support multiple database engines. Initially reading Database Agnostic on the feature list thought this could be a silver bullet for me, but that doesn't quite seem to be as far as I imagined.

I appreciate it is explicitly called out elsewhere this is not an ORM, but I imagined I would be able to write some simple/common SQL queries that would work against both engines. It looks like I would need to have B provisioned dev/local databases (1 for each engine) set up to provide offline SQL analysis for the CLI tool, and then have a different rust library for each SQL engine with all the queries written engine specific (even if they are the same basic CRUD queries with just $1 vs ?1 being the differences), and then include them in my common project binary and use them as appropriately.

This library is database agnostic in the sense of managing connections/pools/migrations, and being able to bind queries to rust structs. Does this feel like a fair summary? I wonder if this should be called out better? There is only 1 comment in 1 code example on the landing page that hints that database agnostic doesn't mean what I assumed it might:

    // Make a simple query to return the given parameter (use a question mark `?` instead of `$1` for MySQL/MariaDB)

But as I say, this was my assumption and others might assume differently when reading that.

[abonander]

"database agnostic" is an ideal we aspire to, but there's idiosyncrasies to each database that get in the way, and more work that needs to be done.

The core API is designed to be agnostic, and if you restrict yourself to just standard SQL syntax and types, it should work across databases.

But MySQL conspires to foil us here, because it implements different behavior for the || operator by default. We made the executive decision to set the PIPES_AS_CONCAT flag by default, which is something we've gotten a lot of pushback on, because there's third-party implementations (like PlanetScale) that don't support it, and it's somewhat surprising for people who are used to MySQL.

Among other non-standard behaviors, MySQL also uses backticks (`) instead of double-quotes for quoted identifiers, but that's another sql_mode setting we could toggle. It would likely also be controversial (and a breaking change at this point) to enable it by default, but we could (and should) expose it.

Bind parameters are definitely a problematic area, as you've already identified. The problem is, they're not in standard SQL as far as I know. They're a hacky patch to the standard to solve SQL injection that every database flavor has implemented differently. SQLite is the most flexible here, as it basically supports every other database's syntax. But Postgres, MySQL and MSSQL (when we supported it) are mutually incompatible.

We have a plan to solve that in #875 which I was working on in #1076 but I got stuck on the expansion for arrays and had to move on to other things. If I picked it back up, I would focus on just binding scalars and leave the array expansion for later.

The macros generating code for specific databases is another problem. The ideal is to tell them to generate code using the Any database, but that also just needs to be implemented (which would ideally build on #3383). And even then, we have no way to guarantee that the SQL you write is portable without just checking against multiple databases, or parsing it ourselves which is its own can of worms (FAQ answer).

At the end of the day though, 99% of applications are written with a specific database in mind, and that's what we've mainly focused on. Trying to support literally everything generally means you end up supporting nothing all that well.

And big feature work is pretty much entirely bottlenecked on myself and my own energy and availability, because generally people only open PRs to make changes that directly support their use-cases. And I have a full-time job that isn't working on SQLx (yet).

[peter-lockhart-pub]

Thank you so much for such a detailed answer. Plenty of bits there for me to follow as well with everyone's amazing efforts.

Are you aware of any multi-database project that idealises how to do things just now?