As an example, the sqlalchemy docs[0] make this very clear: there's an ORM, but there's also just a core expression library that simply helps you connect to the database and construct queries.

[0]: https://docs.sqlalchemy.org/en/13/

I love how it let's you use SQL, while taking full advantage of TypeScript's wonderful typing system to give you intellisense and compile-time checking. Reminds me a bit of the SQL type provider for F# (which I was amazed by when I first saw it in action).

I really like the way the readme has been written too - it gives a real insight into the thought processes that led to the final result.

Good news. :)

Maybe we can agree that typegen smells less than codegen?

Reminds me a bit of the SQL type provider for F# (which I was amazed by when I first saw it in action).

I should look that up — sounds interesting.

I really think typescript would benefit from a good solution to this.

I am old fashioned. I like to start with the database schema and generate code from that. I make a change in the schema I regen the code. Thanks for partial classes in C# I can persist customizations between code-gens if necessary.

What just blew me away is the thing with the `JOIN` and the `to_jsonb(authors)`, all with complete typing support for the nested author object. I was actually looking to use a classical, attribute driven query generator (with the sort of chaining API everyone is used to: `tableName.select(...coumns)` etc.) for my next project involving to maybe replace/wrap/rewrite a Rails app and its ORM with Typescript and Node. Maybe I'm trying this instead I'm already half sold. Just worried about forcing colleagues having to learn SQL instead of using a fancy wrapper.

> Just worried about forcing colleagues having to learn SQL instead of using a fancy wrapper.

My current team is pretty junior, and I don't see any problem with this. Simple SQL queries are really easy to learn, and complex queries are harder to understand with ORMs than in raw SQL.

Moreover, knowing SQL is a useful, marketable skill that will stay relevant for many years to come. If there's some resistance, I can easily convince the team that going this route will benefit them personally.

Back to the README, there are two questions I'd like to see addressed:

1. Whether `Selectable[]` can be used to query for a subset of fields and how.

2. In the `to_jsonb(authors)` example, what would you get back in the `author` field if there were multiple authors with the same `author.id` value? An array of `author.Selectable` objects? This part is awesome but brittle, isn't it?

I would love to see this move forward! I will definitely play with it and consider it for my next project.

:)

1. Whether `Selectable[]` can be used to query for a subset of fields and how.

Right — this is not (currently) supported. I guess if you had wide tables of large values, this could be an important optimisation, but it hasn't been a need for me as yet.

2. In the `to_jsonb(authors)` example, what would you get back in the `author` field if there were multiple authors with the same `author.id` value? An array of `author.Selectable` objects? This part is awesome but brittle, isn't it?

Multiple authors with the same id isn't going to happen, since id is intended as a primary key, so I'd argue that the example as given isn't brittle. On the other hand, there's a fair question about what happens for many-to-many joins, and since my use case hasn't yet required this I haven't given it much thought.

    type authorBookSQL = s.authors.SQL | s.books.SQL;
    type authorBookSelectable = s.authors.Selectable & { books: s.books.Selectable };

    const
      query = db.sql<authorBookSQL>`
        SELECT ${"authors"}.*, jsonb_agg(${"books"}.*) AS ${"books"}
        FROM ${"books"} JOIN ${"authors"} 
          ON ${"authors"}.${"id"} = ${"books"}.${"authorId"}
        GROUP BY ${"authors"}.${"id"}`,

      authorBooks: authorBookSelectable[] = await query.run(db.pool);

I'll update demo.ts and README shortly.

Your second case, if I recall this correctly (ActiveRecord made my SQL skills fade away), this plain JOIN would just return a row with the same book but a different author. `to_jsonb(authors.*)` is just operating on a single row. But what you want is possible (aggregating rows into a JSON object) by using `jsonb_agg`. Whether the lib supports inferring the correct typings for that is another question though.

I'd argue that learning SQL is essential for any developer.

It's also a "reusable" skill that will stand them in good stead for decades - whereas learning how to use the fancy wrapper is only useful until the next new shiny comes along.

The long-standing ORMs do a pretty decent job of writing efficient queries these days though. You can go pretty far without knowing much and that’s not a bad thing either.

