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
SOCIAL SHARE CARD GENERATOR