Creating Custom Postgres Data Types in Rails

Postgres ships with the most widely used common data types, like integers and text, built in, but it's also flexible enough to allow you to define your own data types if your project demands it.

Say you're saving price data and you want to ensure that it’s never negative. You might create a not_negative_int type that you could then use to define columns on multiple tables. Or maybe you have data that makes more sense grouped together, like GPS coordinates. Postgres allows you to create a type to hold that data together in one column rather than spread it across multiple columns.

In Rails, all attributes pass through the attributes API when they’re entered by the user or read from the database. Rails 5 introduced the Attributes API, allowing you to define your own attribute types and use them in your application.

In this tutorial, you'll learn how to work with two of the most common custom types available in PostgreSQL. You'll also see how to incorporate them into your Rails application using the Attributes API.

Should you be interested in learning how to create custom Postgres data types in Django, we've got you covered! Just read our dedicated article about it here: Creating Custom Postgres Data Types in Django.

Custom Data Types in Postgres

There are two custom data types you'll learn about in this post:

• Domain types: These allow you to put certain restrictions on a data type that can be reused later.
• Composite types: These let you group data together to form a new type.

First, let's take a look at how to create a domain type. Say you want to ensure a username doesn't contain a !:

CREATE DOMAIN string_without_bang as VARCHAR NOT NULL CHECK (value !~ '!');

After that, you can use this domain type when you create our users table:

CREATE TABLE users (
 id serial primary key, 
 user_name string_without_bang
);

Let’s try creating a user with a username that contains an exclamation point. You'll see an error message:

INSERT INTO users(user_name) VALUES ('coolguy!!');
-- ERROR:  value for domain string_without_bang violates check constraint "string_without_bang_check"

You can even use a domain in the definition of another domain:

CREATE DOMAIN email_with_check AS string_without_bang NOT NULL CHECK (value ~ '@');

CREATE TABLE email_addresses (
  user_id integer,
  email email_with_check
);

INSERT INTO email_addresses(email) VALUES ('frank!@gmail.com');
-- ERROR:  value for domain email_with_check violates check constraint "string_without_bang_check"

INSERT INTO email_addresses(email) VALUES ('joshgmail.com');
-- ERROR:  value for domain email_with_check violates check constraint "email_with_check_check"

Composite Types

Composite types allow you to group different pieces of data together into one column. They're useful for information that has more meaning when grouped together, like RGB color values or the dimensions of a package.

Let’s start by creating a dimensions type:

CREATE TYPE dimensions as (
  depth integer,
  width integer,
  height integer
);

Next, let’s create a table using this new type. Try also using the domain type you created previously:

CREATE TABLE orders (
  product string_without_bang,
  dims dimensions
);

Add some data and take a look at the output when you query the table:

INSERT INTO orders(product, dims) VALUES('widget', (50,88,101));

SELECT * FROM orders;

 product |    dims     
---------+-------------
 widget  | (50,88,101)

You'll see that all the data related to the dimensions of the package is saved together in the dims column. But don't worry, you'll still be able to access the individual ints.

SELECT (dims).width FROM orders;

 width 
-------
  88

Custom Types in Rails

In order to use our custom types in Rails, you’ll have to do two things:

• Create the migration that sets the types up for us in the database.
• Tell Rails how to handle your new type so you can easily work with it in Ruby.

Currently, Rails doesn't offer any built-in solution for creating types in migrations, so you'll have to run some raw SQL. The code below runs exactly what you ran above to create the type directly in PostgreSQL, then immediately uses the types to build the orders table:

class CreateOrders < ActiveRecord::Migration[6.1]
  def up
    execute <<~SQL
      CREATE TYPE dimensions as (
        depth integer,
        width integer,
        height integer
      );
      CREATE DOMAIN string_without_bang as VARCHAR NOT NULL CHECK (value !~ '!');
    SQL
    create_table :orders do |t|
      t.column :product, :string_without_bang
      t.column :dims, :dimensions
    end
  end

  def down
    drop_table :orders
    execute "DROP TYPE dimensions"
    execute "DROP DOMAIN string_without_bang"
  end
end

You'll need to use up and down methods here since you’re running some raw SQL that Rails won't be able to easily undo on its own if you want to do a rollback.

Run the migrations, and you'll see output that looks similar to this:

== 20210211230550 Orders: migrating =========================================
-- execute("CREATE TYPE dimensions as (\n  depth integer,\n  width integer,\n  height integer\n);\nCREATE DOMAIN string_without_bang as VARCHAR NOT NULL CHECK (value !~ '!');\n")
   -> 0.0012s
-- create_table(:Orders)
   -> 0.0058s
== 20210211230550 Orders: migrated (0.0071s) ================================

unknown OID 25279: failed to recognize type of 'dims'. It will be treated as String.

