5 ms·

It's horrible. You don't need API, just give to clients of your API credentials to your database and they will use DB directly. And it still will be horrible (b

It's horrible. You don't need API, just give to clients of your API credentials to your database and they will use DB directly. And it still will be horrible (because only good way to use application by another application is API, not direct access to data). Also, stored procedures is a bad thing - business logic should be written in code, not in data and not in the database.

ocharles 14y ago
> Also, stored procedures is a bad thing - business logic should be written in code, not in data and not in the database. Why? Your post generally makes a lot of criticisms, but fails to back up any of the points made.
- NickNameNick 14y ago
  Primarily, because the stored procedures are not effectively under version control.
  - icebraining 14y ago
    Only if your workflow is bad and you're editing stuff directly on the DB. Stored procedures work fine with VCS if you write them on files and then run those. Where I work, creating a new database with all the procedures is just a matter of loading the right .sql file.
  - JoelJacobson 14y ago
    Maybe yours aren't, but mine are. I've been using this simple tool for three years now. https://github.com/gluefinance/PgDeploy https://github.com/gluefinance/PgDeploy It gives you an extra safety net, in case the version in the database would differ compared to the version in the VCS, even though they shouldn't, someone might have been evil behind your back. :) PgDeploy lets you preview the change and show you a diff. If the diff is expected, you can proceed and deploy.
- EugeneOZ 14y ago
  Lot of reasons. Refactoring, unit-tests, readability... If you don't understand my points, it doesn't means they falls :)
  - mryan 14y ago
    You did not mention any of these things in your original post. Why would you assume that he doesn't understand them, if you didn't even bother to type them? But yes, completely failing to mention a point when attempting to make an argument generally means they fail :)
    - EugeneOZ 14y ago
      You talk like teenager.
      - mryan 14y ago
        Dude, that's like, totally SO not true. You might want to read the HN posting guidelines - your comment history suggests you have not done so yet. I have no interest in continuing a conversation with you, based on the level of discourse evident in your previous comments. Have a good day.
