[HN Gopher] Sketch of a Post-ORM
___________________________________________________________________
Sketch of a Post-ORM
Author : Tomte
Score : 60 points
Date : 2023-06-02 13:11 UTC (9 hours ago)
(HTM) web link (borretti.me)
(TXT) w3m dump (borretti.me)
| kshahkshah wrote:
| Good tools explain the tradeoffs they make and provide escape
| hatches for when you need to sink down to the lower level (since
| all abstractions are leaky in practice).
|
| Use the escape hatches appropriately. Also - not turning on
| logging for your ORM and looking at what it generates or using an
| n+1 detector, linter, etc is the fault of the dev team, not the
| 'incompetent PM'.
|
| Also the example declarative migration doesn't really feel
| declarative to me. Seems pretty... procedural? To me declarative
| is "describe the final state desired and I will generate/execute
| the diff". In practice I find migrations need backfill + biz
| logic sprinkled in.
|
| > Migrations first, not schema first.
|
| Similarly I don't think this works in the real world either.
| Please consider the poor junior dev who checks out the code base
| for the first time and tries to run migrations and then seed
| data.
|
| The best practice has, imo, long been to load a schema file which
| represents what the db should be at the head of a branch and then
| load up your seeds. Migrations are to catch your team and live
| environments up. They occasionally get nuked from the codebase as
| everyone reconciles their environment.
|
| > And the problem with portability is it comes at the cost of
| specificity. I don't want a database access tool, I want a
| Postgres access tool. I want it to expose Postgres' power user
| features as first-class features,
|
| I don't know about Python based ORMs but ActiveRecord does this.
| RadiozRadioz wrote:
| > The best practice has, imo, long been to load a schema file
| which represents what the db should be at the head of a branch
| and then load up your seeds. Migrations are to catch your team
| and live environments up.
|
| I'm delighted to read this - I tried this method as an
| experiment in one of my projects, and I really liked it, but I
| had never seen anyone else do it. Good to hear a confirmation
| that it's worked elsewhere too.
|
| I'd like to add that it's also advantageous when spinning up
| DBs in Docker; you can mount your "head" schema files right
| into initdb.d and have them execute when the container runs.
| raman162 wrote:
| Yes I thought ActiveRecord's ORM addressed a lot of the pain
| the OP mentioned with traditional ORM's.
|
| ActiveRecord is also the first and only ORM I've used
| extensively and I've been a fan of it. Prior to that, I used to
| stick to raw sql, most apps were smaller but even then it felt
| like a drain to write repetitive sql to find, insert and update
| records.
| cryptonector wrote:
| > > Migrations first, not schema first.
|
| > ...
|
| TFA says this in the context of ORMs. But in the context of
| not-ORMs I think migration-first == schema-first: just use
| `CREATE .. IF NOT EXISTS`, `ALTER ..`, etc. to set up the
| schema from the get-got, and you'll think about and cover
| migrations as you go. The problem with ORMs is that they hide
| the SQL bits and so if they don't get this right then you'll
| suffer.
| llimllib wrote:
| > They occasionally get nuked from the codebase as everyone
| reconciles their environment.
|
| Yes, and also migrations really have to be expressed as code,
| and you don't want to have to maintain your old functions
| forever because they are tied to a migration; alternately you
| don't want to always have to be refactoring your migrations,
| which is a dangerous and thankless task
| jeremyjh wrote:
| Or just avoid all use of application functions in your
| database migrations, and you never have to worry about either
| problem.
| cryptonector wrote:
| > The next problem is type-checking disappears at the query
| boundary.
|
| This is a limitation of current tools. It doesn't have to be that
| way. The encoding of result row sets could elide type information
| and then the parser on the receiving end could just know the
| types from knowledge of the query that produced the result being
| parsed. If the application and the RDBMS were colocated then
| serialization could be avoided, though the price to be paid for
| that is that the application would have to use DB types.
|
| > Migrations should not be written in SQL but in some parseable,
| declarative format like JSON or YAML.
|
| > Why? Because to typecheck queries, you need to know what the
| schema looks like. To know what the schema looks like, you can
| either query the live database (it's arguable whether this is
| good), or build a virtual model of that schema. I prefer static
| solutions.
|
| Nothing stops you from compiling a schema from a spec, or vice-
| versa even.
|
| I've used PostgreSQL's `COMMENT` feature to set JSON-coded
| comments, then I have a SQL query and jq-using script that
| produces a very nice JSON representation of the entire schema a)
| using all the metadata about the schema that PG itself makes
| available in the `pg_catalog`, b) enriched with the JSON
| `COMMENT`s embedded directly in the resulting JSON. Such
| `COMMENTs` in my case have UI-related metadata such that the
| unified schema JSON can be used to drive UI codegen. No need to
| live-query the DB once this JSON has been compiled unless you
| make schema changes.
|
| > If the database schema/migrations are defined in a declarative
| format, and the query language is some type-checked compile-to-
| SQL language, the database access tool can easily typecheck the
| queries separately from your codebase, and generate code to:
| [...]
|
| You can do it the other way around too (see above), but only if
| SQL's DDL is augmented. PostgreSQL's `COMMENT` allows for user-
| defined augmentation -- it's only not great because comments are
| free-form text, whereas a `JSONCOMEMNT` or alike that enforces
| valid JSON would be better.
|
| Everything TFA says about NoSQLs resonates.
|
| > I want strong and static types. I want queries I can typecheck
| statically, before executing them.
|
| Yes.
|
| > SQL is bad, for two reasons:
|
| > The syntax is bad.
|
| > Type checking is absent.
|
| SQL itself does not preclude static type-checking. PG even has
| the appearance of static type-checking, except it's done at run-
| time and is a bit leaky -- but this is an implementation problem,
| not a language problem.
|
| Not that SQL is a great language. Linq is clearly a superior
| idea, except that it dispenses with a language as such, so it's
| not quite a superior idea :)
|
| > > _Why can't SQLx just look at my database schema /migrations
| and parse the SQL itself?_
|
| PG nowadays makes an AST of queries available. I don't recall
| what the stability of that AST's schema is, but it is possible to
| do this. It's still complicated, but so is writing a compiler,
| and writing a compiler is what we're talking about here.
|
| > Maybe someone can work out a way to do stored procedures that
| isn't a huge liability with regards to migration and deployment.
|
| "Be more careful"
|
| Now for my opinion:
|
| - SQL is still evolving, and so is PostgreSQL -- we can expect
| better type-checking and better schema augmentation in the
| future, and we should demand it
|
| - PostgREST and similar make it very easy to build pure-SQL (or
| pure-PlPgSQL or similar extended SQLs) applications without a
| thought of ORMs, and without applications really having to know
| anything about SQL -- VIEWs, table-valued functions,
| JSON/whatever schema compiled from the SQL, etc. are enough to
| make the application SQL-unaware
|
| - therefore I think the post-ORM future is PostgREST-like front-
| ends for RDBMSes.
| al2o3cr wrote:
| TL;DR - "post-ORM" is to ORMs what "post-rock" is to rock music
|
| Same sounds, same patterns, but the performers are 100% more
| convinced of the uniquely genius nature of THEIR power-chords.
| linkdd wrote:
| ORMs (especially the Django ORM) fulfill 95% of my needs. For the
| 5%, I use raw SQL via the ORM's escape hatch (which Django and
| SQLAlchemy provides).
|
| To be fair, my needs aren't that complex, I do mostly CRUD on
| database tables, most of the business logic is handled in my
| code, eventually wrapped in a `@transaction.atomic` (thank you
| Django).
|
| If I need stored procedures, I'll add a Django migration which
| creates the said procedure with raw SQL.
|
| If I need to manage the schema myself, I'll also make a Django
| migration with raw SQL, then I'll create Django unmanaged models.
|
| For the portability across RDBMS, it's nice to have an SQLite
| database in the dev environment, so that the developer does not
| need to run a PostgreSQL instance (in docker or whatever). And
| for the test suite, I even use an in-memory SQLite database.
|
| I do put my ORM queries in a specific module which provides a
| higher level API, I will have functions like
| `get_users_sent_invites` or `publish_article` etc... so that the
| rest of my code never sees database code. A function
| `get_user_by_id` will return the User model or None, and handle
| the ORM's potential exceptions to return meaningful errors to the
| business logic.
|
| It also makes database access easier to test and benchmark. You
| might call this DAO or not, terms are irrelevant, it's just good
| practice to separate concerns IMHO.
| eximius wrote:
| What I want: PRQL (which they do eventually mention at the very
| bottom) + a database that supports something like protobuf
| defined tables.
|
| I'm half convinced that after my current gig I'll do a startup
| based on that idea. I just want it to exist!
| stcg wrote:
| To me this looks a lot like ecto https://github.com/elixir-
| ecto/ecto
|
| Is there a significant difference?
| samtho wrote:
| For years now, I've been using a query builder that supports
| migrations and basic, non-instanced "model" functions that
| represent the cases of fetching and modifying data as required by
| my application.
|
| This allows me to have as much to data access as I need in easy-
| to-stub methods, without an overly opinionated ORM stepping in.
| The query builder does its job to simply build queries I ask of
| it and I don't have to worry about a gigantic piece of code
| liability. I really tend to avoid using a library for everything
| these days and using libraries that do far too much (and if I do,
| I limit exposure by wrapping calls to it in functions I create).
| mvdtnz wrote:
| Just write the damn SQL. I can't believe this stupid conversation
| is still going on.
| Xeoncross wrote:
| The future isn't writing query handling code by hand or using
| ORM's. The future is reflection or code generation tools like
| https://sqlc.dev
|
| Whether you use traditional queries or ORM's you still have to
| write all the model query code by hand in traditional systems.
| Projects like sqlc generate all the shapes/objects, validation,
| and everything else for you.
|
| Simply define all your SQL queries in an easy to audit file and
| sqlc generates the entire model/store package for you. doesn't
| matter what language the generated code is in, as long as you can
| all read SQL you can understand the app. Then just plug the
| generated package into your HTTP API or GraphQL revolvers.
|
| Try it out: https://play.sqlc.dev/
|
| This reminds me of the benefits that OpenAPI brings. A lot of
| teams are still writing client libraries by hand for each
| language instead of using an OpenAPI/Swagger spec.
| Dowwie wrote:
| > Migrations first, not schema first. > Migrations are specified
| in a declarative format, not in SQL.
|
| Strongly disagree about this. Consider how Terraform works. You
| declare what the end state should look like and it decides what
| the "migrations" should be to get you there (the plan). With a
| relational DB, we would declare the end-state DDL and execute a
| "plan" that will derive the migration for you and, preferably,
| derive corresponding types in whatever language you are using.
| seabrookmx wrote:
| SQL Server Data Tools (SSDT) does this. It's the biggest thing
| I've missed after moving to postgres.
| mixedCase wrote:
| The problem is that when you go beyond DDL, or you want to
| execute backwards incompatible DDL changes, you can no longer
| make these migrations declaratively.
| rco8786 wrote:
| I was kind of onboard until I realized there is code generation
| steps required for every DB query you want to perform. That would
| be a dealbreaker for me.
| oweiler wrote:
| Or use JOOQ today.
| agedcayenne wrote:
| I feel like SQLAlchemy is the elephant in the room here that
| addresses most of his perceived shortcomings with ORMs.
| hobo_mark wrote:
| I tried to learn SQLAlchemy to avoid writing explicit SQL, but
| except for the most trivial relationships it requires me to
| write several times more code in an arcane DSL to accomplish
| the same thing, at which point, why bother?
| nicwolff wrote:
| And SQLAlchemy Core, among many other SQL generators, gives the
| lie to "The state of things is bimodal: you either write raw
| SQL, or you use an ORM."
| [deleted]
| BulgarianIdiot wrote:
| Regarding "SQL is not typed": as a matter of fact you can always
| type-check your queries against a transaction. If it fails,
| there's your syntax and type error right there. If it doesn't
| fail, then it either reads, or also modifies the database. But
| when you are _in a transaction_ , you can simply not commit the
| transaction. You're always isolated. You can always check your
| SQL, safely.
|
| BTW the author doesn't know what the word "liability" means.
|
| As for Post-ORM, like ORM it treats the DB as a hidden
| implementation detail, which means no free interaction with
| existing DBs and uneasy solutions when something isn't in your
| abstraction, but it's trivial in the database itself. This
| approach has its place, but it doesn't seem like Post-ORM as much
| as a continuation of what ORM did in the first place, including
| many of the problems following.
| codr7 wrote:
| Yeah, so I don't agree with much of this.
|
| I usually end up rolling my own abstraction layer on top of SQL
| in whatever language I'm using, and have been doing so for 25
| years now; it's not that much code if you only write what you
| need.
|
| First of all, it makes the same class=table assumption that's
| hurting every ORM I've come across. This only scratches the
| surface of what a relational database can do. You want support
| for records that may contain arbitrary number of columns from
| different tables on some level.
|
| Secondly, while I agree that migrations should be first class, I
| also want support for them (and DDL) in the host language. My
| last project was based on event sourcing; which meant that to be
| able to replay the event stream reliably, migrations had to
| create events.
|
| First class queries is another thing that I've never seen out in
| the wild. It's not that complicated to add a composable layer on
| top of SQL to generate arbitrary queries.
|
| And while there are certainly differences between databases and
| SQL dialects, I've been involved in 2Mloc projects that ran on
| several different databases with a thin compatibility layer in
| between. It's perfectly doable.
| hparadiz wrote:
| My abstraction layer over SQL turned into an ORM over time.
| Same pattern.
| karmakaze wrote:
| > First class queries is another thing that I've never seen out
| in the wild. It's not that complicated to add a composable
| layer on top of SQL to generate arbitrary queries.
|
| I'm interpreting the post as wanting to do just that with
| static typing and calling it an ORM.
|
| From TFA
|
| > I want a query language that's better than SQL. Specifically,
| it has to be 1) composable, 2) statically typed and with 3) a
| sane syntax. I want sum types.
|
| I want a statically-typed way of constructing composable
| queries that follow SQL rather than reinvent a different thing.
| It doesn't have to be the same syntax but it has to be the same
| structuring.
|
| I started writing one[0] and stopped before doing all the
| boilerplate code generation, having moved on from the JVM
| ecosystem for the time being. One thing it does is treat most
| things like sets so we don't end up with N+1 queries. Another
| trick it uses is collapsing constant expressions via an
| expression evaluation library[1].
|
| [0] https://github.com/karmakaze/safeql
|
| [1] https://github.com/karmakaze/moja
| ndriscoll wrote:
| The Scala ecosystem has a few ways to do composable type-safe
| query building, e.g. Slick[0] or more recently Quill[1]. I
| believe both also have ways to do compile-time string
| interpolation (e.g. sql"""select * from users where id =
| ${user.id}""".as[User]) which generate prepared statements (I
| know Slick does prepared statements. Quill appears to have a
| similar interpolator).
|
| [0] https://scala-slick.org/
|
| [1] https://zio.dev/zio-quill/
| dkarl wrote:
| The author should check out Quill[1] as prior art. Compile-time
| query generation where you can see the generated query just by
| hovering over the code is _chef 's kiss_.
|
| One of the terrible things about most ORMs is that you can't see
| the queries when you look at the code. There's usually some
| awkward hoop you have to jump through to see it, like writing a
| unit test and logging the query from it, and Quill bypasses all
| of that.
|
| [1] https://getquill.io/
|
| Overall I'd say that this post does a better job of presenting
| the trade-offs than most. Some of the statements ring false to
| me, but I won't bother picking nits.
|
| > Portable Across Languages
|
| This is interesting, because what you're proposing in this
| section is a better SQL.
|
| I don't think that's a bad idea. A relational DDL and query
| language that is optimized for integrating with modern
| programming languages might be enough of a step forward to
| actually displace SQL. However, implementing it as a middle layer
| between SQL and a programming language doesn't seem like the way
| to go, because it adds an extra layer that programmers have to
| understand. Make no mistake, people will encounter problems even
| if the tooling is perfect, because their own understanding won't
| be perfect, and they will have to go on debugging odysseys to fix
| their understanding, by tracing down through the layers from
| their code to SQL. Now you're adding a language that is just as
| powerful as SQL, maybe more powerful, right in the middle. I
| would never in a million years use a tool architected like that.
| The potential for getting bogged down in the details of how the
| different layers translate is too high.
|
| This is why after all these years of ORMS and other database
| libraries people often still embed SQL in strings in their code.
| It isn't (always) because they're stupid or stubborn. It's
| because they know that embedding literal SQL puts a hard ceiling
| on an entire dimension of debugging.
|
| Painfully often, when I ask somebody, "I think there's an error
| in this code that queries the database. What SQL does it
| generate?" they reply, "Oh, give me a second, I have a Datadog
| trace that shows it, I just have to find that link," or, "Let me
| run this unit test that logs it." Or, even more depressingly,
| they haven't even seen the SQL yet, because there were other
| things that were easier to investigate, so they investigated the
| easy things first even though they suspected the query was the
| culprit, like the proverbial drunk looking for his keys under the
| streetlight instead of where he thought he dropped them.
|
| Introducing another equally powerful conceptual layer between
| code and SQL is a step in the wrong direction. Replacing SQL with
| a better query language that integrates better with code would be
| great. It will happen someday; fingers crossed it will happen in
| our lifetime.
| ltbarcly3 wrote:
| This is just not liking SQL, which is fine, but it's not 'the
| future of querying databases'. It's just a simplified language
| that is under-specified and doesn't really support even a
| fraction of the use cases of SQL, and therefore looks 'cleaner'.
| It's a bad abstraction.
|
| Here is my attempt at a 'post orm' if anyone is interested, as a
| bonus it is fully implemented and some people actually use it:
| https://github.com/justinvanwinkle/Norm
| pphysch wrote:
| You completely lost me at the sum types part. Humanity needs sum
| types... so that we can generate offensively denormalized RDBMS
| schemas? Echos of OOP-mania.
|
| However, I strongly agree about "pointless portability". 'Django'
| would be perfect if it just focused on Postgres. Cut the call
| stack in half and add more power+perf. Point SQLite users to
| SQLAlchemy, and forget about the others.
| runako wrote:
| > You have omnipresent performance problems. There are n+1
| queries everywhere, but where specifically? You don't know. It's
| impossible to statically determine where a specific query is
| happening. You have to instrument at runtime, which is rarely
| done rigorously or uniformly, tracing every call and staring at
| logs until you find your performance problems.
|
| One of the tools I've really come to enjoy in our modern age is
| telemetry tools that are designed to solve these kinds of
| problems. If one finds oneself tasked with identifying the
| sources of omnipresent database-related performance problems and
| one is not in a position to rewrite the database access layer for
| the project(!), I find tools like New Relic to be incredibly
| helpful. For some setups, they even let you identify the line of
| code that produces slow queries. Those can then be optimized
| either within or outside of the ORM.
|
| Having pitched both options, I have also found that "let's
| install this tool, which may be free for our use" to be an easier
| sell than "let's rewrite the database layer."
|
| I honestly found myself tuning out of the essay after the intro
| exhibited ignorance of modern tooling built to address the
| specific issues under consideration in the essay.
|
| IMHO it still makes sense to use an ORM to build quickly, given
| that we have tools to help effectively profile downstream. This
| is the approach we take for code (by using a language like Python
| and not C/Rust), and the tooling around databases makes the same
| approach viable for db code.
| dventimi wrote:
| Well written article, but this is HN so naturally I have a, "Why
| don't you just..." You know your situation better than anyone
| else, so take it with a grain of salt.
|
| But, why don't you just not use a general-purpose language? No
| really. Hear me out.
|
| It seems the main problem is the proverbial impedance mismatch
| between SQL and say, Perl or Python or whatever. Ok. One way to
| remove that mismatch is to remove the boundary altogether. Remove
| the general-purpose language and really embrace the "just use
| SQL" path. That may have drawbacks and we can talk about those or
| we could not, but one thing it certainly would do is remove the
| problem of the impedance mismatch.
| cryptonector wrote:
| "Write your entire app in SQL" is a perfectly sensible thing to
| say and do. I've done it. PostgREST and related projects make
| it real easy, and then you don't have to wish you could have
| sum types in SQL like TFA does.
| TheFlyingFish wrote:
| As in, write your entire application in SQL? That sounds like a
| nightmare of epic proportions. Every time I've tried to use SQL
| for anything more general-purpose (like string manipulation,
| say) I've wanted to gouge my eyes out by the end.
| dventimi wrote:
| Yes. Write your entire application in SQL, IF it's the kind
| of application for which this can be done. Is the application
| a flight simulator or a game engine or resource manager or a
| distributed task scheduler? No, that's probably not a good
| case for SQL. Neither would be a machine learning application
| that relies heavily on Python packages, and it's easy to
| think of other examples as well. But, is the application
| responding to user input to query and display data, perform
| transformations over those data, impose constraints and
| invariants over those data, and validate user input against
| those data? Those are tasks for which relational databases
| with SQL (and other kinds of databases as well, but here
| we're talking about SQL) are tailor-made. What then does,
| say, Python bring to the party?
| TheFlyingFish wrote:
| The obvious thought that occurs to me is UI - how are users
| going to access this hypothetical application? If it's via
| the web, then you'll need a whole setup for generating
| HTML, and doing that in SQL sounds extremely painful. If
| it's a native app, then you've got to interact with the OS
| somehow to do things like draw to the screen, and I've
| never even heard of trying to do that from SQL.
|
| More broadly, this just seems like it would sharply limit
| how you could extend the application in the future. What if
| at some point you want to query a web API for some
| additional data with which to enrich what you're returning
| from your database? (Not an unusual situation, in my
| experience.) You'd be stuck trying to make web requests
| from SQL, which again seems needlessly painful.
|
| Note that I'm not against the basic idea of "do as much
| data-munging in SQL as possible" - in my experience that's
| a great way to ensure that your application stays fast and
| efficient. It' just all the ancillary things _surrounding_
| the data-munging for which I don 't think SQL is the best
| fit.
| cryptonector wrote:
| > The obvious thought that occurs to me is UI - how are
| users going to access this hypothetical application? If
| it's via the web, then you'll need a whole setup for
| generating HTML, and doing that in SQL sounds extremely
| painful. If it's a native app, then you've got to
| interact with the OS somehow to do things like draw to
| the screen, and I've never even heard of trying to do
| that from SQL.
|
| Generating HTML?? No, generate UI declarations from the
| SQL schema (enriched with extra metadata) then interpret
| those in JS in minimal static pages that use JS to talk
| to PostgREST.
|
| PG has a `COMMENT` statement that can be used to attach
| commentary to every single schema element -- tables,
| columns, views, indices, etc., all can have free-form
| commentary. Use JSON COMMENTs and then extract the whole
| schema using the pg_catalog as one big JSON blob, then
| post-process to generate UIs. See below for links.
| dventimi wrote:
| The OP didn't write about a UI and we don't know what
| their needs are, so for all we know they're writing a
| Python-based back-end API in REST or GraphQL which is
| being consumed by a mobile UI or a SPA UI in React or
| Vue.js or whatever, such that that back-end API doesn't
| have to provide a UI. If the existing Python back-end
| doesn't provide a UI then any proposed substitute--
| including one in SQL--shouldn't have to provide a UI
| either, just an API. In that case, there are some ready-
| made solutions already available:
|
| - PostgREST (https://postgrest.org/): REST API for
| PostgreSQL
|
| - PostGraphile (https://www.graphile.org/) GraphQL API
| for PostgreSQL
|
| - pg_graphql (https://github.com/supabase/pg_graphql)
| GraphQL API for PostgreSQL
|
| - Hasura (https://hasura.io/) GraphQL API for various
| databases
|
| If you want to blend data from a web API and you're
| content with GraphQL, for some use-cases (not all, but
| some), there are options:
|
| - Apollo Federation
| (https://www.apollographql.com/apollo-federation/)
|
| - GraphQL Mesh (https://the-guild.dev/graphql/mesh)
|
| - Hasura Remote Schema
| (https://hasura.io/blog/tagged/remote-schemas/)
|
| If you want more control over the web API and you were
| going to fetch the data within your Python back-end and
| process it there, for some use-cases (not all, but some),
| there are options:
|
| - pg_http (https://github.com/pramsey/pgsql-http)
|
| Life is about trade-offs. Doing the work in SQL is not
| without its drawbacks, but it's also not without its
| benefits, and that's true for doing the work in a
| general-purpose language as well. Whatever the drawbacks
| of doing it in SQL, one of the benefits has got to be
| eliminating the impedance mismatch (for people who regard
| that mismatch as a problem, and the OP seems to be one
| such person). What I claim is that doing the work
| directly in the database shouldn't be ruled out in
| general (the specifics of a given use-case may rule it
| out in particular) any more than the other common
| patterns (API hand-written in Python, for instance)
| shouldn't be ruled out in general.
| cryptonector wrote:
| > The OP didn't write about a UI and we don't know what
| their needs are, [...]
|
| > What I claim is that doing the work directly in the
| database shouldn't be ruled out in general (the specifics
| of a given use-case may rule it out in particular) any
| more than the other common patterns (API hand-written in
| Python, for instance) shouldn't be ruled out in general.
|
| Hear hear.
|
| With PG's COMMENT feature one can enrich SQL schema with
| the sorts of metadata one needs for UI generation.
|
| Here's a script that generates a JSON view of a PG SQL
| schema enriched with JSON from COMMENTs:
| https://github.com/twosigma/postgresql-
| contrib/blob/master/s... and
| https://github.com/twosigma/postgresql-
| contrib/blob/master/s...
| paulddraper wrote:
| > SQL is bad, for two reasons:
|
| > 1. The syntax is bad.
|
| In this context, "bad" means "hard for a computer to parse." And
| this statement is 100% true.
|
| Though from a human perspective, the syntax is relatively fluent.
|
| Which would would you (as a human) prefer for writing raw ad-hoc
| queries? SQL or MongoDB? For me, it's no contest the former.
|
| > 2. Type checking is absent.
|
| ?
|
| PostgreSQL complains at me if my types are wrong.
|
| E.g. if I try to sum two strings.
| WesolyKubeczek wrote:
| It's hard to compose (you could call it metaprogramming maybe,
| since you're writing SQL programs with a program), too.
|
| You know the problem: take this query but with this extra table
| joined and this other condition in the WHERE clause.
|
| Solutions exist, but they mostly are married to ORMs, thus the
| frustration. But I'm confident that "either raw paleolithic SQL
| or clunky ORMs" is a false dichotomy (and I've written a thing
| in PHP over a decade ago to scratch my own itch and compose
| SQL, so I just know for a fact how false it is).
| lalaithion wrote:
| The author mentions prql as an alternative to sql in the post.
| I personally find prql much more fluent than sql.
| hobo_mark wrote:
| How is SQL hard for a computer to parse? SQLite can parse its
| flavour of SQL using a plain old LALR grammar and that covers a
| lot of SQL.
|
| And even if it was, how does having an ORM generate the SQL
| behind your back change anything for the computer that still
| has to parse SQL?
| paulddraper wrote:
| I'm not sure about SQLite, but try looking for "PostgreSQL
| parser".
|
| It's far from simple.
| tracker1 wrote:
| It's not so much the parsing... it's the validation against
| an underlying data source. I can write `SELECT A, B from Foo`
| and it's "valid SQL" but that doesn't mean the underlying Foo
| table exists, or that the columns are there. Beyond this,
| that doesn't mean it understands that A should map to an
| Int32 or that B should be a UTF-8 byte array in the outer
| language I'm working with.
|
| Personally, I'm fine with the likes of type mappers like what
| Dapper in C# does, or serde_postgres offers in Rust. For that
| matter template string processors in JS/Node/Deno are also
| really nice. Yeah, you need to understand the underlying data
| source, but that's often far easier than massive amounts of
| boilerplate.
| actuallyalys wrote:
| I suspect what is really meant is that it is time consuming
| to write a parser that encompasses all of its syntax, not
| that it is performance intensive or is high on the Chomsky
| hierarchy.
| hobo_mark wrote:
| But, at least for SQLite, the parser is autogenerated from
| a plain grammar.
| zackb wrote:
| I found JDBi[1] to be a really nice balance between ORM and raw
| SQL. It gives me the flexibility I need but takes care of a lot
| of the boilerplate. It's almost like a third category.
|
| 1. http://jdbi.org
| stevebmark wrote:
| For my money there isn't a better tool than Prisma right now.
| It's a truly fantastic database experience. It's significantly
| better than TypeORM and/or Knex, and of course better than the
| unfiltered nightmare of ActiveRecord. SQLc for Golang isn't a
| great library, but it has the right overall idea: stay close to
| SQL while still getting language typings, and codegen. I think
| everyone should learn from the Prisma developer experience, it's
| the best of all worlds: static typing, codegen, seamless database
| interface, while still having as much granular control as you
| want.
| Glench wrote:
| > unfiltered nightmare of ActiveRecord
|
| Huh, I've only heard good things about ActiveRecord. Care to
| elaborate?
| stevebmark wrote:
| The entire article is about problems with ActiveRecord.
| rlili wrote:
| I agree.
|
| However, a tool like Prisma only seems possible (with all its
| benefits) within Typescript, which is able to compute arbitrary
| types dynamically.
| stevebmark wrote:
| I don't entirely follow, because the Prisma client is static
| code generated from your database schema. There's nothing
| dynamic about it, unless you're referring to generics?
| cloogshicer wrote:
| Great article, thank you!
| develatio wrote:
| > Performance: the generated SQL is often badly optimized.
|
| I can't but disagree completely with this statement. There might
| be an edge case in which a raw query, tailored to a specific need
| or a particular use case, might be faster, but generally speaking
| ORMs will know much much better than the average developer how to
| optimise queries.
|
| They will also know exactly what DB features to use to leverage
| extra features (for example, Django's F() functions).
|
| Edit: I'm totally amused that the author didn't even mention the
| fact that ORMs will go ways to prevent you from shooting yourself
| in the foot (from a security stand point). ORMs have made SQLi so
| much less common than it used to be. Of course, there still might
| be a bug here and there which will lead to an SQLi, but it's like
| day and night compared to how it used to be before, when average
| Joe would just slap a bunch of queries in a "queries.php" and
| call it a day.
|
| Edit 2:
|
| > I think migrations should come first. You should write
| migrations as separate files
|
| Why? Why would you want any of that? The entire point of having
| the ORM do its thing is so that you (the developer) can focus on
| the code itself (the models, in this case). Modifying the models
| to adapt them to the requested changes is something that you'll
| do anyways, as that's your job. Then the ORM generates the
| migrations between each modification you did in the code, which
| leads you to not having to think or even care about how to apply
| these changes to the database. Why would you want it any
| different? We've literally reached the point where the code
| itself can represent "what the database should look like", and
| the changes to that code represent (via migrations generated by
| the ORM) "how the database should be changed in order to adapt to
| the new code".
| SanderNL wrote:
| I literally never saw an ORM output anything approaching
| optimized.
|
| It's mostly.. adequate, which is fine. I guess. It depends.
|
| Also have a bit of a history with these "magic migrations". It
| doesn't come close to "we have reached the point", it's more
| like "in some circumstances and under extremely strict
| conditions these features _may_ provide some benefit and will
| keep working over the years".
| oweiler wrote:
| > There might be an edge case in which a raw query, tailored to
| a specific need or a particular use case, might be faster, but
| generally speaking ORMs will know much much better than the
| average developer how to optimise queries.
|
| This statement is wrong. ORMs can't use DB specific features,
| so they have to generate lowest common denominator SQL.
|
| With Postgres e.g. I can insert an entry and return the
| generated ID in a single query. ORMs can't do that.
|
| I can also upsert entries in a single statement. ORMs can't do
| that.
| hparadiz wrote:
| Laravel's Eloquent does upserts no problem. Please actually
| do some research.
| matthewmacleod wrote:
| This is absolutely not the case. In fact I've never used an
| ORM myself that generates SQL directly - they all generate
| some kind of abstract model then render that to SQL using a
| database-specific adapter.
|
| Specifically, Rails' ActiveRecord will transform e.g.
| Customer.create(name: 'test')
|
| into the following when using Postgres INSERT
| INTO "customers" ("name") VALUES ('test') RETURNING "id";
|
| Things like Customer.upsert_all([{name:
| 'test'}])
|
| will also work to perform upserts.
|
| In practice, ORMs are more advanced than you might think, and
| can be a useful tool in some cases!
| develatio wrote:
| > ORMs can't use DB specific features
|
| This is technically false. There is no technical reason an
| ORM couldn't use DB-specific features. It's also practically
| false. Have a look at
| https://docs.djangoproject.com/en/4.2/ref/databases/
|
| Django's ORM will do quite clever stuff, specific to your
| database (eg, if you're using Postgres, it will create
| additional indexes using appropriate PSQL operator classes).
|
| > I can insert an entry and return the generated ID in a
| single query. ORMs can't do that
|
| This is also false. Calling .save() on a model will issue an
| INSERT statement, then the ORM will assign the auto-
| incremented PK to the "id" property of the model without
| issuing any further queries. https://docs.djangoproject.com/e
| n/dev/ref/models/instances/?...
|
| > I can also upsert entries in a single statement. ORMs can't
| do that.
|
| This is also technically and practically false. There is no
| technical reason an ORM couldn't do an upsert with a single
| query (granted that the database itself supports it).
| Practically, while Django's ORM itself can't do this, you
| could easily use django-postgres-extras (which is just an
| extension to the ORM's capabilities) to issue an "ON CONFLICT
| target", which will achieve an upsert with a single query:
| https://django-postgres-
| extra.readthedocs.io/en/master/confl...
| raman162 wrote:
| I think making schema changes via migrations would be explicit
| and should lead to it being predictable. Maybe the best of both
| worlds is the ORM spitting out what it thinks the migration
| file should be based on the database schema and the new model
| changes?
|
| The migration process then runs the actions in the migration
| file. If it fails in new environments this is telling that
| something is different in that environment and needs to be
| investigated.
| develatio wrote:
| This is how it works. ORMs generate migration files
| automatically.
| kevinmgranger wrote:
| Not strictly relevant, but:
|
| I've yet to see tools that handle zero-downtime-migrations well.
| The manual task of keeping two versions of the same query / model
| alive (version N and version N+1) is... well, how else would you
| handle it?
|
| While tools that can generate the model or schema are nice, they
| never seem to account for this.
|
| Is there some alternative approach I'm missing?
| efxhoy wrote:
| Have your query as a function in the database. BEGIN, do the
| migration, replace the function, COMMIT. Postgres DDL is
| transactional so at no point does the function stop working.
___________________________________________________________________
(page generated 2023-06-02 23:02 UTC)