logo-new mail facebook Dribble Social Icon Linkedin Social Icon Twitter Social Icon Github Social Icon Instagram Social Icon Arrow_element diagonal-decor rectangle-decor search arrow circle-flat

Welcome to Tandem. We were formerly known as DevMynd. Read more about our new identity.

February 8, 2017

DB Generated Values and ActiveRecord

Chief Technology Officer

Hello everyone!

Before we get started I should warn you this post doesn’t contain anything groundbreaking or revolutionary. It’s just a simple journey of how a minor nuance of ActiveRecord cost me an afternoon and how I thought through that problem. Since I dug deep into documentation and source code returning alive and coherent, I figured I’d share the journey, complete with anti-climatic ending.

The problem begins

Everything started because I wanted to have Postgres generate a default UUID on a column. This is done easily if you set the ID to a type UUID but we also needed to maintain a sequential integer on each record. The operations team would use this ID in verbal communications. Things like, “hey did you get orders 345 and 346″ roll of the tongue easier than, ” I just packed 6e0e and 7ffe, getting started on 1d2c.” Humans right?

In case you’re wondering what that migration looks like, it’s nothing special.

class AddUuidToOrder < ActiveRecord::Migration[5.0]
  def change
    execute 'CREATE EXTENSION IF NOT EXISTS "uuid-ossp"'
    add_column :orders, :uuid, :uuid, default: "uuid_generate_v4()", 

Now because we’re doing Postgres specific stuff, we’ll need to switch from a schema.rb to a structure.sql. The link provides details on that but don’t forget to delete your schema.rb and update CI builds to use rake db:structure:load lest ye breaketh thine build. Speaking from experience here folks.

After executing migration and calling order = Order.create, our order’s uuid would still be nil despite it being generated and set on the database record. When using a constant value or a Ruby proc, Rails is smart enough retrieve and set that attribute on the model, but unless it’s the ID column Rails won’t automatically keep the instance of your model and the DB record in sync.

So how does AR get the generated IDs?

The answer to that is, it depends.

I first went directly to psql with insert statements to see what gets returned from an insert statement. Turns out nothing unless you add a RETURNING clause to your INSERT statement. Digging into ActiveRecord, it turns out this is exactly what is going on. There is even a configuration option to instead query the current value of the sequence as a fallback for older versions or if you’re insane. Nobody wants to make two trips to the moon (round trip distance to database, relative to registers < RAM < Disk < etc…), not for frivolous reasons at least.

Turns out this is quite common. I was reminded of OracleSequenceMaxValueIncrementer from my Spring JDBC days, although that seems covered in Ruby. Then there’s MySQL which doesn’t support RETURNING (or RETURNING WITH in Oracle’s case) in which case AR uses the generic implementation for inserts. I assume ActiveRecord must make that 2nd trip to the moon because I gave up digging into how the MySQL driver gets the last id.

Of course these trips are protected by transactions automatically so you don’t have to worry about wasting precious integers.

What did I want to have?

I’d really like to be to specify additional columns to be returned. I’m fine with it being driver specific because who has ever switched their app’s database? One school of thought would be to return everything and reset it ensuring things are in sync, although that results in a lot of network traffic. Perhaps I could expect Rails to figure out which columns need to be returned. It already packs me a sack lunch complete with squeeze yogurt and only because the community holds it to that high of a standard.

What did I actually do?

Although what I wanted is actually already being thought through, I needed a fix now. So this happened:

# retrieve the database generated number attribute after create
after_create :reload

Yeah…. wholesale reload via a callback. Deal with it.

I later realized generating and setting the slug in Ruby was probably a better solution but nobody wins by erasing history with a shelf full of victories. But like most problems they, are self imposed. I wanted to have Postgres generate a default UUID. Sigh, ugh…. programming.

Tandem is custom software development company in Chicago with practice areas in web application and custom mobile development.

Related Blog Posts
Let’s do something great together

We do our best work in close collaboration with our clients. Let’s find some time for you to chat with a member of our team.

Contact Us