Just for reference, I have implementations of the following for Chez and
MIT, based on the ffi:

(query connection sql)
(query connection sql column-names?)

(query* connection sql values)
(query* connection sql values column-names?)

for SQLite (libsqlite3), postgres (libpq), mysql (mysqlclient) and
Oracle (ocilib). Depending on the database, they do a bit of automatic
conversion to the relevant Scheme types. They all work ok. The biggest
problem is Oracle, as you need to register to actually get the driver
files from the website.

They return a list of rows, each row is a list of values. if
column-names? is true, the first "row" contains the column names.

From my experience, it is definitely necessary to have a way to send
direct SQL to the different databases, even if there is a common
abstraction API over them, as the dialects differ too widely (e.g.
Oracle does not support LIMIT, only WHERE ROWNUM > n, Oracle handles the
empty string as NULL, ...).

I have some very basic s-expression->sql code, however as SQL is very
inconsistent, I don't really use it much, but find it much easier to
just directly write SQL strings.

I have a separate ORM mapper on top (only targetting SQLite, because it
was written before the other wrappers), but for me it is often necessary
to directly access table data.

I agree that at least SQLite, postgres and mysql should be targetted,
but the commercial ones are useful to have too (I need to connect to our
Oracle cluster almost every day, and am happy to be able to do it from
Scheme).

Greetings, Peter