Schema differences — SQLite vs MySQL vs PostgreSQL in migrations

Concept

schema:dump and migration squashing address a practical problem: as a project matures, it accumulates hundreds of migrations. Running 300+ migrations on a fresh environment takes time and the migration history becomes hard to follow.

php artisan schema:dump: Dumps the current database schema to a SQL file at database/schema/{connection}-schema.sql. When migrate runs on a fresh environment and finds this file, it loads it first (via raw SQL) instead of running individual migration files. Then it runs any new migration files created after the dump.

php artisan schema:dump --prune: Dumps AND deletes all migration files that are already represented in the dump. Only new migrations (created after the dump) remain as PHP files. Dangerous — run this only after all environments have migrated to the current state.

Migration squashing workflow:

1. Ensure all environments (dev, staging, production) are on the latest migration.
2. Run php artisan schema:dump --prune (or schema:dump alone to keep migration history).
3. Commit database/schema/{connection}-schema.sql to git.
4. Future fresh environments use the SQL dump instead of running 300+ migration files.

When to squash:

• After a major release that all servers have applied.
• When migrate:fresh is noticeably slow in development.
• Periodically (every 6-12 months) for mature projects.

Limitations: The schema dump is DB-engine specific. Dumping with MySQL produces MySQL SQL — incompatible with SQLite/Postgres. If you test with SQLite (in-memory), you may not be able to use the dump for tests.

Code Example

# Commands

# Dump current schema to database/schema/mysql-schema.sql
php artisan schema:dump

# Dump AND delete migration files that are captured in the dump
php artisan schema:dump --prune

# For a specific connection
php artisan schema:dump --database=pgsql

# What the dump file looks like (truncated example):
# database/schema/mysql-schema.sql
# CREATE TABLE `migrations` (
#   `id` int unsigned NOT NULL AUTO_INCREMENT,
#   `migration` varchar(255) NOT NULL,
#   `batch` int NOT NULL,
#   PRIMARY KEY (`id`)
# ) ENGINE=InnoDB;
#
# CREATE TABLE `users` (
#   `id` bigint unsigned NOT NULL AUTO_INCREMENT,
#   `name` varchar(255) NOT NULL,
#   `email` varchar(255) NOT NULL UNIQUE,
#   ...
# );
# -- ... rest of schema

// After squashing — new migration files still work normally
// database/migrations/ only has NEW migrations (post-squash)
// Fresh migrate:
// 1. Loads database/schema/mysql-schema.sql (base state)
// 2. Runs any new migration files

// For SQLite-based tests — disable schema load
// In phpunit.xml or TestCase setUp:
// If using RefreshDatabase, it calls migrate:fresh which loads the dump
// SQLite incompatibility workaround:
// - Don't use --prune if you test with SQLite
// - Or maintain both SQLite and MySQL migration paths
// - Or switch tests to use mysql/pgsql as well