PostgreSQL database constraints in Rails

postgresql_database_constraints.rb

# frozen_string_literal: true

# Read the blog: https://tejasbubane.github.io/posts/using-postgres-database-constraints-in-rails/

require "bundler/inline"

gemfile(true) do
  source "https://rubygems.org"

  # Activate the gem you are reporting the issue against.
  gem "activerecord", "~> 8.0.2"
  gem "pg", "~> 1.5.9"
end

require "active_record"
require "minitest/autorun"
require "logger"

ActiveRecord::Base.establish_connection(
  adapter: 'postgresql',
  host: 'localhost',
  port: '5432',
  username: 'tejas',
  password: 'foobar',
  pool: '5'
)

begin
  ActiveRecord::Base.connection
rescue ActiveRecord::NoDatabaseError
  ActiveRecord::Base.connection.create_database("penguins")
end

ActiveRecord::Base.logger = Logger.new(STDOUT)

ActiveRecord::Schema.define do
  create_table :orders, force: true do |t|
    t.integer :user_id
    t.integer :channel_id
    t.decimal :price, precision: 8, scale: 2
    t.check_constraint "num_nonnulls(user_id, channel_id) > 0", name: :orders_user_or_channel_present
  end

  create_table :events, force: true do |t|
    t.string :name
    t.timestamp :starts_at
    t.timestamp :ends_at
    t.check_constraint "ends_at > starts_at", name: :events_starts_greater_than_ends
  end
end

class Order < ActiveRecord::Base
  validates :user_id, presence: true, if: -> { channel_id.blank? }
  validates :channel_id, presence: true, if: -> { user_id.blank? }
end

class Event < ActiveRecord::Base
  # Normally you would also have a corresponding validation here
  # validates :ends_at, comparison: { greater_than: :starts_at }

  # But I am leaving it out to demonstrate handling DB error below
end

class FeatureTest < Minitest::Test
  def test_orders_validation
    assert_raises ActiveRecord::RecordInvalid do
      Order.create!(user_id: nil, channel_id: nil)
    end
  end

  def test_events_database_constraint
    assert_raises ActiveRecord::StatementInvalid do
      Event.create!(starts_at: Time.parse("2025-03-10"), ends_at: Time.parse("2025-03-09"))
    end
  end
end