mryan 14y ago
"business logic should be written in code, not in data and not in the database" This makes no sense. Stored procedures are code - just code that executes within your DB, instead of on your app servers. I agree that it makes sense to keep your business logic all in one codebase, but there are times when stored procs are a very efficient solution to business-logic problems. Overall I agree that this "DB API" solution is not entirely elegant, and might provide as many security holes as it does interesting debugging challenges. However, there are some situations in which I could see this being incredibly useful. Combining this Nginx's JSON/API modules means some parts of your API could entirely bypass your app servers. Let's say you have an API that shows the ten items most recently liked by a particular user. Typically, this request would hit Nginx, your app server, then your cache and/or DB. By using the "shared procs API" and some Nginx modules, you could provide the same data by letting Nginx query your cache/DB directly.
- EugeneOZ 14y ago
  No, stored procedures is not a part of business logic. Read books of Robert Martin and Martin Fowler, they will explain it for you.
  - fdr 14y ago
    Procedures are procedures regardless of where they are written. In many aspects it can be mechanically more difficult to deal with SQL UDFs but that is a usability weakness that just needs some more treatment rather than anything particularly fundamental, I feel. There are three common reasons I write UDFs: * The SQL type system and dependency tracking are deemed helpful. This applies only to 'LANGUAGE SQL' functions, which are just regular queries. Unlike dynamically generated SQL from the application, type checks and dependency tracking on other SQL objects can be employed. This is hard to address in any tooling without having a complete copy of the catalogs (and many databases are not as self-hosted complete as Postgres' catalogs, so sometimes that may not be enough...) and semantic analyzer. * Diverse language and/or library access. If there is a series of obscure rules to hold simple invariants locked into exactly one application's libraries, it makes it pretty much impossible to use any other library. This can be addressed, but it's often expensive to do so. * Performance, in rare cases where network round trip dominates execution time and I need more than one plain SQL statement; this is only the case for the integrated 'procedural' languages. One could accomplish something similar by running the application's code closer to the machine, and I think that'd be better, actually, but requires some orchestration.
JoelJacobson 14y ago
That's a religious argument. Many applications are a perfect fit for a database centric design with business logic written in stored procedures. Not all, but some. You might find find old article interesting, http://ora-00001.blogspot.com/2011/07/mythbusters-stored-procedures-edition.html http://ora-00001.blogspot.com/2011/07/mythbusters-stored-pro...
gaius 14y ago
This is silly. If you have many applications accessing the database - and not every app will go via your API, unless you plan to rewrite everything - then you want your business rules enforced in one place, that will be effective for any client, be it a command line, a web app, client-server, Excel, third party products, anything. The web world has produced people for whom there is a 1:1 correlation between app and DB. In the enterprise world, most apps are written to run against existing DBs, some of which have been around for decades. This is scalable development processes. And the funny thing is, web kids will blithely repeat "DRY" without ever understanding the truth of it.
- JoelJacobson 14y ago
  What's wrong with a 1:1 correlation, when it makes sense? If what you need to do is to access or modify data, then a stored procedure language such as PL/pgSQL is perfect for the job. But if you need to render some graphics using GD in PHP, that's obviously not a job for the database. The total API of a system is probably not only stored procedures, but quite a lot of API methods, stored procedures can take care of the entire task, without the need to involve PHP or any other middle-layer language.
  - gaius 14y ago
    Because it's a tiny corner of the database world, and isn't terribly representative of the majority of database work. The kids who do all their logic in the app, are storing up trouble for themselves in future, when they want to use the DB for more than just the website.
einhverfr 14y ago
I agree with you about API's (and would further note that stored procedures are a way to build API's around your database, as are views). However, I disagree that stored procedures are a bad thing. There are several ways that stored procedures can be used. 1) Named queries (An API around your db!) 2) Set-intensive operations are really best done in SQL. You can do a heck of a lot (of even multi-stage logic!) in a single query when you throw in some in-line views, common table expressions. Writing in stored procedures gives you a single, testable reference for functionality. 3) Exporting application functionality to other programs that may want to use the db (like Excel for example). 4) Unit tests in the db are very, very helpful and highly underrated. Gotta love the fact that rollback means "none of this ever happened."

ocharles 14y ago

> Also, stored procedures is a bad thing - business logic should be written in code, not in data and not in the database. Why? Your post generally makes a lot of criticisms, but fails to back up any of the points made.

NickNameNick 14y ago
Primarily, because the stored procedures are not effectively under version control.
- icebraining 14y ago
  Only if your workflow is bad and you're editing stuff directly on the DB. Stored procedures work fine with VCS if you write them on files and then run those. Where I work, creating a new database with all the procedures is just a matter of loading the right .sql file.
- JoelJacobson 14y ago
  Maybe yours aren't, but mine are. I've been using this simple tool for three years now. https://github.com/gluefinance/PgDeploy https://github.com/gluefinance/PgDeploy It gives you an extra safety net, in case the version in the database would differ compared to the version in the VCS, even though they shouldn't, someone might have been evil behind your back. :) PgDeploy lets you preview the change and show you a diff. If the diff is expected, you can proceed and deploy.
EugeneOZ 14y ago
Lot of reasons. Refactoring, unit-tests, readability... If you don't understand my points, it doesn't means they falls :)
- mryan 14y ago
  You did not mention any of these things in your original post. Why would you assume that he doesn't understand them, if you didn't even bother to type them? But yes, completely failing to mention a point when attempting to make an argument generally means they fail :)
  - EugeneOZ 14y ago
    You talk like teenager.
    - mryan 14y ago
      Dude, that's like, totally SO not true. You might want to read the HN posting guidelines - your comment history suggests you have not done so yet. I have no interest in continuing a conversation with you, based on the level of discourse evident in your previous comments. Have a good day.

mryan 14y ago

