12 ms·

Show HN: Write universally accessible SQL, not library-specific ORM wrapper APIs

112 points by ckmar 5y ago

1MachineElf 5y ago
FYI, it's only for PostgreSQL.
ldd 5y ago
This may sound odd, but I never understood using heavy classes with relational databases. I use javascript too, and at most I need a couple of HOF (higher order functions) to do most of the work I need. Then again, I do use typescript and its type system, so maybe I am not the target audience.
- ranguna 5y ago
  Same, I usually run away when an ORM is forcing classes on me, sequelize ftw
cpursley 5y ago
I bounced when I saw the Class extends syntax.
- devwastaken 5y ago
  Elaborate
  - penthief 5y ago
    Enforced class hierarchies are not very adaptable. A simple approach would expect records, a mapping function and the findOne, findList, findSet plumbing. There should not be a need to enforce a class hierarchy framework upon the integrating code. Somewhat counter intuitively, type hierarchies cause leaky abstraction layers.
tomrod 5y ago
I mean, good luck when you are later porting to another DB system.
- joppy 5y ago
  How big of a concern is this? Nowhere I’ve ever worked has been concerned with the ability to swap the database for a completely different one, in the same way they are not concerned with the ability to swap whatever programming language is being used for a different one.
  - MattGaiser 5y ago
    I worked for an organization that used Oracle for relatively trivial applications. The difficulty of switching to MySQL and then later to Postgres convinced them to use an ORM going forward. Basically if your app might last 20 years (this one went online when I was 4) it might be a consideration.
    - setr 5y ago
      IME if you didn’t use the DB -specific features, migration is painful, but not that much so. If you did use the DB specific features, you’re screwed regardless of an ORM. NB: I work for a DB migration firm, specializing in legacy->modern — where our usual customer has thousands of SP’s that have to be migrated and an ORM would have saved you maybe 5% of the effort; so my experience is biased towards that level of difficulty.
      - sumtechguy 5y ago
        TSQL and PSQL ideas of a stored procedure are not really the same. If your SQL is mostly bound columns or some sort of ORM only then you can get lucky and just play the data type matching game. But most of the ones I have ever had to port was playing the 'rewrite these 300 stored procs again' game. They usually ended up in some sort of stored procedure for one of a few reasons 'they only knew how to do that', performance, that is what the senior guy wants.
sparker72678 5y ago
I mean, more power to you if this is something you want, but writing raw SQL is something I will never miss.
- purerandomness 5y ago
  Why? Just like learning Emacs/vim, the time investment is well worth it. You get much more elaborate the more time you invest in learning, and the skills gained will still be valuable in 25 years - as opposed to whatever subset of SQL the query builder du jour would give you.
  - smcl 5y ago
    It sounds like the commenter knows SQL, so it’s not like they want to avoid learning it. I can only guess, but more likely they dislike the fact that you have to represent your raw queries etc safely as a string in your application’s language - which isn’t hard but it does look a little ungainly and tends not to scale too well. As for the “du jour” part - is there that much churn and fanboyism in ORMs?
    - spfzero 5y ago
      Strings do give you the ability to copy them right into your code from the SQL console after testing.
      - smcl 5y ago
        While that is true, I think most queries would involve parameters of some sort, so those would need to be fiddled with at the very least after pasting to your code, plus you'd also want to format them so they don't look that out of place. And at that point you've likely got a single maybe-weirdly indented query that appears as a plain string, which is fine if you have just a couple but can get a little tricky if you have a larger application. I'm not super religious about this - a colleague and I were discussing Hangfire.io's SQL Server code which has inline SQL[0] and we ended up agreeing that it's fine - but if I'm writing an application with a SQL backend I'm definitely leaning towards using something like EF in .NET [0] = https://github.com/HangfireIO/Hangfire/blob/master/src/Hangfire.SqlServer/SqlServerStorage.cs#L496-L499 https://github.com/HangfireIO/Hangfire/blob/master/src/Hangf...
