3 ms·

Why wouldn't you use stored procedures for it? For internal teams why is it better to have a API? I can see usecases in which API could make sense, but it does

Why wouldn't you use stored procedures for it? For internal teams why is it better to have a API? I can see usecases in which API could make sense, but it doesn't matter in most cases. SQL already has authorization/authentication built in. For rate limiting you can use something like planetscale's traffic control.

jeremyjh 16d ago
Because SQL is a beautiful declarative language, and a disgusting imperative language.
- saxenaabhi 16d ago
  You mentioned elsewhere "But PL/SQL is a horrifying language. You have barely any facilities for modularity, encapsulation or composition" That's not true about PL/SQL. You can modularize/encapsulate and compose multiple sql functions. Even plain SQL can be composable in some cases via views.
  - jeremyjh 16d ago
    Compared to any modern language, the facilities are primitive.
    - deleted 16d ago
      [deleted]
degamad 16d ago
I think the idea is that the stored procedures ARE an Application Programming Interface, as an alternative to a REST API. (I agree with you that for some use cases, an API consisting of a set of stored procedures is just as good as an API layered on top of the database in another language.)
- bunderbunder 16d ago
  They are, but a REST API likely isn’t a good alternative because few RDBMSes speak REST. More likely they meant creating a database interface library in whatever programming language the application uses. And then have it talk to the DB through that library instead of scattering a fine mist of ad-hoc querying (or, shudder, active records) throughout your application.
  - jeremyjh 16d ago
    The client of the REST API is not an RDBMS. The REST API serves application clients - may be browsers, may be other services. The REST API alone talks to the database. An API that serves only backend applications could be implemented in stored procedures instead of REST or GRPC. It could also be implemented in SOAP, CORBA, DCOM and other fossils, but no one is doing that for new applications.
bunderbunder 16d ago
For one example, I might choose against stored procedures when I’m at an organization with internal policies or a devops setup that makes schema migrations costly and I expect the table and indexing structure to change less frequently than the queries. That does not mean I’d let the queries devolve into chaos. Just that I’d do the query management and change control in a way that’s more pragmatic in light of other realities.

jeremyjh 16d ago

Because SQL is a beautiful declarative language, and a disgusting imperative language.

saxenaabhi 16d ago
You mentioned elsewhere "But PL/SQL is a horrifying language. You have barely any facilities for modularity, encapsulation or composition" That's not true about PL/SQL. You can modularize/encapsulate and compose multiple sql functions. Even plain SQL can be composable in some cases via views.
- jeremyjh 16d ago
  Compared to any modern language, the facilities are primitive.
  - deleted 16d ago
    [deleted]

degamad 16d ago

I think the idea is that the stored procedures ARE an Application Programming Interface, as an alternative to a REST API. (I agree with you that for some use cases, an API consisting of a set of stored procedures is just as good as an API layered on top of the database in another language.)

bunderbunder 16d ago
They are, but a REST API likely isn’t a good alternative because few RDBMSes speak REST. More likely they meant creating a database interface library in whatever programming language the application uses. And then have it talk to the DB through that library instead of scattering a fine mist of ad-hoc querying (or, shudder, active records) throughout your application.
- jeremyjh 16d ago
  The client of the REST API is not an RDBMS. The REST API serves application clients - may be browsers, may be other services. The REST API alone talks to the database. An API that serves only backend applications could be implemented in stored procedures instead of REST or GRPC. It could also be implemented in SOAP, CORBA, DCOM and other fossils, but no one is doing that for new applications.

bunderbunder 16d ago

For one example, I might choose against stored procedures when I’m at an organization with internal policies or a devops setup that makes schema migrations costly and I expect the table and indexing structure to change less frequently than the queries. That does not mean I’d let the queries devolve into chaos. Just that I’d do the query management and change control in a way that’s more pragmatic in light of other realities.