DEV Community

Surya
Surya

Posted on • Edited on

Generating SQL Insert Statements from ActiveRecord Models in Ruby on Rails

#ruby #rails #activerecord #programming

📌 Overview & Preview

Goal: Convert any ActiveRecord model instance into a SQL INSERT statement — handling hashes, arrays, and enum columns gracefully.

Example Input:

# setup User.create!(name: "John", role: "admin", preferences: { theme: "dark" }, access_levels: ["read", "write"]) # Get insert statement User.last.insert_sql
Enter fullscreen mode Exit fullscreen mode

Expected SQL Output:

INSERT INTO users (id, name, role, preferences, access_levels, created_at, updated_at) VALUES (1, 'John', 1, '{"theme":"dark"}', '["read","write"]', '2025-04-21 10:00:00', '2025-04-21 10:00:00');
Enter fullscreen mode Exit fullscreen mode

📌 Part 1: Generating a Basic SQL Insert Statement for a Specific Model 

user = User.last insert_statement = <<-SQL INSERT INTO users (#{user.attributes.keys.join(', ')}) VALUES (#{user.attributes.values.map { |v| ActiveRecord::Base.connection.quote(v) }.join(', ')}) SQL puts insert_statement
Enter fullscreen mode Exit fullscreen mode

Problem: If your model has a Hash or Array attribute (like preferences), this raises:

TypeError: can't quote Hash

👉 Next up: handle non-scalar values.

📌 Part 2: Handling Hash and Array Columns

Solution: Serialize them to JSON before quoting.

require 'json' user = User.last columns = user.attributes.keys values = user.attributes.values.map do |value| value = value.to_json if value.is_a?(Hash) || value.is_a?(Array) ActiveRecord::Base.connection.quote(value) end insert_statement = <<-SQL INSERT INTO users (#{columns.join(', ')}) VALUES (#{values.join(', ')}); SQL puts insert_statement
Enter fullscreen mode Exit fullscreen mode

✅ Hashes and arrays now safely handled.

👉 Next up: handle enum columns.

📌 Part 3: Handling Enum Columns

Problem: Rails stores enums as integers in the DB but exposes them as strings.

Example:

class User < ApplicationRecord enum role: { guest: 0, admin: 1, super_admin: 2 } end
Enter fullscreen mode Exit fullscreen mode

If user.role is "admin", we need to insert 1 in SQL.

Solution:

enum_columns = User.defined_enums values = user.attributes.map do |attr_name, value| if enum_columns.key?(attr_name) enum_mapping = User.send(attr_name.pluralize) int_value = enum_mapping[value] ActiveRecord::Base.connection.quote(int_value) else value = value.to_json if value.is_a?(Hash) || value.is_a?(Array) ActiveRecord::Base.connection.quote(value) end end
Enter fullscreen mode Exit fullscreen mode

✅ Enum values converted to DB integers.

👉 Next up: make it generic.

📌 Part 4: Building a Generic SQL Insert Generator for Any Model

Solution:

def generate_insert_sql(model_instance) model_class = model_instance.class table_name = model_class.table_name enum_columns = model_class.defined_enums def safe_quote(model_class, attr_name, value, enum_columns) if enum_columns.key?(attr_name) enum_mapping = model_class.send(attr_name.pluralize) int_value = enum_mapping[value] ActiveRecord::Base.connection.quote(int_value) else case value when Hash, Array ActiveRecord::Base.connection.quote(value.to_json) else ActiveRecord::Base.connection.quote(value) end end end columns = model_instance.attributes.keys values = columns.map do |attr_name| value = model_instance.attributes[attr_name] safe_quote(model_class, attr_name, value, enum_columns) end <<-SQL INSERT INTO #{table_name} (#{columns.join(', ')}) VALUES (#{values.join(', ')});  SQL end
Enter fullscreen mode Exit fullscreen mode

✅ Works for any model instance.

👉 Next up: make it Rails-native.

📌 Part 5: Extending ActiveRecord::Base for a Reusable Method

Goal: Add an insert_sql instance method to all models via ApplicationRecord.

Solution:

class ApplicationRecord < ActiveRecord::Base self.abstract_class = true def insert_sql model_class = self.class table_name = model_class.table_name enum_columns = model_class.defined_enums def safe_quote(model_class, attr_name, value, enum_columns) if enum_columns.key?(attr_name) enum_mapping = model_class.send(attr_name.pluralize) int_value = enum_mapping[value] ActiveRecord::Base.connection.quote(int_value) else case value when Hash, Array ActiveRecord::Base.connection.quote(value.to_json) else ActiveRecord::Base.connection.quote(value) end end end columns = attributes.keys values = columns.map do |attr_name| value = attributes[attr_name] safe_quote(model_class, attr_name, value, enum_columns) end <<-SQL INSERT INTO #{table_name} (#{columns.join(', ')}) VALUES (#{values.join(', ')});  SQL end end
Enter fullscreen mode Exit fullscreen mode

✅ Now you can call:

puts User.first.insert_sql
Enter fullscreen mode Exit fullscreen mode

Sample Output:

INSERT INTO users (id, name, role, preferences, access_levels, created_at, updated_at) VALUES (1, 'John', 1, '{"theme":"dark"}', '["read","write"]', '2025-04-21 10:00:00', '2025-04-21 10:00:00');
Enter fullscreen mode Exit fullscreen mode 
puts Department.last.insert_sql
Enter fullscreen mode Exit fullscreen mode

Sample Output:

INSERT INTO departments (id, name, created_at, updated_at) VALUES (42, 'Engineering', '2025-04-20 12:00:00', '2025-04-20 12:00:00');
Enter fullscreen mode Exit fullscreen mode

Bonus: Works across your entire app — clean, DRY, and Rails-idiomatic.

📌 Final Thoughts

We went from:

  • 🔸 Hardcoding a single model (User)
  • 🔸 Handling hashes and arrays (preferences, access_levels)
  • 🔸 Handling enum columns (role)
  • 🔸 Building a generic function for any model
  • 🔸 Extending ActiveRecord elegantly with insert_sql

Top comments (0)