We had to abandon Zapatos because a) it doesn’t support multiple schemas; b) the types wouldn’t always be very readable.

PgTyped was the alternative, and 2 years later I’m very glad we made the switch.

It does now support multiple schemas: https://jawj.github.io/zapatos/#60

I don’t know if this has changed recently, but I’m using multiple schemas with Zapatos just fine. The type you can import looks like `auth.user.Selectable`, where auth is the schema and user the table.

What do you mean multiple schemas ? What is an example of a non readable type?

Like this, it's pretty common way to use Postgres (with multiple schemas):

  CREATE SCHEMA a;

  CREATE SCHEMA b;

  CREATE TABLE a.foo (id SERIAL PRIMARY KEY);

  CREATE TABLE b.foo (id SERIAL PRIMARY KEY);

I didn’t know support for schemas had landed, but the docs still seem obtuse for joins and upserts. Compared to plain SQL files in PgTyped — which admittedly needs some annotations to work, and lacks transactions —, I find the lateral joins in Zapatos really verbose and ugly. Lots of extra parameters too.

Which isn’t to say it’s not a great tool! You pick what you like :)

Glad you are liking pgTyped. Transactions are supported as pgTyped is just a thin wrapper around node-postgres: https://github.com/adelsz/pgtyped/discussions/448

Ah, I meant having a single transaction in a SQL file, as that’s how we’re using it. So having multiple queries with a BEGIN and COMMIT wrapping them.

Ever try drizzle?

I haven’t, but it’s an ORM so an automatic no. We tested 6 ORMs before deciding that plain SQL with introspection was the way to go. No regrets.

Seconded. The lateral join features in combination with the shortcut functions make it exceptionally easy to build backends with actual SQL, without compromising on ease of use.

Does that work also with Postgres-compatible backends like Bigquery et al ?

It relies on pg (node-postgres) to talk to Postgres, so it should work with anything pg can talk to.

At first glance Zapatos doesn't appear to be Cloudflare workers compatible though I'd love to be corrected.

It should be, now that pg runs fine there. If it’s not, please file an issue!

It also works with @neondatabase/serverless on platforms without TCP connections (though it’s on my TODO list to make this less fiddly): https://github.com/neondatabase/neon-vercel-zapatos

Do you know why zapatos relies on pg and not PostgresJs?