zzzeek 5y ago
SQLAlchemy author here. I would just note that these two statements are contradictory: > The name pureORM reflects both that it is pure ORM (there is no query builder dimension) and then > Specifying all the columns is tedious; lets use BaseBo.getSQLSelectClause() to get them for free. the "getSQLSelectClause()" is absolutely a query builder function. Building out the columns to select from is in fact where things get very complicated if you are for example using SQL aliases, selecting the entity from subqueries, etc. I would predict this method would have to be very complicated to truly be useful in such real world scenarios, so you'd end up with a "pure" ORM that still has a significant query builder, just one that has its own particular brand of awkwardness in that the textual SQL you write has to match up with the assumptions of getSQLSelectClause().
- BiteCode_dev 5y ago
  It's always the same with the anti orm crowd, they either avoid any wrapper and prevent having a standard api to build uppon and inspect, or they create a light wrapper that ends up being a poor badly tested and documented implementation of 1% of SQLA.
  - gabereiser 5y ago
    Took the words right out of my mouth. As someone who has been adamant about using orm’s for the last 15 years, it shocks me to no end that an engineer (or group of engineers) think they can roll a DAO better than a battle hardened orm like SQLAlchemy, Hibernate, Entity Framework. It’s hubris in the least and straight arrogance at best.
    - ckmar 5y ago
      YOLO
    - blacktriangle 5y ago
      It depends what you're doing with the data. If you're trying to map into an object based view of your data, then yes ORMs have at least thought of most of the issues there. However if you'd like to view your relational data gasp relationally, then the ORM is a giant anchor around your neck.
cabalamat 5y ago
In the readme you say: >This contrasts against traditional ("stateful") ORMs which use query builders (rather than raw SQL) to return database-aware (rather than pure) objects. >The name pureORM reflects both that it is pure ORM (there is no query builder dimension) as well as the purity of the mapped Objects. I don't know what you mean by "pure" or "purity" here, and i think an explantion would help. Also, in the code: db.one(query) "one" does not strike me as a particularly expressive method name.
- athenot 5y ago
  I'm not familiar with it, but at first glance, "one()" stands in contrast to "multiple()". Either running a single query or running multiple queries in a request.
  - zachrip 5y ago
    It is used for the amount of objects returned (not the amount of raw rows returned, to be clear). Many orms have `findOne` or `findAll` methods that return an object or an array respectively. Just helps to make things more ergonomic and usually appends a LIMIT clause.
    - athenot 5y ago
      Thanks for the correction. I guess that validates the OP's point after all.
  - cabalamat 5y ago
    Ah, that makes more sense now.
- ptx 5y ago
  "single" might be a better name. The Kotlin standard library uses that name for a variant of the "filter" method (on collections) that returns a single matching element or throws an exception if there are more or fewer.
  - 5y ago
gigatexal 5y ago
IMO ORMs are an abstraction too far. I’d rather use a query builder. It gives you better control over the query if you must use such an abstraction. Of course I would much prefer raw SQL and then doing the mapping to objects and serialization myself but that’s not for everyone and yadda yadda move fast … startups etc etc
ltbarcly3 5y ago
I tried to do something similar with https://github.com/justinvanwinkle/Norm https://github.com/justinvanwinkle/Norm about 10 years ago. It hasn't generated a lot of interest, but I find it quite useful to construct queries without having to learn the minutia of an ORM library, or even a SQL generation library. Probably one of the best parts of Norm, and a big part of why I wrote it, is that it doesn't require you to have a hardcoded copy of the databases schema in your code, it just works like SQL. I put quite a bit of effort into making bulk inserts efficient, as well as making sure rows could be streamed from the database while buffering as few as possible in memory on the client. I still maintain and update for my own use. Feel free to make suggestions or request features.
- spfzero 5y ago
  Nice work!
simonbarker87 5y ago
I feel like one of the few JS devs who is happy to just write SQL. Inline or as a stored procedure I just don’t see how learning an ORM and all it’s issues and bugs is harder than learning SQL.
- dyeje 5y ago
  There is no good JS ORM, so it's for the best.
  - bitwize 5y ago
    Best practice, in the case of JS, is to use MongoDB, which has a native JS query API, and then use microservice patterns to provide joins, two-phase commit, and (eventual) consistency.
    - yowlingcat 5y ago
      Why would your database choice have anything to do with your language choice?
      - bitwize 5y ago
        Because your programmers don't know SQL and don't want to. In an ideal world of perfectly spherical programmers, choosing JavaScript would be completely orthogonal to database choice, but in reality one common symptom of JavaScriptitis is considering SQL an old relic that's too much of a pain in the ass to learn. Thankfully, MongoDB's query language is JavaScript and its result sets are JSON objects. To be fair, this sort of feeling toward SQL is not found uniquely among JavaScript programmers; one major reason why we have ORM hell is because a previous generation of programmers did not want to leave the warm cocoon of treating data as objects in Java.
        yowlingcat 5y ago
        Assuming you're not being facetious (which I'm not sure of)... You have described why people make such decisions, but you have not yet made an argument for whether it's wise or not. Can you imagine why it would be an unwise decision? Even if you have engineers who stubbornly refuse to learn SQL (a problem with your engineering management if there ever was one), there's always options like Hasura [1]. [1] https://hasura.io/ https://hasura.io/
