Building a Minimal ORM in Ruby: From SQL to ActiveRecord

Hudson

Jun 26

RUBY

English, ORM, RUBY

Why build an ORM?

Ruby on Rails is powerful partly because ActiveRecord hides a large amount of database plumbing behind a consistent object-oriented API. A call such as:

1	Comic.find(6)

looks simple, but several things happen underneath it:

The model is mapped to a database table.
A SQL query is generated.
Query parameters are bound safely.
PostgreSQL executes the query.
The result is converted into a Ruby object.

Understanding this pipeline makes it easier to use ActiveRecord deliberately instead of treating it as magic.

This article builds a deliberately small ORM on top of the pg gem. It is not intended to replace ActiveRecord. Its purpose is to make the core mechanism visible.

The complete example is available in the simple-orm-ruby repository.

What we are going to build

The example will support two operations:

1 2	Comic.all Comic.find(6)

The implementation has three small layers:

Database: owns the PostgreSQL connection.
Record: provides behavior shared by models.
Comic: maps a Ruby class to the comics table.

A real ORM would also need inserts, updates, deletes, associations, validations, transactions, type casting, connection pooling and much more. We will intentionally leave those features out so the essential design remains easy to inspect.

1. Project setup

Create a project with a Gemfile:

1
2
3

source "https://rubygems.org"

gem "pg"

Install the dependency:

1	bundle install

For the example, assume PostgreSQL contains a table similar to this:

CREATE TABLE comics (
  id BIGSERIAL PRIMARY KEY,
  title TEXT NOT NULL
);

The schema is not part of the ORM itself. It is the database contract that the model expects.

2. Managing the database connection

The original version used a global connection and hard-coded credentials. That approach is convenient for a quick experiment, but it makes testing, configuration and connection lifecycle harder to control.

A small connection module is a better boundary:

# connection.rb
require "pg"

module Database
  module_function

  def connection
    @connection ||= PG.connect(
      host: ENV.fetch("PGHOST", "127.0.0.1"),
      port: ENV.fetch("PGPORT", "5432"),
      dbname: ENV.fetch("PGDATABASE", "simple_orm_development"),
      user: ENV.fetch("PGUSER", "postgres"),
      password: ENV.fetch("PGPASSWORD", "")
    )
  end
end

The important design decision is not the exact environment variable names. It is keeping connection configuration outside the model code. The same code can then run locally, in CI or in production with different environment variables.

For a production application, a single global connection would still be insufficient. The application would need a connection pool so multiple requests could safely share database connections.

3. Building the base record class

The base class will provide:

a table name;
all to load every row;
find to load one row by primary key;
conversion from PostgreSQL rows into Ruby objects.

# record.rb
require_relative "connection"

class Record
  class << self
    def table_name(value = nil)
      @table_name = value if value
      @table_name || "#{name.downcase}s"
    end

    def all
      result = connection.exec("SELECT * FROM #{table_name}")
      build_records(result)
    end

    def find(id)
      result = connection.exec_params(
        "SELECT * FROM #{table_name} WHERE id = $1 LIMIT 1",
        [id]
      )

      result.ntuples.zero? ? nil : new(result[0])
    end

    private

    def connection
      Database.connection
    end

    def build_records(result)
      result.to_a.map { |attributes| new(attributes) }
    end
  end

  def initialize(attributes = {})
    @attributes = attributes.transform_keys(&:to_s)
  end

  def [](attribute)
    @attributes.fetch(attribute.to_s)
  end

  def attributes
    @attributes.dup
  end

  def inspect
    "#<#{self.class.name} #{attributes.inspect}>"
  end
end

3.1. Why use `exec_params`?

This version deliberately uses:

1	connection.exec_params(sql, [id])

instead of interpolating the value into the SQL string:

1 2	# Do not do this with user-controlled input. "SELECT * FROM comics WHERE id = #{id}"

Direct interpolation can create SQL injection vulnerabilities. PostgreSQL parameter binding keeps the SQL structure separate from the values supplied by the caller.

The table name is interpolated because SQL parameters cannot be used for identifiers. In this minimal implementation, the table name is controlled by the model code, not by a request parameter. A production ORM must also validate or safely quote dynamic identifiers.

3.2. Why does `find` return `nil`?

Returning nil when no row exists keeps this minimal API explicit:

comic = Comic.find(999)

if comic.nil?
  puts "Comic not found"
end

Frameworks often provide both non-bang and bang methods, such as find and find!, where the latter raises an exception. That distinction is useful, but it is outside this small implementation.

4. Defining a model

A model only needs to inherit from Record and declare the table it represents:

# comic.rb
require_relative "record"

class Comic < Record
  table_name "comics"
end

The explicit table name is safer than relying entirely on inflection. A simple rule such as Comic → comics works for this example, but it breaks for irregular names such as Person → people.

5. Using the ORM

# app.rb
require_relative "comic"

puts "All comics:"
Comic.all.each do |comic|
  puts "#{comic[:id]}: #{comic[:title]}"
end

comic = Comic.find(6)

if comic
  puts "Found comic: #{comic[:title]}"
else
  puts "Comic not found"
end

Run the example with:

1	ruby app.rb

The ORM is now responsible for querying and materializing records. The application is responsible for deciding what to do with those records.

That separation matters. A data-access layer should return data, not print to standard output or update UI state as the original example did.

6. What this implementation is still missing

The example demonstrates the core mapping, but it is far from a production ORM. A serious implementation would need to address:

INSERT, UPDATE and DELETE operations;
query composition such as where, order and limit;
SQL identifier quoting;
type casting from PostgreSQL values to Ruby values;
validations and lifecycle callbacks;
transactions;
associations and joins;
migrations and schema management;
prepared statements;
connection pooling;
logging, tracing and query instrumentation.

For example, adding a where method is not just a matter of concatenating a condition to the SQL string. Values must still be parameterized, and column names must come from a trusted or validated set.

7. How this relates to ActiveRecord

ActiveRecord solves the same fundamental problem at a much larger scale:

1	Comic.where(published: true).order(created_at: :desc).limit(10)

Behind this interface, ActiveRecord builds a query representation, generates SQL, binds parameters, executes the query and wraps each row in a model object.

The difference is the amount of infrastructure around the core idea. ActiveRecord also handles associations, scopes, callbacks, validations, transactions, schema conventions, adapter differences and integration with the Rails lifecycle.

Building a minimal ORM is therefore not about recreating Rails in a few dozen lines. It is about isolating the smallest useful abstraction and seeing exactly where the complexity begins.

Conclusion

An ORM is a translation layer between two different models of the world:

relational databases organize data into tables, rows and columns;
object-oriented programs organize behavior and state into objects.

The essential responsibilities are straightforward:

map a model to a table;
execute SQL safely;
convert rows into objects;
expose a useful application-level interface.

The difficult part is everything required to make that interface reliable at scale: query composition, transactions, concurrency, type systems, performance and failure handling.

A small ORM cannot replace ActiveRecord, but it can make ActiveRecord much easier to reason about.