BetterStructureSql - Clean Database Schema Dumps for Rails (Beta)

I've been working on BetterStructureSql, a Ruby gem that generates clean, deterministic database schema dumps for Rails without using pg_dump, mysqldump, or other external tools.

Currently in beta (v0.2.1) and running in 4 production applications. Looking for feedback from the community.

The Problem

Rails' default pg_dump creates noisy schema files with version-specific comments and metadata that pollute git diffs. This makes developers avoid using advanced database features like triggers, materialized views, and custom types.

pg_dump output:

--

-- PostgreSQL database dump

--

-- Dumped from database version 15.4

-- Dumped by pg_dump version 15.4



SET statement_timeout = 0;

SET lock_timeout = 0;

-- ... 50+ lines of noise ...

Different PostgreSQL versions create different output, causing merge conflicts on metadata instead of actual schema changes.

The Solution

BetterStructureSql uses pure Ruby to query database system catalogs directly:

• PostgreSQL: information_schema and pg_catalog
  


• MySQL: information_schema and mysql system tables


• SQLite: sqlite_master and PRAGMA introspection

BetterStructureSql output:

SET client_encoding = 'UTF8';



-- Extensions

CREATE EXTENSION IF NOT EXISTS "pgcrypto" WITH SCHEMA public;



-- Tables

CREATE TABLE users (

  id BIGSERIAL PRIMARY KEY,

  email VARCHAR NOT NULL,

  created_at TIMESTAMP(6) NOT NULL,

  updated_at TIMESTAMP(6) NOT NULL

);



-- Indexes

CREATE INDEX index_users_on_email ON users (email);



-- Functions

CREATE FUNCTION update_updated_at_column()

RETURNS TRIGGER AS $$

BEGIN

  NEW.updated_at = NOW();

  RETURN NEW;

END;

$$ LANGUAGE plpgsql;



-- Triggers

CREATE TRIGGER update_users_updated_at

  BEFORE UPDATE ON users

  FOR EACH ROW

  EXECUTE FUNCTION update_updated_at_column();

Same output every time. Change one trigger, see one trigger in the diff.

Installation

gem 'better_structure_sql'

bundle install

rails generate better_structure_sql:install

rails db:schema:dump_better

Key Features

1. Pure Ruby - No External Dependencies

No pg_dump, mysqldump, or sqlite3 CLI required. Just the database gem (pg, mysql2, or sqlite3). Works anywhere Rails runs.

2. Schema Versioning with Deduplication

Store schema versions in your database with hash-based deduplication:

rails db:schema:store

Only creates new versions when schema actually changes:

rails db:schema:versions



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

Includes web UI for browsing and downloading versions:

# config/routes.rb

authenticate :user, ->(user) { user.developer? || user.admin? } do

  mount BetterStructureSql::Engine, at: '/schema_versions'

end

Why this matters: Production schema often differs from local development. New developers join, run migrations locally, but their schema doesn't match production. The web UI lets developers download the actual production schema without needing database access—critical for debugging production-specific issues.

3. Multi-File Output for Large Schemas

Real-world problem: I have an application with 300+ tables. The single structure.sql file was over 8,000 lines—a nightmare to work with. Git diffs were useless, code reviews impossible, and AI assistants couldn't handle it.

Multi-file mode solves this:

# config/initializers/better_structure_sql.rb

BetterStructureSql.configure do |config|

  config.output_path = 'db/schema'  # directory instead of file

  config.max_lines_per_file = 500

end

Generates organized directory structure with numbered prefixes for load order:

db/schema/

├── _header.sql              # SET statements

├── _manifest.json           # Load order metadata

├── 01_extensions/

│   └── 000001.sql

├── 02_types/

│   └── 000001.sql

├── 03_functions/

│   ├── 000001.sql

│   └── 000002.sql

├── 04_sequences/

│   └── 000001.sql

├── 05_tables/

│   ├── 000001.sql          # First ~500 lines of tables

│   ├── 000002.sql          # Next ~500 lines

│   ├── 000003.sql

│   ├── 000004.sql

│   └── 000005.sql          # 300 tables split across 5 files

├── 06_indexes/

│   ├── 000001.sql

│   └── 000002.sql

├── 07_foreign_keys/

│   └── 000001.sql

├── 08_views/

│   └── 000001.sql

├── 09_triggers/

│   └── 000001.sql

└── 20_migrations/

    └── 000001.sql          # schema_migrations INSERT

How it works:

• Splits each object type (tables, indexes, etc.) into ~500 line chunks


• Numbers directories by dependency order (extensions → types → tables → foreign keys)


• Includes _manifest.json with file count, line count, and load order


• Stores as ZIP archive in database for schema versioning

Benefits for 300+ table schemas:

• 
  Git diffs: Only see files that changed (e.g., added one table? One file changes, not 8,000 lines)


• 
  Code review: Review specific sections (just check 05_tables/000003.sql)


• 
  AI assistants: 500-line files work perfectly—no "file too large" errors


• 
  Navigation: Find triggers in 09_triggers/, views in 08_views/
  


• 
  Performance: Incremental file writing, handles 50,000+ objects

Loading multi-file schemas:

rails db:schema:load_better  # Reads manifest, loads files in order

Automatically detects single-file vs multi-file mode. For multi-file, uses manifest to load in correct dependency order.

4. Complete PostgreSQL Feature Support

• Tables, indexes, foreign keys, constraints


• Triggers and functions (plpgsql, SQL)


• Views and materialized views


• Extensions (pgcrypto, uuid-ossp, pg_trgm, etc.)


• Custom types (ENUMs, domains)


• Sequences


• Partial indexes and expression indexes

Multi-Database Support

PostgreSQL is production-ready. MySQL and SQLite adapters are experimental.

Production Experience

Running in 4 production applications including:

• E-commerce platform with 200+ tables


• SaaS application with triggers and materialized views


• API service with custom PostgreSQL ENUMs


• Multi-tenant application with extensive use of database functions

Zero issues with deterministic output. Schema versioning provides clear audit trail for schema evolution.

Configuration Example

# config/initializers/better_structure_sql.rb

BetterStructureSql.configure do |config|

  # Output mode

  config.output_path = 'db/structure.sql'



  # Replace default rake tasks (optional)

  config.replace_default_dump = true



  # Schema versioning

  config.enable_schema_versions = true

  config.schema_versions_limit = 10



  # Feature toggles

  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

Migration Example

Using database triggers 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

Materialized views 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

Looking for Feedback

Beta Status (v0.2.1):

• PostgreSQL support is production-ready


• MySQL and SQLite are experimental


• APIs are stable but may see refinements before v1.0

Feedback Needed:

• Edge cases with your database schema


• Integration with specific Rails setups


• Feature requests for v1.0


• Performance with very large schemas (1000+ tables)


• MySQL and SQLite compatibility issues

Links

• GitHub Repository


• Documentation


• RubyGems

Try It

gem 'better_structure_sql'

bundle install

rails generate better_structure_sql:install

rails db:schema:dump_better

If you try BetterStructureSql, please share your experience. Issues and pull requests welcome on GitHub.

Made by sebyx07