hardwaresofton 5y ago
In this space but a little lighter: https://github.com/gajus/slonik https://github.com/gajus/slonik

112 points by ckmar 5y ago

FYI, it's only for PostgreSQL.

ldd 5y ago

This may sound odd, but I never understood using heavy classes with relational databases. I use javascript too, and at most I need a couple of HOF (higher order functions) to do most of the work I need. Then again, I do use typescript and its type system, so maybe I am not the target audience.

ranguna 5y ago
Same, I usually run away when an ORM is forcing classes on me, sequelize ftw

cpursley 5y ago

I bounced when I saw the Class extends syntax.

devwastaken 5y ago
Elaborate
- penthief 5y ago
  Enforced class hierarchies are not very adaptable. A simple approach would expect records, a mapping function and the findOne, findList, findSet plumbing. There should not be a need to enforce a class hierarchy framework upon the integrating code. Somewhat counter intuitively, type hierarchies cause leaky abstraction layers.

tomrod 5y ago

I mean, good luck when you are later porting to another DB system.

joppy 5y ago
How big of a concern is this? Nowhere I’ve ever worked has been concerned with the ability to swap the database for a completely different one, in the same way they are not concerned with the ability to swap whatever programming language is being used for a different one.
- MattGaiser 5y ago
  I worked for an organization that used Oracle for relatively trivial applications. The difficulty of switching to MySQL and then later to Postgres convinced them to use an ORM going forward. Basically if your app might last 20 years (this one went online when I was 4) it might be a consideration.
  - setr 5y ago
    IME if you didn’t use the DB -specific features, migration is painful, but not that much so. If you did use the DB specific features, you’re screwed regardless of an ORM. NB: I work for a DB migration firm, specializing in legacy->modern — where our usual customer has thousands of SP’s that have to be migrated and an ORM would have saved you maybe 5% of the effort; so my experience is biased towards that level of difficulty.
    - sumtechguy 5y ago
      TSQL and PSQL ideas of a stored procedure are not really the same. If your SQL is mostly bound columns or some sort of ORM only then you can get lucky and just play the data type matching game. But most of the ones I have ever had to port was playing the 'rewrite these 300 stored procs again' game. They usually ended up in some sort of stored procedure for one of a few reasons 'they only knew how to do that', performance, that is what the senior guy wants.

sparker72678 5y ago

I mean, more power to you if this is something you want, but writing raw SQL is something I will never miss.

purerandomness 5y ago
Why? Just like learning Emacs/vim, the time investment is well worth it. You get much more elaborate the more time you invest in learning, and the skills gained will still be valuable in 25 years - as opposed to whatever subset of SQL the query builder du jour would give you.
- smcl 5y ago
  It sounds like the commenter knows SQL, so it’s not like they want to avoid learning it. I can only guess, but more likely they dislike the fact that you have to represent your raw queries etc safely as a string in your application’s language - which isn’t hard but it does look a little ungainly and tends not to scale too well. As for the “du jour” part - is there that much churn and fanboyism in ORMs?
  - spfzero 5y ago
    Strings do give you the ability to copy them right into your code from the SQL console after testing.
    - smcl 5y ago
      While that is true, I think most queries would involve parameters of some sort, so those would need to be fiddled with at the very least after pasting to your code, plus you'd also want to format them so they don't look that out of place. And at that point you've likely got a single maybe-weirdly indented query that appears as a plain string, which is fine if you have just a couple but can get a little tricky if you have a larger application. I'm not super religious about this - a colleague and I were discussing Hangfire.io's SQL Server code which has inline SQL[0] and we ended up agreeing that it's fine - but if I'm writing an application with a SQL backend I'm definitely leaning towards using something like EF in .NET [0] = https://github.com/HangfireIO/Hangfire/blob/master/src/Hangfire.SqlServer/SqlServerStorage.cs#L496-L499 https://github.com/HangfireIO/Hangfire/blob/master/src/Hangf...

zzzeek 5y ago

SQLAlchemy author here. I would just note that these two statements are contradictory: > The name pureORM reflects both that it is pure ORM (there is no query builder dimension) and then > Specifying all the columns is tedious; lets use BaseBo.getSQLSelectClause() to get them for free. the "getSQLSelectClause()" is absolutely a query builder function. Building out the columns to select from is in fact where things get very complicated if you are for example using SQL aliases, selecting the entity from subqueries, etc. I would predict this method would have to be very complicated to truly be useful in such real world scenarios, so you'd end up with a "pure" ORM that still has a significant query builder, just one that has its own particular brand of awkwardness in that the textual SQL you write has to match up with the assumptions of getSQLSelectClause().

