Porting ActiveRecord validations to Postgres

Let's try to port some ActiveRecord validations to Postgres using constraints!

validates_presence_of

class User < ActiveRecord::Base  
  validates_presence_of :email
end

ALTER TABLE users ALTER COLUMN email SET NOT NULL;  
ALTER TABLE users ADD CONSTRAINT email_presence CHECK (char_length(email) > 0);  

validates_uniqueness_of

class User < ActiveRecord::Base  
  validates_uniqueness_of :email
end

ALTER TABLE users ADD CONSTRAINT email_uniqueness UNIQUE (email);  

class User < ActiveRecord::Base  
  validates_uniqueness_of :email, case_sensitive: false
end

ALTER TABLE users ALTER COLUMN email TYPE citext;  
ALTER TABLE users ADD CONSTRAINT email_uniqueness UNIQUE (email);  

class User < ActiveRecord::Base  
  validates_uniqueness_of :email, scope: :account_id
end

ALTER TABLE users ADD CONSTRAINT email_uniqueness UNIQUE (email, account_id);  

validates_numericality_of

class User < ActiveRecord::Base  
  validates_numericality_of :age, greater_than_or_equal_to: 18
end

ALTER TABLE users ADD CONSTRAINT age_numericality check (age >= 18);  

class User < ActiveRecord::Base  
  validates_numericality_of :age, equal_to: 50
end

ALTER TABLE users ADD CONSTRAINT age_numericality check (age = 50);  

class User < ActiveRecord::Base  
  validates_numericality_of :age, odd: true
end

ALTER TABLE users ADD CONSTRAINT age_numericality check (age % 2 != 0);  

class User < ActiveRecord::Base  
  validates_numericality_of :age, even: true
end

ALTER TABLE users ADD CONSTRAINT age_numericality check (age % 2 = 0);  

validates_inclusion_of

class User < ActiveRecord::Base  
  validates_inclusion_of :age, in: [1, 2, 3]
end

ALTER TABLE users ADD CONSTRAINT age_inclusion check (age IN (1, 2, 3));  

class User < ActiveRecord::Base  
  validates_inclusion_of :age, in: 18..25
end

ALTER TABLE users ADD CONSTRAINT age_inclusion check (age IN generate_sequence(18, 25));  

class User < ActiveRecord::Base  
  validates_inclusion_of :age, in: (1..100).step(2)
end

ALTER TABLE users ADD CONSTRAINT age_inclusion check (age IN generate_sequence(1, 100, 2));  

validates_exclusion_of

class User < ActiveRecord::Base  
  validates_exclusion_of :age, in: [1, 2, 3]
end

ALTER TABLE users ADD CONSTRAINT age_exclusion check (age NOT IN (1, 2, 3));  

class User < ActiveRecord::Base  
  validates_exclusion_of :age, in: 18..25
end

ALTER TABLE users ADD CONSTRAINT age_exclusion check (age NOT IN generate_sequence(18, 25));  

class User < ActiveRecord::Base  
  validates_exclusion_of :age, in: (1..100).step(2)
end

ALTER TABLE users ADD CONSTRAINT age_exclusion check (age NOT IN generate_sequence(1, 100, 2));  

validates_length_of

class User < ActiveRecord::Base  
  validates_length_of :password, minimum: 6, maximum: 32
end

ALTER TABLE users ADD CONSTRAINT password_length CHECK (char_length(password) BETWEEN 6 AND 32);  

validates_format_of

class User < ActiveRecord::Base  
  validates_format_of :email, with: /\A([^@\s]+)@((?:[-a-z0-9]+\.)+[a-z]{2,})\Z/i
end

ALTER TABLE users ADD CONSTRAINT email_format CHECK (email ~* '\A([^@\s]+)@((?:[-a-z0-9]+\.)+[a-z]{2,})\Z');  

class User < ActiveRecord::Base  
  validates_format_of :email, with: /.+@.+/, allow_nil: true
end

ALTER TABLE users ADD CONSTRAINT email_format CHECK (email IS NULL OR email ~* '.+@.+');  

class User < ActiveRecord::Base  
  validates_format_of :email, with: /.+@.+/, allow_blank: true
end

ALTER TABLE users ADD CONSTRAINT email_format CHECK (email = '' OR email ~* '.+@.+');  

class User < ActiveRecord::Base  
  validates_format_of :email, with: /.+@.+/, allow_blank: true, allow_nil: true
end

ALTER TABLE users ADD CONSTRAINT email_format CHECK (email IN (NULL, '') OR email ~* '.+@.+');  

custom validations

class User < ActiveRecord::Base  
  validate :validate_email_domain

  def domain
    email.to_s.split('@').last
  end

  private

  def validate_email_domain
    if banned_domains.include?(domain)
      errors.add(:email, 'domain is invalid')
    end
  end

  def banned_domains
    %w(gmail.com live.com)
  end
end

CREATE OR REPLACE FUNCTION validate_email_domain()  
  RETURNS trigger
AS $$  
  DECLARE
    banned text[];
    domain text;
  BEGIN
    banned := ARRAY['gmail.com', 'live.com'];

    FOREACH domain IN ARRAY banned LOOP
      IF NEW.email ~* (domain || '$') THEN
        RAISE EXCEPTION 'Invalid email domain %', domain;
      END IF;
    END LOOP;

    RETURN NEW;
  END;
$$ language plpgsql;

CREATE TRIGGER email_domain_validation  
  BEFORE INSERT OR UPDATE ON users
  FOR EACH ROW EXECUTE PROCEDURE validate_email_domain();

Caveats

• You'll need convert these constraint exceptions into user friendly error messages within your applications. The exceptions contain the name of the failed constraint like email_domain_validation so a common way to accomplish this is to map the name to a hash containing messages like Your email domain #{domain.inspect} has been banned.

• Postgres returns the first raised exception that is encountered. This means that you can't get a list of ALL failing validations when calling save, only one at a time as each error is fixed.

If you enjoyed this, check out how to port ActiveRecord counter cache and soft delete behavior to Postgres as well!