Laravel migration - is it possible to use SQL instead of schema commands to create tables and fields etc?

user_1072187 0
Viewed 4283

We have an existing complex database schema complete with indexes, constraints, triggers, tables etc.

With liquibase, you can point to pure sql files in your changesets, which could be a dump of the whole DB for the first (initial schema creation) migration.

Is there any way to do this with the laravel artisan migration system?

We would like to do all our db updates using the SQL language (because we know it already, and because we will only ever user mysql), but need the framework (migrate or liquibase) to apply the changes in the right order etc. (so they keep a log on the DB of the changes already applied etc).

If not, has anyone used liqubase with laravel? The only issue is that it wont be able to read the .env db connection strings, and that each developer will need to install liqubase (not the end of the world, but if the laravel built in system can use sql, it would save us time and effort)

laravel migration sql database
2 Answers

Yes, it is possible to create migrations which use raw SQL

You are not limited to what code you can run in your migrations. Run raw SQL queries using the DB facade. This example shows both methods being used in the same migration.

class AddColumnsToUsersTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::table('users', function (Blueprint $table) {
            $table->string('name');
            $table->string('age');
            $table->timestamps();
        });

        DB::update('update users set age = 30 where name = ?', ['John']);
    }
}
user_32394360

I believe there are plenty of benefits of using raw / plain SQL migrations in certain cases

  • complex schema(s),
  • taking advantage of vendor specific extensions to SQL (both DDL and DML)
  • richer / additional data types
  • using vendor specific procedural code (i.e. PL/pgSQL anonymous blocks in PostgreSQL)
  • etc

Although there is a way to execute raw SQL in Laravel migrations, writing and managing raw SQL code in variables even with heredoc is painful and unnatural.

Full disclaimer: here goes a shameless plug

Recently I've created an experimental package laravel-sql-migrations that I use in my projects to abstract the details of raw SQL execution and allow for writing and keeping SQL migrations in *.sql files almost like in Liquibase or Flyway.

The package among other things extends make:migration and make:model commands so that you can use a familiar workflow 

php artisan make:migration create_users_table --sql

or

php artisan make:model User --migration --sql

which will produce three files

database
└── migrations
    ├── 2018_06_15_000000_create_users_table.down.sql
    ├── 2018_06_15_000000_create_users_table.php
    └── 2018_06_15_000000_create_users_table.up.sql

At this point you can forget about 2018_06_15_000000_create_users_table.php unless you want to configure or override behavior of this particular migration (i.e. set a specific connection and / or make use of transactions if your database supports them for DDL i.e. PostgreSQL).

If you don't use reverse / down migrations you can delete the corresponding *.down.sql file.

Here is how migrations for the standard Laravel users table might look like if you were to use PostgreSQL

-- 2018_06_15_000000_create_users_table.up.sql
CREATE TABLE IF NOT EXISTS users (
    id BIGSERIAL PRIMARY KEY,
    name CITEXT,
    email CITEXT,
    password TEXT,
    remember_token TEXT,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);
CREATE UNIQUE INDEX IF NOT EXISTS users_email_idx ON users (email);

-- 2018_06_15_000000_create_users_table.down.sql
DROP TABLE IF EXISTS users;
user_19202320
Related