Rails 8 App: Create an Academic software app using SQL without using ActiveRecord – Part 1 | users | products | orders

Let’s create a Rails 8 app which use SQL queries with raw SQL instead of ActiveRecord. Let’s use the full Rails environment with ActiveRecord for infrastructure, but bypass AR’s ORM features for pure SQL writing. Let me guide you through this step by step:

Step 1: Create the Rails App with ActiveRecord and PostgreSQL (skipping unnecessary components)

rails new academic-sql-software --database=postgresql --skip-action-cable --skip-jbuilder --skip-solid --skip-kamal

What we’re skipping and why:

  • –skip-action-cable: No WebSocket functionality needed
  • –skip-jbuilder: No JSON API views needed for our SQL practice app
  • –skip-solid: Skips Solid Cache and Solid Queue (we don’t need caching or background jobs)
  • –skip-kamal: No deployment configuration needed

What we’re keeping:

  • ActiveRecord: For database connection management and ActiveRecord::Base.connection.execute()
  • ActionController: For creating web interfaces to display our SQL query results
  • ActionView: For creating simple HTML pages to showcase our SQL learning exercises
  • PostgreSQL: Our database for practicing advanced SQL features

Why this setup is perfect for App with raw SQL:

  • Minimal Rails app focused on database interactions
  • Full Rails environment for development conveniences
  • ActiveRecord infrastructure without ORM usage
  • Clean setup without unnecessary overhead

=> Open config/application.rb and comment the following for now:

# require "active_job/railtie"
...
# require "active_storage/engine"
...
# require "action_mailer/railtie"
# require "action_mailbox/engine"
...
# require "action_cable/engine"

=> Open config/environments/development.rb config/environments/production.rb config/environments/test.rb comment action_mailer

🤔 Why I am using ActiveRecord (even though I don’t want the ORM):

  • Database Connection Management: ActiveRecord provides robust connection pooling, reconnection handling, and connection management
  • Rails Integration: Seamless integration with Rails console, database tasks (rails db:create, rails db:migrate), and development tools
  • Raw SQL Execution: We get ActiveRecord::Base.connection.execute() which is perfect for our raw SQL writing.
  • Migration System: Easy table creation and schema management with migrations (even though we’ll query with raw SQL)
  • Database Configuration: Rails handles database.yml configuration, environment switching, and connection setup
  • Development Tools: Access to Rails console for testing queries, database tasks, and debugging

Our Learning Strategy: We’ll use ActiveRecord’s infrastructure but completely bypass its ORM methods. Instead of Student.where(), we’ll use ActiveRecord::Base.connection.execute("SELECT * FROM students WHERE...")

Step 2: Navigate to the project directory

cd academic-sql-software

Step 3: Verify PostgreSQL setup

# Check if PostgreSQL is running
brew services list | grep postgresql
# or
pg_ctl status

Database Foundation: PostgreSQL gives us advanced SQL features:

  • Complex JOINs (INNER, LEFT, RIGHT, FULL OUTER)
  • Window functions (ROW_NUMBER, RANK, LAG, LEAD)
  • Common Table Expressions (CTEs)
  • Advanced aggregations and subqueries

Step 4: Install dependencies

bundle install

What this gives us:

  • pg gem: Pure PostgreSQL adapter (already included with --database=postgresql)
  • ActiveRecord: For connection management only
  • Rails infrastructure: Console, generators, rake tasks

Step 5: Create the PostgreSQL databases

✗ rails db:create
Created database 'academic_sql_software_development'
Created database 'academic_sql_software_test

Our Development Environment:

  • Creates academic_sql_software_development and academic_sql_software_test
  • Sets up connection pooling and management
  • Enables us to use Rails console for testing queries: rails console then ActiveRecord::Base.connection.execute("SELECT 1")

Our Raw SQL Approach:

# We'll use this pattern throughout our app:
connection = ActiveRecord::Base.connection
result = connection.execute("SELECT s.name, t.subject FROM students s INNER JOIN teachers t ON s.teacher_id = t.id")

Why not pure pg gem:

  • Would require manual connection management
  • No Rails integration (no console, no rake tasks)
  • More boilerplate code for connection handling
  • Loss of Rails development conveniences

Why not pure ActiveRecord ORM:

  • We want to do SQL query writing, not ActiveRecord methods.
  • Need to understand database performance implications.
  • Want to practice complex queries that might be harder to express in ActiveRecord.

Step 6: Create Users table

mkdir -p db/migrate

class CreateUsers < ActiveRecord::Migration[8.0]
  def up
    # create users table
    execute <<~SQL
      CREATE TABLE users (
        id INT,
        username VARCHAR(200),
        email VARCHAR(150),
        phone_number VARCHAR(20)
      );
    SQL
  end

  def down
    execute <<~SQL
      DROP TABLE users;
    SQL
  end
end


class CreateOrders < ActiveRecord::Migration[8.0]
  def up
    # create table orders
    execute <<~SQL
    SQL
  end

  def down
    execute <<~SQL
    SQL
  end
