Schema Dump

The CQL::SchemaDump class provides functionality to reverse-engineer existing database schemas and generate CQL schema definition files. This is useful when you have an existing database and want to create a CQL schema that matches its structure.

🆕 Integration with Migrations: Schema dumping is now integrated with the migration system to provide automatic schema file synchronization. See Integrated Migration Workflow for the complete workflow.

Overview

Schema dumping allows you to:

Inspect existing database tables, columns, indexes, and foreign keys
Automatically map SQL types to the correct CQL column methods
Generate idiomatic CQL schema files using proper method calls
Smart detection of timestamps for clean timestamps macro usage
Support SQLite, PostgreSQL, and MySQL databases

Basic Usage

Creating a Schema Dumper

require "cql"

# For SQLite
dumper = CQL::SchemaDump.new(CQL::Adapter::SQLite, "sqlite3://path/to/database.db")

# For PostgreSQL
dumper = CQL::SchemaDump.new(CQL::Adapter::Postgres, "postgresql://user:pass@localhost/database")

# For MySQL
dumper = CQL::SchemaDump.new(CQL::Adapter::MySql, "mysql://user:pass@localhost/database")

Generating Schema Files

# Dump schema to a file
dumper.dump_to_file("src/schemas/my_schema.cr", :MySchema, :my_schema)

# Generate schema content as a string
content = dumper.generate_schema_content(:MySchema, :my_schema)
puts content

# Always close the connection when done
dumper.close

Generated Schema Format

The generated schema files follow the standard CQL schema format:

MySchema = CQL::Schema.define(
  :my_schema,
  adapter: CQL::Adapter::SQLite,
  uri: "sqlite3://path/to/database.db") do

  table :users do
    primary :id, Int32
    text :name
    text :email, null: true
    integer :age
    timestamps
  end

  table :posts do
    primary :id, Int32
    text :title
    text :body
    integer :user_id, null: true
    foreign_key [:user_id], references: :users, references_columns: [:id]
  end
end

Type Mapping

The schema dumper automatically maps SQL types to the appropriate CQL column methods:

SQLite Type Mapping

INTEGER → integer (Int32)
BIGINT → bigint (Int64)
TEXT → text (String)
VARCHAR → text (String)
BOOLEAN → boolean (Bool)
TIMESTAMP → timestamp (Time)
BLOB → blob (Slice(UInt8))
JSON → json (JSON::Any)

PostgreSQL Type Mapping

integer → integer (Int32)
bigint → bigint (Int64)
text → text (String)
varchar → text (String)
boolean → boolean (Bool)
timestamp → timestamp (Time)
bytea → blob (Slice(UInt8))
jsonb → json (JSON::Any)

MySQL Type Mapping

INT → integer (Int32)
BIGINT → bigint (Int64)
VARCHAR → text (String)
TEXT → text (String)
TINYINT(1) → boolean (Bool)
DATETIME → timestamp (Time)
BLOB → blob (Slice(UInt8))
JSON → json (JSON::Any)

Features

Table Structure

Extracts table names and generates table blocks
Identifies primary keys and generates primary declarations
Uses the correct CQL column methods (integer, text, boolean, etc.)
Maps all column types with proper nullability
Handles default values when present

Column Method Generation

Automatically uses the appropriate CQL column methods instead of generic column calls
Maps INTEGER → integer, TEXT → text, BOOLEAN → boolean, etc.
Generates clean, idiomatic CQL schema definitions
Follows the same patterns used throughout the CQL codebase

Before vs After Example

Before (Generic approach):

table :users do
  primary :id, Int32
  column :name, String
  column :age, Int32
  column :active, Bool
  column :created_at, Time
  column :updated_at, Time
end

After (CQL-specific methods):

table :users do
  primary :id, Int32
  text :name
  integer :age
  boolean :active
  timestamps
end

Foreign Keys

Detects foreign key relationships
Generates foreign_key declarations with proper references
Includes ON DELETE and ON UPDATE actions when specified
Supports composite foreign keys

Smart Timestamps

Automatically detects created_at and updated_at columns
Generates the timestamps macro when both are present
Excludes individual timestamp columns when using the macro
Produces cleaner, more maintainable schema definitions

Complete Example

require "cql"

# Connect to existing database
dumper = CQL::SchemaDump.new(CQL::Adapter::SQLite, "sqlite3://myapp.db")

begin
  # Generate and save schema
  dumper.dump_to_file("src/schemas/myapp_schema.cr", :MyAppDB, :myapp_db)
  puts "Schema successfully dumped to src/schemas/myapp_schema.cr"

  # You can also get the content as a string
  schema_content = dumper.generate_schema_content(:MyAppDB, :myapp_db)
  puts "Generated schema:"
  puts schema_content

ensure
  # Always close the connection
  dumper.close
end

Use Cases

Database Migration

Convert an existing database to use CQL:

Dump the existing schema using SchemaDump
Review and adjust the generated schema as needed
Use the schema in your CQL application

🆕 Migration Integration

The schema dump functionality is now integrated with the migration system:

# Automatic schema synchronization with migrations
config = CQL::MigratorConfig.new(
  schema_file_path: "src/schemas/app_schema.cr",
  schema_name: :AppSchema,
  auto_sync: true
)

migrator = AppDB.migrator(config)

# Bootstrap from existing database
migrator.bootstrap_schema  # Uses SchemaDump internally

# Run migrations - schema file automatically updated
migrator.up  # Uses SchemaDump to keep schema file current

# Manual schema updates
migrator.update_schema_file  # Uses SchemaDump to regenerate schema

Schema Documentation

Generate up-to-date schema documentation by dumping the current database structure.

Testing

Create test schemas that match your production database structure.

Limitations

Views are not included in the dump (only base tables)
Indexes are not yet included (coming in future versions)
Some database-specific types may fall back to generic text columns
Complex constraints beyond foreign keys are not included
UInt32 and UInt64 types map to integer and bigint respectively (no specific unsigned methods in CQL)

Error Handling

The schema dumper includes proper error handling:

begin
  dumper = CQL::SchemaDump.new(CQL::Adapter::SQLite, "sqlite3://invalid.db")
rescue CQL::SchemaDump::Error => ex
  puts "Failed to connect: #{ex.message}"
end

Tips

Review Generated Schemas: Always review the generated schema files before using them in production
Idiomatic CQL: The generated schemas now use proper CQL column methods (integer, text, etc.) that match the CQL coding style
Timestamps Macro: When both created_at and updated_at columns are detected, the dumper automatically uses the timestamps macro
Custom Types: You may need to manually adjust some type mappings for domain-specific needs
Backup First: Always backup your database before making changes based on dumped schemas
Connection Management: Always call close() on the dumper when finished to free database connections

Recent Improvements

v2.0 - Enhanced Column Method Generation

✅ Proper CQL Methods: Now generates integer, text, boolean, etc. instead of generic column calls
✅ Smart Timestamps: Automatically detects and uses timestamps macro when both created_at and updated_at are present
✅ Idiomatic Output: Generated schemas match the exact style used throughout the CQL codebase
✅ Cleaner Code: Produces more maintainable and readable schema definitions

Before:

column :name, String
column :age, Int32
column :created_at, Time
column :updated_at, Time

After:

text :name
integer :age
timestamps

PreviousMigration Guide NextCaching Guide

Last updated 4 months ago

Was this helpful?