Database

Hero image for Database

This section lists some of the important database conventions we must follow. The code examples are mainly in Rails/ActiveRecord, but we must implement similar practices in other frameworks like Phoenix as well.

Relational Database

  • Prefer using PostgreSQL for relational data storage.

Naming

  • Use snake_case for both database tables and columns. But use plural for database tables and singular for column names.

    +---------------------------+
    | campaign_locations        |
    +-------------+-------------+
    | id          | ID          |
    | name        | STRING      |
    | location_id | FOREIGN KEY |
    | updated_at  | DATETIME    |
    +-------------+-------------+
    
  • Use predicate-like names for boolean database columns.

    add_column :users, :enabled, :boolean, null: false, index: true
    
    add_column :users, :is_enabled, :boolean, null: false, index: true
    
  • Use the _at suffix for the database column name when adding a new date or datetime database column.

    add_column :users, :deleted_date, :date
    add_column :users, :last_sign_in_time, :datetime
    
    add_column :users, :deleted_at, :date
    add_column :users, :last_sign_in_at, :datetime
    
  • Combine table names to name join tables choosing to pluralize based on the intent of the table.

    # Given these two models
    class Campaign < ApplicationRecord
      has_many :influencers, inverse_of: :campaign
    end
    
    class Influencer < ApplicationRecord
      has_many :campaigns, inverse_of: :influencer
    end
    
  • With the resulting join table

    class Campaign < ApplicationRecord
      has_many :influencers, through: :campaigns_influencers
      has_many :campaigns_influencers, inverse_of: :campaign
    end
    
    class Influencer < ApplicationRecord
      has_many :campaigns, through: :campaigns_influencers
    end
    
    class Campaign < ApplicationRecord
      has_many :influencers, through: :campaign_influencers
      has_many :campaign_influencers, inverse_of: :campaign
    end
    
    class Influencer < ApplicationRecord
      has_many :campaigns, through: :campaign_influencers
    end
    
  • Name the migration files to describe the operations they perform. The general template is [ChangeMade]+To/From+[TableNamePlural]. For instance, when dropping a column named surname from the table users, name of the migration will be DropSurnameFromUsers. In case when multiple fields are dropped at once, try to provide a small context info apart from table name. Eg.: DropContactDetailsFromUsers

    class DropSurnameColumn < ActiveRecord::Migration[6.0]
      def change
        remove_column :users, :surname
      end
    end
    
    class DropSurnameFromUsers < ActiveRecord::Migration[6.0]
      def change
        remove_column :users, :surname
      end
    end
    
    class DropContactDetailsFromUsers < ActiveRecord::Migration[6.0]
      def change
        remove_column :users, :phone
        remove_column :users, :email
      end
    end
    
  • For data migration, use meaningful name also.

    class UpdateWebsite < ActiveRecord::Migration[6.0]
      def change
        # your changes to DB here
      end
    end
    
    class UpdateWebsiteColumnOfUsers < ActiveRecord::Migration[6.0]
      def change
        # your changes to DB here
      end
    end
    

Indexing

  • Use database indexes on foreign keys and boolean columns for faster queries.

    add_column :users, :location_id, :integer, foreign_key: true, index: true
    add_column :users, :is_enabled, :boolean, null: false, default: false, index: true
    

Constraints

  • Prefer to use both database constraints and validations when using libraries e.g. Ecto, ActiveRecord, etc.

    class User < ActiveRecord::Base
      validates :name, presence: true
    end
    
    create_table 'users', force: :cascade do |t|
      t.name :string, null: false
    end
    
  • Use null constraint for columns that CANNOT be blank.

    create_table 'users', force: :cascade do |t|
      t.name :string, null: false
    end
    
  • Use default constraint for columns with default values.

    create_table 'users', force: :cascade do |t|
      t.name :string, null: false
      t.view_count, default: 0
    end
    
  • Use unique constraint for columns with unique values, such as email.

    create_table 'users', force: :cascade do |t|
      t.name :string, null: false
      t.email, :string, null: false, unique: true
    end
    
  • Use check constraint wherever possible for limiting a columns value.

    create_table 'products', force: :cascade do |t|
      t.price :integer, null: false
      t.check_constraint "price_check", "price > 100"
    end
    
  • Define both default value and NOT NULL constraint for boolean columns to prevent the three-state boolean problem.

    add_column :users, :is_enabled, :boolean, null: false, default: false, index: true
    

Data Durability

  • Always soft-delete relational data for integrity and audit purposes. This means every deleted row in the database should store a timestamp value for a column named deleted_at or discarded_at.

  • For Rails, prefer to use gem discard.

Data Types

  • Use the bigint or uuid data type for storing primary IDs.

  • Prefer the citext data type for storing case insensitive data such as emails.

  • Prefer the jsonb data type for storing object-like data over hstore and json. Settings-like or configuration-like data are good candidates to be stored as jsonb.

  • Use the numeric data type for storing monetary types. The numeric type can be configured to use different precisions according to the currency in use. Do not use real or double precision types because they are inexact types.

Security

  • Use prepared statements for database operations requiring user inputs to prevent SQL injections.

    User.where("last_seen_at > #{params[:start_datetime]}")
    
    User.where('last_seen_at > ?', params[:start_datetime])
    
  • Use hash-based parameters for database operations requiring constants.

    User.where('status = ?', :new)
    
    User.where(status: :active)
    
  • Beware of initialing objects directly based on user input. Always add a validation layer or even prefer using factories.

    class FooForm; end
    class BarForm; end
    
    form_klass = "#{params[:kind].camelize}Form".constantize
    form_klass.new.submit(params)
    
    klasses = {
      'foo' => FooForm,
      'bar' => BarForm
    }
    
    klass = klasses[params[:kind]]
    if klass
      klass.new.submit(params)
    end
    
    # The best
    class FormFactory
      def self.build(type, *args)
        case type
        when :foo
          FooForm.new(*args)
        when :bar
          BarForm.new(*args)
        end
      end
    end
    

Non-relational Database

  • Prefer using Redis for key/value data storage.

  • Prefer using ElasticSearch for document-oriented data storage.