4 ms·

My advice: go straight to PG. And avoid ORMs.

by cryptonector 4y ago

rco8786 4y ago
My advice: advice with no reasoning or context is not useful to anyone.
- cryptonector 4y ago
  Much much e-ink has been spilled on the subject of ORMs-good-or-bad. I shan't always reprise that.
  - rco8786 4y ago
    Literally you are the person who brought up ORMs.
    - cryptonector 4y ago
      https://news.ycombinator.com/item?id=31909806 https://news.ycombinator.com/item?id=31909806
philliphaydon 4y ago
ORMs are fine but they are not a silver bullet that means you never need to look at the database or the queries generated. I think too many people use ORMs and rely on them to do everything, then blame the database when things are slow. :(
- cryptonector 4y ago
  Devs start with ORMs because they promise to make everything easy. Then their apps grow complex enough that the ORMs get in the way. Then the devs get sad.
  - bbkane 4y ago
    Re: no ORMs - do you have any advice on how I could make this raw SQL insertion ( https://github.com/bbkane/starghaze/blob/54d255f524f8ef73384482b5513087c746e31089/format.go#L225 https://github.com/bbkane/starghaze/blob/54d255f524f8ef73384... ) more readable and less error prone? Right now I'm basically concatenating strings and hoping I don't misorder my parameters when I exec. 200 lines of this!! Am I doing raw SQL wrong?
    - cryptonector 4y ago
      > Right now I'm basically concatenating strings Well, you're using query parameters, but I think I understand what you mean. What I would do is write a stored procedure that takes whatever parameters, then I'd write PG SQL to generate bindings to that procedure for whatever language you're using (Golang here). I might write the bindings by hand if it's not a lot of work, but definitely would write a little compiler if it's a tone of work. That way you can get type-safety.
      - bbkane 4y ago
        Thank you. I'm using SQLite3 to keep deployment simple, which unfortunately doesn't support stored procedures. Do you have any other suggestions to improve this code?
        cryptonector 4y ago
        SQLite3 very much supports something like stored procedures though! Only, instead of calling them that it calls them `INSTEAD OF INSERT` triggers. So, you'd do something like: CREATE VIEW IF NOT EXISTS things AS ...; CREATE TRIGGER IF NOT EXISTS create_thing INSTEAD OF INSERT ON things FOR EACH ROW BEGIN ... END; and to invoke such a "stored procedure" you'd INSERT INTO things (...) VALUES (...), ...; or `INSERT .. SELECT ..;`. Now, with SQLite3 you don't get reflection via SQL, like you do in PG via the `pg_catalog` schema, but you do get reflection via the API. Also, because it's SQLite3, you don't get strong typing, and you get very few types, so even with reflection via the API, generating type-safe stub functions to invoke the "stored procedure" is not exactly easy or possible. FYI, SQLite3 is Turing-complete. You can loop via recursive triggers. And you can do conditionals via `WHERE` clauses on the statements in your trigger bodies (and also via `OR IGNORE` and such). EDIT: I made a post of this: https://news.ycombinator.com/item?id=31913062 https://news.ycombinator.com/item?id=31913062
- 1ark 4y ago
  If using Python, then I think Peewee strikes a perfect balance. In my pov it rather helps writing and reason about queries, which in turn you can get as query+params to use in other places such as pandas. It is neither in the way nor magic bloat.
rr888 4y ago
I like that but its the testing that makes it hard. Being able to use ORM then mock yourdatabase with sqlite or similar is very useful. You can use testcontainers but not in my firm.
- cryptonector 4y ago
  You can test with PG. Trivially.
- heurisko 4y ago
  This is the promise, but in experience nasty bugs occur when the abstraction inevitably doesn't work. If it doesn't work with an real life implementation e.g. docker test container, then I don't consider it a robust test.
  - rr888 4y ago
    Yeah one problem is my corp employer doesnt let us use docker.
    - heurisko 4y ago
      Would plain virtual machines be acceptable?
ormaaj 4y ago
You do know an ORM is a thing that is used with a database rite? Perhaps you actually meant "don't use a relational database". Either way, LOL
oh-4-fucks-sake 4y ago
In the JVM world, jOOQ is great happy-medium technology between the extremes of string SQL and full-blown ORM: - Type-safe DSL generated from your schema that looks like SQL - (If you can write the SQL, you can write it in jOOQ) - SQL-injection protection - Strong multi-dialect and vendor-specific support - Custom-type mappers - Composable queries - Compile-time, schema-aware query safety - Fine-grained transaction control - And so much more...
- gmac 4y ago
  There are various non-ORM database libraries for TypeScript too. I develop Zapatos, but Slonik and pg-typed are probably better known. https://jawj.github.io/zapatos/ https://jawj.github.io/zapatos/ https://github.com/gajus/slonik https://github.com/gajus/slonik https://pgtyped.vercel.app/ https://pgtyped.vercel.app/
heurisko 4y ago
Hibernate+Spring Data allow me to write native queries, and maps them to interfaces for me. Using an ORM doesn't mean you have to rely on behaviour that generates slow (e.g. n+1) query issues.

My advice: go straight to PG. And avoid ORMs.

rco8786 4y ago

My advice: advice with no reasoning or context is not useful to anyone.

cryptonector 4y ago
Much much e-ink has been spilled on the subject of ORMs-good-or-bad. I shan't always reprise that.
- rco8786 4y ago
  Literally you are the person who brought up ORMs.
  - cryptonector 4y ago
    https://news.ycombinator.com/item?id=31909806 https://news.ycombinator.com/item?id=31909806

philliphaydon 4y ago

ORMs are fine but they are not a silver bullet that means you never need to look at the database or the queries generated. I think too many people use ORMs and rely on them to do everything, then blame the database when things are slow. :(

cryptonector 4y ago
Devs start with ORMs because they promise to make everything easy. Then their apps grow complex enough that the ORMs get in the way. Then the devs get sad.
- bbkane 4y ago
  Re: no ORMs - do you have any advice on how I could make this raw SQL insertion ( https://github.com/bbkane/starghaze/blob/54d255f524f8ef73384482b5513087c746e31089/format.go#L225 https://github.com/bbkane/starghaze/blob/54d255f524f8ef73384... ) more readable and less error prone? Right now I'm basically concatenating strings and hoping I don't misorder my parameters when I exec. 200 lines of this!! Am I doing raw SQL wrong?
  - cryptonector 4y ago
    > Right now I'm basically concatenating strings Well, you're using query parameters, but I think I understand what you mean. What I would do is write a stored procedure that takes whatever parameters, then I'd write PG SQL to generate bindings to that procedure for whatever language you're using (Golang here). I might write the bindings by hand if it's not a lot of work, but definitely would write a little compiler if it's a tone of work. That way you can get type-safety.
    - bbkane 4y ago
      Thank you. I'm using SQLite3 to keep deployment simple, which unfortunately doesn't support stored procedures. Do you have any other suggestions to improve this code?
      - cryptonector 4y ago
        SQLite3 very much supports something like stored procedures though! Only, instead of calling them that it calls them `INSTEAD OF INSERT` triggers. So, you'd do something like: CREATE VIEW IF NOT EXISTS things AS ...; CREATE TRIGGER IF NOT EXISTS create_thing INSTEAD OF INSERT ON things FOR EACH ROW BEGIN ... END; and to invoke such a "stored procedure" you'd INSERT INTO things (...) VALUES (...), ...; or `INSERT .. SELECT ..;`. Now, with SQLite3 you don't get reflection via SQL, like you do in PG via the `pg_catalog` schema, but you do get reflection via the API. Also, because it's SQLite3, you don't get strong typing, and you get very few types, so even with reflection via the API, generating type-safe stub functions to invoke the "stored procedure" is not exactly easy or possible. FYI, SQLite3 is Turing-complete. You can loop via recursive triggers. And you can do conditionals via `WHERE` clauses on the statements in your trigger bodies (and also via `OR IGNORE` and such). EDIT: I made a post of this: https://news.ycombinator.com/item?id=31913062 https://news.ycombinator.com/item?id=31913062
1ark 4y ago
If using Python, then I think Peewee strikes a perfect balance. In my pov it rather helps writing and reason about queries, which in turn you can get as query+params to use in other places such as pandas. It is neither in the way nor magic bloat.

rr888 4y ago

I like that but its the testing that makes it hard. Being able to use ORM then mock yourdatabase with sqlite or similar is very useful. You can use testcontainers but not in my firm.

cryptonector 4y ago
You can test with PG. Trivially.
heurisko 4y ago
This is the promise, but in experience nasty bugs occur when the abstraction inevitably doesn't work. If it doesn't work with an real life implementation e.g. docker test container, then I don't consider it a robust test.
- rr888 4y ago
  Yeah one problem is my corp employer doesnt let us use docker.
  - heurisko 4y ago
    Would plain virtual machines be acceptable?

ormaaj 4y ago

You do know an ORM is a thing that is used with a database rite? Perhaps you actually meant "don't use a relational database". Either way, LOL

oh-4-fucks-sake 4y ago

In the JVM world, jOOQ is great happy-medium technology between the extremes of string SQL and full-blown ORM: - Type-safe DSL generated from your schema that looks like SQL - (If you can write the SQL, you can write it in jOOQ) - SQL-injection protection - Strong multi-dialect and vendor-specific support - Custom-type mappers - Composable queries - Compile-time, schema-aware query safety - Fine-grained transaction control - And so much more...

gmac 4y ago
There are various non-ORM database libraries for TypeScript too. I develop Zapatos, but Slonik and pg-typed are probably better known. https://jawj.github.io/zapatos/ https://jawj.github.io/zapatos/ https://github.com/gajus/slonik https://github.com/gajus/slonik https://pgtyped.vercel.app/ https://pgtyped.vercel.app/

heurisko 4y ago

Hibernate+Spring Data allow me to write native queries, and maps them to interfaces for me. Using an ORM doesn't mean you have to rely on behaviour that generates slow (e.g. n+1) query issues.