DEV Community

Cover image for BetterStructureSql - Clean Database Schema Dumps for Rails (Beta)
sebyx07
sebyx07

Posted on

BetterStructureSql - Clean Database Schema Dumps for Rails (Beta)

#ruby #rails #database #postgres

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 ...
Enter fullscreen mode Exit fullscreen mode

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();
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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

Feature PostgreSQL 12+ MySQL 8.0+ SQLite 3.35+
Tables & Indexes ✓ ✓ ✓
Triggers ✓ ✓ ✓
Views ✓ ✓ ✓
Materialized Views ✓ - -
Functions ✓ plpgsql ✓ stored procs -
Extensions ✓ - -
Custom ENUMs ✓ inline only via CHECK

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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

Try It 

gem 'better_structure_sql'
bundle install
rails generate better_structure_sql:install
rails db:schema:dump_better
Enter fullscreen mode Exit fullscreen mode

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

Made by sebyx07

Top comments (0)