> I don't really understand this.  So you send SQL to the driver program,
> which uses the C API to talk to the database?  That means for everything
> except SQLite it's Scheme -> Driver -> DB server -> Driver -> Scheme.

Correct. And the driver lives in the subprocess written in C.

> Also, are you talking to the driver over a socket (TCP or local, doesn't
> matter) or over a pair of pipes?  If the latter, you have to make sure that
> both sides flush frequently, or you will get deadlocks.

I was thinking it would work identically over pipes or a socket. Scheme
would write its request, then flush and wait for the response. The C
side would read the request, write a response, flush, and wait for
another request. Does this simple arrangement cause concern of deadlocks?

A timeout should probably be applied on the Scheme side. Since the
subprocess doesn't have anything else to do, it may as well wait forever
for each request.

> It would also be possible to run the standard CLI client for the database
> using a PTY.  That solves the flushing problem, but you'd need to carefully
> pick an output format (or small set of formats) that all CLI clients can
> produce. that doesn't throw away header names or manifest typing
> information, and unambiguously indicates the end of the result set somehow.

The subprocess would be an alternative to the standard CLI client. The
point would be to have a clean, generic binary interface optimized for
machine parsing. Parsing/quoting into REPLs - that way lies madness...

In a big Scheme with sockets, you'd probably want to write pure-Scheme
Postgres and MySQL clients that talk the native protocol of those
databases directly to the DB server. But for situations where a simple
implementation is important and performance is not an issue, the generic
implementation would do wonders.

I would also put SQLite into a subprocess instead of having in the
Scheme process. A Scheme with FFI that wants really fast SQLite (is
there a substantial speed difference, though?) would embed it in the
Scheme process and talk to it via FFI. The FFI may also be easier to
code. It all depends on the implementation.