Once again, thanks to John for the draft.

> - It seems unnecessarily complicated to me to have a special
>     "transaction" object.  All SQL databases I know of have a
>     transaction be a state of the connection.  So you can't
>     create multiple transactions on the same connection.
>     Dropping this and having transaction be a property of the
>     "db" object would be simpler and reduce the API surface.

> - I would extend transaction support to include a "level".  Like I
>    mentioned in my other mail, you can nest transactions via savepoints
>    which can be individually rolled back or "committed" (which is a no-op).

The main thing is that we need to have an API where transactions can be
nested arbitrarily without manually counting the nesting depth (or even
knowing what the depth is). In my experience, gets troublesome to manage
even a relatively small codebase if you can't wield "with-transaction"
blocks around freely.

I don't know how to get the best support for this pattern from the DB
engines, but whatever it takes, I'd almost rate it a must.

>     It's a bit of an antipattern to store file data in
>     databases.

It's true that it's frowned upon, but I think a lot of it is the same
purist thinking that says even the simplest problems should be modeled
as objects and classes. Unless you need to store giant files, it's
extremely convenient to store them in the same place where you store all
your other data. You can use the same access credentials and API, the
same transactions, the same backup arrangement, and get the same ACID
guarantees. And if you use SQLite your entire app's data is in a single
file that's easy to dump, scp and archive anywhere. Plus the DB engines
have seen decades of development to do all of that quickly and reliably.

If you go with separate files, you'll end up doing an ad-hoc bug-ridden
informally-specified implementation of half of those things ;-)

In a small-to-medium scale web app, I'd write all files into the
database without a moment's thought. I'm pretty sure there is no
scenario in which registering for a separate Amazon S3 account and using
a separate library is easier unless you have huge traffic, huge files or
huge numbers of them. Even them, storing them in PostgreSQL might be as
fast. And if it's slow, you may be able to get away with a cache for
reads. Lots of big web services use a SQL database as the "ground truth"
and archival system, with Redis and other caches on top to handle most
of the action.

I may have stored the files base64-encoded, though, when I did this.
Can't quite remember. There may be some problems porting blobs between
databases. Even with base64 it's very convenient unless you need unusual
speed/space savings.

> - Drop the difference between statements and queries; in Postgres at
>     least you can get a result set with INSERT...RETURNING, so the
>     difference is moot.  Everything can return a result set.
>     I don't know if this can be maintained always though; needs some
>     more investigation.

+1