That lastpost you can map a half dozen Java frameworks to each of the acts.

Personally I never found an orm that tracked which attrs in objects were actually mutated so that only mutated columns would be updated/inserted, but again I never did a lot of orm.

const existingBooks = await pool.exe(select("books", { authorId }));

or

const existingBooks = await pool(select("books", { authorId }));

However, I've never understood why people write this mapping code manually. I believe in code generation tooling as a potential solution for this (where types and maybe a full data access API is auto-generated based on the database schema).

[0]: https://github.com/craigmichaelmartin/pure-orm

What's the big difference? Why do you like the former but not the latter? What are the characteristics of the former that makes it distinct from the latter?

That being said, I personally have found that I do not like OO languages for back end dev and I find that functional languages such as any variety of LISP marry extremely well to the transnational and process oriented nature of back-end systems as well as lend themselves to not having to jump thru hoops to contort the data into relational sets (Clojure's destructuring is an absolute life saver here). I find that there is little to no duplication of code in regards to transferring data to the db. You may want to give Clojure or F# (depending on your stack) a try for your back end and see if it does not alleviate a host of issues with trying to develop a process and transaction oriented system, which most back ends fit that definition.

I find the converse to be true for the front end. I find most attempts to deal with the UI in anything other than objects and components (read jQuery, React Hooks), turns to spaghetti rather quickly.

If you are using OO languages to communicate and transfer data to the DB you may very well be trying to solution for the impedance mismatch that is easily solved by using a functional language.

What alternatives have you tried?

This is one of the reasons I have long been a huge proponent of Javascript despite it warts, as it can be OO when I need it to be OO and functional when I need it to be functional.

You can do that in an OO language, too; relations (whether constant or variable, and whether there data is held locally by the program or remotely, as in an RDBMS) are perfectly valid objects.

A query builder carefully preserves the underlying relational and RPC semantics and exposes all of that to the user in an easier-to-use form. That’s just good cautious modest abstraction.

An ORM believes it knows way better than those dumb RDBs how a database ought to behave by ingeniously pretending that everything you’re dealing with is just nice simple familiar arrays of local native class instances. Which, like all lies, ends up spawning more lies as each one starts to crack under inspection, until the whole rotten pile catastrophically collapses under the weight of its own total bullshit.

And of course it goes without saying which of these approaches our arrogant grandstanding consequence-shirking industry most likes to adopt.

Maybe the problem is not that you don't need a mapping layer, but because ORMs are obscure. And maybe they are obscure not because SQL is such a cursed spot, but because object-oriented programming ITSELF drift toward obscurity and magic. Don't you get the same feeling of obscurity about other libraries, e.g. web servers or clients? I often find the bare specs much clearer than (supposedly simplified) OO libraries that implement them.

If you stick to using the ORM for what amounts to (mostly) just PODs, it's syntactic sugar that can really help readability.

For example, a company with many data analysts/scientists who may each be writing their own queries. As a basic example, the definition of some “very important” company metric changes, then there would need to be a large number of disperse queries to change.

But an ORM isn’t the answer for the above situation either.

It also sounds like you would be well served using a service abstraction at that point to remove the data layer from client scope entirely.

The "model changes, now we have to change it every where" isn't going to be solved by abstraction, it's only limited by the amount you're willing to limit access to the underlying model, if you need that information, you need to share the model.

The best solution to this I've seen in practice is domain modelling, colocating shared code near other users. When things get too distant you start using anti corruption layers which allows more flexible model changing.

But at the end of the day this is essential complexity, orm, or any other solution is never going to be able to hide the fact that you need information elsewhere in the system to be useful.

> Well, redundancy and dependency both have downsides, but in this case...

I've used hand rolled pseudo ORMs before.

I prefer just plain SQL but for the application I had there was a common access pattern that was worth abstracting out in a DRY sense.

That doesn't mean I want or need a complete ORM. Just a consistent access at certain table types.

There is usually a feature that no one thought about and then you have to make modifications to the custom ORM and you get an even bigger mess.

I wouldn't say what I built was a better ORM. But I would 100% say it's a better solution to the problem I faced.

It didn't get in the way of writing SQL, but it did reduce the boilerplate and repetitive gruntwork.

"Issues" - no, it was a function call deep and incredibly clear and close to what was happening.

Is your pseudo ORM as well documented as a commonly used ORM? Can I google (or search your wiki) for common issues?

Micro ORMs.

A lot of people conflate ORM's leaking because of poor designed library with ORM being a bad abstraction in general.

There is no real abstraction. You write standard SQL and it maps your recordset to an object. You know exactly what code is running.

There are extensions that will take a POCO object and create an insert statement and I believe updates, but where ORMs usually get obtuse and do magic are Selects. It’s hard to generate a suboptimal Insert or Update.

There are times I dislike things about it, and it can be quite heavy, but it’s very easy to mix and match ActiveRecord ORM code and raw SQL even within a single model class.

[1][https://github.com/prisma/photonjs]

> Micro ORMs.

And there's the mystical fourth option of simply not bothering with objects in the first place. No objects, no need to do object-relational mapping.

Nothing says the structure which make sense for an application can't be a generic array of generic dictionaries.

If your favourite programming language forces you to go through the silly hoops of data mapping in order to do useful things with query output, I can understand why an ORM might make sense for you.

Which bring us to the topic of tradeoffs and the synthesis of balance, by way of weighing competing advantages and costs fairly.

On the one hand, code you must write and understand. On the other, code someone else wrote, that you can just use. There is no clear winner here. It's always a tradeoff.

I rewrote our entire database layer in Hibernate for our (incredibly complex monolith) webapp. Then I was tasked with rewriting a major core piece of search functionality that builds a query from user selections / saved queries.

I was told that contrary to previous work, Hibernate Criteria would not be allowed, since it was deprecated. Hibernate's official replacement for programmatic queries is JPA Criteria, but Hibernate's support for this was not feature equivalent to Hibernate Criteria, so this was out too.

So what I got the green-light to go on was rewriting my own pseudo-ORM wrapper that generates HQL query strings and parameters. Hql is not deprecated, you see.

It's ended up working out moderately well, it's a thin layer and as long as you avoid the rough edges it actually works fairly well, as well as providing a convenient point to translate query language from the old kodo format into hibernate (cringe, code smell).

There have been times I've had to do some very awkward query shit that I've only managed to lever in via HQL. You have no idea, views on top of views.

No idea what'll happen after I leave, that's their problem!

Thanks for the job security, Hibernate team. Your incredibly-poorly-executed transition from a well-supported standard to the "new new" has been exquisitely great for my job security.

Bet there's Python3 devs who feel the same way!

Feature parity aside, I also found this to perhaps be the most verbose API for query building I've ever used.

Having written OSS and also having written enterprise applications, it seems plainly obvious to me why a homegrown solution is preferred. Code developed internally is understood by the team (you may not understand the underlying implementation of a dependency), and can be tailored exactly to suit your needs (ignoring edge cases that aren’t relevant, removing unneeded features). And you never have to worry about maintainers disappearing, breaking changes being introduced, or bugged releases that you can’t do anything about.

I don’t mean to sound crass but how on earth could you think this is an example of Poe’s law? What’s so extreme about being a responsible developer? I didn’t say “every solution should be developed in house” (though I think most large projects would be better for it!) obviously there’s is a cost associated with in house solutions and you should gauge that cost to see if it’s worth it for your application. But if you’re going to be working with that application for years and years to come then I highly recommend trying to write your own code instead of relying on libraries.

1. A high level database or queue lib, or a custom / powerful serialization lib or, relevant to this topic) an ORM or other foundational/low-level part of your tech stack.

What you can expect to happen is a bunch of very good programmers early on build powerful abstractions using macros, metaprogramming, advanced type system concepts and build up a codebase adding up to a few thousands of lines. It just works, it's a good system - a few bugs are patched by the team every month, but that's fine. Fast forward a few years, the programmers have moved on, "onboarded" the rest of the team to the codebase during their respective last week, but given how complicated the codebase is no one is really capable of debugging it and fixing issues. And given that it's not open-source, it never got an opportunity to build a community of contributors. Your team is now SOL, and it's going to take _months_ to replace it with a more well-maintained open-source solution.

2. Building a A/B testing implementation in-house - again a couple of good programmers build a working, scalable, basic system in a weekend. It actually works and the code is good, simple, readable and well-tested. But then, your PM team or your Marketing team wants you do add graphs. Then export the data to RedShift. And then tweak the algorithms powering the backend. Then multi-arm bandit. And so on. Now, what was now a weekend project, turns into months of work - whereas there exist paid services that do this for you.

Sometimes, it's unavoidable, external alternatives are genuinely not good*. But I strongly think, you have to be very, very careful about building systems in-house when they are not your business.

> I don’t mean to sound crass but how on earth could you think this is an example of Poe’s law.

Sorry for this. I do feel quite strongly against your original comment (at least the way it was written without context), and I think it's the _opposite_ of being a "responsible developer" in all but edge cases, and think you are wrong. But calling it an example of Poe's law was not right on my part, and was harsh.

> But if you’re going to be working with that application for years and years to come then I highly recommend trying to write your own code instead of relying on libraries.

I've done this, and have done both in-house and oss code, but in-house, very reluctantly - for example - when there's just one or two maintainers committing code, and there's no alternative. But even then, I have usually forked the code and used that or parts of that as the base, rather than starting from scratch

It’s really a balance, but I don’t think it’s a balance most devs consider and they really should.

Such as:

- API abstraction layers (like SDL, Allegro, SFML, etc.): you want to support new operating systems / new APIs by default. And most of the time, you don't want to spent time learning about the specifics of X11 window creation or Win32 events, as this would be throw-away knowledge anyway.

- hardware abstraction layers: you want to support new hardware by default, this is why we use operating systems and drivers.

- Format/protocols abstraction layers: if your game engine only uses JPEG files directly coming from your in-house asset pipeline, it's perfectly fine to develop in-house loaders (from scratch or from stb_image). But if your picture processing command-line tool aims to support every file format (especially, the ones that don't exist yet), then you should rather go with an updeatable third-party library, which will allow you to get all new formats by default.

