https://gajus.medium.com/stop-using-knex-js-and-earn-30-bf41...

https://github.com/gajus/slonik

[0]: https://github.com/felixfbecker/node-sql-template-strings

[1]: https://github.com/moll/js-sqlate

While I think this is totally the way to go, it is kind of amusing that PHP's PDO system was the "right" way to go all along in some ways

  sql`SELECT id FROM foo WHERE bar = ${barValue}`

1. https://developer.mozilla.org/en-US/docs/Web/JavaScript/Refe...

I must be misunderstanding I guess.

Migrations allow a development team to synchronize database schema changes across multiple branches of development.

It is the main reason I end up using tools like knex.js (node) and sqlalchemy (python).

As far as knex is concerned, you can use it to manage migrations without using it in your application code.

FWIW, I do use knex for migrations, but I hate the SQL builder part of it, so most of my migrations are littered with knex.raw statements.

This was discussed at length in this slonik issue, https://github.com/gajus/slonik/issues/42 , with some recommendations for migrations functionality.

``` ALTER TABLE IF EXISTS ADD COLUMN IF NOT EXISTS foo varchar; CREATE TABLE IF NOT EXISTS sample ( -- all the fields including foo ); ```

Run it on a clean DB, you get the table. Run it on an existing DB, it adds the column. Run it on a current DB, it does nothing. Run it once or a hundred times, the DB ends up in the same target state.

Can only be used on some databases though. The DBs without transactional DDL are a risk. Then again, they were a risk no matter what method you use. (I'm looking at you, MySQL.)

https://github.com/porsager/postgres#readme

You are correct. But note that pgtyped is really only able to generate types because it actually needs to run your queries against a running instance of your DB. Some contributors have commented they are working on something similar for Slonik, https://github.com/gajus/slonik/pull/267#issuecomment-840559...

But in reality, those abstractions will be leaky, and you'll be spending significant time trying to understand what the ORM is doing, and finding workarounds for ORM problems.

Also, each time something fails, there will be one more moving part to troubleshoot.

Once you troubleshoot your problems and analyze what the ORM is doing, you'll realize that the price you paid for convenience is very high: you have sold your future in exchange for some "convenience" that actually gives you more work to do, makes everything slower and you didn't even need.

Why? Because most ORMs try to target every database, so they offer a feature set that is the lowest common denominator for all the databases they support. Does that sound like a good idea? no.

off topic, but I recently learn that this phrase dates back to the Bhagavad Gita!

    If the radiance of a thousand suns
    Were to burst at once into the sky
    That would be like the splendour of the Mighty One

Confusingly [1] links to the passage in a different (1909-14) translation [2] which features the 'of a thousand suns' part but is otherwise different.

I just wonder, for example if the original was something like लक्षाणाम् सूर्याणाम् 'lakshāñām sūryāñām' (I think that's what I mean, I'm learning Hindi not Sanskrit! Just looking up लाख & सूरज roots.) but translated to 'thousand' on the basis that it just meant 'a lot', and that sounded better in English and was perhaps even already a phrase.

I don't mean to doubt you, just thought it was interesting, and translated texts are a bit of a can of worms.

[0] - https://books.google.com/ngrams/graph?content=of+a+thousand+...

[1] - https://www.bartleby.com/73/123.html

[2] - https://www.bartleby.com/45/4/11.html#44

It involves writing SQL inside of XML files and doing a lot of manual mapping from columns back to the objects.

At first I REALLY disliked it but over time I have grown to absolutely love being able to just write SQL queries that can return whatever. Writing raw SQL really lets you optimize and create complex queries when you need to in a way that you can't with an ORM.

But how do you deal with refactoring? If I want to change a column on a db table or a property in a Java/C# class can the compiler tell me where I forgot to update without much ceremony?

This is a problem I had in the past on a large project. The result is that we devs started to get afraid of changing schema or code that touched database because that could create runtime errors as opposed to compilation errors. Being afraid to refactor is really bad and piles up quickly as technical debt.

Some ORMs solve this by scanning the database schema and creating a class per table and a field/property per column. This way we get compilation errors if database doesn't match code and have the opportunity to investigate it before it blows on the customer screen as a runtime error.

Yes, this definitely happens! Like somebody else said, tests can cover a lot of this. But of course they aren't perfect.

Where I'm at right now, we have multiple microservices reading from the same database so schema changes are a terrifying nightmare ("which service will break when we remove this column?!"). Unused columns have piled up over the years; in our `users` table we have almost a dozen unused columns. Onboarding new folks is always fun; "No, you don't want to look at the `role` column, you want to look at `role_id`!" Note that this issue would still be a problem if we were using an ORM (unless we had a shared library between all microservices that are talking to the database )

I've used ORMs extensively before and they do make refactoring a little bit easier, but I still would rather write raw SQL. Basically personal preference at this point; if I were to join somewhere that was using an ORM I would learn it and probably be happy with it!

Like, all from the same base tables or each with their own schema with service-specific views? Also, isn't shared-DB integration by-definition incompatible with “microservices”, which are isolated and sovereign over their own data?

I didn't say it was good architecture. It's a remnant of when we were a startup in "move fast break things" mode (we have since been acquired) and as an org paying down tech debt has never been a priority so we're left with a lot of bad decisions that we have to live with (to the point where it's a miracle when we actually ship any meaningful features...)

