Timestamp SQL Files for Consistent Laravel Migration

Background

As applications scale and become more complex, advanced database concepts like functions, triggers, and stored procedures are often introduced. In Laravel, these concepts can be implemented by writing SQL query files and executing them through migration files. However, as the application evolves and additional functionality is added, the need to update these database functions arises. This can lead to migration issues if the modified function declaration refers to new relations and columns that have not been created yet.

Solution

To overcome this challenge, one effective approach is to create timestamped SQL files that correspond to the migration files using them. By associating timestamped SQL files with migration files, developers can ensure consistency between the database schema and the application code. These timestamped SQL files serve as snapshots of the database function at the time of their usage, ensuring that modifications to the functions do not disrupt the migration process.

This blog post explores the benefits and implementation of timestamped SQL files for Laravel migration, focusing on the application stack of Laravel and PostgreSQL.

Benefits of Timestamped SQL Files

By associating timestamped SQL files with migration files, developers can ensure consistency between the database schema and the application code. Here are some key benefits of using this approach:

Seamless Migration Execution: Timestamped SQL files allow migrations to be executed without conflicts, even when modifications are made to the database functions. This prevents errors and ensures smooth application deployment.

Version Control Integration: Timestamped SQL files enable easy tracking of changes made to database functions. By following a timestamped naming convention, it becomes simple to identify and compare different versions of SQL files, enhancing version control practices.

Team Collaboration: Timestamped SQL files facilitate collaboration among team members by providing a clear history of modifications. Developers can work concurrently on different SQL files and migrations, reducing conflicts and improving productivity.

Implementation Steps

Now, let's dive into the steps required to implement timestamped SQL files for Laravel migration.

Step 1: Creating Timestamped SQL Files

Choose a naming convention for the SQL files that includes a timestamp. A proper way to name an SQL file would be timestamp, action, entity name and type. For example, YYYY_MM_DD_HHMMSS_extract_cleaned_company_name_function.sql

Note: Here we have used timestamp format used by Laravel when creating migration files. Using this way ensures our files are listed properly in IDE's file explorer.

Store the SQL files in a designated directory within your Laravel project, such as the database/migrations/sql folder.
For Example of SQL above file, 2020_01_09_072809_create_extract_cleaned_company_name_function.sql

CREATE FUNCTION extract_cleaned_company_name(companyName varchar)
    RETURNS varchar AS
$$
DECLARE
    cleanedCompanyName varchar(255);
    replacePattern varchar(255);
BEGIN
    replacePattern:= '\s+|-';
    cleanedCompanyName := REGEXP_REPLACE(cleanedCompanyName, replacePattern, '', 'g');
    cleanedCompanyName := LOWER(cleanedCompanyName);

    RETURN cleanedCompanyName;
END
$$ LANGUAGE plpgsql;

Step 2: Writing Migration Files

Create a migration file using the artisan command-line tool, following Laravel's migration conventions.

php artisan make:migration create_extract_cleaned_company_name_function

Inside the migration file's up() method, use the DB::unprepared() function to execute the corresponding timestamped SQL file.
For example, 2020_01_09_072809_create_extract_cleaned_company_name_function.php

public function up()
{
    $this->down();

    $sql = file_get_contents(database_path('migrations/sql/2020_01_09_072809_create_extract_cleaned_company_name_function.sql'));
    DB::unprepared($sql);
}

public function down()
{
    $sql = "DROP FUNCTION IF EXISTS extract_cleaned_company_name(varchar);";
    DB::unprepared($sql);
}

Note: We call down() method to avoid creating a function that already exists when we execute the SQL query to create our function.

Step 3: Modifying Database Functions

When changes are required in the database functions, create a new timestamped SQL file with the updated function declaration.
Ensure that the new SQL file name follows the same timestamp format as the new migration file.
For example,
SQL file: 2022_05_09_012450_create_extract_cleaned_company_name_function.sql
Migration file: 2022_05_09_012450_update_extract_cleaned_company_name_function.php

Step 4: Running Migrations

Use Laravel's migration commands php artisan migrate to execute the migrations, including the timestamped SQL files.
Laravel will handle the execution of the SQL files, ensuring consistency between the database schema and the application code.

Conclusion

Timestamped SQL files offer a robust solution for managing changes in advanced database concepts within Laravel applications. By associating SQL files with migration files and adopting a timestamp naming convention, developers can seamlessly update database functions without risking migration conflicts.

This approach improves version control practices, enhances collaboration among team members, and ensures a smooth deployment process. Leveraging the power of Laravel and PostgreSQL, you can confidently scale your application while maintaining a consistent and reliable database schema.

Remember to regularly update your timestamped SQL files as your application evolves, and enjoy the benefits of a well-maintained and flexible database structure within your Laravel project.

JoBins Engineering