- all kind of optimizers, including compilers, code JIT-ters, audio/video encoders, etc. More generally, all code that uses some heuristic so try to solve a problem that's not completely solved/solvable. You might be ready to accept the performance of a specific version of, for example, libjit. But you might instead consider that in your case, not having state-of-the-art JIT performance might be detrimental to your business, in this case you want to get the performance enhancements by default.

It’s crazy to me how many people on HN are ignorant to the costs of third party dependencies and the benefits of in house solutions when building large applications.

If you do test and document your own stuff properly you are in a small minority. Why not release it for others to use?

I wonder what your experiences had been if after dropping the ORM you had gone one step more and dropped the objects.

After contemplating my distaste for ORMs more carefully, I've come to the realisation that my objections aren't so much to do with the concept of an ORM but rather object orientation itself—and the fetish of treating it as the perfect hammer for every nail.

For the projects I've worked on, I've almost never wanted to turn data into objects. And on the occasions when I've thought otherwise, it usually turns out to be a mistake; de-objectifying can often result in simpler, shorter code with fewer data bugs.

Ultimately, the right answer depends on the nature of your particular business logic, how data flows in your wider ecosystem, and pragmatically, the existing skills of your workforce.

Seriously Jesse, isn't this the very same reason __some__ people end up implementing yet another programming language without realizing it?

