How Ruby on Rails Creates Unique IDs ?

Hai this is Krishna, Senior Software Developer at Actionboard Private Limited in Chennai. This is my first blog, Why I choose this as my first blog?

Here is the answer. One of my hobby project, we moved our database server planetscale to Nion.tech and we imported the backup. Everything was perfect. We have a model Article and the table contains ID as a primary key which is numeric and auto generated field. And the last ID was generated as 70. But after it migrated to the new database server, new records are being created with old IDs. So there I started digging How rails creates Unique primary Keys.

Let’s dive more into the topic

In Rails, ActiveRecord automatically generates unique primary keys (usually integers) for each new record. This process relies on the underlying database management system (DBMS) to manage these IDs.

Here's a detailed explanation on how Rails handles the creation of unique IDs and where the last ID is stored

Primary Key Generation

By default, Rails uses an auto-incrementing integer primary key for each table. When a new record is inserted, the database automatically assigns the next available integer as the primary key.

Database-Specific Behaviour

Different databases handle auto-incrementing primary keys differently:

• PostgreSQL: Uses sequences. Each table with an auto-incrementing primary key has an associated sequence object that generates unique numbers.

• MySQL: Uses the AUTO_INCREMENT attribute on columns.

• SQLite: Uses the AUTOINCREMENT keyword for primary keys.

I’ve used PostgreSQL.

Where the Last ID is Stored

The last used ID is stored in the database's sequence object for PostgreSQL

Sequence Object

For each table with an auto-incrementing primary key, PostgreSQL creates a sequence object. For example, for the users table, it might create a sequence called usersidseq

Retrieving the Last ID

You can query the sequence object to retrieve the current value of the sequence, which represents the last generated ID.

SELECT currval('users_id_seq');

Understanding currval and nextval

• nextval: Advances the sequence and returns the next value. It increments the sequence.

• currval: Returns the last value returned by nextval in the current session. It does not increment the sequence.

we couldn’t call currval directly because currval needs nextval to have been called at least once in the current session to have a current value to return.

Managing IDs in Rails

Rails handles primary key generation seamlessly through ActiveRecord, but you can also interact with sequences directly if needed

Creating a Record

When you create a new record in Rails, ActiveRecord sends an INSERT SQL statement to the database. The database then generates the next available ID and assigns it to the new record.

user = User.create(name: "John Doe")
puts user.id # This will print the newly generated ID

Accessing the Last Inserted ID

After creating a new record, Rails provides the id attribute of the ActiveRecord object, which contains the primary key assigned by the database.

Resetting the Sequence

Now we have an idea about how the unique ID is being generated. So how i solved the actual problem? Yes, the answer is we’ve to reset the last ID sequence.

Resetting the sequence in PostgreSQL using the below code.

SELECT setval('users_id_seq', (SELECT MAX(id) FROM users));

In Rails we use below code to resetting the ID sequence, below is the method i’ve used.

ActiveRecord::Base.connection.tables.each do |table|
  primary_key   = ActiveRecord::Base.connection.primary_key(table)
  sequence_name = "#{table}_#{primary_key}_seq"

  max_id_result = ActiveRecord::Base.connection.execute("SELECT MAX(#{primary_key}) FROM #{table}")
  max_id        = max_id_result.first["max"]

  if max_id
    ActiveRecord::Base.connection.execute("SELECT setval('#{sequence_name}', #{max_id})")
  end
end

What is in the above code?

1. Fetch all tables: ActiveRecord::Base.connection.tables fetches the list of all tables in the database.

2. Identify the primary key: For each table, ActiveRecord::Base.connection.primary_key(table) gets the primary key column, which is typically id.

3. Determine the sequence name: Constructs the sequence name using the table name and primary key column.

4. Fetch the maximum ID: Executes a SQL query to get the maximum ID value currently in the table.

5. Set the sequence: Executes a setval statement to set the sequence to the maximum ID, ensuring future inserts will use the correct next value.

I Hope i’ve discussed something interesting and I’m happy to write this as my first blog. If any grammatical error is there please suggest me i’ll correct it in my next blog and please do support me with your likes and sharing, Thanks and Happy reading!!!

* Where there is Will there is Way *