Stream: ideas

Topic: SQL package

Luke Boswell (Mar 22 2024 at 01:15):

@Agus Zubiaga do you think it would be useful to have a SQL package that has common primitives that would map across different SQL DBs?

I've not put much research into this idea, but wanted to raise it for discussion.

I am looking at basic-webserver SQLite implementation and thinking of switching to libSQL to get support form remote DBs.

One benefit of a more tailored package like roc-pg is "unlocking the DB features" with less abstraction.

But maybe there could be common primitives that could be shared across different platforms in the Roc ecosystem or serve as a base?

For example the values supported by this library include the following which map nicely to Roc types.

pub enum Value {
    Null,
    Integer(i64),
    Real(f64),
    Text(String),
    Blob(Vec<u8>),
}

Interested to know your thoughts.

Luke Boswell (Mar 22 2024 at 01:20):

I think the common parts which might be useful include Connection Database Row Column Statement Transaction etc.

Agus Zubiaga (Mar 22 2024 at 01:20):

It’s definitely doable. The most commonly used interfaces in most languages are database agnostic.

Personally, I think this leads to underutilized database features, because you’re often restricted or incentivized to stick to the common denominator.

That’s why I want to make roc-pg and its query builder specific to Postgres. I think it’s a path that hasn’t really been explored.

Luke Boswell (Mar 22 2024 at 01:22):

Once we have module params, I would be interested to explore design ideas here with you. It would be cool to have both common types, but also really tailored DB specific packages.

Agus Zubiaga (Mar 22 2024 at 01:25):

Yeah, there might be a way to have it both ways. I would prefer not to compromise, though. Especially since I always end up using Postgres anyway :laughing:

Agus Zubiaga (Mar 22 2024 at 01:27):

My last 3 jobs all moved from a different engine to Postgres

Richard Feldman (Mar 22 2024 at 01:42):

yeah I like the idea of having similar APIs that each target a particular DB

Richard Feldman (Mar 22 2024 at 01:42):

as in, if you've learned one you can pick up another one easily

Richard Feldman (Mar 22 2024 at 01:42):

but they aren't lowest-common-denominator either

Richard Feldman (Mar 22 2024 at 01:46):

my experience with ORMs when it comes to multiple databases has been that:

• I've never worked anywhere that used a library that ran DB-agnostic SQL that you were supposed to connect to whatever SQL DB you happened to be using, but if there's desire for that, having a dedicated "generic SQL builder for library authors" package seems like a fine solution for that since it all ends up as strings anyway
• transitioning from one SQL DB to another in practice turns out to be a gigantic engineering project whether or not you've used an ORM

Richard Feldman (Mar 22 2024 at 01:47):

so I say embrace the DB you've got and use it to the full extent it supports, because switching is going to be a huge project even if you intentionally avoid using features you'd otherwise benefit from

Luke Boswell (Mar 22 2024 at 01:55):

I wasn't really thinking ORM or String building... I was thinking more about the glue parts. Like using a similar Connection API in roc, with the specific options available tailored to suit the specific DB.

Luke Boswell (Mar 22 2024 at 01:55):

Or like having a result returns Rows of data that can be decoded somehow into Roc types in a similar way

Agus Zubiaga (Mar 22 2024 at 02:08):

Yeah, I think there are probably some helpers that could be shared. For example, the command type/module in roc-pg is probably generic enough.
Decoding probably can’t be shared, though. I expect most engines to have no consistency there.

Agus Zubiaga (Mar 22 2024 at 02:09):

I’d wait for another library to exist to determine if it’s worth extracting these things into its own package.

Last updated: Jun 16 2026 at 16:19 UTC