First they start out of exasperation with X language they use, because they hit some obstacles or limitations, and before they know it they end up implementing a newly created language.

You know what's the fun part? In their attempt to fix the aforementioned language's issues, they end up introducing __the very same problems__ in their own language, only under different "cloak" so to speak.

It's a vicious cycle I'm afraid...

ORMs are terrible in this sort of world since they tightly couple the application to the data. But if you will only ever have one application anyway the abstraction of a separate schema is pointless.

Why is this the case?

1. If you are doing anything interesting, people are going to ask questions about what you are doing, and the best way to answer those questions is going to be by querying your database.

2. One day you might want to rewrite some of your service/s, split them into microservice/s, etc. At that point, there will be a minimum of two services talking to your datastore: the legacy service and whatever you're replacing it with. I suspect any alternative to this arrangement will be an even worse idea, e.g. taking a deliberate outage to perform a likely-irreversible migration.

You should not do this. It removes almost all of the benefits of extracting things into a separate service (services should own their data and the only means of accessing it should be via their APIs). That's not utopian; that's one of the main reasons you do a service extraction in the first place.

Don't choose general data access patterns for the infrequent occurrence of cutover. Cutover is when you break a few rules and then immediately stop doing so. Build for everyday access patterns instead (which should be through the API of whatever owns the data--SQL is a powerful language and a really shitty API).