BiteCode_dev 5y ago
It's always the same with the anti orm crowd, they either avoid any wrapper and prevent having a standard api to build uppon and inspect, or they create a light wrapper that ends up being a poor badly tested and documented implementation of 1% of SQLA.
- gabereiser 5y ago
  Took the words right out of my mouth. As someone who has been adamant about using orm’s for the last 15 years, it shocks me to no end that an engineer (or group of engineers) think they can roll a DAO better than a battle hardened orm like SQLAlchemy, Hibernate, Entity Framework. It’s hubris in the least and straight arrogance at best.
  - ckmar 5y ago
    YOLO
  - blacktriangle 5y ago
    It depends what you're doing with the data. If you're trying to map into an object based view of your data, then yes ORMs have at least thought of most of the issues there. However if you'd like to view your relational data gasp relationally, then the ORM is a giant anchor around your neck.

cabalamat 5y ago

In the readme you say: >This contrasts against traditional ("stateful") ORMs which use query builders (rather than raw SQL) to return database-aware (rather than pure) objects. >The name pureORM reflects both that it is pure ORM (there is no query builder dimension) as well as the purity of the mapped Objects. I don't know what you mean by "pure" or "purity" here, and i think an explantion would help. Also, in the code: db.one(query) "one" does not strike me as a particularly expressive method name.

athenot 5y ago
I'm not familiar with it, but at first glance, "one()" stands in contrast to "multiple()". Either running a single query or running multiple queries in a request.
- zachrip 5y ago
  It is used for the amount of objects returned (not the amount of raw rows returned, to be clear). Many orms have `findOne` or `findAll` methods that return an object or an array respectively. Just helps to make things more ergonomic and usually appends a LIMIT clause.
  - athenot 5y ago
    Thanks for the correction. I guess that validates the OP's point after all.
- cabalamat 5y ago
  Ah, that makes more sense now.
ptx 5y ago
"single" might be a better name. The Kotlin standard library uses that name for a variant of the "filter" method (on collections) that returns a single matching element or throws an exception if there are more or fewer.
- 5y ago

gigatexal 5y ago

IMO ORMs are an abstraction too far. I’d rather use a query builder. It gives you better control over the query if you must use such an abstraction. Of course I would much prefer raw SQL and then doing the mapping to objects and serialization myself but that’s not for everyone and yadda yadda move fast … startups etc etc

ltbarcly3 5y ago

I tried to do something similar with https://github.com/justinvanwinkle/Norm https://github.com/justinvanwinkle/Norm about 10 years ago. It hasn't generated a lot of interest, but I find it quite useful to construct queries without having to learn the minutia of an ORM library, or even a SQL generation library. Probably one of the best parts of Norm, and a big part of why I wrote it, is that it doesn't require you to have a hardcoded copy of the databases schema in your code, it just works like SQL. I put quite a bit of effort into making bulk inserts efficient, as well as making sure rows could be streamed from the database while buffering as few as possible in memory on the client. I still maintain and update for my own use. Feel free to make suggestions or request features.

spfzero 5y ago
Nice work!

simonbarker87 5y ago

I feel like one of the few JS devs who is happy to just write SQL. Inline or as a stored procedure I just don’t see how learning an ORM and all it’s issues and bugs is harder than learning SQL.

dyeje 5y ago
There is no good JS ORM, so it's for the best.
- bitwize 5y ago
  Best practice, in the case of JS, is to use MongoDB, which has a native JS query API, and then use microservice patterns to provide joins, two-phase commit, and (eventual) consistency.
  - yowlingcat 5y ago
    Why would your database choice have anything to do with your language choice?
    - bitwize 5y ago
      Because your programmers don't know SQL and don't want to. In an ideal world of perfectly spherical programmers, choosing JavaScript would be completely orthogonal to database choice, but in reality one common symptom of JavaScriptitis is considering SQL an old relic that's too much of a pain in the ass to learn. Thankfully, MongoDB's query language is JavaScript and its result sets are JSON objects. To be fair, this sort of feeling toward SQL is not found uniquely among JavaScript programmers; one major reason why we have ORM hell is because a previous generation of programmers did not want to leave the warm cocoon of treating data as objects in Java.
      - yowlingcat 5y ago
        Assuming you're not being facetious (which I'm not sure of)... You have described why people make such decisions, but you have not yet made an argument for whether it's wise or not. Can you imagine why it would be an unwise decision? Even if you have engineers who stubbornly refuse to learn SQL (a problem with your engineering management if there ever was one), there's always options like Hasura [1]. [1] https://hasura.io/ https://hasura.io/

hardwaresofton 5y ago

In this space but a little lighter: https://github.com/gajus/slonik https://github.com/gajus/slonik