Just because most developers use their DB as a dumb store doesn't mean it needs to be. There are also plenty of successful software systems that place the majority of their business logic and use a generic programming language and runtime only for the presentation layer.
If you're comfortable fully exploiting the capabilities of your DB, then the intelligent combination of a relational model with custom data types, constraints, triggers, views and stored procedures can make the DB the perfect place to implement business logic.
Until db vendors start taking developer happiness seriously, stored procedures and triggers are a total non starter for any serious work.
I mean, in most databases you can't even rename a column without causing an enormous blocking migration. What? Why can't this happen in the background? Why can't I have column aliases? The very basics of developer happiness aren't covered, let alone the harder bits, like versioning stored procedures, switching schema and code when switching to a new git branch, and so on.
(EDIT: of course there are open source tools that help with all of the above, but they're all swimming upstream, fighting a database that simply can't imagine change, and are usually terribly leaky abstractions as a result)
Can you be more specific? What exactly are you missing?
As long as you put your code and data into separate schemes and follow good technical practices, it shouldn't be too different from other technologies.
> I mean, in most databases you can't even rename a column without causing an enormous blocking migration.
Changing a column name is just a metadata change, so it shouldn't take too long in Postgres.
> The very basics of developer happiness aren't covered, let alone the harder bits, like versioning stored procedures, switching schema and code when switching to a new git branch, and so on.
Does Tomcat or any other application server version your WAR files for you or does it manage git for you?
I’d expect most DBs to be like this: https://dba.stackexchange.com/questions/189794/performance-i...
A rename should be just a metadata change.
The trick is to keep declarative business logic in the database layer, and imperative business logic in the application layer.
This allows a large team of developers to move quickly without breaking things.
Can you give some examples of what would constitute declarative business logic vs imperative business logic?
This new feature in PostgreSQL is a great example of that: generated columns (declarative logic) were introduced to reduce the need for triggers (imperative logic).
In SQL, declarative logic consists of constraints, indexes, views, and prepared statements. They can significantly increase the efficiency and reliability of the entire system. Imperative logic is mostly triggers and stored procedures, both of which can become hard to maintain and scale.
This way I can select the top N trades for a given key without having to do the computation in the application, or storing redundant information in the DB.
Changing database schema is a big deal. It might take a lot of time or it must be done with great caution to keep database online. It's hard to properly version it and it's often hard or just impossible to roll back bad update.
Generally database is state and application is stateless. You can couple it, but decoupling works better.
* Change your application logic very easily.
* Use a transaction to deploy new code! Zero downtime! [2]
* Use read replicas to balance load in many cases.
* Rollback bad application updates
* Test anything [3]
* Use miriads of development tools from Git to CI systems.
* Use row level security, so that every user can only see his own data [4]
* Only allow applications to call your code schema, never let them touch your data directly.
[1] https://www.postgresql.org/docs/current/ddl-schemas.html
[2] https://wiki.postgresql.org/wiki/Transactional_DDL_in_Postgr...
[4] https://www.postgresql.org/docs/current/ddl-rowsecurity.html
Looking at the Postgres driver it seems easier but is there any good tutorial on how to do error handling properly for databases?
Putting complex business logic in the database is opening up all sorts of interesting new ways for data to be unavailable, corrupted or complicated to access. It is easy to imagine it working out well for simple requirements where there just needs to be something that works.
PostgreSQL is a piece of software that takes data and enforces the relational data model on it. Great idea. But the relational model of data is really only tuned to relational algebra. Put complex logic in there and all that is really being accomplished is now you can't migrate away from PostgreSQL. Relational databases already have great integration with every other programming language in current use.
That's what schemas are for. You have a schema for your code and a schema for your data. You can redeploy the code scheme independently of the data scheme and set up permissions so that higher layers can only use objects from the code scheme and never touch the data.
> Put complex logic in there and all that is really being accomplished is now you can't migrate away from PostgreSQL.
Say you wrote your code in PHP, now you want to migrate to Ruby or NodeJS. You can't, you have to rewrite everything. How often do you plan to migrate to another database? In my experience this almost never happens in reality, but the layers above come and go.
That all just sounds a touch complicated compared to data in database, code somewhere else (like git). I'm circling back to the same point in a couple of different ways, but pgSQL specialises in the relational model of data. That isn't a great data model for code and there are already better ways to manage code than shoehorning it into the database. Its cool that it is possible, and I'm not saying that someone who does is making a mistake. But I also don't think they are gaining an advantage and there is a really easy opportunity to separate out where bugs can occur from where data lives.
> How often do you plan to migrate to another database? In my experience this almost never happens in reality, but the layers above come and go.
If you are using a database for its advanced general purpose programming capabilities? The chances probably start to get more likely.
Databases that are a store of data don't need to change because they already do their one job (disk <-> relational model translation) really well. If they are pressured to do 2 or 3 tasks like business logic then suddenly it is a lot more likely that there will be pressure to swap databases.
If I were using SQLite and someone wants to do fancy triggers then maybe I need to swap to PostgreSQL. Avoiding that sort of decision making is a great reason to seal the data completely away from the code.
You use Postgres as a deployment target and not as a replacement for git. It's not complicated at all. You even get features like transactional deployments and the ability to prohibit applications from directly touching the data.
> pgSQL specialises in the relational model of data
The relational model is SQL:92, Postgres does much more than that. Postgres has JSON support, recursive CTEs, Row Level Security, and Window functions that would require dozens of lines of procedural code to do what can be done with a single OVER in its SELECT clause.
> If I were using SQLite and someone wants to do fancy triggers then maybe I need to swap to PostgreSQL.
If you want to put your business logic into an RDBMS, you wouldn't be using an embedded DB anyway, but rather Oracle, Postgres, SQL Server or DB2, which are designed for this type of architecture.
Could you share a few cases where it is better to use a custom type as opposed to a relation? The only things I can think of are generic things like uuid. Would there be a need to create an Employee type vs an Employee relation?
Also, how is the experience with using Python for stored procedures? One reason they are not used is that the language pl/sql is non-familiar to most. If anyone has any experience with that, could you share some of your thoughts?
No, you could always do the materialized equivalent via triggers, so it's not necessary for correctness. And since this feature is limited to the materialized form, too, it doesn't offer much change other than simpler expression of what is going on (which, to be fair, is a big win.)
> I fear for putting business logic and meanings into the wrong the layer
If you don't have some mechanism for calculated columns, you are forced to put domain logic that belongs in the DB in the app layer, which is especially problematic in the (increasingly out of fashion, apparently) circumstance where there are multiple consumers of a DB, as it promotes inconsistency.
If it's something that's not application-specific and fits nicely into the query language (like in the example given) it makes sense to have it inside the DB, because for queries involving sorting, limiting, etc. you can do all that stuff inside the DB and return the few values that interest you, instead of transferring all the data and having to write code to do that inside your app.
What is missing is a modern tool chain that addresses the painpoints of such a development model.
Say you suddenly don't have data for a column anymore, because the API that you read from has stopped providing it. Now you can add a computed column that uses a heuristic as an interim until you have time to remove all the reads from that column.
Or say that you have an object with a separate display name, and product management decides that the display name now can't be chosen freely anymore, but must be generated from name and title. A computed column can do that trick pretty well.
I'm often creating wide, flat views with extended attributes. Many columns are nearly identical, for example, year as an integer, year a date type, etc. This is trivial with views, but ...
For fast query performance, I usually materialize those views. But that's a multi-step and often manual process, requiring schedules or triggers. This feature combines both of those worlds, with fewer total database objects. Especially with ALTER TABLE.
One example is where we need to store a special identifier code. The identifier code is required by law to have a certain format, and part of it is the date the code was generated.
Users frequently want to view items with codes generated on a specific day. For making presentation and reporting easier, as well as significantly faster, we added a computed date column that extracts the date from the code and indexed it.
Keeping an extra field in sync in code would inevitably have lead to bugs, especially as we have several different codebases which could update the id code.
With PostgreSQL, you can define business logic in everyday languages, including JavaScript (plv8).
Works great if you’re sure you won’t ever need to scale huge horizontally. One of my RoR apps connects to a schema of views, with instead of triggers calling procedures for updates. Something like 30 normalized tables are rolled up into 5 denormalized views, leaving hardly any ActiveRecord woes and still excellent performance.
But after contemplating it, is this really necessary? I fear for putting business logic and meanings into the wrong the layer; There are use and abuse, and my consideration fears the latter.