We solved this problem by having thorough automated test coverage for all SQL queries.

Here's basic lifecycle of a test: 1) Create the test database 2) Migrate the database to the latest schema version 3) Insert test data 4) Execute the SQL query 5) Validate the results of the query

1) We now need 100% test coverage for queries. Forget one of them and the castle might fall on the client side. This can be mitigated with gatekeepers on CI/CD pipeline to ensure 100% coverage. But it's still quite the effort.

2) Our feedback loop for developing refactoring is now slower now since we need to run all tests to see what broke after a schema change. In contrast to an IDE giving compilation errors.

3) If we build SQL queries by composing them, which is common pattern for business rule validations, there will be many branches to be tested.

4) We won't have auto-completion of table and column names in the IDE. And if you mistype them your feedback is slower.

I say that as someone who went all in SQL query builders and composability in a project but when it grew large we were still afraid of changing schema despite having a very high test coverage.

1) Experience. I've seen so many terrible performance issues because of ORM-centric programming. Talking pages that take tens of seconds or a couple minutes(!) to load when they should take under 5s.

2) Ditto on the "ORMs apparently encourage people to write terrible schemas" observation.

3) "Database-agnostic" (not strictly an ORM thing and not required for an ORM, but strongly associated with ORMs) is a bad idea at least 90% of the time (I suspect more like 99%) it's applied. I've seen codebases replaced atop databases several times. I've yet to see a database replaced on an actual, live product. Moreover, your "database-agnostic" code means it's absolute hell to write any code that touches the DB that doesn't use your main codebase as an intermediary, especially if it's written in another language. That's crippling for your liberty to Move Fast and leverage the data you have, if you're thinking in terms of the business and a product suite rather than a single software product. Use your database. Let it do the work. Your customers will thank you for faster feature delivery ("Oh no, we can't use that feature in this database that immediately and perfectly solves this problem, because that would lock us in to it"), better response times, and lower chances of data loss or corruption.

4) Object-per-table isn't technically the only way to operate in ORMs, but boy is it sure treated that way in-the-wild, more often than not. You want your DB schema and your object hierarchy to be nonsense? Have I got the technology for you!

5) For the tedious cases where you do actually just want to map a select to an object and then write "row.save()" or whatever instead of a SQL statement... that's so easy to write. For the single-table cases it's trivial to write something highly re-usable, even. There, now you have much of the day-to-day benefit of an ORM with 1% of the fat and tech debt.

And re: TypeORM, in particular—I entirely do not get the appeal. It's like some kind of obfuscation engine for both SQL and the intent of the code itself, in a way no other ORM I've seen is. What the hell.

Some databases are so far behind, they really shouldn't be abstracted by the same library. They have VERY different use cases and abilities. The access model for a SQLite-based app is quite different from a Postgres-based app.

It's like having an F-150, a Cybertruck, and a Prius while hiring a driver that won't take either off-road or for more than 100 miles at a time because he also has to be able to drive a Nissan Leaf the exact same way.

But folks still hire him because he claims to handle anything with a steering wheel and pedals. Technically true, but misses the point of the different options.

That's an ORM.

And once you have the data from the SQL database are you keeping it just in arrays/dictionaries? Or should it at least be mapped to a class structure?

As someone dealing with a bespoke SQL schema and mix of in-house SQL translation layers for different DBs, wrapper functions... I would think any ORM would be better designed since it has a singular purpose and database experts work on their development.

Not sure the parent comment would like it, but there's a middle ground between ORM and raw SQL that I consider a sweet spot. It's more of a "query builder" library that gives you language-appropriate constructs for building any SQL you like, but also provides more correctness guarantees than just writing raw SQL strings.

SQLAlchemy's "expression" layer, for example, does this really nicely. It existed long before the higher "ORM" layer came along, and can be used without having to touch the ORM.

In Go, I like the Goqu library for the same purpose.

In Rails land, my understanding is that the underlying Arel layer is more like this pattern, as opposed to the higher level Active Record ORM.

