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 name for boolean database columns.

    # Bad
    add_column :users, :enabled, :boolean, null: false, index: true
    
    # Good
    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.

    # Bad
    add_column :users, :deleted_date, :date
    add_column :users, :last_sign_in_time, :datetime
      
    # Good
    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
    
    # Bad
    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
    
    # Good
    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

    # Bad
    class DropSurnameColumn < ActiveRecord::Migration[6.0]
      def change
        remove_column :users, :surname
      end
    end
    
    # Good
    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.

    # Bad
    class UpdateWebsite < ActiveRecord::Migration[6.0]
      def change
        # your changes to DB here
      end
    end
    
    # Good
    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 bigint or uuid data type column for storing primary IDs.

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

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

Security

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

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

    # Bad
    User.where('status = ?', :new)
    
    # Good
    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
    
    # Bad
    form_klass = "#{params[:kind].camelize}Form".constantize
    form_klass.new.submit(params)
    
    # Good
    klasses = {
      'foo' => FooForm,
      'bar' => BarForm
    }
    
    klass = klasses[params[:kind]]
    if klass
      klass.new.submit(params)
    end
    
    # 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.