Thanks for the detailed remarks Peter!

> In the CHICKEN ecosystem, we have a few DB eggs that people enjoy using.
> The one that comes up most is sql-de-lite[1], which is indeed a delight
> to work with, very elegant and high-level.  It offers a rather
> "functional" approach with fold and map and such over rows.  I've modeled
> the postgresql[2] egg on it.  I think these APIs are very Schemely and
> not so "object-oriented" as you see in some other libraries.

Some lesser-known Schemes have GDBM and ODBC support. Some SRFIs might
make sense in this area. We could try to come up with a good
partitioning of the problem domain to SRFIs before starting work on any
of them. I only know basic SQL myself, so I'll leave this to others.

> At least with Postgres, the type system is extensible, so you can create
> new custom SQL datatypes, which can be mapped to Scheme types.  One
> tricky thing is data types like arrays and lists.  I've chosen to map
> vectors to arrays and lists to tuples (which are compound objects).
> It's unclear what Postgres JSON or HStore objects should map to, though,
> for example.  And in MySQL/Mariadb I think you have only JSON but no
> true arrays, which might make choosing vectors to represent arrays in
> general a bit awkward; one would probably prefer to use vectors for
> JSON arrays (and alists for JSON objects?).

I'm drafting a JSON SRFI. The most promising convention is the one you
also propose: represent JSON arrays as Scheme vectors and JSON objects
as alists. It would make sense to use the same mapping for all other
hierarchical formats too.

What are Postgres tuples like - are they named tuples? Is it
conventional or easy to get the names of the fields and use them, or is
the field mapping implicit based on the order the values are written?

A HStore object looks just like a JSON object:
<https://www.postgresql.org/docs/9.0/hstore.html>. Are there subtle
differences, and can you nest JSON within a HStore and/or vice versa?

> And in MySQL/Mariadb I think you have only JSON but no
> true arrays, which might make choosing vectors to represent arrays in
> general a bit awkward

In what way do you mean it is awkward?

> For now mapping arrays to vectors this seems to be a decent choice, but
> there are some issues with Postgres array bounds.  For one, arrays are
> one-based by default.  But they can also have custom bounds aren't
> one-based.  For example, '[-1,2]={1,2,3}'::int[] constructs an array that
> has a lower bound of -1 and an upper bound of 2.

Like Pascal arrays. Can there be holes in the indexes?

Are Postgres arrays typed, untyped, or either? What's the difference
between arrays, byte arrays and (different kinds of) strings?

> Another important thing we should nail down early is what to do with the
> SQL NULL type.  In CHICKEN, we have a separate sql-null[3] egg that deals
> with these values, which is used by some (but, annoyingly, not all) eggs
> as a common denominator.  Some interfaces use #f to indicate NULL, but
> that causes trouble if the database in question also supports a boolean
> type.

For JSON we're going with the symbol `null` as the null indicator. Many
Scheme implementations have a void or undef type, which might be better,
but it's not as portable.