end

execute <<~SQL is a Rails migration method that allows you to run raw SQL statements. Let me break it down:

Components:

  1. execute – A Rails migration method that executes raw SQL directly against the database
  2. <<~SQL – Ruby’s “squiggly heredoc” syntax for multi-line strings that automatically strips leading whitespace (read: https://www.rubyguides.com/2018/11/ruby-heredoc/)

Usage:

class SomeMigration < ActiveRecord::Migration[8.0]
  def change
    execute <<~SQL
      CREATE INDEX CONCURRENTLY idx_users_email_lower 
      ON users (LOWER(email));
    SQL
  end
end

Why use it?

  • Database-specific features: When you need PostgreSQL-specific syntax, MySQL features, etc.
  • Complex SQL: For operations that Rails DSL doesn’t support easily
  • Performance: Direct SQL can sometimes be more efficient
  • Raw SQL requirements: When you specifically need to write SQL (as mentioned in our requirement)

Example for creating a users table with raw SQL:

class CreateUsers < ActiveRecord::Migration[8.0]
  def up
    execute <<~SQL
      CREATE TABLE users (
        id BIGSERIAL PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        email VARCHAR(255) NOT NULL,
        created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
      );

      CREATE UNIQUE INDEX idx_users_email ON users(email);
    SQL
  end

  def down
    execute <<~SQL
      DROP TABLE IF EXISTS users;
    SQL
  end
end

The <<~SQL heredoc ends when it encounters the closing SQL keyword, and the ~ strips indentation so your SQL can be properly formatted within the Ruby code.

Q) 🤔 Is it a best practise to repeat the database constraints in model validation in Rails? DB level vs App level? or both?

Best Practice: Use BOTH 🎯

Why both levels are recommended:

Database Level (Last Line of Defense)

-- Database constraints
price DECIMAL(10,2) NOT NULL CHECK (price > 0),
category product_category NOT NULL,
title VARCHAR(250) NOT NULL

Pros:

  • ✅ Data integrity guarantee – No bad data can enter, ever
  • ✅ Performance – Database enforces constraints efficiently
  • ✅ Bulletproof – Protects against bulk operations, direct SQL, data imports
  • ✅ Concurrency safe – Prevents race conditions
  • ✅ Multi-application protection – If other apps access same DB

Application Level (User Experience)

validates :price, presence: true, numericality: { greater_than: 0 }
validates :category, inclusion: { in: %w[men women kids infants] }

Pros:

  • ✅ User-friendly errors – “Price must be greater than 0” vs “CHECK constraint violated”
  • ✅ Better UX – Validation before form submission
  • ✅ Easier testing – Can unit test validations
  • ✅ Conditional logic – Complex business rules
  • ✅ Framework features – Callbacks, custom validators

Real-world scenarios where each matters:

Database saves you when:

# Bulk operations bypass Rails validations
Product.update_all(price: -10)  # DB constraint prevents this

# Direct SQL injection attempts
# DB constraints are your last line of defense

App validations save you when:

# User gets friendly error instead of:
# PG::CheckViolation: ERROR: new row violates check constraint
@product = Product.new(price: -5)
@product.valid? # => false
@product.errors.full_messages # => ["Price must be greater than 0"]

Practical Implementation:

class Product < ApplicationRecord
  # App-level validations for UX
  validates :title, presence: true, length: { maximum: 250 }
  validates :price, presence: true, numericality: { greater_than: 0 }
  validates :category, inclusion: { in: %w[men women kids infants] }

  # Don't duplicate precision validation if DB handles it
  # The DECIMAL(10,2) constraint is sufficient at DB level
end

-- DB-level constraints for data integrity
CREATE TABLE products (
  id BIGSERIAL PRIMARY KEY,
  title VARCHAR(250) NOT NULL,
  price DECIMAL(10,2) NOT NULL CHECK (price > 0),
  category product_category NOT NULL,
  -- DB handles precision automatically with DECIMAL(10,2)
);

What NOT to duplicate:

  • ❌ Precision constraints – DECIMAL(10,2) handles this perfectly
  • ❌ Data type validation – DB enforces INTEGER, BOOLEAN, etc.
  • ❌ Complex regex patterns – Better handled in app layer

Conclusion:

Use both, but strategically:

  • Database: Core data integrity, type constraints, foreign keys
  • Application: User experience, business logic, conditional rules
  • Don’t over-duplicate simple type/precision constraints that DB handles well

This approach gives you belt and suspenders protection with optimal user experience.

to be continued … 🚀

Unknown's avatar

Author: Abhilash

Hi, I’m Abhilash! A seasoned web developer with 15 years of experience specializing in Ruby and Ruby on Rails. Since 2010, I’ve built scalable, robust web applications and worked with frameworks like Angular, Sinatra, Laravel, Node.js, Vue and React. Passionate about clean, maintainable code and continuous learning, I share insights, tutorials, and experiences here. Let’s explore the ever-evolving world of web development together!

Leave a comment