Run raw SQL in migration

sql

raw-sql

migrations

database

byAlex Kataev·Aug 5, 2024

When you need a quick solution for running your raw SQL within migrations, use the execute approach. This works in Rails like this:

execute "CREATE TABLE your_table (id SERIAL PRIMARY KEY);"

For those working with Django, you can do it like so:

migrations.RunSQL("CREATE TABLE your_table (id serial PRIMARY KEY);")

These run your custom SQL directly and modify your database schema accordingly.

Understanding raw SQL in migrations

Raw SQL statements in your migrations give you flexibility and help you utilize database-specific features. However, they lack the protective layer of an ORM, so tread carefully!

Executing SQL in Laravel migrations

In Laravel, we use DB::statement() to execute raw SQL directly. Here's an example of how it looks. The wisest of coders would say: "With great power comes great responsibility...to write SQL statements correctly." 😏

use Illuminate\Support\Facades\DB;

DB::statement('ALTER TABLE users MODIFY age DATETIME');

Heed the Oracle's advice (pun intended). Avoid using backticks 👍. Stick to ANSI SQL for smooth sailing across different database systems.

Choosing between structured and raw SQL

In some cases, ORM's syntax provides a safer alternative to raw SQL for complex changes. For instance, modifying columns in Laravel could be done more safely with methods like $table->dateTime('column_name')->change(). Bookmark your Laravel documentation for easy reference!

Precautions when using raw SQL

Back up your database before running any raw SQL queries in your migrations. ORM or not, bugs can always creep in, especially when you're performing complex modifications. As code poets say: "Backup today, or you may rollback in dismay!" 😄

Practical pointers for raw SQL migrations

In this world of raw SQL migrations, it's survival of the fittest. Here are some nuggets of wisdom:

Syntax compatibility and error handling

Different databases, from MySQL to MariaDB, have unique nuances. A slight difference can turn a benign migration into a catastrophic data crisis (total bummer, right?). So, cross-check your syntax.

Rollback readiness

Utilize the down() method in migrations effectively. Play it safe, because sometimes you just have to hit ctrl + Z on your changes.

Checking user permissions

If you don't have the rights to perform a table-altering party, it can lead to access violations. Ask yourself, "do I have an invite to this party?".

Extending the functionality

For projects that frequently deal with raw SQL migration, consider encapsulating the logic into a common package. Who doesn't like their code DRY, after all?