[HN Gopher] Prisma - ORM for Node.js and TypeScript
___________________________________________________________________
Prisma - ORM for Node.js and TypeScript
Author : Sujan
Score : 372 points
Date : 2021-04-21 10:40 UTC (12 hours ago)
(HTM) web link (www.prisma.io)
(TXT) w3m dump (www.prisma.io)
| pistoriusp wrote:
| We've been using Prisma in RedwoodJS since the early days. It's a
| great ORM that they're constantly improving, I'm super excited
| for the future!
| dang wrote:
| Past related threads:
|
| _Prisma Raises $12M Series A_ -
| https://news.ycombinator.com/item?id=23651605 - June 2020 (111
| comments)
|
| _Prisma 2.0 - Type-safe and auto-generated database client_ -
| https://news.ycombinator.com/item?id=23466834 - June 2020 (107
| comments)
|
| _Prisma 2.0 Beta: Type-safe Database Access_ -
| https://news.ycombinator.com/item?id=22739121 - March 2020 (122
| comments)
|
| _Comparing Database Types_ -
| https://news.ycombinator.com/item?id=21060866 - Sept 2019 (170
| comments)
|
| _Prisma - Database tools for modern application development_ -
| https://news.ycombinator.com/item?id=19598287 - April 2019 (83
| comments)
|
| _Show HN: Prisma - Turn Any Database into a GraphQL API_ -
| https://news.ycombinator.com/item?id=17076202 - May 2018 (16
| comments)
|
| _Prisma Cloud - A GraphQL Database Platform_ -
| https://news.ycombinator.com/item?id=16529450 - March 2018 (9
| comments)
|
| _Prisma - An open-source GraphQL API layer for your database_ -
| https://news.ycombinator.com/item?id=16160803 - Jan 2018 (34
| comments)
|
| _Prisma: Turn your database into a real time GraphQL API_ -
| https://news.ycombinator.com/item?id=16159874 - Jan 2018 (9
| comments)
| deepstack wrote:
| Hmm, how does this compare to sequelize? Seems like the only
| reason to use prisma is the GraphQL integration. If anyone has
| used both please share. I've being mainly using sequelize and
| have being quite happy with it.
| nikolasburk wrote:
| Hey, Nikolas from Prisma here!
|
| Can you elaborate what exactly you mean with "GraphQL
| integration"? You might be referring to Prisma 1 which was a
| "GraphQL layer for your database". Prisma 2 (which we are
| referring to with this article) doesn't have a native GraphQL
| integration any more :)
|
| Prisma is very different from Sequelize in various ways (see
| our docs [1]):
|
| - Your data model lives in the Prisma schema
|
| - Database access doesn't happen via model instances but via
| the Prisma Client API [2] which always returns plain JS objects
| making your queries much easier to reason about - Prisma
| Migrate auto-generates migrations based on your data model (and
| lets you customize these migrations when needed)
|
| - Prisma comes with Prisma Studio out of the box - a modern
| database GUI
|
| - Prisma is not a community-project but built by a VC-funded
| company
|
| - We care a lot about our community, documentation, and
| providing support (something we've seen lots of developers
| complain about with other ORMs)
|
| [1]
| https://www.prisma.io/docs/concepts/more/comparisons/prisma-...
|
| [2] https://www.prisma.io/client
| deepstack wrote:
| >Prisma is not a community-project but built by a VC-funded
| company
|
| Thanks for clarifying this. I know there tend to be a
| kneejerk reaction towards VC-funded software projects. How do
| you guys plan to make money on this?
| nikolasburk wrote:
| We've explained this in the blog post here:
| https://www.prisma.io/blog/prisma-the-complete-orm-
| inw24qjea...
|
| I think this quote summarizes our plans nicely: _Prisma 's
| vision is to democratize the custom data access layer used
| by companies like Facebook, Twitter and Airbnb and make it
| available to development teams and organizations of all
| sizes._
|
| The open-source ORM we're launching today will of course
| remain open-source and we'll keep investing into it since
| it's be the foundation for the commercial tools that folks
| will be able to use on top.
| goliatone wrote:
| I've read the post you linked and I still don't know how
| you plan to make money. Also, the blurb you copy pasted
| it tells me nothing in a clear way.
| gmac wrote:
| I've found this a very helpful summary of TypeScript SQL
| libraries: https://phiresky.github.io/blog/2020/sql-libs-for-
| typescript...
| codeulike wrote:
| New idiom - "Well that was a complete ORM"
|
| (sorry just a joke, ORMs do have their place and this looks
| useful. I'd think of Prisma as a lightweight ORM because it
| doesn't try to do "Business Rules" as they are known. But then,
| trying to do Business Rules will generally turn everything into a
| nightmare anyway so maybe lightweight ORM is the way to go)
| mst wrote:
| Even as an ORM author this made me laugh.
|
| ORMs are useful. ORMs aren't always the right answer. ORMs that
| don't let you mix and match so you can bypass any given layer
| of abstraction as required annoy the pants off me ... usually
| even more than anti-ORM zealouts annoy the pants off me.
|
| So, yeah, joke appreciated :D
| thatwasunusual wrote:
| A few months ago I spent a week testing all of the available ORMs
| for JS/TS, and Prisma came out on top. That said, Prisma isn't
| exceptionally good in any way, it's just not as bad as the
| alternatives; Mikro-ORM, TypeORM, Sequelize etc.
| subsection1h wrote:
| My team experimented with Prisma, and we ended up deciding to
| continue using raw SQL. Each of us has 10-20 years of experience
| writing SQL for PostgreSQL, so it wasn't a surprise that we
| decided against using an ORM yet again. But Prisma is probably
| the best ORM for Node.js that we've tried.
|
| For anyone who uses PostgreSQL and is interested in Prisma, check
| out the following message, which has a section that includes a
| list of features that were deemed out-of-scope (e.g., bulk
| upserts):
|
| https://github.com/prisma/prisma/issues/4998#issuecomment-76...
| RivieraKid wrote:
| How does it compare to MikroORM? I was researching different ORM
| options and almost wanted to give up until I discovered MikroORM,
| which is simply great.
|
| Edit: After quickly going through the intro, I prefer MikroORM,
| it's simpler.
| cjpb wrote:
| I've tried Prisma, TypeORM and MikroORM and I'm certainly the
| most impressed with MikroORM.
|
| It's simple and the use of identity map within a unit of work,
| tied to a request context (for example), is just brilliant.
|
| The handling of relations (with configurable loading
| strategies) and other features such as Embeddables and Filters
| make it a real joy to use.
|
| And if at any point I can't achieve something with MikroORM
| itself (which generally in my case is calling a PostgreSQL
| function), I can easily grab the underlying Query Builder from
| Knex.
|
| It also has the tooling to generate a schema from your
| Entities, or generate Entities from an existing schema - so
| it's easy to get started on something new or existing.
| [deleted]
| oso2k wrote:
| Maybe it's too late to change the name. When Palo Alto Networks
| acquired Twistlock (Container Scanning/Security products) 2 years
| ago [0], they folded it in & renamed it to Prisma Cloud [1].
|
| Food for thought if prisma.io team is watching.
|
| [0] https://www.paloaltonetworks.com/company/press/2019/palo-
| alt...
|
| [1] https://blog.paloaltonetworks.com/2019/11/cloud-prisma-
| cloud...
| htkien wrote:
| I am over ORMs. I am looking at the documentation for Prisma
| Client at the moment, and I just think to myself "yet another ORM
| I have to learn". I have learnt so many ORMs, it is just stupid.
| SQL is SQL. I wish I had spent all that time further developing
| my SQL skills rather than learning the latest ORMs API.
|
| For my latest large project, I just used SQL and I am happy. All
| I did was add some code to make sure the snake case was converted
| to camel case. Things like returning arrays of child relations
| can all be done in SQL these days with things like json_agg and
| json_build_object and so on.
|
| Half of my SQL on the other hand doesn't even map to an ORM.
| Where is the insert ... select ... where not exists ... These
| things aren't even possible in the ORM concept model.
| zackify wrote:
| It took me all of 1 hour to learn how to use prisma. It's not
| as hard as you make it seem.
|
| They have a vs code extension for editing the prisma schema.
|
| It's not hard to see that you make fields "String" @unique,
| etc.
|
| Just make one model and that's 80% of anything you ever need.
|
| Want to make a relation? Make a new model
|
| model Posts { users User[] }
|
| Hit save, it adds the required code to relate it to the User
| model.
|
| Run the generate command. Make a client instance.
|
| Create something: db.NameOfModel.create()
|
| And as you're typing, typescript is auto completing it.
|
| There's barely anything else to learn. And it's a quick doc
| search away.
|
| Don't hate on it because you love sql, it's a great tool.
| htkien wrote:
| >It took me all of 1 hour to learn how to use prisma.
|
| That is not how this works at all. You don't know what you
| don't know until you need "how do I do this query in prisma".
| You then spend an hour trying to figure it out, and maybe it
| isn't possible, then on to the next thing. It takes more than
| an hour to learn SQL, so if it takes you an hour to learn
| Prisma, you are missing something.
| joshmanders wrote:
| Have you even looked at Prisma's docs?
|
| I would rather forget everything about Prisma the second I
| wake up in the morning and have to read their docs on how
| to do everything all over again, every single day, than try
| to understand what the heck is going on in SQL's docs.
| zackify wrote:
| Yeah exactly.
|
| Most queries you can figure out just from typescript auto
| complete too...
|
| Majority of queries in an app are basic relationships and
| crud actions... doesn't take endless hours figuring this
| out with prisma.
|
| If it did, we wouldn't use it.
| zackify wrote:
| I've had to write complicated queries with prisma.
|
| In fact. One that prisma didn't support.
|
| Ordering by the count of a relationship, and returning the
| few highest counts.
|
| So I added a GitHub issue that is being tracked.
|
| And what did I do for now?
|
| I wrote the SQL using prisma' raw query function.
|
| You can learn SQL and use an ORM.
|
| Things don't always have to be one vs the other
| qudat wrote:
| > It took me all of 1 hour to learn how to use prisma. It's
| not as hard as you make it seem.
|
| That's because the ORM cannot do a lot. Unless you're doing
| extremely simple SQL queries it will not handle what you want
| it to do. Now what? Drop into RAW sql and lose all type
| safety and you are on your own.
|
| No thanks, I started using Prisma for https://listifi.app and
| switched it over to knex.js because the second I hit a query
| that Prisma couldn't handle I was on my own. Knex on the
| other hand could handle everything because it's a simple
| query builder.
| beders wrote:
| From the web page: "Application developers should care about data
| - not SQL"
|
| That is fundamentally wrong - or at least - it is very limiting.
|
| Think very hard about adopting a framework that tries to shield
| you from SQL. Because at the end of the day, you will be looking
| at SELECTS and INSERTS in your logs because you are wondering why
| things are so slow or why your memory usage skyrockets.
|
| It looks like Prisma allows you to go to raw SQL if you must, yet
| you won't have a good time using some of PostgreSQL more advanced
| features.
|
| If your domain model is simple and _will remain simple in the
| future_ , then - by all means - use a builder that creates
| queries from your domain model. Use a single-source-of-truth
| approach that is usable from the back-end and front-end.
|
| In other cases, where your domain model changes over time, be
| very careful when choosing an ORM but extra double careful in
| picking the right data store.
| nerdkid93 wrote:
| Over a year ago, I was investigating using Prisma to be the ORM
| for a GraphQL API of a Postgres database. When doing a proof-of-
| concept, I discovered that under the hood @prisma/client was
| spinning up it's own GraphQL server that it would send requests
| to in order to generate SQL to send to postgres. This extra
| middleware layer between my frontend code and postgres generated
| some pretty poor performing queries that took 50% longer to
| complete than the queries generated by using Hasura as our whole
| GraphQL API.
|
| A quick glance at the @prisma/client code makes it seem like this
| pattern is still the case, since they have an "engine-core"
| package that downloads a binary server behind the scenes and runs
| it on a free port in the background.
| inshadows wrote:
| > they have an "engine-core" package that downloads a binary
| server behind the scenes and runs it on a free port in the
| background
|
| O_____O
| camjohnson26 wrote:
| That extra layer gives them flexibility to combine queries and
| cache
| vptr wrote:
| Yup, would not touch Prisma with a 10 foot pole
| thedavidprice wrote:
| I'm on the RedwoodJS core team and can 100% confirm early
| performance issues. That's no longer been the case recently.
| I've seen bulk operations perform just fine and even data
| pipeline use cases. Now the issue is DB performance in a
| serverless infra, which needs extra set up and config to
| perform consistently and at scale.
| zackify wrote:
| For years node had pretty bad libraries for interacting with
| databases.
|
| Prisma is the first time it all just makes sense.
|
| The code generation, auto migrating without any code, automatic
| crud resolvers with nexus, the list goes on.
|
| After using it, I don't know how you can use any other language
| for backend. It would just take you much longer and require so
| much extra work. Nothing like prisma exists in other languages.
| segmondy wrote:
| I'm not a fan of ORM, never been and never will be.
| https://en.wikipedia.org/wiki/Object%E2%80%93relational_impe...
|
| But my gosh "Application developers should care about data - not
| SQL"
| spmurrayzzz wrote:
| That line triggered me as well. I have built hundreds of
| applications on top of databases w/ sql and nosql, and from
| that experience-- the notion that a single ORM abstraction
| somehow ameliorates the burden of caring about how your queries
| are written is alarming to me.
| heroic wrote:
| We tried converting from Sequelize to Prisma and learned
| transactions were not fully supported... Can't build an app
| without transactions
| arcturus17 wrote:
| Is this for real? And what are people building that wouldn't
| need this?
| nikolasburk wrote:
| Transactions _are_ supported in Prisma, see this guide in our
| docs [1].
|
| I guess the post refers to our opinionated stance on "long-
| running transactions" which Prisma indeed does not at the
| moment. The best resources to learn about this are on GitHub
| [2] and our blog [3].
|
| [1] https://www.prisma.io/docs/guides/performance-and-
| optimizati...
|
| [2] https://github.com/prisma/prisma/issues/1844
|
| [3] https://www.prisma.io/blog/how-prisma-supports-
| transactions-...
| knodi wrote:
| I used Prisma with TypeScript for rapid prototyping. It did what
| it said and did it well. I would highly recommend giving this a
| try.
| metta2uall wrote:
| Prisma looks quite good but what held me back is the out-of-
| process "engine" that handles all queries and talks to the DB. I
| would imagine all this inter-process communication adds a decent
| amount of overhead.
| Tronno wrote:
| The only time I have had to query a database using JS/TS is in
| React Native + SQLite. However, as far as I can tell, Prisma has
| no support for this stack, so TypeORM remains the only practical
| choice here - rough edges and all.
|
| I would love to try Prisma otherwise.
| skrebbel wrote:
| IMO any discussion about Node/TypeScript data access tools
| deserves a reference to Zapatos, which sort of is the anti-
| Prisma. It has all the type safety with none of the ORM (and it's
| Postgres-specific, for better and worse). I think it's got one of
| the best designed APIs I've come across.
|
| https://jawj.github.io/zapatos/
| smt88 wrote:
| PgTyped is similar, and it's superior to any other data access
| layer I've ever used. Ever.
| jadbox wrote:
| Have you compared PgTyped to Zapatos (I haven't yet).
| smt88 wrote:
| Not yet, but I will at some point. I don't really have any
| pain points with pgTyped yet.
| parhamn wrote:
| > prioritis[ing] ease of switching to another database tomorrow
| over effective use of this database today, are a source of
| misery.
|
| hah. This looks cool, thanks for sharing!
| nikolasburk wrote:
| Fully agree, Zapatos is super nice - and so are similar tools
| like pgtyped and Slonik. We actually mention all these in our
| docs here: https://www.prisma.io/docs/concepts/overview/should-
| you-use-...
|
| I think the main difference of these libraries compared to
| Prisma is the level of abstraction. If you _want_ to work with
| SQL and you appreciate type-safety, then all of these tools
| will be fabulous options for you! If you want to work on a
| somewhat higher level of abstraction but still have full type-
| safety, Prisma will be more appropriate.
| karmakaze wrote:
| I was reading through the 'where' cases to see how complex
| queries are _composed_. I couldn 't find any examples. e.g.
| WHERE id IN (<subquery>)
|
| if I built up <subquery> using the ORM.
|
| The other uncommon thing I look for is eliminating N+1 not just
| based on a given query and related entities, but given a starting
| collection and loading their related entities and further related
| entities.
|
| Icing is if it can do all the above while making intermediate
| results asynchronously available as it works through it all.
| abaldwin99 wrote:
| I'm completely perplexed at some of the functionality that's
| absent in Prisma? I'm coming from the Rails/Django world for
| reference. Can anyone help me understand if I'm out in left field
| or does this technology only cover basic use cases?
| - No supported way to do a case-insensitive sorting.
| https://github.com/prisma/prisma/issues/5068 - Can't sort
| by an aggregate value like user's post count.
| https://github.com/prisma/prisma/issues/3821 - Can't
| control between inner/left join. - Can't do subqueries.
| - Migration rollbacks are experimental maybe unsupported now? At
| least I only see mention in Github issues and not in the docs.
| - Transactions appear to expect a series of queries? It doesn't
| look like you can execute any app code during a transaction?
| - No support for pessimistic row locking e.g. SELECT... FOR
| UPDATE ? - No way to mixin raw query partials like
| `where('name ILIKE ?')`. You either need to write the whole query
| raw or not. - Validations are done at the database level.
| - Complex validations seem tricky to write in this format
| - No built-in way to make clean user-facing validation messages.
| - You can't check that a model instance is valid without just
| trying to insert it into the database - The official
| documented validation example has you connecting via psql and
| adding a constraint? - So following the offical example
| my validations aren't documented in the codebase via a model or a
| migration? - Also they don't have a validation example
| documented if you're using MySQL instead of Postgres? -
| Cascading deletes are handled the same way as validations. As in
| Prisma basically does nothing other than document how to
| implement it yourself outside of the library. - No model
| methods. I guess that's not a surprised because it's "not an
| ORM". A model really is just a data mapping? Anyways it seems
| like you would end up rolling your own wrapper around this and
| there's no recommendations on standardized architecture. -
| No callbacks. These have been controversial at times so are teams
| using Prisma writing something akin to "services" instead?
| - Syntax nitpick but one of these is vulnerable to a SQL
| injection and it seems really easy for a new developer to get
| mixed up? - prisma.$queryRaw(`SELECT \* FROM User WHERE
| email = ${email}`); - prisma.$queryRaw`SELECT \* FROM
| User WHERE email = ${email}`; - No way of batch loading
| like Active Records's find_in_batches / find_each. All objects
| are just loaded into memory? - No way of hooking into
| queries for instrumentation. e.g.
| ActiveSupport::Notifications.subscribe
|
| FYI - This is after fairly brief research. Not guaranteed 100%
| accurate.
| abaldwin99 wrote:
| @nikolasburk can you comment? Before compiling this list I was
| seriously considering using Prisma to kick off a big upcoming
| project. In the spirit of being open-minded I'd really like to
| know if I'm fundamentally misunderstanding Prisma capabilities.
| nikolasburk wrote:
| This is a pretty extensive list! Are _all_ of these points
| are full blockers for you or are there individual points that
| you care more about while others might be rather "nice to
| have"? If most of them are real blockers, you probably
| shouldn't use Prisma [1], and that's ok :)
|
| Prisma certainly is not _perfect_ and whether you should use
| it depends on your project and individual requirements. What
| I can tell you is that we are shipping releases [2] with new
| features and improvements every two weeks. We are also very
| eager to learn about more use cases that people want to
| accomplish with Prisma. The best way to bring these to our
| attention is by commenting on existing GitHub issues and
| creating new ones if the one for your use case doesn 't exist
| yet. This helps us prioritze and implement these new
| features. We also have a roadmap [3] where you can see all
| the features that we are currently working on.
|
| Hope that helps for now!
|
| [1] https://www.prisma.io/docs/concepts/overview/should-you-
| use-...
|
| [2] https://github.com/prisma/prisma/releases
|
| [3] http://pris.ly/roadmap
| abaldwin99 wrote:
| Thanks for getting back to me!
|
| Most of these are pretty important to us. I did try to put
| lower priority items near the bottom of the list. However,
| I did omit several nice-to-haves already. e.g.
| ActiveRecord's ability to diff changes after an update
| (ActiveModel::Dirty)
|
| I wouldn't say any of them are complete full blockers by
| themselves but cumulatively yes they prevent us from
| considering Prisma for most projects.
| sadturnip wrote:
| Afaik many of these issues are why the company i worked for
| ended up giving up on Prisma. We ended up switching to
| Objection.js.
|
| The biggest issue was the transactions which was a non starter
| for us. It wasn't very helpful when after explaining our use
| case and being told "we are doing it wrong" in the GH
| discussions, and instead were told to write rollback code
| manually instead of using transactions was a very poor answer.
| tomhoule wrote:
| I can clarify one point, relative to migration rollbacks. We
| indeed chose not to implement down migrations as they are in
| most other migration tools. Down migrations are useful in two
| scenarios: in development, when you are iterating on a
| migration or switching branches, and when deploying, when
| something goes wrong.
|
| - In development, we think we already have a better solution.
| Migrate will tell you when there is a discrepancy between your
| migrations and the actual schema of your dev database, and
| offer to resolve it for you.
|
| - In production, currently, we will diagnose the problem for
| you. But indeed, rollbacks are manual: you use `migrate
| resolve` to mark the migration as rolled back or forward, but
| the action of rolling back is manual. So I would say it _is_
| supported, not just as convenient and automated as the rest of
| the workflows. Down migrations are somewhat rare in real
| production scenarios, and we are looking into better ways to
| help users recover from failed migrations.
| wdb wrote:
| This sounds like you aren't able to easily rollback a release
| that had migrations
| tomhoule wrote:
| No question that there are cases where down migrations are
| a solution that works. In my experience though, these cases
| are more limited than you might think. There are a lot of
| presuppositions that need to hold for a down migration to
| "just work":
|
| - The migration is reversible in the first place. It did
| not drop or irreversibly alter anything (table/column) the
| previous version of the application code was using.
|
| - The up migration ran to the end, it did not fail at some
| step in the middle.
|
| - The down migration actually works. Are your down
| migrations tested?
|
| - You have a small enough data set that the rollback will
| not take hours/bring down your application by locking
| tables.
|
| There are two major avenues for migration tools to be more
| helpful when a deployment fails:
|
| - Give a short path to recovery that you can take without
| messing things up even more in a panic scenario
|
| - Guide you towards patterns that can make deploying and
| recovering from bad deployment painless, i.e. forward-only
| thinking, expand-and-contract pattern, etc.
|
| We're looking into how we can best help in these areas. It
| could very well mean we'll have down migrations (we're
| hearing users who want them, and we definitely want these
| concerns addressed).
| atleta wrote:
| The first thing I've looked at on your site is how migrations
| work. Because honestly, I think that's one of the best things
| about Django. They just got it right, and as you say, not
| many other tools get close.
|
| I wonder if you have looked at how it works. Because they
| have put in something like a decade to make it work and it's
| very powerful and a joy to use.
|
| Down migrations are indeed very useful and important once you
| get used to it. First and foremost they give you a very
| strong confidence in changing your schema. The last time I
| told someone who I helped with django to "always write the
| reverse migration" was yesterday.
|
| No way you can automatically resolve the discrepancies you
| can get with branched development. Partially because you can
| use migrations to migrate the _data_ not just to update the
| schema. It 's pretty simple as long as we're just thinking
| about adding a few tables or renaming columns. You just
| hammer the schema into whatever the expected format is
| according to the migrations on that branch. But even that can
| go wrong: what if I introduced a NOT NULL constraint on a
| column in one of the branches and I want to switch over? Say
| my migration did set a default value to deal with it.
| Hammering won't help here.
|
| The thing is that doing the way Django does it is not that
| hard (assuming you want to write a migration engine anyway).
| Maybe you've already looked at it, but just for the record:
|
| - they don't use SQL for the migration files, but python
| (would be Typescript in your case). This is what they
| generate. - the python files contain the schema change
| operations encoded as python objects (e.g. `RenameField` when
| a field gets renamed and thus the column has to be renamed
| too, etc.). - they generate the SQL to apply from these
| objects
|
| Now since the migration files themselves are built of python
| objects representing the needed changes, it's easy for them
| to have both a forward and the backward migration for each
| operation. Now you could say that it doesn't allow for
| customization, but they have two special operations. One is
| for running arbitrary SQL (called RunSQL (takes two params:
| one string for the forward and one for the backward
| migration) and one is for arbitrary python code (called
| RunPython, takes two functions as arguments: one for the
| forward and one for the backward migration).
|
| One would usually use RunSQL to do the tricky things that the
| migration tool can't (e.g. add db constraints not supported
| by the ORM) and RunPython to do data migrations (when you
| actually need to move data around due to a schema change).
| And thanks to the above architecture you can actually use the
| ORM in the migration files to do these data migrations. Of
| course, you can't just import your models from your code
| because they will have already evolved if you replay older
| migrations (e.g. to set up a new db or to run tests). But
| because the changes are encoded as python objects, they can
| be replayed in the memory and the actual state valid at the
| time of writing the migration can be reconstructed.
|
| And when you are creating a new migration after changing your
| model you are actually comparing your model to the result of
| this in-memory replay and not the db. Which is great for a
| number of reasons.
| tomhoule wrote:
| Yep we looked at Django ORM as an inspiration. I
| unfortunately don't have the bandwidth right now to write a
| lengthy thoughtful response, but quickly on a few points:
|
| - The replaying of the migrations history is exactly what
| we do, but not in-memory, rather directly on a temporary
| (shadow) database. It's a tradeoff, but it lets us be a lot
| more accurate and be more accurate, since we know exactly
| what migrations will do, rather than guessing from an
| abstract representation outside of the db.
|
| - I wrote a message on the why of no down migrations above.
| It's temporary -- we want something at least as good, which
| may be just (optional) down migrations.
|
| - The discrepancy resolving in our case is mainly about
| detecting that schemas don't match, and how, rather than
| actually migrating them (we recommend hard resets + seeding
| in a lot of cases in development), so data is not as much
| of an issue.
| atleta wrote:
| Well, of course I don't know about the internals, but
| having used Django migrations for a decade now (it used
| to be a standalone solution called "South" back then), I
| haven't really run into any inaccuracies and can't really
| imagine how those could happen. As far as I can see, the
| main difference is that they are storing and intermediate
| format (that they can map to SQL unambigously) while you
| immediately generate the SQL.
|
| Django doesn't try (too hard) to validate your model
| against the actual DB schema. Because why would it? You
| either ran all the migrations and then it matches or you
| didn't and then you have to. (Unless you write your own
| migrations and screw them up. But that's rare and you can
| catch it with testing.) While your focus then seems to be
| to check if the schema (whatever is there in the db)
| matches the model definition. Based on my experience (as
| a user) this latter is not really something that I need
| help with.
|
| Data is actually an issue in development and hard resets
| + (re)seeding is pretty inconvenient compared to what
| django provides. E.g. in my current project we're using a
| db snapshot that we've pulled from production about two
| years ago (after thorough anonymization, of course). We
| initialize new dev environments and then it gets
| migrated. It probably takes about half a minute to run as
| opposed to about 2 seconds of back migrating 2-3 steps.
| tomhoule wrote:
| It makes a lot of sense. I have a fair amount of Rails
| experience with ActiveRecord, and it was also my
| impression that the database schema drifting in
| development is rarely a problem, but I now think it's a
| bit of a fuzzy feeling and discrepancies definitely sneak
| in. The main sources of drift in development would be 1.
| switching branches, and more generally version control
| with collaborators, 2. iterating on/editing of
| migrations, 3. manual fiddling with the database
|
| One assumption with Prisma Migrate is that since we are
| an abstraction of the database, and support many of them,
| we'll never cover 100% of the features (e.g. check
| constraints, triggers, stored procedures, etc.), so we
| have to take the database as the source of truth and let
| users define what we don't represent in the Prisma
| Schema. On SQL databases, we let you write raw SQL
| migrations for example, so you have full control if you
| need it.
| abaldwin99 wrote:
| So future features notwithstanding, is the typical Prisma
| workflow that if a migration failed during a production
| deploy the developer would have to manually work out how to
| fix it while the application is down?
| tomhoule wrote:
| As of now there is no strong opinionation in the tool --
| you could absolutely maintain a set of down migrations to
| recover from bad deployments next to your regular
| migrations, and apply the relevant one (manually,
| admittedly) in case of problem.
| debug-desperado wrote:
| I'd still take Hibernate and Entity Framework over any of the
| Node ORMs I've seen, including Prisma. The former usually have
| the feature you need to generate the queries you want, but you
| just haven't read enough of the docs to understand how to do so.
| The latter straight up lack the functionality and lack sufficient
| docs.
| vaughan wrote:
| I think its unfortunate that Prisma is only server-side due to
| its Rust-based native DB module.
|
| I think we can achieve a huge simplification in the industry if
| we develop a universal state-management solution that runs in the
| frontend and backend.
|
| Without a consistent model, we are forced to write so much
| additional code to manage caching, optimistic UI updates, and
| offline capabilities.
|
| Current RDBMS' are not ideal (most data is better represented as
| a graph), but because the relational model is so widely used in
| the backend, they are a good candidate for a universal data store
| on frontend and backend. And it is possible to represent graph-
| like structures in RDBMS' too, but it makes SQL querying very
| unfriendly.
|
| GraphQL is an ideal language for declaratively specifying what
| data you need for the UI layer. If you doubt this, play around
| with the [Github GraphQL API][1]. It's an incredible experience
| to be able to explore and pluck exactly what you need. SQL and
| REST don't come close to this experience.
|
| But GraphQL is essentially providing us a nicer way to write SQL
| compared to using JOINs, and to output it in a nested format for
| convenient rendering with nice typing. But it lacks a huge amount
| of flexibility.
|
| So we should keep GraphQL as our API, but we should implement it
| client-side against a data model similar to what we use in our
| cloud db: SQL, and then do real-time syncing or use it as the
| model for our server-cache.
|
| An added benefit is we don't have to worry anymore about what is
| local-state and what is server-persisted, or worry about choosing
| a good state management solution client-side.
|
| The necessary pieces to make this happen don't exist at the
| moment, but I would have liked to use Prisma as the DB API for
| in-browser SQLite in the beginning.
|
| I think there is then the opportunity to go full-stack and offer
| developers a client-side GraphQL library that talks to an in-
| browser SQL DB and to end the constant turnover in state-
| management libraries. I think every frontend developer is
| overwhelmed at this point by the proliferation of these
| libraries, and the simplicity they go seeking in things like
| "just use React.Context api", don't offer them what they will
| eventually need which is optimistic UI updates, offline modes,
| real-time collaboration, local-first experiences, etc.
|
| I think we are all still in search of the right state management
| solution and it would be cool if Prisma did it!
|
| [1]: https://docs.github.com/en/graphql/overview/explorer
| SlackingOff123 wrote:
| I don't have much experience with node.js, so this question might
| sound silly. I don't see anything about database support in the
| blog post. Is Prisma enough to work with a PostgreSQL database
| from node.js? If not, what else am I missing?
| atraac wrote:
| Prisma does support Postgres.
| https://www.prisma.io/docs/reference/database-reference/supp...
| Sujan wrote:
| Easy to miss in the long blog post:
|
| > Prisma currently supports PostgreSQL, MySQL, SQLite, SQL
| Server (Preview). A connector for MongoDB is in the works, sign
| up for the Early Access program here
| (https://prisma103696.typeform.com/to/FriDuIeM).
| [deleted]
| seer wrote:
| I know prism adds soo much more than just data access for your
| project, but for just replacing ORMs I think I really prefer the
| approach of https://github.com/adelsz/pgtyped where you have raw
| sql files / template literals and it will create types for
| parameters and results.
| nickreese wrote:
| Pgtyped made sql+typescript a pleasure.
| Coxa wrote:
| Have been experimenting with mammoth [1] for postgresql and am
| quite happy with how close it is to actual SQL.
|
| [1] https://github.com/Ff00ff/mammoth
| daralthus wrote:
| Can someone compare to Hasura please? What has been your
| experience?
| nwienert wrote:
| I use Hasura and generally like it, it certainly has some sharp
| edges, but overall has worked. I think Hasura has much richer
| GraphQL support, which I pair with gqless[0] to get what I
| think is a much nicer client side / ORM experience overall.
|
| Other upsides are a much better RBAC column/row permissions,
| migrations with up/down, a better admin UI.
|
| [0] https://gqless.com
| sbacic wrote:
| What sharp edges are you referring to?
|
| I find that with new tech, I'm more interested in what it
| can't do rather than what it can.
| terrortrain wrote:
| I came here with the same question.
|
| Hasura is really awesome, so far as I have used it.
|
| Now I gotta look into prisma
|
| I almost wonder if they aren't mutually exclusive. With hasura,
| you need to run your own server anyways, for auth or special
| cases. Maybe that server could run prisma? Let hasura do the
| migrations, and prisma track it?
| nickreese wrote:
| For those of you that want typed SQL queries check out
| pgtyped[1]. Using it in production and it is great for writing
| arbitrary SQL queries and getting full typescript support.
|
| [1] https://github.com/adelsz/pgtyped
| nikolasburk wrote:
| pgtyped is awesome! We mention it along with similar tools like
| Slonik and Zapatos in our docs here:
| https://www.prisma.io/docs/concepts/overview/should-you-use-...
| fuadnafiz98 wrote:
| Is it a good suggestion to use prisma as a beginner other than
| using some low level query builders like knex.js?
| tobyhinloopen wrote:
| Meanwhile I just want to write my migrations in plain SQL. Why do
| we care so much about hiding SQL, it's easy.
| kofejnik wrote:
| Used it once, it is nice(-ish) for simple use cases, but
| unfortunately it doesn't support working with multiple postgres
| schemas, which is surprising given that many complex apps use
| them to namespace their DBs. It was a total showstopper for us
| and we're back to writing raw sql for now
| ponyous wrote:
| I'm just about to start working on DB layer for our app with
| TypeScript. Any other alternatives to prisma you guys prefer and
| why?
| kapv89 wrote:
| TypeORM or MikroORM, any of those should be fine
| eydwales wrote:
| I suggest to not add a layer. Stay close of the SQL request,
| you can simply have a function that will fulfill an access
| pattern. No need for ORM.
| ponyous wrote:
| Yeah, I have no problem with minimum ORM (prepared statements
| type of thing) - That's why I am asking the question, Prisma
| seems a bit too much. Usually I just use simple ORM helpers
| (get by primary key for example) and anything complex is raw
| sql.
|
| What I am looking for is ideally something that helps with
| type saftey and seeds/migrations/schema creation.
| gmac wrote:
| https://jawj.github.io/zapatos/ (no built-in migration
| support, though)
| topicseed wrote:
| Try Knex!
| tekkk wrote:
| I have been using Flyway with Node.js. Works all right and
| you write everything (migrations, schema, seeds) in SQL. It
| does make working with SQL quite straightforward and non-
| magical.
|
| The thing is of course that with very complex queries
| you'll end up making quite intricate string building stuff
| which may not be very pretty. I myself am curious to find
| out if Prisma can make that part go away.
| domlebo70 wrote:
| https://github.com/gajus/slonik
| heisenbit wrote:
| I'm wondering at the moment whether Knex (solid query
| builder and migration) and Objection.js would not do this
| trick. That combo seems to give me json validation which is
| nice and seemingly both a way to handle entities and graphs
| in an efficient way.
| nikolasburk wrote:
| Hey, Nikolas from Prisma here.
|
| There are quite a few other ORMs for working with DBs in
| TypeScript (none of them providing the same type-safety
| guarantees that Prisma does though [1]).
|
| In fact, we laid them out on a docs page [2] in case you want
| to learn which tool might be appropriate for your use case.
|
| [1]
| https://www.prisma.io/docs/concepts/more/comparisons/prisma-...
|
| [2] https://www.prisma.io/docs/concepts/overview/should-you-
| use-...
| ponyous wrote:
| Thanks, that's really useful.
| williamdclt wrote:
| (haven't tried Prisma)
|
| TypeORM -> classic ORM, the "default" choice in node, but not
| very mature compared to what exists in Java or things like
| Django ORM. In my experience it's not an amazing lib,
| transaction management is a bit painful, I've seen a couple
| bugs happen.
|
| Knex -> it works but not sure why you'd use that rather than
| TypeORM
|
| MikroORM -> not a lot of experience, one of my colleagues
| really likes it, read the principles behind it and see if that
| talks to you as it's a bit opinionated
|
| Slonik -> Never used it but probably what I'd set up on a new
| project. Like many people I think that query builders are BS
| and I'd rather use SQL: Slonik makes that very easy while
| giving you the usual getOne/getMany utilities
|
| I'd like to try Prisma though, I've heard good things
| presentation wrote:
| TypeORM is poorly maintained (the lead author had a breakdown
| and appears to be inactive, and failed to delegate ownership
| to others), and is riddled with bad abstractions, poor design
| choices and an enormous pile of game-breaking bugs that make
| the TypeScript types unsafe and it's usage clunky and
| dangerous. I would absolutely use knex, which works, over
| TypeORM, which routinely doesn't.
| sickcodebruh wrote:
| This matches my team's experience with TypeORM. It's full
| of bad surprises. We use it exclusively with a read-only
| account because we don't trust it to change our data under
| any circumstance. Our current plan is to move to MikroORM,
| Prisma 2, or just give up on Node completely.
| nikolasburk wrote:
| Just as a side-note, we have migration guide from Prisma
| to TypeORM in our docs:
| https://www.prisma.io/docs/guides/migrate-to-
| prisma/migrate-...
| hardwaresofton wrote:
| Interesting -- I actually haven't had as many problems as
| you have with TypeORM -- it's just mostly worked. Then
| again, maybe I'm a rare case, because I basically ignore
| the ORM side and use a little repository pattern, the query
| builder, and write my own migrations in SQL (as in all the
| migrations are `await queryRunner.query(...);`).
|
| It's been excellent for me -- I ignore the bad
| abstractions, have a good underlying database (postgres),
| and drop down to parametrized raw SQL whenever I need to,
| it's held up extraordinarily well I think.
|
| Could you expand on some specific issues/frustrations? Just
| curious about the side I'm clearly not seeing.
| sickcodebruh wrote:
| This is the one upside I've had of using TypeORM: I find
| its query abstractions so unreliable and surprising that
| it's forced me to get better at SQL!
| hardwaresofton wrote:
| And once you're good at SQL, it will never let you down.
| Query builders + reasonably abstracted libraries for the
| repetitive things (the repository pattern in TypeORM gets
| me just about all the CRUD I need), and query
| building/raw queries for the rest.
|
| The things that the DB can do (I'm thinking about
| postgres) are amazing, and you just aren't going to get a
| chance to use a lot of it really from a lowest common
| denominator ORM. Yeah, you probably don't need it -- but
| then again why not just use a tool like Postgrest from
| the start?
| mirthflat83 wrote:
| Lmao. This happened to me too no joke
| Seb-C wrote:
| If you want an orm that encourages you to use sql instead of
| abstracting it, you can look at my library:
| https://github.com/Seb-C/kiss-orm
| RivieraKid wrote:
| MikroORM. I think it's the best ORM for TypeScript.
| sorenbs wrote:
| There are many discussions on reddit about Prisma vs TypeORM in
| particular. For example here:
| https://www.reddit.com/r/node/comments/l3xu6w/is_prisma_a_vi...
|
| There is also a good discussion going on here:
| https://www.reddit.com/r/node/comments/l00fit/what_do_you_th...
| danielskogly wrote:
| My preferred DB layer is Objection.js[0] which is simply an
| ORM-layer on top of knex[1], with official TypeScript support.
|
| [0] https://vincit.github.io/objection.js/
|
| [1] https://knexjs.org/
| snarkypixel wrote:
| I love knex and simply write small functions and roughly keep
| one file/folder per table. So: user.create(db, { name: .. })
| brap wrote:
| I've been using Prisma for a while and I quite like it. Best ORM
| for TypeScript I've used. My biggest issue with it is
| testability. Sure, I can mock the Prisma client in tests with
| Jest or something, but if I want to test state I pretty much have
| to reimplement an in-memory DB using JS mocks. I can also connect
| to a real DB made for testing but it's quite overkill, I'm
| usually not interested in testing Prisma itself, just my own
| code. I wish Prisma had test mode, where you could replace the
| client with a temporary in-memory SQLite DB without writing tons
| of boilerplate.
|
| A drop-in replacement for PrismaClient for tests would have been
| wonderful.
| matthewmueller wrote:
| Thanks for this feedback. We're very aware of this problem and
| are are actually working on a guide for working with Jest right
| now.
|
| It's a tricky problem because our use of the type-system is
| quite complex.
|
| I quite like your idea of a test mode or maybe a mock client
| that we generate.
| nahtnam wrote:
| I wonder if its possible to start a transaction at the
| beginning of each test and then rollback. That way we just
| need a db to run but it will never really be written to.
|
| Or maybe just invest in this: https://github.com/oguimbal/pg-
| mem/issues/27 He has already put a lot of effort into it and
| I think with just a bit more support he can support Prisma
| brap wrote:
| > I quite like your idea of a test mode or maybe a mock
| client that we generate.
|
| Yes please!! :)
| high_density wrote:
| using tmpfs... it'll help a bit (I used mysql-on-tmpfs before)
| vbsteven wrote:
| Could you elaborate on why you find a real DB overkill for
| testing purposes?
|
| Typically you would have a set of unit tests which test your
| code only and use mocks for external dependencies, in this case
| you would mock Prisma.
|
| Tests involving DB state are not unit tests anymore and require
| a bit more setup but most frameworks provide a test
| harness/runner to quickly spin up a part of your app with
| dependencies resolved (instead of mocked) so you can call
| controller/handler/service methods directly and verify their
| behavior.
|
| The boilerplate for this should mostly be limited to switching
| out an environment variable for the database connection and
| maybe setup some fixtures. Is this not possible in Prisma?
| ragnese wrote:
| Isn't that pretty much always the dilemma with database stuff
| and testing?
|
| On the one hand, you can wrap your DB operations in some kind
| of mockable interface(s), and then test your business logic.
|
| On the other hand, you really need to test that the actual SQL
| ops work anyway- what if you screwed up a foreign key
| constraint?- that wouldn't show up in your business logic tests
| or your in-memory SQLite or whatever.
| bwship wrote:
| What is the business model for them? It seems that an ORM in and
| of itself is not sustainable, especially one that is free.
| nikolasburk wrote:
| https://news.ycombinator.com/item?id=26888735
| agustif wrote:
| Oh also, Blitz.js is a nextjs fullstack framework that uses and
| autogenerates prisma code to work with.
|
| Also FoalTS is a nice small framework for building rest endpoints
| with TypeScript, and recently landed @prisma
| support/documentation besides typeorm.
|
| Just random fyi, hope someone finds it useful if they wan't to
| try prisma in a more batteries included package.
| yewenjie wrote:
| I am a noob in these matters which one is advised if one is to
| start a GraphQL API from the beginning
|
| - use DBs that directly provide GraphQL endpoints such as Fauna,
| Upstash, etc. - use an ORM with more traditional DBs.
|
| What are the pros and cons of each?
| databrecht wrote:
| There is value in both, at Fauna we provide GraphQL out the
| box. Using Fauna directly would eliminate an indirection and is
| probably slightly more efficient. However, if there is a
| GraphQL layer like Prisma in between you could essentially
| change to any database with less impact on your application.
| This is tremendously interesting for people who develop
| frameworks, using prisma gives them the advantage of supporting
| multiple databases immediately. Or for application developers
| it could allow you to move from a non-scalable database to a
| scalable database once it becomes necessary or simply just
| switch databases if the database maintenance is causing you
| grief. I'm for one looking forward to Prisma supporting Fauna
| since if the interface is the same, there are even less reasons
| not to choose a scalable managed database instead of managing
| your own db :). And I would say that Prismas interface is quite
| great!
|
| Note: the performance impact does depend heavily on whether
| your database maps well on ORMs. Traditional databases have an
| impedance missmatch when it comes to translating tables to an
| objet format. Graph databases or the way Fauna works (documents
| with relations and map/reduce-like joins) map well on ORMs so
| the performance impact would be small.
| elitan wrote:
| I prefer having the database as a single source of truth and
| use something like [Hasura](https://nhost.io) to generate the
| GraphQL API.
| ridicter wrote:
| I tried out nhost a few weeks ago and it was great!
| ic4l wrote:
| I second this, Hasura is a solid choice.
| steipete wrote:
| How are they going to make money?
| nikolasburk wrote:
| We've explained this in the blog post here:
| https://www.prisma.io/blog/prisma-the-complete-orm-inw24qjea...
|
| I think this quote summarizes our plans nicely: _Prisma 's
| vision is to democratize the custom data access layer used by
| companies like Facebook, Twitter and Airbnb and make it
| available to development teams and organizations of all sizes._
|
| The open-source ORM we're launching today will of course remain
| open-source and we'll keep investing into it since it's be the
| foundation for the commercial tools that folks will be able to
| use on top.
| goliatone wrote:
| How does "democratize the custom data access layer" make
| money?
| vptr wrote:
| I have no idea what this means either. Sounds like a bunch
| of buzzwords about nothing.
| nikolasburk wrote:
| It means that Prisma will provide a data access layer (we
| call it "application data platform" [1]) similar to the
| custom data access layers built by big companies [2]
| (e.g. TAO by Facebook or Strato by Twitter) that enables
| application developers to better access their databases.
|
| We've explained that in the blog post here in the "Open-
| source, and beyond"-section [3].
|
| Does that help? :)
|
| [1] https://imgur.com/O1lwo0v.png
|
| [2] https://imgur.com/Hb9VOWN.png
|
| [3] https://www.prisma.io/blog/prisma-the-complete-orm-
| inw24qjea...
| Boxxed wrote:
| I think we all appreciate the links but unless I'm
| missing something, no, it doesn't help -- what's the plan
| to make money? That blog post doesn't mention anything
| along those lines at all.
| bwship wrote:
| I don't think they will.
| wasd wrote:
| How's the support for window functions? One thing I like about
| ActiveRecord is it's lazily evaluated and you compose queries.
| You can do p = Post.where(published: true); if x; p.where(x:
| true). Is that supported?
|
| Checked here for window function support.
| https://www.prisma.io/docs/concepts/components/prisma-client...
| ARussell wrote:
| I'm sure a lot of hard work went into this, so I mean no offense
| here when I give my honest feelings.
|
| The big differences between v1 and v2 make me uneasy. I am
| reminded of the constant API churn of React-Router, or the
| complete change from AngularJS to Angular. This approach makes me
| very hesitant to learn the library, because I am afraid of having
| the choice of painful upgrade or a dead dependency down the road.
|
| I am also wary of the library being VC-funded, because I am
| afraid of what kinds of features will be held at ransom down the
| line once they need to start making money.
| kkarimi wrote:
| Prisma is singlehandedly making my work life easier and better
| every day, can't fault it much at all. Really lovely experience
| and the public slack community is super helpful too!
| dickfickling wrote:
| I LOVE Prisma. I've used Django, SQLAlchemy, Sequelize, Knex, and
| TypeORM in the past. all had rough edges that continually
| frustrated me or didn't provide the functionality i needed.
|
| Prisma is different. It's absolutely got rough edges, but the
| extremely strong type safety makes Sequelize look like a joke.
| The query engine itself, written in rust, combines and optimizes
| queries inside every tick of the event loop so GraphQL N+1 issues
| are a thing of the past.
|
| Also, the team and community behind it are amazing! I never
| thought having an active dev community behind an ORM would be
| important, but as the author of Sequelize-Typescript was forced
| to abandon it late last year and the author of TypeORM was also
| pretty much absent, Prisma was a breath of fresh air. I REALLY
| hope they can find a way to build a sustainable business out of
| it. Support packages, feature development contracts, something to
| keep them financially incentivized to keep making it better.
|
| Happy to answer any questions about my experience using it if
| anyone has any.
| brap wrote:
| > GraphQL N+1 issues are a thing of the past.
|
| Huh, care to explain this one? I'm using Prisma with Apollo
| Server without doing anything fancy in my resolvers. I just
| assumed I'm getting N+1 issues but didn't bother to optimize
| yet.
| dickfickling wrote:
| in short the separate engine process allows them to combine
| every findX call you make during one tick of the event loop
| into a single SQL query, following the dataloader pattern, so
| you don't have to implement it yourself. i'm sure
| @nikolasburk can shed some more light if you're interested.
| hnjobaccount wrote:
| For the interested, here's an article that explains how the
| dataloader manages to pull this off
| https://www.mikealche.com/software-development/advanced-
| prom...
| tehlike wrote:
| If it's optimizing with figuring the calls in a single tick
| out, one can probably still optimize further by using db
| specific features, like postgres supports json_agg which
| let's you not only break n+1 in a way, but also prevents
| cartesian product explosion with joins
| eurasiantiger wrote:
| I have a feeling that does not work at all in non-trivial
| scenarios, e.g. if both composite keys and date range
| matching are required to resolve a reference.
| sbarre wrote:
| Could you test this and get back to us with the results?
| sorenbs wrote:
| There are pretty extensive docs on how Prisma optimises
| n+1 queries:
| https://www.prisma.io/docs/guides/performance-and-
| optimizati...
| Sujan wrote:
| No need to test really - indeed more complicated cases
| are not covered by this yet. But happy to look at any
| Github issues with reproduction of cases that are not
| working, but could or should work to make your life
| better.
| brap wrote:
| Very interesting, thank you. Is there a way for me to
| monitor what Prisma is doing in my use case?
| dickfickling wrote:
| using `new PrismaClient({ log: ["query"] });` it'll log
| all the actual sql queries it's running, so you'll be
| able to see if queries are being combined
| brap wrote:
| Awesome, thanks!
|
| Turns out Prisma wasn't optimizing my queries, I think it
| has to do with me not using the sub query API and instead
| querying tables seperately (e.g posts.findMany({ where: {
| owner: user } }))
| Sujan wrote:
| Pleeeeeease open an issue if you have a simple
| reproduction for this. That will make sure we will make
| sure this _does_ work (sooner or later, not committing to
| a timeline here on HN of course :p).
| nesarkvechnep wrote:
| You haven't mentioned the best query builder I've ever used -
| Elixir's Ecto. I encourage to give both Elixir and Ecto a try
| as they're worth learning.
| ragnese wrote:
| To be fair, Sequelize makes itself look like a joke. I normally
| wouldn't call out an individual library to shit on, because I
| understand that a lot of work has gone into it.
|
| But I used it for years and it was always buggy. Sometimes
| options wouldn't work correctly because the authors liked to do
| that clever JavaScript thing where they write `foo =
| optional_thing || default_value` even for BOOLEAN options. I
| always had issues with it not being able to truncate/drop
| tables correctly that had foreign keys (it didn't attempt to
| order them intelligently), some of the options weren't
| compatible with each other even though they were orthogonal
| (I'm thinking about the snake_casing options and the
| created_at/updated_at column features), etc, etc.
|
| It just... didn't actually work. Over many versions. I think I
| used it from late 3.x somewhere through 5.x.
|
| Maybe it's awesome now, but I doubt it.
| Capaj wrote:
| No it's not. Even if they fixed all the bugs which I doubt
| the API design is bad.
| ThalesX wrote:
| > I normally wouldn't call out an individual library to shit
| on, because I understand that a lot of work has gone into it.
|
| I don't think I've _ever_ called out on a library, or not
| felt truly thankful that it was there to help me. But
| Sequelize... man...
|
| When I first got into Node, and got Sequelize, after working
| for years with .NET, Entity Framework, NHibernate and the
| likes, it just felt like horrible-everything. I've forced
| myself to use it in some projects, because node is cheap, and
| I kept thinking that I'm missing something. Some brilliance
| behind the questionable... everything. No. I can't even bring
| myself to think about that design mess. Sorry for the rant,
| Sequelize makes me feel insecure, and little, in the chaos of
| the Universe.
| ragnese wrote:
| Same. Honestly, I only have not-nice things to say about
| Node, JavaScript, and its ecosystem, so I should probably
| just shut up.
|
| But I was like you- I picked up Node because we had an
| existing project in it. It only used libraries that had the
| most "stars" or whatever on NPM and they were all pretty
| deficient (and some were shockingly slow). But none were so
| frustrating as Sequelize.
| Scarbutt wrote:
| _I only have not-nice things to say about Node,
| JavaScript, and its ecosystem,_
|
| The NPM ecosystem can be frustrating and annoying
| sometimes but today nodejs is a solid server runtime.
| ragnese wrote:
| Node-the-software-project is a feat of engineering.
|
| But the API, the client language, the ecosystem, and even
| the _idea_ are bad, IMO.
|
| Bad API example: Tell me how to deal with time zones in
| Node without pulling in a big third party library.
| Answer: You can't. So, a "scalable" backend platform
| can't handle datetimes from different time zones? Neat!
|
| "Well, that's JavaScript's fault" you say. Node certainly
| has (a few) APIs that don't apply to the frontend, so why
| not add more of the important ones for backend work?
|
| Or, maybe JavaScript really just doesn't belong on the
| backend at all...
|
| Not to mention that it's single-threaded, so the answer
| to scaling up better is to just run more instances. -_-
|
| See? I should've stayed quiet. ;)
| matheusmoreira wrote:
| > Not to mention that it's single-threaded, so the answer
| to scaling up better is to just run more instances.
|
| Yeah, this sucks. It's impossible to do any actual
| processing in javascript since functions that don't
| return immediately will clog the event loop. Why can't we
| have javascript code that runs asynchronously and reports
| completion and results as events? Even browsers seem to
| have this now. I tracked issue #2133 on GitHub for a long
| time and nothing materialized.
|
| Not sure if Node.js still lacks this feature. If so then
| it means Node.js is nothing but an I/O scripting
| platform. You get events and you make the system copy
| data from some source to some destination in response.
| Any sort of actual processing means latency becomes
| unacceptably high.
| midrus wrote:
| Having used in the past both Django and Rails ORMs, I totally
| agree with this. Prisma is awesome, it's been the best
| experience I've ever had so far with SQL databases.
| eurasiantiger wrote:
| > GraphQL N+1 issues are a thing of the past.
|
| This one I will believe when I see it. It would be a general
| solution to cache invalidation.
| mixedCase wrote:
| You can disable caching if that's a problem for your
| application. But the idea is that DataLoader simply holds
| onto promises that requested an entity, and fires them all in
| batches according to a scheduler function. In Node.js, the
| default is to use scheduler magic (relies on how the event
| loop works).
| hnjobaccount wrote:
| Here's a small article that explains how the dataloader
| pattern works under the hood
| https://www.mikealche.com/software-development/advanced-
| prom...
| mirthflat83 wrote:
| Prisma 2 is a delight to use. Gave it a try after typeorm-
| model-generator's author suggested using something else than
| TypeORM. Prisma blows other JS/TS ORM libraries out of the
| water. It integrates so well with VS Code
| wdb wrote:
| I wish Prisma had built-in Opentelemetry support for tracing
| and metrics.
|
| They have a whole blog article about why monitoring is
| important but don't seem come with it:
| https://www.prisma.io/blog/monitoring-best-practices-monitor...
| bnt wrote:
| Laravel has Eloquent and it's an absolute delight to use. I've
| used Rails and Django, and I always felt as if I was fighting
| the ORM, where as with Eloquent everything just seems natural.
| joshmanders wrote:
| I am a former PHP developer, absolutely loved Laravel and
| adored Eloquent as the ideal ORM. I switched to
| Node/JavaScript in 2015 and have been chasing a good ORM
| since, nothing could ever compare with Eloquent in Nodeland,
| I all but gave up and started building my own, in TypeScript
| to match Eloquent as much as possible.
|
| Then Nikolas Burk reached out to me and did his absolute best
| to convert me, but I was stuck in my ways, it HAD to behave
| like Eloquent, or it was not good enough and that DSL layer?
| No thanks, I don't like it.
|
| Started writing code for my own ORM when I was like "What the
| heck am I doing? this gets me nowhere" And messaged Nikolas
| back saying I was dropping everything and planned to give
| Prisma a real solid try.
|
| I'm so glad I did. I LOVE it now. I consider it a part of my
| GOAT stack.
| nikolasburk wrote:
| :heart:
| wdb wrote:
| It's unclear what's the pros are of Prisma compared to TypeORM.
| I do find it confusing I always thought Prisma was GraphQL
| related.
| nikolasburk wrote:
| > I do find it confusing I always thought Prisma was GraphQL
| related.
|
| This is a common misconception that stems from our history as
| a company and being early contributors to the GraphQL
| ecosystem. With the move to Prisma 2 however, there is no
| native GraphQL layer in Prisma any more. I've talked about
| this extensively in a recent livestream on Youtube [1] if you
| want to learn more :) There's also this article "How Prisma
| and GraphQL fit together" [2] that explains the historic
| dimensions of this if you're interested.
|
| > It's unclear what's the pros are of Prisma compared to
| TypeORM.
|
| I guess you could argue that one benefit is the superior
| type-safety Prisma provides [3]. Other folks have also called
| out that they prefer way how data is modeled with Prisma (via
| the Prisma schema), the migration system as well as the
| active maintenance, regular releases, the active community,
| the support and thorough documentation of Prisma. Ultimately
| it'll come down to your personal preference though which one
| is the more appropriate for your project :)
|
| [1] https://www.youtube.com/watch?v=hMWMPpy4ta4&list=PLz8Iz-
| Fnk_...
|
| [2] https://www.prisma.io/blog/prisma-and-graphql-
| mfl5y2r7t49c
|
| [3] https://www.prisma.io/docs/concepts/more/comparisons/pris
| ma-...
| loloquwowndueo wrote:
| " Application developers should care about data - not SQL"
|
| Anyone who has been bitten by an ORM generating inefficient SQL
| (and subsequent having to learn and care about SQL) knows the
| above not to be true.
| dj_mc_merlin wrote:
| Not just this - I'm currently working on an application built
| by people with no understanding of databases beyond "they're
| kind of like key value stores". No attention is given to
| transactions, stale updates, locking mechanisms or anything
| more complicated than the equivalent of SELECT and UPDATE. It
| is an HTTP service running on many threads, so the problem is
| only amplified.
|
| If you're going to use a SQL database, you need to understand
| what it is and what rules to obey to get its benefits. Hiding
| that complexity away makes people think they're writing a fast,
| concurrent high-availability (TM) service while it's actually a
| ball of mud that sometimes gets into the right shape.
| einrealist wrote:
| I agree.
|
| That statement is so wrong. All ORMs suffer from leaky
| abstraction. And good ORMs would never hide that fact, nor
| would they try to deny native access to the database and force
| a all-or-nothing principle onto the developer.
|
| If "application developers" care about data that is stored in a
| relational database, they have to care about the database
| itself and the access patterns (SQL).
|
| ORMs _only_ provide convenience! They cannot substitute
| knowledge about the underlying technologies. And they cannot
| hide complexity, but shift it to the ORM.
| dariosalvi78 wrote:
| if you define yourself as a developer do yourself a favour and
| learn SQL. It's probably much easier than whatever programming
| language you use and will avoid you many headaches.
| whatl3y wrote:
| While some people are hard lined enough on their anti-ORM
| stance that it sort of becomes weird, I agree with you that my
| beef with ORMs comes from being burned before a couple of times
| by a super inefficient aggregation ActiveRecord did on GROUP BY
| queries that it 1. not only took a really long time to figure
| out why a particular page in our app was loading slow but 2. we
| ended up having to write raw SQL to fix it.
|
| I think the answer of whether to use one depends on the type
| and load/volume of app you're working with combined with the
| dynamics, size, and skill level of your team(s). I'm extremely
| comfortable writing, profiling, query planning, and debugging
| SQL queries. Others aren't, and therefore having an ORM to
| query data in the DB with the syntax of the language you're
| using in your projects makes way more sense, if nothing other
| in order to speed your team up.
| cknoxrun wrote:
| Not sure when you used ActiveRecord, but slow query logging
| helps a lot to identify these issues. I also appreciate that
| in the docs for ActiveRecord they make it very clear that
| jumping to raw SQL is absolutely fine and normal, and they
| make the interface for doing that very nice.
| ragnese wrote:
| The statement is true, IMO. The problem is that ORMs don't ever
| solve the problem (and I don't believe they CAN).
|
| We _should_ only have to care about data and not SQL. But the
| reality is that we very much _do_ have to care about SQL.
| Frost1x wrote:
| I agree. Clean and efficient abstractions (oxymoron?) to
| handle data management just doesn't exist. ORMs are great
| until they're not, at which point you do need to understand
| SQL and idiosyncrasies of the underlying relational database.
|
| I tend to prefer avoiding ORMs as a preference because of
| this but unfortunately, development expectations in the
| current culture don't pad time enough to do this, so you're
| often reliant on an ORM and wait until things break before
| someone paying the bills will be swayed to allow developers
| to go in and clean things up. The "wait until it breaks to go
| fix and redesign everything" mentality isn't unique to ORMs
| though, it plagues any abstraction, framework, library,
| whatever that gets enough done to supply something mostly
| functional in a shorter time scale.
| nikolasburk wrote:
| This statement is certainly provocative (great that it was the
| first thing picked up here :D) but I'm happy to explain our
| rationale for this a bit more.
|
| SQL is an impressive technology and has stood the test of time!
| Yet, we claim that it's not the _best tool for application
| developers_ who are paid to implement _value-adding_ features
| for their organizations.
|
| SQL is complex, it's easy to shoot yourself in the foot with
| and its data model (relational data / tables) is far away from
| the one application developers have (nested data / objects)
| when working in JS/TS. Mapping relational data to objects
| incurs a mental as well as a practical cost!
|
| This is why we believe that in the majority of cases (which for
| most apps are fairly straightforward CRUD operations)
| developers shouldn't pay that cost. They should have an API
| that feels natural and makes them productive. That being said,
| for the 5% of queries that need certain optimizations, Prisma
| allows you to drop down to raw SQL and make sure your desired
| SQL statements are sent to the DB.
|
| I see Prisma somewhat analogous to GraphQL on the frontend,
| where a similar claim could be: "Frontend developers should
| care about data, not REST endpoints". GraphQL liberates
| frontend developers from thinking about where to get their data
| from and how to assemble it into the structures they need.
| Prisma does the same by giving application developers a
| familiar and intuitive API.
| megous wrote:
| Actually nested objects kinda suck even for frontend devs, if
| you're trying to keep things in sync efficiently.
|
| Often times it's much better to be able to lookup some object
| by ID of the entity from some Map, than consuming endpoints
| returning some crazy nested partial data for the current
| view.
|
| Depends on how much your app relies on client side caching
| and incremental sync of data.
| intergalplan wrote:
| As soon as you need a lot of complex data all displayed in
| one place _plus_ high performance, you 're gonna find you
| need _a list_ , not a hierarchy. You need to be able to
| treat the data coming in like a stream, not to _traverse_
| anything. Read the row, maybe do some state-machine stuff
| to decide how to treat it, then drop all that on the floor
| and move to the next row.
|
| This is more-true the less efficient the language is that
| you're using. I see people screw this up when writing e.g.
| dashboards that source their data from SQL, while leaning
| on an ORM, _all the time_ , and it always kills performance
| (talking tens of seconds to _minutes_ for things that
| should take a single-digit count of seconds, at worst).
|
| What's good at turning complex relational data into a list,
| and _fast_? Yep, SQL.
| megous wrote:
| That too. Though I used _efficiently_ in a sense of
| minimizing the communication between client /server by
| normalizing data and just sending changes.
|
| Which is easier if you keep data normalized on the client
| side too, and look up related objects in a Map when
| needed, instead of keeping multiple copies of objects
| representing the same entity everywhere in your client
| code in some/vasrious denormalized forms.
|
| It would actually be very nice for some use cases if I
| had a SQL interface to local data on the client side too,
| so that I can query/join them up arbitrarily as needed
| from what's loaded up to client storage. That's
| unpleasant to do in the browser with current platform
| APIs.
|
| Basically I want WebSQL back in some form, instead of
| this IndexedDB thing. :)
| vaughan wrote:
| > if you're trying to keep things in sync efficiently
|
| I think we are all abusing GraphQL in this sense. Caching
| GraphQL data is a world of hurt.
|
| The easiest way to sync is if your data is in the same
| model as the way it is stored, which is normalized if using
| SQL. Even when we do normalize most people will represent a
| 1-M as an array of foreign keys: `posts.comments = [1, 2,
| 3]` when in the db we use a foreign key on the `comment`
| entity: `comment.post_id = 1`. This causes such headaches
| for optimistic UI updates. It's a mess.
|
| We should be using GraphQL to retrieve data and then render
| it directly.
|
| Ultimately, I think we should be implementing our GraphQL
| API client-side against a local SQL DB that acts as a pass-
| through cache. Then your entire app runs offline and
| instantly respond to queries eliminating the need for a
| separate client-side cache, and you don't have to worry
| about about data model mismatch anymore.
|
| > Depends on how much
|
| I think all apps want this - unless they are SSRing with
| <100ms on every action.
| megous wrote:
| Ha :) Yeah, I just replied in a similar vein in the other
| reply to my original comment. Agreed!
| vaughan wrote:
| I keep sayin' it - SQL in the browser!
|
| I'd love to get rid of all these client-side state
| management libraries and just drop an SQL DB in there and
| allow components to subscribe to changes to queries on
| record updates, and then sync it to a cloud DB.
|
| One interesting problem I came across is how to know when
| your local DB has enough records to be able to run a
| given query. E.g. If your team has 100K tasks in their
| cloud DB, and you run `SELECT * FROM tasks WHERE
| tasks.assignee = user.id`, and then another query of
| `SELECT * FROM tasks WHERE tasks.assignee = user.id AND
| tasks.completed = true`, the second task is a subset of
| the first, so it doesn't need to hit the server except to
| check if any updates were made to the tasks table. Then
| apply this to all queries including ones with joins.
| Actually some fun relational algebra can be used figure
| it out - one of the perks of working with SQL. The
| problem is actually similar to how to [incrementally
| maintain a materialized view](https://wiki.postgresql.org
| /wiki/Incremental_View_Maintenanc...) which Postgres
| actually does not support (Oracle does).
| loloquwowndueo wrote:
| Oh don't get me wrong - ORMs have their place and do enable
| higher agility. They do seem magical the first time you
| encounter them.
|
| What I object to is the blanket "shouldn't care about sql"
| statement because that's what empowers people to use the ORM
| indiscriminately without understanding what's under it (an
| understanding for which SQL is relevant) and then it's the
| non-value-adding developers' job to come in and untangle the
| mess, usually could have been avoided by dropping down one
| level, looking at the SQL that was generated (or maybe
| analyzing the query plan - again kind of hard if you don't
| understand SQL) and realizing the ORM is doing something
| crazy.
| alireza94 wrote:
| Exactly. ORMs, especially powerful and mature ones like
| DjangoORM or Active Record, can help with productivity and
| maintainability and for more complex use cases you have the
| chance to switch to raw SQL; However, it's important for
| developers to know what's happening under the hood and to
| know what will happen if they use a certain feature of an
| ORM. I can't even count the number of times that when one
| of my coworkers and I tried to fix a performance issue, and
| after digging deep into queries and mechanics of the ORM,
| we've realized how ORM heed so much complexity from them
| and, how a certain data structure design and coding in a
| certain way can result in an inefficient data flow.
| htkien wrote:
| If everything is simple, then so are the SQL queries.
| abraxas wrote:
| I think SQL gets a lot of undeserved praise that I'm having a
| difficult time understanding. The only impressive thing about
| SQL is its prevalence but that's a pretty poor yardstick
| unless one thinks that an appeal to popularity is an
| indicator of quality.
|
| Now let me count the ways in which SQL is bad:
|
| - it composes poorly due to its unwieldy cobolesque syntax
|
| - it is a leaky abstraction revealing a lot of underlying
| implementation tradeoffs
|
| - it doesn't properly implement Cobb's relational model
|
| - it is poorly standardized with a ton of proprietary
| extensions and alterations present in virtually every
| implementation
|
| - it is still poorly supported by tools because the model
| metadata lacks any standard interface to make universal
| tooling possible
|
| A lot of the praise for SQL is just bandwagon hopping and
| cargo cult behaviour or a lack of vision by most people of
| how things could be much better
| nicoburns wrote:
| The great thing about SQL is the relational model. You
| typically can't compose parts of SQL queries easily from
| the programming language calling it, but SQL queries
| themselves compose incredibly flexibly. The result of of a
| SELECT query is itself a "relation" that can be queried
| just like a table.
|
| I do wish someone would create a compile-to-sql language
| that adds variables and basic conditional support. I think
| T-SQL (SQL Server) has a lot of this, but it's missing in
| the Open Source world.
| dragonwriter wrote:
| > I think T-SQL (SQL Server) has a lot of this, but it's
| missing in the Open Source world.
|
| Every database with a procedural language based on SQL
| (Oracle PL/SQL, Postgres PL/pgSQL, etc.) has it, T-SQL
| just doesn't segregate procedural and declarative code
| the way most engines do. Mostly, I think this is a
| negative for T-SQL, but sometimes segregation means
| there's no way to do one-off procedural scripts without
| making and then calling a procedure; but Postgres, for
| example, since 9.0 has the DO statement for this use case
| (which supports _any_ procedural language installed in
| the DB, but defaults to PL /pgSQL.)
| TheCoelacanth wrote:
| I don't think SQL does compose well. Queries aren't
| first-class values in SQL.
|
| I can write a query that queries the result of another
| query, but I can't store a query and then pass that
| somewhere to query from.
| pmontra wrote:
| Maybe the WITH statement and CTEs can help you, if I
| understood correctly your complaint.
|
| Check the example at the beginning of
| https://www.postgresql.org/docs/13/queries-with.html
| spion wrote:
| What are you comparing it to? What is the better
| alternative?
| sonthonax wrote:
| SQL isn't the only RDMS language, but it is the one with
| 50 years of incumbency.
|
| Postgres before it was called 'PostgreSQL', used
| something called QUEL, which is quite a bit cleaner.
| There is also Microsoft's LINQ.
|
| A typical programming language can express all that SQL
| can. In a way, an ORM is a cross-compiler from your
| programming language to SQL.
| vaughan wrote:
| > A typical programming language can express all that SQL
| can. In a way, an ORM is a cross-compiler from your
| programming language to SQL.
|
| SQL has always been a sort a magical, black-box in that
| you have very limited control over the query planner, and
| have to hint at it to do the right thing. (I guess
| `EXPLAIN` allows you to peak in the box a little)
|
| I found [Apache
| Calcite](https://calcite.apache.org/docs/algebra.html)
| rather interesting. It provides the primitives that a
| DBMS is built from.
|
| I would be interested to compare how PSQL is built in
| comparison. I would love a more layered/pluggable
| database that everyone can build off of. I think
| FoundationDB had this approach.
| vaughan wrote:
| Would be interested in hearing what database and query
| language you think are better?
| abraxas wrote:
| I think Datalog as implemented by Datomic has a much
| better syntax though I don't like Datomic per se.
| htkien wrote:
| An ORM API on the other hand is completely meaningless
| adhoc "whatever works". It doesn't have grounding in
| anything.
| abraxas wrote:
| I'm making no statements about ORMs. I'm just picking
| apart the mindless "SQL is the bee's knees" mantra so
| prevalent on HN.
| nicoburns wrote:
| > SQL is an impressive technology and has stood the test of
| time! Yet, we claim that it's not the best tool for
| application developers who are paid to implement value-adding
| features for their organizations.
|
| I'm not sure about that. We've recently switched from
| JavaScript based querying code to mostly raw SQL, and we've
| reduced our code to about 25% of what it was, and it's much
| simpler to understand than it was before.
|
| > I see Prisma somewhat analogous to GraphQL on the frontend,
| where a similar claim could be: "Frontend developers should
| care about data, not REST endpoints".
|
| I'm not sure about Prisma, but IMO that GraphQL model isn't
| great. Realistically (for performance, etc) it will make a
| difference where that data came from. Not for super simple
| queries, but super-simple queries are super simple to do with
| REST/SQL anyway. I also feel like this distinction between
| front-end and back-end developers isn't great.
|
| The GraphQL approach also leads to deployment issue with non-
| web clients. e.g. it can take a day to get an iOS build
| released, and there is no way to force users of older clients
| to update, so this can take months. So fixing a bad query is
| difficult. With a proper backend you can just swap out the
| query.
| tored wrote:
| I think the strongest argument for or against an ORM is
| that it changes the structure of your code, how you reason
| about your data.
|
| Things like performance, ease of use, reusability, schema
| generation etc are minor points and not the big issue you
| need to think about.
|
| Personally I feel that an ORM will lead your project in the
| wrong direction and your database schema will suffer.
|
| ORMs will usually mix both reading and writing within the
| same class, but those are not the same thing.
|
| Readers will have different views of your model depending
| on who they are or what time it is. Writing on the other
| hand must fulfill your constraints.
|
| Other common mistakes of ORMs is mapping one class with one
| table, but your data consist of series of relationships,
| that can't be explained with a class.
|
| An ORM creates an illusion that your data is actually the
| class entities in your repository. This can have the effect
| of creating constraints in your own mental model of your
| data, thus making it harder to evolve your schema because
| you are to fixed on how your classes are designed.
| IggleSniggle wrote:
| Great comment! I've been thinking about a tangentially
| related subject:
|
| TypeScript is like a relational database (and other
| structural statically typed languages) of your
| application behavior, allowing you to query the current
| possible states of your data at any given point in your
| application code.
|
| I've been thinking about this more since TypeScript got
| template-string generics, and folks have experimented
| with eg type-checking SQL strings back into application
| code.
|
| This is the piece that draws me to an ORM: that it brings
| the "relational model" into application code, NOT the
| "object" part of an ORM.
|
| Does anyone have any opinions about which language
| ecosystems create the most effective type-mappings
| between a RDBMS and application language?
|
| Part of me also wonders if there's a ton of time lost on
| mapping between languages like this when the tooling
| that's really missing (afaik) is better tooling for
| producing, consuming, and generally interacting with SQL
| and db schemas _as the data runs through your application
| pipeline_.
| notJim wrote:
| > We've recently switched from JavaScript based querying
| code to mostly raw SQL
|
| Curious what your use case is, because in every codebase
| I've worked on, people pretty quickly get tired of writing
| out SELECT * FROM [table] WHERE id=? and UPDATE [table] SET
| field=? and looping over database cursors all day, and you
| end up with a half-done, buggy non-ORM.
| nicoburns wrote:
| We're using knex.js, so we're not messing around with
| database cursors (we get an array of objects), and we
| don't have to write out the columns for inserts/updates.
| We also sometimes use the knex query builder for super
| trivial selects from a single table.
|
| But for anything more complex than that (e.g. SELECT
| queries joining several tables) we're using knex.raw so
| the query itself is raw SQL where we're very much
| thinking in the relational model rather than in terms of
| objects.
| notJim wrote:
| Ahh that makes more sense. My preference has been to use
| an ORM for your normal CRUD, but write more advanced
| queries using SQL (or sometimes just write the where
| clause using SQL, if the select is otherwise simple.) I
| am not a fan of query builders.
| lelanthran wrote:
| > Yet, we claim that it's not the best tool for application
| developers who are paid to implement value-adding features
| for their organizations.
|
| You have two options when marrying RDBMS SQL and OO: either
| mismanage the relational data so that developers can use a
| class hierarchy, or stop using a class hierarchy to represent
| data.
|
| IME, applications come and go. They get rewritten, thrown
| away, obsoleted. The database, however, is there to stay.
| Mismanaging the data purely so that the application
| developers don't have to touch all that icky relational stuff
| almost always results in more work for less returns.
| sorenbs wrote:
| Prisma does not use a class hierarchy that are mapped
| directly to tables. Instead we take advantage of structural
| typing to give you a very smooth developer experience that
| is faithful to the relational model.
| jcpst wrote:
| I agree, and find the reluctance to care about SQL interesting.
|
| Sure, SQL is not perfect, and has it's flaws. But instead of
| learning an established, declarative language designed for data
| access, there are developers that would much rather learn new
| tools and libraries to avoid it. If you need to use another
| programming language for data access, you're off looking for
| another ORM. Or the next, better ORM of the year comes out.
|
| There are stored procedures at my work that are more than 20
| years old. They don't need to change when the application code
| calling it is uplifted.
|
| It's not that much different than a modern React dev not
| wanting to understand how a plain HTML form works.
| htkien wrote:
| Yeah, ORMs were marketed as "if your database changes, you
| don't have to change anything!". What about if your
| programming language changes? You have to learn an entirely
| new ORM each time. What about all of the people who have to
| read your code? Which ORM's do they know? Everyone knows SQL.
| joshmanders wrote:
| > What about if your programming language changes?
|
| Very rarely does an organization completely rewrite their
| products in completely different languages. And if they do,
| I'd hope they take the due diligence to investigate the
| tools they plan to switch to and make sure they are a good
| fit.
|
| > You have to learn an entirely new ORM each time.
|
| You have to learn an entirely new everything each time.
| ORM's aren't anything different. New language = new
| paradigm, new routing, new templating, new everything.
|
| > What about all of the people who have to read your code?
|
| What about them?
|
| > Which ORM's do they know?
|
| I'd assume that the team would have conversations to gather
| information on what ORM's everyone knows and decide which
| is best for their team. I don't see how this is an issue.
| Do you just randomly pull people into your company and just
| like, let them wander aimlessly?
|
| > Everyone knows SQL.
|
| No they don't. And if they did, a lot of stuff isn't huge
| massive complicated apps, most are basic CRUD apps where
| ORM's do all the lifting for 98% of the stuff needed done.
| A lot of us haven't touched SQL in so long that we couldn't
| construct a simple SELECT statement if we tried. I know
| because I wrote SQL from 2000-2014, but then tried to write
| a simple SELECT statement in a `pg` CLI to fetch some stuff
| and completely forgot how to do it.
| friend-monoid wrote:
| Right. Data management is a hard problem. Just look at SQL
| query planners, they are insane - but they are not perfect,
| and they only work on the premises you provide (schemas,
| indexes, memory, etc). The idea that you could introduce a
| library that fixes all data management issues for you is not
| well considered.
| havernator wrote:
| Hard disagree. If you're not leveraging the features of your
| SQL database to make your life easier, your queries faster, and
| keep your data consistent & valid, then you probably didn't
| need SQL to begin with.
|
| Which, yes, is a common state for many applications to be in.
| Wresting with SQL while using it very poorly in an effort to
| act like they're not.
|
| [edit] disagree with the quotes statement, that is, agree with
| the post. I worded that poorly.
|
| [edit edit] I've been thinking about what you get from using an
| RDBMS while avoiding writing or knowing much about SQL or using
| any of the probably-extremely-nice features of your particular
| SQL DB (why is everyone always so eager to be DB-agnostic? If
| you do it right your DB will survive, and greatly ease,
| _several_ rewrites of your application!) and I 'm coming up
| with:
|
| 1) basic locking (I assume you don't want to know how to
| actually _use_ transactions, beyond what your ORM does
| automatically, so you 're just getting the basics), and
|
| 2) some probably-badly-insufficient indices, and
|
| 3) an ORM should at least get you a little normalization if you
| just follow patterns from its docs & examples, I suppose,
| though you're gonna need to understand some SQL to get much
| benefit out of it in your DB design and in your use of the DB,
| so...
|
| An entire RDBMS seems like serious overkill if that's all
| you're really using.
| lostcolony wrote:
| I have yet to find the person who, being comfortable with both
| raw SQL and a particular ORM, to, when faced with the choice of
| learning a new ORM or using raw SQL, reach for the new ORM. To
| be honest, I haven't met in person anyone who reached for the
| familiar ORM, either, for anything complex in a team
| environment, but that's a more aggressive statement.
|
| I mention this because to my mind it reinforces my belief that
| learning an ORM (which I've done in the past, and then never
| used again) isn't worth the effort; learning SQL is, if for no
| other reason than it's portable (and you'll have to learn it
| anyway when the ORM's abstraction leaks).
| void_mint wrote:
| The answers is both. Devs cannot work on a system (involving
| "data") without knowing about that data/how it works/how it is
| structured and how to store and retrieve it. Pretending you can
| do one without the other leads to a boatload of issues in the
| medium and long term.
| hmsimha wrote:
| I have mixed feelings about Prisma. I do think it might be the
| best general-purpose ORM for relational databases in the Node
| landscape, and support for Typescript with autogenerated
| interfaces is _amazing_. But it seems to fall short in some ways
| as well (my experience with ORMs in the past is limited to
| ActiveRecord and Mongoose, both of which I haven 't touched in
| years). I've only been using Prisma for ~2 months, for an
| internal application on which I'm the sole developer, and I'll
| keep using it. But given a new project and freedom to choose,
| there's a very good chance I'd go with another ORM depending on
| the use case.
|
| One of the things that is most perplexing to me is the inability
| to autogenerate a seed file from existing data, to reseed a new
| database. This would be incredibly helpful for testing and
| working with staging data. For example, we have a staging
| database and staging server, and want to be able to reseed the
| database with some data set, but don't want to write all the data
| inserts by hand. I'm sure there are many ways to avoid doing this
| (db-specific export/import, scripting the inserts in a loop over
| a collection of arrays of values which you've curated by hand, or
| in our case just copying the sqlite db file). Still, Prisma's
| story around seeding and data fixtures in general currently
| leaves a lot to be desired.
|
| There have also been many rough edges I've run into around
| shaping results (getting category names which have been applied
| to all blog posts by a given user, for example), and applying
| schema changes, but I'm not willing to rule out user error /
| inexperience for many of these
| vsskanth wrote:
| What's the most recommended .net equivalent
| Zealotux wrote:
| How is Prisma's support (if any) of Mongo? I use it in
| combination of Postgres for prototyping and I'm wondering if they
| play well together.
| codethief wrote:
| See https://news.ycombinator.com/item?id=26888427
| CGamesPlay wrote:
| I have adopted Prisma in my latest project and I have mixed
| feelings about it.
|
| - The generated client is top-tier. Fully specified in TypeScript
| with intelligent types that can be extended by the user.
|
| - The schema language is great. It provides a cohesive experience
| that can fully express your database structure, and it also
| provides a migration framework to manage that structure's
| evolution.
|
| - There's no hook for implementing access control at the model
| level, so you end up needing to create a higher-level API around
| the base queries to implement these yourself, which is a fairly
| large investment. I'd love to see a pair of functions that can
| modify the query before it gets sent to the Prisma engine to add
| extra query values, and a second function that can filter models
| before they are returned to the caller.
|
| Where Prisma falls short is in testing. The testing story in
| Prisma is that you can either use a live database, or you can
| completely stub out all calls to Prisma in your application. The
| latter means you end up writing a bunch of tests for
| implementation rather than for behavior, or you end up manually
| writing an in-memory database that fits the Prisma API. The
| former means that you need to completely recreate your database
| after every test case.
|
| The discussions on the Github seem to indicate that the old "wrap
| the test in a transaction" trick is forbidden, and Prisma seems
| architecturally set up to ensure that you can't even hack this
| behavior in.
|
| All in all, we probably won't switch away from it, but I will
| continue to look for a better way to test my endpoints.
| matthewmueller wrote:
| Thanks for sharing your thoughts. The lack of Client
| extensibility is definitely a shortcoming right now. We're
| thinking of ways to wrap the Client a bit better. Middleware
| works is a decent workaround, but doesn't fit all these use
| cases.
|
| On testing, we're working on some documentation right now on
| how to mock the Prisma Client. I could see us generating a
| mockable client to help with this.
| agustif wrote:
| Hey totally off-topic maybe as it's not prisma-
| related/required.
|
| What do you think of using something like msw.io for mocking
| your endpoints as needed?
| kami8845 wrote:
| While depending on a live DB for testing isn't great, why not
| just blow away database state after every test by running
| TRUNCATE / DELETE against all tables? DELETE especially is very
| fast (couple ms) if you're not inserting large amounts of data
| during your test runs.
| molf wrote:
| 1) You can't run tests in parallel.
|
| 2) It is at least an order of magnitude slower than not using
| a database at all.
|
| Combined this makes for very slow tests. Certainly for larger
| applications with thousands of tests.
|
| Having said that, I don't think there is a single right
| answer to the problem of testing applications that use a
| database and your suggestion still can be a valid solution.
| pimeys wrote:
| If you run postgres, make a schema per test and do a
| cascading drop to the schema after the test. Name the
| schema either by the test or with a random string. Same
| applies for SQL Server and on MySQL you'd do this with
| databases instead.
|
| Parallelizes quite well when we test the engines at
| Prisma...
| megous wrote:
| 1) Actuallly you can, in multiple ways:
|
| - parallel sessions on one database just like multiple
| clients accessing your web app at the same time
|
| - create multiple copies of a schema and run different
| independent testusite legs on each (you just have to pass
| X-Test-Schema in the HTTP header to select on which schema
| should the web app operate)
|
| - I have an even better trick up my sleeve for testing an
| app with a real DB backend, and that's passing X-Test-
| Force-Time header that allows me to override time under
| which app and DB operates _per-request_ , so I can test
| even time dependent behavior, like timeouts, timed queues
| and events, time dependent DB queries, etc. And in general
| just have a stable predicatble timestamps in the database
| for each test run and be able to compare database states at
| the end of the test run and check the diff for
| irregularities against the previous test runs, or known
| good state.
|
| I have to patch postgresql to allow for output of now()
| current_timestamp, etc. to be overridable per session, but
| it's absolutely worth it to be able to simulate/control the
| flow of time across the whole stack.
|
| You can actually do a lot of useful stuff with schemas and
| search_paths, without disrupting connected clients, and
| without having to re-create databases, all in a
| transactional/atomic manner, that is very useful for
| testing.
| mst wrote:
| Spinning up a temporary e.g. postmaster instance for a test off
| a pre-built data directory (bonus: use zfs or btrfs or similar
| snapshots to make this even faster) is, IME, while still
| effectively "completely recreate" also both much more robust
| than wrap-in-transaction and generally not the slowest thing
| about your tests (and of course allows reliable
| parallelisation).
|
| Bit of setup effort but well worth it IME even in situations
| where your tooling isn't strongly pushing you towards the
| approach.
| gsvclass wrote:
| GraphJin is very similar but in Go. Also you can either embed the
| whole service as a library or just the GraphQL to SQL compiler.
| Another advantage of GraphJin is that the you write the queries
| in pure GraphQL not a library specific API.
|
| https://github.com/dosco/graphjin
| eloff wrote:
| I don't like ORMs, but Prisma is one of the better ones. It
| avoids a lot of shortcomings of popular ORMs. I hope they find a
| way to make it work as a company. I've been following them over
| the years from the beginning of their journey through various
| pivots.
|
| To use an ORM effectively you must learn both the API of the ORM
| and SQL. It does not absolve you from learning SQL, which I think
| was one of the unstated attractions to junior developers. Then
| you have to map between the API of the ORM and how it translates
| that to SQL under the hood. Then you have to understand how it
| handles caching and sessions and how that all works under the
| hood. This has been the source of so much complexity and so many
| bugs over the years that I no longer think the benefits are worth
| the cost. As always, there are exceptions and caveats, but I now
| believe it's better to use SQL directly than to use an ORM. In
| general there is too much complexity in all the layers of
| abstraction in software development these days, and I think the
| industry is strangely blind to the consequences of this.
| Complexity is death to software, and it should not be taken on
| lightly. The entire craft of a software engineer boils down to
| eliminating complexity and simplifying problems, the better you
| can do that, the more productive an engineer you will be.[1]
|
| [1]
| https://github.com/sqljoy/sqljoy/blob/master/docs/pages/orm....
| anticristi wrote:
| "The ORM saves us time working, but they don't save us time
| learning." Paraphrasing Joel Spolsky. Also, I hate every single
| time he is right, which at this point is daily.
| swyx wrote:
| to wit: how exactly does Prisma make money?
| eloff wrote:
| I think they don't, yet. I'm sure they have a plan for that,
| probably some kind of managed cloud service.
| dickfickling wrote:
| https://news.ycombinator.com/item?id=26888735
| eloff wrote:
| I see, their long term goal is to make and sell an
| enterprise data access layer / tooling on to of databases
| and data sources, like those used inside big tech companies
| to mediate developer access to data.
| matheusmoreira wrote:
| > I now believe it's better to use SQL directly than to use an
| ORM
|
| I reached the same conclusion. Everything is so much simpler
| that way. I ask the database for the data, the database gives
| it to me. That's the end of it.
|
| I used to like highly abstract libraries but the truth is they
| provide the lowest common denominator in features at a huge
| cost in complexity. Now I'd rather work as closely to the
| implementation as possible. I feel like I actually understand
| how stuff works now.
| ckmar wrote:
| PureORM[1] is an implemented thought experiment on what a
| "pure" object mapping library would look like - where you write
| regular native SQL and receive back properly structured
| (nested) pure business objects.
|
| It would contrast 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.
|
| [1] https://github.com/craigmichaelmartin/pure-orm
| nawgz wrote:
| > I think the industry is strangely blind to the consequences
| of this
|
| Well, I think if you're not blind to those things to some
| degree, you're stuck forever in a best-practices search, which
| I feel like more often than not ends up implying you have to
| rewrite your stack.
|
| For example, I do most of my development thru Hasura now. It
| writes insanely performant SQL queries translated directly from
| GraphQL. As a data consumer, you only have to worry about the
| shape of the data you want, and none of the implementation
| details. There is one endpoint, and you pass it queries against
| the schema it exposes. Implementation details are abstracted
| heavily here, but in a "good" separation of concerns way.
|
| I will never hand-write SQL again. Why would I? My front-ends
| can load everything in my data model expressively and with type
| safety thanks to graphql-codegen.
|
| Now imagine you read this and realize you work in a completely
| different method and this answers some of your problems. You
| may start parallel development, but you certainly can't stop
| forward progress just to evaluate your stack. So I think the
| blinders are key to progressing in the short term despite the
| productivity losses you're taking in the long term, and then
| when you phrase it like this it is surely no surprise companies
| behave like that.
| eloff wrote:
| > Well, I think if you're not blind to those things to some
| degree, you're stuck forever in a best-practices search,
| which I feel like more often than not ends up implying you
| have to rewrite your stack.
|
| Can you expand on that? The chain of reasoning is probably
| clear to you, but I can't follow it.
|
| > For example, I do most of my development thru Hasura now.
| It writes insanely performant SQL queries translated directly
| from GraphQL. As a data consumer, you only have to worry
| about the shape of the data you want, and none of the
| implementation details. There is one endpoint, and you pass
| it queries against the schema it exposes. Implementation
| details are abstracted heavily here, but in a "good"
| separation of concerns way.
|
| I like Hasura a lot, I've advocated it at a previous company
| I worked for, and after many months of meetings I got it
| approved to add to our stack - which was then never
| prioritized, but that's another story.
|
| > I will never hand-write SQL again. Why would I? My front-
| ends can load everything in my data model expressively and
| with type safety thanks to graphql-codegen.
|
| I don't think writing GraphQL is superior to writing SQL, and
| again you have to think not just in terms of GraphQL but also
| in terms of how Hasura translates that to SQL under the hood.
| I am slightly biased though, because I'm creating on a
| framework to query the db using SQL from the frontend, I'm
| curious how you think that would compare with Hasura:
| https://github.com/sqljoy/sqljoy
|
| > Now imagine you read this and realize you work in a
| completely different method and this answers some of your
| problems. You may start parallel development, but you
| certainly can't stop forward progress just to evaluate your
| stack.
|
| I'm not really sure how this connects to my argument about
| complexity, although I think the link is clear to you, can
| you expand on that?
| rendall wrote:
| I despise ORMs. I don't judge programmers very often, but
| invariably if there is an ORM, there is a fragile, tightly
| coupled, difficult-to-maintain project nearby
| jdc0589 wrote:
| currently staring at what's left in a large legacy
| rails/activerecord app figuring out what to do with. the
| suffering is real.
| fabian2k wrote:
| I do agree with the argument that you need to understand both,
| the ORM and SQL. The really annoying part is that you also need
| to understand how the ORM translates queries into SQL to some
| degree.
|
| I find ORMs very convenient and useful for simple queries, and
| especially for inserts/updates with many-to-many relations. And
| that is a very large part of a typical application. But you can
| very quickly meet the limits of an ORM with more complex
| queries, and I find it easier to just step down to SQL in those
| cases before learning the more complex parts of the ORM.
___________________________________________________________________
(page generated 2021-04-21 23:02 UTC)