6 ms·

All these things fall short the moment you need real "production" features, such as reliable migrations (writing them by hand? no thanks. Outsourcing to another

by midrus 6y ago

All these things fall short the moment you need real "production" features, such as reliable migrations (writing them by hand? no thanks. Outsourcing to another library like knex? no thanks), transactions, community support, relationship/nested/join queries without a ton of boilerplate and being battle tested. So far, the best thing I've found in the node ecosystem is Prisma [1], and it's better than the alternatives by a very long shot in my opinion. [1] https://www.prisma.io/docs/understand-prisma/why-prisma https://www.prisma.io/docs/understand-prisma/why-prisma

bradstewart 6y ago
This. I've actually used Rails/ActiveRecord migrations in Node projects. More than once...
Seb-C 6y ago
> reliable migrations (writing them by hand? no thanks. Outsourcing to another library like knex? no thanks) IMHO, "real production features" means also caring about the long term maintenance, performance and reliability of the data. My experience with ORMs (especially ActiveRecord patterns) is that it always becomes a mess once you reach a certain level of complexity. It is very fast to get started, but gets slower over time once you start having a lot of bugs and hard-to-solve behaviours.
- bakedbeanz 6y ago
  Interesting, then what is your alternative? It seems like hand-rolling all of your migrations would be an enormous pain on larger projects.
  - Seb-C 6y ago
    Well, this project is my alternative, and so far I did not see any case where it fails, but I'd be happy to learn. This migration pattern is quite common in many frameworks, I just removed the query-building abstraction layer.
nicoburns 6y ago
I actually much prefer writing migrations by hand. It's actually pretty simple, and it gives you complete control over the schema.
- midrus 6y ago
  Agree, it's pretty simple writing them. The problem is deciding which ones to apply for a given version of your application, when, running them automatically on each of your developer's laptops, your testing environment, and automate and keep track of all the changes. Or are we talking of just writing a "CREATE TABLE..." and hand it over the wall to the ops? I agree that's simpler. Not something I like to do these days.
  - dgellow 6y ago
    1. create a "schema_version" table 2. have a unique, incremental migration ID in each migration filename 3. apply them one by one in order depending on the current schema_version from the target environment, and update the schema_version accordingly 4. rollback the transaction in case of error, otherwise commit and enjoy your updated schema I used golang-migrate/migrate in production for the past 3 years to do exactly this but you can easily implement it yourself too. golang-migrate/migrate can be used either as a Go library or as a CLI tool. https://github.com/golang-migrate/migrate https://github.com/golang-migrate/migrate Or did I misunderstand the issues you're talking about?
    - peburrows 6y ago
      So... do pretty much exactly what ORMs with migration support already do for you?
      - nicoburns 6y ago
        Yes, but write the DDL by hand rather than having it auto-generated.
      - dgellow 6y ago
string 6y ago
I love Prisma and am a huge fan of Nexus[1] in particular. But it's interesting that you mention transactions as Prisma does not yet support long running transactions[2]. I have a side project that I've been working on for a little while, but I haven't touched it in months as I'm waiting on LRT support. Prisma is being very actively developed though as far as I can see, so I'm confident that there will be a solution for this eventually. [1] https://nexusjs.org/ https://nexusjs.org/ [2] https://github.com/prisma/prisma/issues/1844 https://github.com/prisma/prisma/issues/1844
- midrus 6y ago
  Because to me, long running transactions are not something that should be done so lightly. A transaction is expensive for the database, so interpolating transactions with requests to external systems, other databases, etc is a bad idea from the get go to me. Just write down what you know at the moment, and update when you have the result, or use a small CQRS implementation for that specific use case. It is more reliable and scalable.
  - string 6y ago
    I'm no expert when it comes to transactions or databases in general, so excuse my ignorance, but it seems to me an API to perform extremely simple transactions like the one outlined in that issue (no external systems or other databases involved) would be a very basic requirement for many projects? Been "hanging out" in the Prisma discord and reading through issues for a while and it comes up quite a lot. It doesn't seem anyone has come up with a simple/viable workaround so far.
exevp 6y ago
I find that most of the production features you mentioned are actually more difficult using a fat ORM. How many hours have i wasted figuring out how i can write and map some complex joins or aggregation query with <insert ORM name here>? Would have been a 3 minute task if all i had to write was just SQL ... Plus i have a hard time seeing the benefit of Prisma. You are learning an entirely new DSL just to define your schema - which actually isn't that far off standard JS or TS syntax-wise so it feels like a complete waste of time to come up with the DSL in the first place. I can only imagine the hard time you have once you first have to break out of the frameworks cage because you hit a case which isn't easily solved by the framework itself ...
- midrus 6y ago
  >I find that most of the production features you mentioned are actually more difficult using a fat ORM. Prisma doesn't look like the traditional ORM to me. In fact, the library from the OP uses classes, which prisma and Knex do not use > How many hours have i wasted figuring out how i can write and map some complex joins or aggregation query with <insert ORM name here>? Would have been a 3 minute task if all i had to write was just SQL In my experience this is just a very, very small percent of the cases. Most of the time I find myself doing pretty simple CRUD operations, and the boilerplate for the simple cases goes out of hand quickly, specially when running joins.
  - exevp 6y ago
    > In my experience this is just a very, very small percent of the cases. Most of the time I find myself doing pretty simple CRUD operations, and the boilerplate for the simple cases goes out of hand quickly, specially when running joins. That's true, although the most frustrating percent of the cases :) I think what KISS will eventually need is some simple toolbelt for the basic CRUD queries, e.g. expanding lists of column names, dealing with casing (snake_case to camelCase). So making the stuff you write 90% of the times easy without inventing a custom SQL abstraction. MyBatis (which i mentioned in another comment) approached this for example with sql snippets one could re-use in queries. KISS enables the same thing by allowing nesting of tagged sql strings. I guess time will show which additional helpers are needed to make the devs life easy/reduce the boilerplate for the simple queries.
dathinab 6y ago
> reliable migrations My experience is that libraries which do handle that for you automatically do a really bad job at it. Writing it by hand and most important thoroughly testing it and trying to avoid doing any changes needing complex migrations is in my experience more reliable on the long run. EDIT: removed inappropriate caps usage
- midrus 6y ago
  Tell that to the Django ORM. I've used it in really big projects, for really big companies and it never failed on us. As I said in another thread, the problem is not writing the SQL. The problem is everything else after you have written it (applying on deploy, automating for other's dev envs, rollback, etc)
  - rubber_duck 6y ago
    >As I said in another thread, the problem is not writing the SQL. The problem is everything else after you have written it (applying on deploy, automating for other's dev envs, rollback, etc) What's wrong with migration managers ? They handle all you mentioned just fine. The only problem I've seen with this approach were when working on a team that didn't have proper CI flow, then it was possible for people to check in bad migrations and screw up branches, etc. I've worked with both approaches in multiple languages, and frankly I prefer the no-magic migration manager approach. Migrations automatically generated from model also mean your DB is tied to your app, which I'm not a fan of, if anything the reverse should be true, and the fat model approaches of rails are terrible IMO (I haven't used Django for anything serious in over 10 years so I don't remember how fat their model layer is - I know it has enough metadata to generate the admin CRUD but don't remember if it also encourages having logic on models). The best solution I've seen is F# which uses dynamic type providers (basically compiler plugin) to auto-generate models from DB/schema at compile time, and yesql in clojure (clojure being dynamic and built arround working with data using SQL results inside of it is natural and amazing).
xixixao 6y ago
Looks nice, pretty close to what we use at FB, except we use the builder pattern, so you can compose the query more easily across function calls (instead of passing in a single bag of options to findMany).
- foepys 6y ago
  The builder pattern grew on me over the past few years and I think it's one of the best ways to do anything arbitrarily composable that is reasonably complex. .NET's LINQ can also be used as a builder and I really, really love it - be it as part of an ORM or just .NET in general.
deleted 6y ago
[deleted]
nerdkid93 6y ago
We investigated using Prisma v2 as a way of auto-creating a GraphQL API that directly interfaces with a PostgreSQL database, but we immediately pivoted to other solutions as soon as we discovered that the Prisma Client is really spinning up a behind-the-scenes GraphQL rust server itself to access the db. The performance of fetching a mildly complicated query (3 joins) ended up being more than three times slower than Hasura or PostGraphile. For the life of me, I couldn't figure out why it's desirable to have an intermediary GraphQL server issuing database requests. Is that something you have also discovered while using Prisma?
- varrock 6y ago
  > The performance of fetching a mildly complicated query (3 joins) ended up being more than three times slower than Hasura or PostGraphile. Please forgive my ignorance, but these type of conversations always intrigue me and make me want to learn more. As a software engineer, at what point are you running these performance benchmarks, and how are you doing them exactly? How have you had time to make these decisions and pivot to other technologies?
- vosper 6y ago
  > We investigated using Prisma v2 as a way of auto-creating a GraphQL API that directly interfaces with a PostgreSQL database, but we immediately pivoted to other solutions as soon as we discovered that the Prisma Client is really spinning up a behind-the-scenes GraphQL rust server itself to access the db. The performance of fetching a mildly complicated query (3 joins) ended up being more than three times slower than Hasura or PostGraphile. Do you know what Hasura is doing differently? I assumed it worked the same way as Prisma does, with an intermediate server.
  - nerdkid93 6y ago
    I'm not sure about PostGraphile, but Hasura I know transforms a graphql request into a SQL request that can return the JSON response expected as part of the GraphQL spec. I believe that's the reason that Hasura only works with PostgreSQL, since it has the necessary SQL functions to generate arbitrary JSON objects.

by midrus 6y ago

bradstewart 6y ago

This. I've actually used Rails/ActiveRecord migrations in Node projects. More than once...

Seb-C 6y ago

> reliable migrations (writing them by hand? no thanks. Outsourcing to another library like knex? no thanks) IMHO, "real production features" means also caring about the long term maintenance, performance and reliability of the data. My experience with ORMs (especially ActiveRecord patterns) is that it always becomes a mess once you reach a certain level of complexity. It is very fast to get started, but gets slower over time once you start having a lot of bugs and hard-to-solve behaviours.

bakedbeanz 6y ago
Interesting, then what is your alternative? It seems like hand-rolling all of your migrations would be an enormous pain on larger projects.
- Seb-C 6y ago
  Well, this project is my alternative, and so far I did not see any case where it fails, but I'd be happy to learn. This migration pattern is quite common in many frameworks, I just removed the query-building abstraction layer.

nicoburns 6y ago

I actually much prefer writing migrations by hand. It's actually pretty simple, and it gives you complete control over the schema.

midrus 6y ago
Agree, it's pretty simple writing them. The problem is deciding which ones to apply for a given version of your application, when, running them automatically on each of your developer's laptops, your testing environment, and automate and keep track of all the changes. Or are we talking of just writing a "CREATE TABLE..." and hand it over the wall to the ops? I agree that's simpler. Not something I like to do these days.
- dgellow 6y ago
  1. create a "schema_version" table 2. have a unique, incremental migration ID in each migration filename 3. apply them one by one in order depending on the current schema_version from the target environment, and update the schema_version accordingly 4. rollback the transaction in case of error, otherwise commit and enjoy your updated schema I used golang-migrate/migrate in production for the past 3 years to do exactly this but you can easily implement it yourself too. golang-migrate/migrate can be used either as a Go library or as a CLI tool. https://github.com/golang-migrate/migrate https://github.com/golang-migrate/migrate Or did I misunderstand the issues you're talking about?
  - peburrows 6y ago
    So... do pretty much exactly what ORMs with migration support already do for you?
    - nicoburns 6y ago
      Yes, but write the DDL by hand rather than having it auto-generated.
    - dgellow 6y ago

string 6y ago

I love Prisma and am a huge fan of Nexus[1] in particular. But it's interesting that you mention transactions as Prisma does not yet support long running transactions[2]. I have a side project that I've been working on for a little while, but I haven't touched it in months as I'm waiting on LRT support. Prisma is being very actively developed though as far as I can see, so I'm confident that there will be a solution for this eventually. [1] https://nexusjs.org/ https://nexusjs.org/ [2] https://github.com/prisma/prisma/issues/1844 https://github.com/prisma/prisma/issues/1844

midrus 6y ago
Because to me, long running transactions are not something that should be done so lightly. A transaction is expensive for the database, so interpolating transactions with requests to external systems, other databases, etc is a bad idea from the get go to me. Just write down what you know at the moment, and update when you have the result, or use a small CQRS implementation for that specific use case. It is more reliable and scalable.
- string 6y ago
  I'm no expert when it comes to transactions or databases in general, so excuse my ignorance, but it seems to me an API to perform extremely simple transactions like the one outlined in that issue (no external systems or other databases involved) would be a very basic requirement for many projects? Been "hanging out" in the Prisma discord and reading through issues for a while and it comes up quite a lot. It doesn't seem anyone has come up with a simple/viable workaround so far.

exevp 6y ago

I find that most of the production features you mentioned are actually more difficult using a fat ORM. How many hours have i wasted figuring out how i can write and map some complex joins or aggregation query with <insert ORM name here>? Would have been a 3 minute task if all i had to write was just SQL ... Plus i have a hard time seeing the benefit of Prisma. You are learning an entirely new DSL just to define your schema - which actually isn't that far off standard JS or TS syntax-wise so it feels like a complete waste of time to come up with the DSL in the first place. I can only imagine the hard time you have once you first have to break out of the frameworks cage because you hit a case which isn't easily solved by the framework itself ...

midrus 6y ago
>I find that most of the production features you mentioned are actually more difficult using a fat ORM. Prisma doesn't look like the traditional ORM to me. In fact, the library from the OP uses classes, which prisma and Knex do not use > How many hours have i wasted figuring out how i can write and map some complex joins or aggregation query with <insert ORM name here>? Would have been a 3 minute task if all i had to write was just SQL In my experience this is just a very, very small percent of the cases. Most of the time I find myself doing pretty simple CRUD operations, and the boilerplate for the simple cases goes out of hand quickly, specially when running joins.
- exevp 6y ago
  > In my experience this is just a very, very small percent of the cases. Most of the time I find myself doing pretty simple CRUD operations, and the boilerplate for the simple cases goes out of hand quickly, specially when running joins. That's true, although the most frustrating percent of the cases :) I think what KISS will eventually need is some simple toolbelt for the basic CRUD queries, e.g. expanding lists of column names, dealing with casing (snake_case to camelCase). So making the stuff you write 90% of the times easy without inventing a custom SQL abstraction. MyBatis (which i mentioned in another comment) approached this for example with sql snippets one could re-use in queries. KISS enables the same thing by allowing nesting of tagged sql strings. I guess time will show which additional helpers are needed to make the devs life easy/reduce the boilerplate for the simple queries.

dathinab 6y ago

> reliable migrations My experience is that libraries which do handle that for you automatically do a really bad job at it. Writing it by hand and most important thoroughly testing it and trying to avoid doing any changes needing complex migrations is in my experience more reliable on the long run. EDIT: removed inappropriate caps usage

midrus 6y ago
Tell that to the Django ORM. I've used it in really big projects, for really big companies and it never failed on us. As I said in another thread, the problem is not writing the SQL. The problem is everything else after you have written it (applying on deploy, automating for other's dev envs, rollback, etc)
- rubber_duck 6y ago
  >As I said in another thread, the problem is not writing the SQL. The problem is everything else after you have written it (applying on deploy, automating for other's dev envs, rollback, etc) What's wrong with migration managers ? They handle all you mentioned just fine. The only problem I've seen with this approach were when working on a team that didn't have proper CI flow, then it was possible for people to check in bad migrations and screw up branches, etc. I've worked with both approaches in multiple languages, and frankly I prefer the no-magic migration manager approach. Migrations automatically generated from model also mean your DB is tied to your app, which I'm not a fan of, if anything the reverse should be true, and the fat model approaches of rails are terrible IMO (I haven't used Django for anything serious in over 10 years so I don't remember how fat their model layer is - I know it has enough metadata to generate the admin CRUD but don't remember if it also encourages having logic on models). The best solution I've seen is F# which uses dynamic type providers (basically compiler plugin) to auto-generate models from DB/schema at compile time, and yesql in clojure (clojure being dynamic and built arround working with data using SQL results inside of it is natural and amazing).

xixixao 6y ago

Looks nice, pretty close to what we use at FB, except we use the builder pattern, so you can compose the query more easily across function calls (instead of passing in a single bag of options to findMany).

foepys 6y ago
The builder pattern grew on me over the past few years and I think it's one of the best ways to do anything arbitrarily composable that is reasonably complex. .NET's LINQ can also be used as a builder and I really, really love it - be it as part of an ORM or just .NET in general.

deleted 6y ago

[deleted]

nerdkid93 6y ago

We investigated using Prisma v2 as a way of auto-creating a GraphQL API that directly interfaces with a PostgreSQL database, but we immediately pivoted to other solutions as soon as we discovered that the Prisma Client is really spinning up a behind-the-scenes GraphQL rust server itself to access the db. The performance of fetching a mildly complicated query (3 joins) ended up being more than three times slower than Hasura or PostGraphile. For the life of me, I couldn't figure out why it's desirable to have an intermediary GraphQL server issuing database requests. Is that something you have also discovered while using Prisma?

varrock 6y ago
> The performance of fetching a mildly complicated query (3 joins) ended up being more than three times slower than Hasura or PostGraphile. Please forgive my ignorance, but these type of conversations always intrigue me and make me want to learn more. As a software engineer, at what point are you running these performance benchmarks, and how are you doing them exactly? How have you had time to make these decisions and pivot to other technologies?
vosper 6y ago
> We investigated using Prisma v2 as a way of auto-creating a GraphQL API that directly interfaces with a PostgreSQL database, but we immediately pivoted to other solutions as soon as we discovered that the Prisma Client is really spinning up a behind-the-scenes GraphQL rust server itself to access the db. The performance of fetching a mildly complicated query (3 joins) ended up being more than three times slower than Hasura or PostGraphile. Do you know what Hasura is doing differently? I assumed it worked the same way as Prisma does, with an intermediate server.
- nerdkid93 6y ago
  I'm not sure about PostGraphile, but Hasura I know transforms a graphql request into a SQL request that can return the JSON response expected as part of the GraphQL spec. I believe that's the reason that Hasura only works with PostgreSQL, since it has the necessary SQL functions to generate arbitrary JSON objects.