How to convert Laravel migrations to raw SQL scripts?

Step-by-step Migration Extraction Process

Laravel migrations are the backbone of your DB structure, but sometimes you just need good 'ole raw SQL. Here's how you can get it.

Get SQL from ALL migrations

--pretend is nice, but kind of selective. If you want the SQL from all migrations, try these steps:

1. Roll back all migrations with:

php artisan migrate:reset


and then, guess what, use --pretend again.

2. Or, act like a Laravel ninja and create a new command to walk through the migrator's files, pretending (with db->pretend) to migrate, while secretly writing down everything. Mission Impossible style!

Automate the Extraction Using CI systems

Why do manual work when you have a CI system like TeamCity? Set up a magic spell:

# teamcity-configuration.yaml
steps:
  - script: |
      php artisan migrate --pretend > migrations.sql


Now every time you use TeamCity, it will bottle up that precious SQL nectar for you.

Capture Output without Ansi

Style is good, but not when you're trying to save queries. Strip out those Ansi color codes:

php artisan migrate --pretend | sed $'s, \x1b\\[[0-9;]*[a-zA-Z],,g' > migrations.sql


Poor Ansi. He just wanted to make things colourful.

Dancing with PostgreSQL

Alright, now that you have SQL scripts, are they PostgreSQL-friendly? That nitpicky dancer has some specific styles. Check the scripts for any MySQL steps that PostgreSQL might trip on.

Consider Multiple Databases

When dealing with various database connections, make sure you're buying the right shoes:

Schema::connection('pgsql')->create('...'); 


Replace 'pgsql' with the database connection name of the database you want to dance with.

Handling Potential Issues: SQL Extraction Ninja Style

--pretend is not almighty. When migrations get tricky with dynamic data, unexpected class calls, and the ilk, it can choke. Be ready to get your hands dirty with manual labor!

Class Not Found? Run, Ninja, Run!

Migrations might rely on class references. Before extracting SQL, look for any sneaky PHP-specific code. Can't find the class? Debug the autoload files, ninja!

Test The Output: Revenge Of The DBA

Got SQL scripts? Don't just trust your guts. Rally your DBAs, and let them loose on the scripts. Their validation and testing will keep your code honest!

Automate SQL Extraction

A real tool for this would be a game-changer, right? Well, you can build it. Harness migrator and db's power, wrap them up in a handy Laravel command, and sit back:

Artisan::command('migrate:to-sql', function () {
    // "Do not try to bend the spoon. That's impossible." 🥄
});


Holy automatic SQL extraction, Batman!

Perfecting the SQL Scripts

Formatting constraints, particularly with primary keys and unique keys, can be tricky. Make sure to correctly order and delineate constraints in your final scripts. Your DBA, and your sanity, will thank you.