Use your database!

If you are using a database, the chances of using a relational one a pretty high. I have been using relational databases for a long time, but never really used all the features these databases offer simply because I saw them only as a repository to hold my data and also because I thought an ORM would solve all my problems.

Let's take as an example an entity with two date fields:

import java.time.LocalDateTime
import javax.persistence.Entity
import javax.persistence.Column

@Entity
data class MyEntity(
    // other fields
) {
    @Column(name = "created_at", updatable = false)
    val createdAt: LocalDateTime = LocalDateTime.now()

    @Column(name = "updated_at")
    var updatedAt: LocalDateTime = LocalDateTime.now()
}

When we instantiate an object of that class, the two date fields will have the current date and time, but we still need to update the updatedAt field manually every time we made a change to the object:

val myInstance = MyEntity(/* fields */)
myInstance.updateAt = LocalDateTime.now()

While this approach works, it is error prone since we have to update the updatedAtfield wherever we make a change to its object. We could isolate the logic to change the entites into a single service class, but we can do better.

Why not let the database do the work for us?

First, let's make some changes to our entity:

import java.time.LocalDateTime
import javax.persistence.Entity
import javax.persistence.Column

@Entity
data class MyEntity(
    // other fields
) {
    @Column(name = "created_at", insertable = false, updatable = false)
    val createdAt: LocalDateTime = LocalDateTime.MIN

    @Column(name = "updated_at", insertable = false, updatable = false)
    var updatedAt: LocalDateTime = LocalDateTime.MIN
}

The two date fields cannot be inserted nor updated, but it is OK, since the database will do this for us, we just have to define its schema as follows:

CREATE TABLE my_table(
    -- other columns
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE FUNCTION my_table_updated() RETURNS trigger AS $$
BEGIN
    NEW.updated_at := CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER my_table_updated BEFORE UPDATE ON my_table
    FOR EACH ROW EXECUTE my_table_updated();

A trigger was used to update the updated_at column every time a row in updated. This way, we have to do nothing in the code to get the behavior we want and we also do not have to worry if other applications need make changes to the data since the rules were define at the database level.

Next post I will show a more complex example.

Comments

[Daniel Coturel]

Hi!
This is a very good post, at least as I see it, because it fires a question that I think is key for good architecture:
Should bussiness rules live in the database or in the software?
Saludos,

[Dian Fay]

It's not an either/or proposition.

Lots of stuff -- ephemeral work that gets surfaced to users but never persisted, individual object graph manipulations, and so on -- belongs in the application, because there's no need for storage, because arborescent, cyclic, or recursive structures are difficult or impossible to model in a relational schema, because it's difficult to express object-oriented and other techniques elegantly in a language designed to manipulate and shape data sets instead.

That covers a lot of ground, but it's impossible to escape the fact that just building a schema which includes more than one table is encoding your business logic into the database. Doing something like creating a view to join users with addresses since you need that information frequently and writing that join in SQL instead of rolling your own is a no-brainer differs from the original act of separating users from addresses only by degree, not by nature.

A database is a machine for organizing information. It was never just storage for raw data at rest, and to treat it as an interchangeable storage layer instead of as a concrete component is to ignore some of the most useful things it can do for you.

[Daniel Coturel]

I think that with the last paragraph you just nailed the issue. You can treat a database as "an interchangeable storage layer" or not.

The thing is to make this a design choice rather than anything else. For instance, I worked here in Argentina for about ten years in a company that develops an ERP. When I first started, the ERP was designed to work with several relational database engines. A couple years later, due to a partnership, they decided to work exclusively with one vendor.

Having made that shift, they started to migrate bussiness logic to the database, in order to get more performing querys, etc.. It's been a positive change.

But, what if you want to develop something to work with several database engines? You should then not abandon the "interchangeable storage layer" principle.

What I'm trying to say is that the answer to the question I propose is not an absolute one.

[Dian Fay]

I did start my answer by saying it's not an either/or proposition for that very reason, among others :)

Sometimes you have to build something that can be dropped on top of any more-or-less standards-compliant RDBMS with minimum fuss, so you sacrifice architectural elegance for compatibility. It's kind of a crappy situation to be in, especially if there are things that nonstandard features could do much more elegantly for you, but it happens. Like materialized views can take a lot of pain out of preprocessing large amounts of data -- but if you need to be able to support MySQL, you're stuck with tables and procedures.

[Daniel Coturel]

Yes, I agree!

[Livio Ribeiro]

I would say that you have to be careful before putting logic in the database because it can be harder update (compared to rolling updates on kubernetes or swarm, for example) and probably harder to debug.

I am not an expert, but if I had to share some business logic between several applications, I would rather build a webservice, but for data processing or aggregation, than the database is the better suited for it.