Disclaimer: If you have preconceived notions about making heavy use of “stored procedures” (personally, I just call them “routines” like Postgres does), then you probably won't like what I'm showing you, and I won't try to convince you.
I'm the creator of pg-nano. It's not an ORM, a query builder, or a basic query driver, but it's closest to the last one. The twist is, it's also a migration tool and a code generator. It's not production-ready yet (more on that below).
The link: https://github.com/pg-nano/pg-nano/
It generates TypeScript bindings for your native Postgres routines (think CREATE FUNCTION or CREATE PROCEDURE, excuse the caps). For views (e.g. CREATE VIEW), pg-nano can infer each column's “nullability” via static analysis. I plan to extend that inference to user-defined routines in the near future, but the generated types are already quite good.
From your TypeScript application server, you call your Postgres routines with 100% type safety. The query driver uses libpq, the official C driver, under the hood. I've implemented a connection pool, auto-reconnect with exponential backoff, and query streaming on top of libpq.
It scans a directory for .sql files and instantly updates your local database instance by diffing the current schema with the desired schema. It only drops data if absolutely necessary. Note that I haven't implemented production migrations yet, which will of course err on the safe side.
I use a combination of static analysis (parsing your SQL) and introspection (querying Postgres system tables) at compile time to both generate the TypeScript bindings and the migration plan.
The link again: https://github.com/pg-nano/pg-nano/
I posted all this to get your feedback:
- Could you see yourself using pg-nano? Why or why not?
- Are there specific features you’d like to see, or concerns you have?
I could really use some beta testers, but even your thoughts would help a great deal.
In order to get pg-nano production ready, I have a few things left to do.
Database seeding
Migrations in production
Transactions