Notice that the migration succeeded, but Rails does not know what to do with the composite type, so it will treat it as a string. If you check the database directly, you'll see that the type for dims column is what you expect:

=# \d orders
 Column  |        Type         |
---------+---------------------+
 id      | bigint              |
 product | string_without_bang |
 dims    | dimensions          |

Right now, if you create a new product, you'll need to enter the dims data as a properly formatted string like this:

2.6.3 :001 > o = Order.create product: 'hat', dims: '(1,2,3)'
2.6.3 :002 > o.dims
 => "(1,2,3)" 
2.6.3 :003 > 

This setup doesn't allow you to update the individual elements without having to completely override the entire string. What's needed here is a dimensions class that has methods that know how to deal with this new data type. Luckily, Rails has a solution for this.

Using the Active Record Attributes API to Register new Custom Types in Rails

You can use the Active Record Attributes API to register the new type and control what it looks like when leaving and entering the database.

Start by creating a dimensions class that takes in a string in the initialize method. Data will come in from the database as a string with parentheses "(1,2,3)", so you'll need to parse it and then set some instance variables. Notice the code also includes a to_s method that returns the data back to the string with parentheses that the database will understand.

class Dimension
  attr_accessor :depth, :width, :height

  def initialize(values)
    dims = values ? sanitize_string(values) : [0,0,0] 
    @depth = dims[0]
    @width = dims[1]
    @height = dims[2]
  end

  def sanitize_string(values)
    values.delete("()").split(',').map(&:to_i)
  end

  def to_s
    "(#{depth},#{width},#{height})"
  end
end

This class will act as a wrapper for the dimension type and will make it easier to work with, but you still need to tell Rails how to handle it when saving to the database and instantiating your order objects. Just like Rails knows how to take a Ruby string type or a Ruby int type and pass it off to PostgreSQL in a way it can save and understand, you need to tell Rails how to handle your new dimension type. You can do that by creating a DimensionType that inherits from ActiveRecord::Type::Value and setting up a few methods.

class DimensionType < ActiveRecord::Type::Value
  def cast(value)
    Dimension.new(value)
  end

  def serialize(value)
    value.to_s
  end

  def changed_in_place?(raw_old_value, new_value)
    raw_old_value != serialize(new_value)
  end
end

The #cast method gets called by Active Record when setting an attribute in the model. You can use your new dimension class for this.

The #serialize method converts your dimension object to a type that PostgreSQL can understand. This is why you set up your to_s method in your Dimension class.

Finally, #changed_in_place? takes care of comparing the raw value in the database with your new value. This is what gets called whenever Active Record tries to decide if it needs to make an update to the database. raw_old_value will always be a string because it's read directly from the database. new_value, in this case, will be an instance of Dimension, so it needs to be converted to a string in order to make the comparison.

The last piece of the puzzle will be to tell the order model to use the new DimensionType for the dims attribute.

class Order < ApplicationRecord
  attribute :dims, DimensionType.new
end

Let’s open a Rails console and test out the new type:

2.6.3 :001 > o = Order.new
 => #<Order id: nil, product: nil, dims: #<Dimension:0x00007fda47295e40 @depth="0", @width="0", @height="0">> 
2.6.3 :002 > o.product = 'a wig'
 => "a wig" 
2.6.3 :003 > o.dims.width = 9
 => 9 
2.6.3 :004 > o.dims.depth = 4
 => 4 
2.6.3 :005 > o.dims.height = 1
 => 1 
2.6.3 :006 > o.save
D, [2021-02-22T09:55:41.588716 #79057] DEBUG -- :    (0.2ms)  BEGIN
D, [2021-02-22T09:55:41.607391 #79057] DEBUG -- :   Order Create (0.8ms)  INSERT INTO "orders" ("product", "dims") VALUES ($1, $2) RETURNING "id"  [["product", "a wig"], ["dims", "(4,9,1)"]]
D, [2021-02-22T09:55:41.610457 #79057] DEBUG -- :    (1.2ms)  COMMIT
 => true 
2.6.3 :007 > 

See if you can follow the same process to set up the string_without_bang!

Conclusion

In this article, we walked through how to create two different unique data types in PostgreSQL.

The first, a domain type, allows you to create checks on your data and reuse those checks on multiple columns. The second, the composite type, lets you group data together in a meaningful way for storage in a single column. Finally, we learned how to hook into the Rails Attributes API to help instantiate your new type as an object that Ruby knows how to use.

Share this article: If you liked this article you might want to tweet it to your peers.

About the Author

Josh Alletto is an instructor at Code Platoon. In 2018 he changed careers from education to tech and has been excited to find that his communication and presentation skills have transferred over to his new technical career. He's always looking for a new challenge and a dedicated team to collaborate with.