4 ms·

database as API works fine if you have properly abstracted things with sprocs and views. It will be also far less brittle than 100 services exposed as GraphQL

by qaq 2y ago

Kwpolska 2y ago
Stored procedures just add overhead and make everyone's lives harder. Forget about any ORMs, you're writing raw SQL with all the quirks of PL/pgSQL biting you all the time.
- lelanthran 2y ago
  There's advantages too: - marking some columns as NOT NULL. - referential integrity means you can't accidentally have dangling pointers to non-existant dat. - mutually exclusive columns let's the database enforce things like "at least one of A and B needs to be Nonzero, and both cannot be Nonzero at the same time. - create a type that allows only values matching a specific regex. Seriously, if you want strong typing across composite data, there isn't a language invented yet that comes even close to a RDBMS. Of course the majority of Devs don't know any of this because their ORM doesn't expose any of this; it gives them a way to store and query tabular data, and nothing else.
  - pdimitar 2y ago
    None of this prevents you from doing both inside the DB _and_ the app. That way you cover your bases that (1) your app is sound and has maximum amount of fail-early validations to avoid corrupted data states and (2) even if somebody decides to skip the app and try to be clever in a psql console they'll still not be able introduce corrupted data states and (3) leave the door open for other apps to be able to connect to the same DB and do stuff (or simply to allow for a rewrite in another language).
  - Kwpolska 2y ago
    The things you listed aren’t stored procedures, they are all possible to implement as check constraints. They are great, and they are fully compatible with ORMs. A stored procedure is a bit of code, stored in and executed by the database, usually written in a 1960s-era language (like PL/SQL or PL/pgSQL).
    - lelanthran 2y ago
      > The things you listed aren’t stored procedures, they are all possible to implement as check constraints. They are great, and they are fully compatible with ORMs. I didn't claim that they are not compatible with ORMS. I said the majority of developers have no clue just how much of value they can get out of their database using types and constraints because the only interface they have every used to the RDBMS is the ORM, and the ORM doesn't expose any of this. I've commonly seen developers put in things like `if ((!A && B) || (A && !B)) { /* updateDbWithOneOf(A,B) */ }` in their code rather than use the constraints provided by the RDBMS.
- qaq 2y ago
  Well for starters they can improve your security posture. In proper dbs like PG the version change is transactional so you don't have to deal with schema being out of sync with code. You don't have to plan for all the possible future scenarios where you will need transaction boundary to cross the service boundaries. You can write stored procedures in pretty much any lang. Query optimisers and execution engines are far more tested and preferment vs some GraphQL gateway.

database as API works fine if you have properly abstracted things with sprocs and views. It will be also far less brittle than 100 services exposed as GraphQL

by qaq 2y ago

database as API works fine if you have properly abstracted things with sprocs and views. It will be also far less brittle than 100 services exposed as GraphQL

Kwpolska 2y ago

Stored procedures just add overhead and make everyone's lives harder. Forget about any ORMs, you're writing raw SQL with all the quirks of PL/pgSQL biting you all the time.

lelanthran 2y ago
There's advantages too: - marking some columns as NOT NULL. - referential integrity means you can't accidentally have dangling pointers to non-existant dat. - mutually exclusive columns let's the database enforce things like "at least one of A and B needs to be Nonzero, and both cannot be Nonzero at the same time. - create a type that allows only values matching a specific regex. Seriously, if you want strong typing across composite data, there isn't a language invented yet that comes even close to a RDBMS. Of course the majority of Devs don't know any of this because their ORM doesn't expose any of this; it gives them a way to store and query tabular data, and nothing else.
- pdimitar 2y ago
  None of this prevents you from doing both inside the DB _and_ the app. That way you cover your bases that (1) your app is sound and has maximum amount of fail-early validations to avoid corrupted data states and (2) even if somebody decides to skip the app and try to be clever in a psql console they'll still not be able introduce corrupted data states and (3) leave the door open for other apps to be able to connect to the same DB and do stuff (or simply to allow for a rewrite in another language).
- Kwpolska 2y ago
  The things you listed aren’t stored procedures, they are all possible to implement as check constraints. They are great, and they are fully compatible with ORMs. A stored procedure is a bit of code, stored in and executed by the database, usually written in a 1960s-era language (like PL/SQL or PL/pgSQL).
  - lelanthran 2y ago
    > The things you listed aren’t stored procedures, they are all possible to implement as check constraints. They are great, and they are fully compatible with ORMs. I didn't claim that they are not compatible with ORMS. I said the majority of developers have no clue just how much of value they can get out of their database using types and constraints because the only interface they have every used to the RDBMS is the ORM, and the ORM doesn't expose any of this. I've commonly seen developers put in things like `if ((!A && B) || (A && !B)) { /* updateDbWithOneOf(A,B) */ }` in their code rather than use the constraints provided by the RDBMS.
qaq 2y ago
Well for starters they can improve your security posture. In proper dbs like PG the version change is transactional so you don't have to deal with schema being out of sync with code. You don't have to plan for all the possible future scenarios where you will need transaction boundary to cross the service boundaries. You can write stored procedures in pretty much any lang. Query optimisers and execution engines are far more tested and preferment vs some GraphQL gateway.