In Java, it's jOOQ instead of Hibernate.

It sounds like you're pretty set on that opinion, and that's fine, but I suspect you just haven't run into the case where the raw SQL is far messier than using a query builder.

SQL strings are pretty inflexible. The big advantage of a query builder (note that I'm not saying "ORM") is that you can start with a simple base case and dynamically mutate the query to add clauses specific to the request you're handling. Maybe one user wants 10 results per page and another user wants 25, for example.

I have an example of how far you can take this at http://btubbs.com/postgres-search-with-facets-and-location-a.... I could not endorse trying to build the query interface in that post with raw SQL.

I don’t consider it an ORM in the classical sense. I see it more as a query builder.

Bear in mind I've just used it for personal side projects, nothing too critical.

I recommend you give it a try and form your own opinion.

Feel free to get in touch!

Modern SQL has functions that can be used to map rows into json objects and arrays. That is what I use in nodejs/postgres. Everything is returned in the structure I want it in. The node driver turns the json into javascript arrays and objects (which then get turned back into JSON to send to the client, hah!). I added some code to the driver so that snake case field names are converted to camel case.

Approach where you use pure SQL for reads and ORM for saves is not that uncommon

I used EF Core and Dapper for like 3 years and ORMs boost productivity significantly.

I tend to check what SQL is generated and if something is complicated and generated SQL sucks, then I use micro ORM like Dapper and use raw sql.

I agree that ORMs aren't easy because you have to learn them, but I think it's worth, unless you have to learn many different ORMs.

  @SqlQuery("SELECT * FROM users WHERE id = :id")
  User find(String id);

So sure, you can write this out as multiple simple queries, but do you really want to code the repeated query for every case of: Foos.recent.for_user, Foos.active.for_user, Foos.recent.by_something, Foos.recent.active.by_something? And what if you don't want a whole object in that case, but only one column? ActiveRecord for example has you covered with `.pluck(:single_column)` that you can append at the end without writing yet another full query.

Writing the simple stuff every time, you get footguns simply by repeating trivial code - that leads to copy-paste and forgot-to-change-one-of-them mistakes.

If you're using it to ad-hoc query your db then it's understandable you'll hate it - a leaky and poor abstraction over sql. Probably a bad fit.

Projects where I've seen it work well is when most of the logic is in the app with per row/per aggregate changes. In these apps it's only used for the "object relational mapping" side of things - ie to marshal types to/from db rows.

I've never found auto-migrations in ORMs good for anything less than a 1 day project - it's a world of hurt.

Just to offer a counterpoint.

Every project I worked on that did not have automatic migrations was extremely flawed in other ways as well.

Manually keeping track of your DB schema, and indeed, seeing it as something separate from the code that needs to interact with it is a bad idea in my opinion.

It’s same as ‘infrastructure as code’, database also needs to be ‘database as code’.

Most folks test their up scripts. They almost NEVER adequately test their down scripts, so you're left with this false sense of security moving forward even though revision 63 of 64 has a bug in the down portion…which you only find when you're trying to revert to the state of rev 60.

As for ORM migrations, of course they work. They've dumbed down your use of the database to the lowest common denominator (looking at you, MySQL).

IF EXISTS and IF NOT EXISTS are your good friend. Run the script, the database will be at the target state. When all databases are past a certain point, remove the appropriate ALTER TABLE IF EXISTS ADD COLUMN statements.

Makes source diffing much easier, is a consistent single source of truth, and allows for pruning old parts as needed.

There's another one called (I think) Zapatos that has some similar qualities.

Rails ActiveRecord, for example, handles ALL your mentioned scenarios (includes, joins, select, Transaction.do, update_all - and you can still run SQL queries or fragments thereof).

Theres a lot more in the docs than you will see in "toy examples".

Of course, not all ORMs are created equally...

I think my preferred style (vs. full ORM or raw SQL) is that of Diesel (which is incidentally from a (former?) maintainer of ActiveRecord, Sean Griffin, though I think he may have since stepped back from it) for Rust - it's more like 'SQL bindings' than 'ORM's typically are or grow to be; so you pretty much write SQL, just in Rust syntax with type checking etc. instead of actual SQL in one big Rust string.

Table models are hugely useful. "Business object" models, not as much.

* Write migrations first, so that you define your own schemas

* Disable TypeORM synchronize, make your migrations the source of truth instead of the entities

* Model your entities after your schema

However, your reasons for disliking ORMs seems a little like hearsay. I am interested in seeing how the responses to this post will be, although I’m afraid that this might turn into a flame-war. I wonder how often the pro-ORM flame wars the anti-ORM camp here on HN.