How to convert Laravel migrations to raw SQL scripts?

# How to Convert Laravel Migrations to Raw SQL Scripts: Bridging the Gap Between Development and Operations As developers working within the Laravel ecosystem, we live and breathe migrations. They are the backbone of our application evolution, providing a structured, version-controlled way to manage database schema changes. However, as you've experienced, there is often a friction point between the developer workflow—focused on elegant PHP code—and the operations workflow—which demands executable, raw SQL scripts from Database Administrators (DBAs). The core question is: **Is there a reliable, automated way to extract the exact `UP` and `DOWN` SQL commands generated by Laravel migrations?** The short answer is yes, but it requires understanding both the structure of your migrations and leveraging specific tools or techniques rather than relying on a single built-in command. This guide will walk you through developer-centric methods for achieving this goal, perfect for integrating schema generation into your CI system. ## Understanding the Challenge with Laravel Migrations Laravel migrations are defined in PHP files that use the Schema Builder (e.g., `Schema::create('users', function (Blueprint $table) { ... })`). When these files are run via `php artisan migrate`, Laravel translates this elegant code into the specific SQL dialect required by your configured database (in your case, PostgreSQL). The challenge arises because the migration file is a high-level abstraction. It doesn't contain the raw DDL that the DBA ultimately needs to review or use for backups. We need a mechanism to reverse-engineer or extract this DDL reliably. ## Method 1: Direct File Parsing (The Simplest Approach) For smaller projects, or when you need a quick audit, the most straightforward method is parsing the migration files directly. Since Laravel migrations are plain PHP files, we can write a simple script to read them and extract the relevant `Schema::create`, `table`, and column definitions. This approach requires reading the file content and using string manipulation or basic parsing logic to reconstruct the SQL structure. While custom scripting is feasible, it quickly becomes brittle when dealing with complex constraints, indexes, and custom types common in larger applications. **Example Concept (Conceptual Parsing):** You would read every migration file in your `database/migrations` directory and iterate through the code, looking for calls to methods like `Schema::create()` or `table()`, and then manually map those PHP definitions to PostgreSQL `CREATE TABLE` statements. ## Method 2: Leveraging Database Introspection (The Most Reliable Way) For true accuracy that satisfies a DBA, the most robust method is to let the database itself tell you what has been done. Instead of trying to reverse-engineer the *intent* from PHP code, query the PostgreSQL system catalogs directly. PostgreSQL stores schema history in its information schema tables (like `pg_class` and `pg_attribute`). By querying these views for objects created by your application's schema name, you can generate the exact `CREATE TABLE`, `ALTER TABLE`, and index definitions that exist *right now*. This method is superior because it bypasses the need to perfectly translate complex PHP logic into SQL; it simply outputs the existing, executed state. This aligns well with best practices in managing data integrity, much like the principles guiding modern framework development found on platforms like https://laravelcompany.com. ## Integrating into CI/CD for Production Builds To achieve your goal of integrating this into TeamCity builds, you should implement **Method 2** as a post-migration step. After successfully running `php artisan migrate` in your staging environment, run a custom script that connects to the PostgreSQL database and generates a dump of the current schema structure based on system views. This ensures that the SQL scripts generated for production are not hypothetical translations but an exact reflection of the schema currently residing in the target environment. This process can be scripted using PHP executed via CI/CD runners, ensuring consistency across all environments. ## Conclusion Converting Laravel migrations to raw SQL is less about a single magic command and more about choosing the right tool for the job: file parsing for quick audits, or database introspection for production-grade accuracy. By combining these approaches—using your migration files as the source of truth for development and using PostgreSQL system views for operations—you can successfully bridge the gap between the developer's elegance and the DBA's requirement, streamlining your CI/CD pipeline significantly.