Typesafe PostgreSQL stored procs / function calls

[shah]

What version of drizzle-orm are you using?

0.18.0

Describe the Bug

This is not a bug but a feature request / question. I noticed that #116 will be adding type-safe views and seems like a fantastic idea. In our projects we heavily rely on PostgreSQL stored procedures and functions. Are there any plans to add support for those objects?

This is a fantastic project and I love the concept of "if you know SQL you know Drizzle!" - being a thin, type-safe, wrapper around SQL is an excellent strategy and I'd love to see other PostgreSQL objects supported.

Thanks!

[dankochetov]

We have it on our roadmap, can't provide an ETA yet, though. Stay tuned 🙂

In the meantime, you can just use raw SQL to work with them, like this:

db.run(sql`call proc_name()`);
const rows = db.all<{ col1: string; col2: number; }>(sql`select func_name()`);

To create procedures/functions in migrations, you can just manually add them to one of the migration files. We're working to support first-class custom migrations as well.

[shah]

Excellent idea, will use raw SQL for now.

Having first-class support for procs/functions/domains (especially with auto-generated migrations like for tables) would be fantastic.

I have implemented some of these Typescript template literal procedure/func/domain generators in my own library and would love to discuss how I can contribute that work to integrate into Drizzle. It would be easier to discuss on a video call so if there's a chance to speak to one or more of the core team members about that I would appreciate the opportunity (at your convenience).

Thanks!

[shah]

I saw v0.20.1 drop today, great job with the with addition! I was curious if you had any updates for when the view or when type-safe PG stored procedures / function calls might land. :-)

[AndriiSherman]

@shah thanks!
view support is currently next big thing to be released, so we are currently working on it

Regarding your contribution - we are open to any suggestions. I guess best way would be to join our Discord channel and send us your suggestions on how you see PG stored procedures / function in drizzle. We can continue our discussion with you from there

[questloggg]

Hello! Since quite some time has passed I figured I'd ask whether there is a rough ETA for RPC's/functions or whether it's still unknown. We rely quite a lot on the reusability and performance of RPCs so would love to see first class support. Thanks and keep up the great work!

[benhassineb]

Excellent idea, will use raw SQL for now.

Having first-class support for procs/functions/domains (especially with auto-generated migrations like for tables) would be fantastic.

I have implemented some of these Typescript template literal procedure/func/domain generators in my own library and would love to discuss how I can contribute that work to integrate into Drizzle. It would be easier to discuss on a video call so if there's a chance to speak to one or more of the core team members about that I would appreciate the opportunity (at your convenience).

Thanks!

@shah
I've been working on similar Typescript template literal generators.
Could you please share the code with me? I'm interested in exploring it further. Thank you!

[shah]

Sure, @benhassineb my code is available at https://www.github.com/netspective-labs/sql-aide.

[97albertus]

any update on stored procedures?...

[sathishkannan162]

Any update on this