The Solution to Rails schema.sql Fickleness: BetterStructureSql

Database schema dumps play a larger role in production engineering than most teams realize. A clean, deterministic, version-stable schema file becomes crucial once an application evolves beyond a simple CRUD workflow. Rails traditionally relies on external tools like pg_dump or mysqldump, but these introduce noise, metadata differences between versions, and inconsistent ordering that complicate code reviews, automation, and onboarding.

I have been testing a new approach called BetterStructureSql, a Ruby gem that produces stable, minimal, and deterministic schema dumps using Ruby-level introspection instead of external CLI utilities. This method is currently in beta, but it is already running inside production applications without issues. The goal is to remove nonessential metadata, improve versioning clarity, and support advanced database features without relying on database-specific dump tools.

Why pg_dump and similar tools create problems

Tools like pg_dump embed environment details and version metadata into every dump. These often include client version, server version, random SET statements, and miscellaneous internal config values. None of these represent structural changes to your schema, yet they show up in diffs and code reviews.

Example content frequently found in default dumps:

-- PostgreSQL database dump
-- Dumped from database version 15.4
-- Dumped by pg_dump version 15.4
SET statement_timeout = 0;
SET lock_timeout = 0;

This type of output varies across machines, PostgreSQL versions, and environments, which leads to merge conflicts unrelated to actual schema evolution. Once teams adopt triggers, materialized views, custom PostgreSQL types, or multiple database engines, these inconsistencies become difficult to maintain.

A Ruby-based approach to schema introspection

BetterStructureSql avoids external binaries completely. Instead, it queries database catalogs directly:

PostgreSQL: pg_catalog and information_schema

MySQL: information_schema and mysql system tables

SQLite: sqlite_master and PRAGMA-based introspection

The output reflects only structural information. For example:

SET client_encoding = 'UTF8';

CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,
  email VARCHAR NOT NULL,
  created_at TIMESTAMP(6) NOT NULL,
  updated_at TIMESTAMP(6) NOT NULL
);

CREATE INDEX index_users_on_email ON users (email);

If a trigger changes, only the trigger section changes. If one table changes, only that table’s definition updates. Schema evolution becomes explicit and predictable.

How the gem fits into a Rails workflow

Installation is straightforward:

gem 'better_structure_sql'
bundle install
rails generate better_structure_sql:install
rails db:schema:dump_better

This produces a clean structure dump consistent across all environments as long as the database schema is the same.

Versioning and deduplication for real production schemas

In many teams, local development schemas diverge from production schemas due to migrations, rollbacks, or environment-specific changes. BetterStructureSql introduces internal version tracking with deduplication based on structural checksums:

rails db:schema:store
rails db:schema:versions

Output example:

ID  | Hash     | Created                 | Size
3   | a3f5c9d2 | 2025-01-20 14:30:15     | 125 KB
2   | b7e2d1c4 | 2025-01-19 10:15:42     | 98 KB
1   | c9f8a3b2 | 2025-01-18 08:45:30     | 85 KB

Only meaningful changes create new versions. The gem also provides a small Rails engine that exposes a web interface for authenticated developers to browse or download schema versions without needing direct database access.

Multi-file output for large applications

Single-file schema dumps are impractical for very large systems. One of my test environments contains more than three hundred tables and thousands of lines of schema definitions. Reviewing or diffing such a file is inefficient.

BetterStructureSql provides structured output through directory-based organization with chunking:

db/schema/
  _header.sql
  _manifest.json
  01_extensions/
  02_types/
  03_functions/
  04_sequences/
  05_tables/
  06_indexes/
  07_foreign_keys/
  08_views/
  09_triggers/
  20_migrations/

Each folder contains files capped at around five hundred lines, making diffs meaningful. The manifest file records order and dependency chain so the loader can reconstruct the full schema reliably.

Loading is simple:

rails db:schema:load_better

The loader detects single-file or multi-file mode automatically.

Supported PostgreSQL features

BetterStructureSql handles nearly all real production use cases:

Tables

Indexes

Foreign keys

Sequences

Functions and triggers

Views and materialized views

Extensions

Custom ENUM types

The MySQL and SQLite adapters currently support the majority of their respective feature sets but are still considered experimental.

Production environment observations

The gem has been deployed in several production systems that include:

A multi-tenant SaaS platform

An e-commerce environment with extensive triggers

A reporting application using materialized views

A backend with custom ENUM types and complex constraints

In each case, deterministic output and structured diffs significantly improved schema review and deployment workflows.

Configuration example for Rails

BetterStructureSql.configure do |config|
  config.output_path = 'db/structure.sql'
  config.replace_default_dump = true
  config.enable_schema_versions = true
  config.schema_versions_limit = 10
  config.include_extensions = true
  config.include_functions = true
  config.include_triggers = true
  config.include_views = true
  config.include_materialized_views = true
  config.include_custom_types = true
end

This setup enables deterministic dumps, versioning, and full PostgreSQL feature inclusion.

Example: adding a trigger with clean diffs

class AddUpdatedAtTrigger < ActiveRecord::Migration[7.0]
  def up
    execute <<~SQL
      CREATE FUNCTION update_updated_at_column()
      RETURNS TRIGGER AS $$
      BEGIN
        NEW.updated_at = NOW();
        RETURN NEW;
      END;
      $$ LANGUAGE plpgsql;

      CREATE TRIGGER update_users_updated_at
      BEFORE UPDATE ON users
      FOR EACH ROW
      EXECUTE FUNCTION update_updated_at_column();
    SQL
  end

  def down
    execute "DROP TRIGGER IF EXISTS update_users_updated_at ON users;"
    execute "DROP FUNCTION IF EXISTS update_updated_at_column();"
  end
end

Because the dump is deterministic, only the relevant trigger and function sections update when the migration runs.

Example: materialized view for performance

class CreateUserStatsView < ActiveRecord::Migration[7.0]
  def up
    execute <<~SQL
      CREATE MATERIALIZED VIEW user_statistics AS
      SELECT
        status,
        COUNT(*) AS total_count,
        COUNT(*) FILTER (WHERE created_at > NOW() - INTERVAL '30 days') AS recent_count
      FROM users
      GROUP BY status;

      CREATE UNIQUE INDEX index_user_stats_on_status
      ON user_statistics (status);
    SQL
  end

  def down
    execute "DROP MATERIALIZED VIEW IF EXISTS user_statistics;"
  end
end

This type of structure consistently appears in diffs exactly as written without noise.

Final notes

BetterStructureSql represents a shift toward deterministic, dependency-aware, developer-focused schema dumping for Rails. By removing version metadata and providing stable ordering, it simplifies schema reviews, supports clean automation, and keeps local and production environments aligned. For teams working with large or complex schemas, the multi-file feature and versioning system provide practical improvements not available in traditional pg_dump based workflows.

— MASHRAF AIMAN
AGS NIRAPAD Alliance
Co-founder, CTO, OneBox
Co-founder, CTO, Zuttle