DEV Community

Alec Larson
Alec Larson

Posted on

Postgres TypeScript bindings for "stored procedures"

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

AWS Security LIVE!

Tune in for AWS Security LIVE!

Join AWS Security LIVE! for expert insights and actionable tips to protect your organization and keep security teams prepared.

Learn More

Top comments (0)

A Workflow Copilot. Tailored to You.

Pieces.app image

Our desktop app, with its intelligent copilot, streamlines coding by generating snippets, extracting code from screenshots, and accelerating problem-solving.

Read the docs

👋 Kindness is contagious

Discover a treasure trove of wisdom within this insightful piece, highly respected in the nurturing DEV Community enviroment. Developers, whether novice or expert, are encouraged to participate and add to our shared knowledge basin.

A simple "thank you" can illuminate someone's day. Express your appreciation in the comments section!

On DEV, sharing ideas smoothens our journey and strengthens our community ties. Learn something useful? Offering a quick thanks to the author is deeply appreciated.

Okay