Best Practices for Database

We learn a lot about programming. How to write a good code which is clean, maintainable, readable, reliable and many other dimensions. However, an ecosystem is not only the code but also many other stuffs that database is one. We let our code handle a lot of things. But it should be database’s works. What we should do is trying to balance many parts in our ecosystem. Nowaday, database is so strong. We should limit the number of queries but don’t let our code take over the works that database should take care. It’s time to go to rule number one.

1. Let database do its job.

Let’s understand your database. If you’re using Postgres. Doing some researches about it. Taking advantage of it. Database is designed for us to use the full power, not only for containing data. If a task which database can handle, delegate it to database! Moving the logic from code to database if possible. A lot of tasks which we usually do by language programming at server side, we can delegate them to database with complex query. Complex but not complicate.

2. Write scopes which are chainable and reusable.

We take control of working with queries. Making it clean, readable and extendable. Applying single responsibility in SOLID for each scope. We also see the Open/Closed principle on this practice.

scope :open, -> {
   where(closed_at: nil)
}
scope :closed, -> {
   where.not(closed_at: nil)
}

3. Reduce the number of queries to database.

A lot of queries even simple ones will make performance worse. It’s like we switch tasks regularly, working on multiple tasks. It’s like we receive a bunch of phone calls so that we can’t concentrate to works. We spend time on picking the phone up, listening to them etc. We get messy with multiple dimensions of work.

We can use “join” to reduce the queries. Doing some researches about this practice. For example.

4. Take advantage of Index database

We might know this. Indexes of database will help us to lookup faster. But be careful! Because if we set index to column. It means data will be saved at two locations. Index is like summary of a book. The title will be stored at two places. At its first place and second place is at “table of contents”.

Thus, every time a record is inserted, it will be inserted at both two places.

5. Query Objects

When the query is simple, scope is the best. But if the query is complicated, we should make it as an object. It would be readable, easy for maintaining and testing if we encapsulate the queries like this.

module EvaluationQueries
  class InspectionResults < EvaluationQueries::Inspections
    private
    def sort_default
      [scheduled_date: :desc, status: :desc]
    end

    def get_evaluations
      Evaluation.open
                .includes(:company, :vessel, :approver, :documents)
                .where(status: [:completed, :approved, :disapproved, :closed])
    end
  end
end

Because queries are put in one place corresponding with methods. It will marry the unit test.

Note that ORM – Object Relational Mapping which make it easy for programmers to write a readable code. But we need to practice original SQL queries regularly to keep fundamental knowledge.

6. Avoid stored procedures

One reason. Because we are building the evolving system. We make changes later.