>> I don't know anything about ODBC
>
> I dunno, from what I've seen it looks overengineered enterprise bullshit,
> and old-fashioned as well

You may be right. Here's the current/upcoming C API:
<https://github.com/microsoft/ODBC-Specification/tree/master/Windows/inc>.
It doesn't seem terrible by any means, but it's a C API and there's
quite a lot of stuff.

>> There's also Perl DBI, which is reimplemented(?) in Gauche as "dbi" module.

<https://practical-scheme.net/wiliki/wiliki.cgi?Gauche%3ADBI%2FDBD>

The interface looks reasonable. Not sure how extensible it is.

> The DBI interface in Gauche seems useful, but it does sort of rely on
> having an inheritance hierarchy.  At least, if you want to provide
> non-standard extensions that would be done in the database-specific
> subclass.

I don't think inheritance is necessary; a vtable (virtual method table)
is enough. That can be done using a hashtable, a record or even a vector.

If there's a SRFI that lets people define database drivers, it can have
a procedure that's called like:

(provide-database-driver
  'postgres
  :connect postgres-connect
  :execute postgres-execute
  ...)

That returns an opaque object that has the vtable in the right format
based on the keyword arguments. Then the user can call things like:

(database-execute some-postgres-db "select * from things")

And database-execute does the right thing to look up the method in the
some-postgres-db vtable and call it.

Keyword arguments can be dealt with in one of several ways; we have to
pick something.

> And I do think a way to have non-standard extensions is a
> must when writing "serious" code.  Think for example about the COPY FROM
> interface that Postgres offers, for fast data import/export.  And of
> course things like custom data type registration.

Agreed.

Though for many applications it's nice to be able to stick to ANSI SQL
as much as possible for maximum portability and have some comforts for
dealing with it. I still miss a DB interface that would offer a wrapper
to do the usual upsert (update or insert) dance using only ANSI features.

> Perhaps it's a better idea to define a common API that each module
> exporting database stuff should implement.  Then for Schemes that support
> functors (like CHICKEN), that could be one way of importing them in an
> agnostic way, and a dbi-like functionality could be defined on top?

This is a lot better than nothing, but I'd personally prefer the generic
functions.