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:
pggem: 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_developmentandacademic_sql_software_test - Sets up connection pooling and management
- Enables us to use Rails console for testing queries:
rails consolethenActiveRecord::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:
execute– A Rails migration method that executes raw SQL directly against the database<<~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 … 🚀
The Rails Drop 