"business logic should be written in code, not in data and not in the database" This makes no sense. Stored procedures are code - just code that executes within your DB, instead of on your app servers. I agree that it makes sense to keep your business logic all in one codebase, but there are times when stored procs are a very efficient solution to business-logic problems. Overall I agree that this "DB API" solution is not entirely elegant, and might provide as many security holes as it does interesting debugging challenges. However, there are some situations in which I could see this being incredibly useful. Combining this Nginx's JSON/API modules means some parts of your API could entirely bypass your app servers. Let's say you have an API that shows the ten items most recently liked by a particular user. Typically, this request would hit Nginx, your app server, then your cache and/or DB. By using the "shared procs API" and some Nginx modules, you could provide the same data by letting Nginx query your cache/DB directly.

EugeneOZ 14y ago
No, stored procedures is not a part of business logic. Read books of Robert Martin and Martin Fowler, they will explain it for you.
- fdr 14y ago
  Procedures are procedures regardless of where they are written. In many aspects it can be mechanically more difficult to deal with SQL UDFs but that is a usability weakness that just needs some more treatment rather than anything particularly fundamental, I feel. There are three common reasons I write UDFs: * The SQL type system and dependency tracking are deemed helpful. This applies only to 'LANGUAGE SQL' functions, which are just regular queries. Unlike dynamically generated SQL from the application, type checks and dependency tracking on other SQL objects can be employed. This is hard to address in any tooling without having a complete copy of the catalogs (and many databases are not as self-hosted complete as Postgres' catalogs, so sometimes that may not be enough...) and semantic analyzer. * Diverse language and/or library access. If there is a series of obscure rules to hold simple invariants locked into exactly one application's libraries, it makes it pretty much impossible to use any other library. This can be addressed, but it's often expensive to do so. * Performance, in rare cases where network round trip dominates execution time and I need more than one plain SQL statement; this is only the case for the integrated 'procedural' languages. One could accomplish something similar by running the application's code closer to the machine, and I think that'd be better, actually, but requires some orchestration.

JoelJacobson 14y ago

That's a religious argument. Many applications are a perfect fit for a database centric design with business logic written in stored procedures. Not all, but some. You might find find old article interesting, http://ora-00001.blogspot.com/2011/07/mythbusters-stored-procedures-edition.html http://ora-00001.blogspot.com/2011/07/mythbusters-stored-pro...

gaius 14y ago

This is silly. If you have many applications accessing the database - and not every app will go via your API, unless you plan to rewrite everything - then you want your business rules enforced in one place, that will be effective for any client, be it a command line, a web app, client-server, Excel, third party products, anything. The web world has produced people for whom there is a 1:1 correlation between app and DB. In the enterprise world, most apps are written to run against existing DBs, some of which have been around for decades. This is scalable development processes. And the funny thing is, web kids will blithely repeat "DRY" without ever understanding the truth of it.

JoelJacobson 14y ago
What's wrong with a 1:1 correlation, when it makes sense? If what you need to do is to access or modify data, then a stored procedure language such as PL/pgSQL is perfect for the job. But if you need to render some graphics using GD in PHP, that's obviously not a job for the database. The total API of a system is probably not only stored procedures, but quite a lot of API methods, stored procedures can take care of the entire task, without the need to involve PHP or any other middle-layer language.
- gaius 14y ago
  Because it's a tiny corner of the database world, and isn't terribly representative of the majority of database work. The kids who do all their logic in the app, are storing up trouble for themselves in future, when they want to use the DB for more than just the website.

einhverfr 14y ago

I agree with you about API's (and would further note that stored procedures are a way to build API's around your database, as are views). However, I disagree that stored procedures are a bad thing. There are several ways that stored procedures can be used. 1) Named queries (An API around your db!) 2) Set-intensive operations are really best done in SQL. You can do a heck of a lot (of even multi-stage logic!) in a single query when you throw in some in-line views, common table expressions. Writing in stored procedures gives you a single, testable reference for functionality. 3) Exporting application functionality to other programs that may want to use the db (like Excel for example). 4) Unit tests in the db are very, very helpful and highly underrated. Gotta love the fact that rollback means "none of this ever happened."