- The existence of tools that allow structured access to multiple APIs (GraphQL is a nice middle ground between "YOLO any queries you want" and "you only get row-by-row access exposed by the web APIs").

- The existence of data on multiple internal data stores. Analytics folks usually are not prepared to engage with the complexity of data being stored across handfuls or more of different stores with different schemas. The owner of the application knows how to join that stuff better than they do.

- Building intermediate/denormalized stores isn't frowned upon just because analytics shouldn't run ad hoc queries on the main production DBs. Expose change streams or bulk ("too much" data) endpoints and make it easy to load their results into a reporting system, which can be raw SQL. It's not redundant; if you don't do this, the following conversation starts to happen often: Q: "I'm running raw analytics queries on production and it's not quite working, can we just make $substantial_schema_change so my report works/is fast?" A: "No, we explicitly chose not to structure the DB/index/whatever like that because it seriously fucks up a real user access pattern."

They only address it in so far as they push it downstream to the analyst, who as you mentioned, "is likely not better versed in what means what than the developers who work on the application databases."

There's a reason why datalakes exist, and having used them at past N companies, I think this is why data engineering of the BI flavor becomes necessary at the point that reporting becomes critical. An API is strictly worse than a datalake, and it's not hard to set up and maintain the latter. API versioning and communication are for frontend integrations, paid partner integrations and potentially (although I'd probably lean more on gRPC and the ilk) microservice to microservice interactions. But, I like to avoid building unnecessary API surface when I can.

[1] https://www.hugsql.org/

If you need to run a certain query in multiple places, you need to repeat the same ORM expression or refractor it into a function. I find much better to have a module with all my SQL queries as strings. That way whenever I need to run a query I reference it from there. Of course it helps to use meaningful names.

This approach has a lot of advantages over ORMs: * you know exactly what gets executed * automatic DRY code * the names of the SQL queries in the code are self-explanatory and the reader doesn't have to parse the ORM expression every time

Schema definitions are in standalone SQL files, as well as my migrations.

The only disadvantage is that it may be difficult to switch to a different database system, but that is not a problem for us.

Composing SQL expressions using this library instead of using string interpolation/concatenation has several advantages:

* DRY and composition * safety * portability (if you have switch the underlying DBMS)

Often the result is as good or better than my raw SQL. The fact that Python has an amazing REPL makes the process pretty much like testing queries in the database prompt but with less cognitive switch between languages.

In the end it is a matter of taste, but I have to agree with parent posts, SQLAlchemy raises the bar for other ORMs.

But you can't compose them, so there is a lot of duplication. Also, how would you handle dynamic filters and columns? Concatenating strings? That seems error prone. At least a nice query builder would be useful, but then the whole just write sql thing falls apart.

So maybe building a minimal API, wrapping your SQL queries isn't such a bad idea after all.

[1] https://github.com/craigmichaelmartin/pure-orm

Here's your problem

http://slick.lightbend.com/doc/3.2.0/orm-to-slick.html

You throw in SQL, provide a simple mapper, done. IMHO it's far superior to ORMs when your database is or may become complicated.

My rule of thumb is that I always go with ORMs for MVPs and small apps. Optimizing for speed usually means going deeper and building a system or queries for yourself. Until that point I usually stick to less verbose code and more to business rules.

Maybe you should not do that? Can you give us an idea of the domain problem you were trying to solve that made you feel the need for that?