How to Migrate Your Laravel Database Using PHP Artisan?

Running Migrations

Running migrations in Laravel applies any outstanding migrations to the database. This operation ensures you manage the database schema and verify that it is up to date with the migration files in your project.

Command:

php artisan migrate

This will apply to all migrations that haven’t been run yet. The migrate command checks the migration table (which Laravel automatically creates) and runs any migrations that haven’t been marked as “run.”

Rolling Back Migrations

Rolling back a migration means reverting the most recent batch of migrations applied. This operation is useful when you want to undo recent changes made to your database schema.

Command:

php artisan migrate:rollback

By default, this will roll back the last batch of migrations. If you want to roll back multiple batches, you can specify the –step option.

php artisan migrate:rollback --step=2

This command will roll back the last two migration batches.

Resetting Database Migrations

An effective reset migration removes all migrations from the database and reverts all schema changes. This is useful when you want to undo all migrations and start fresh, completely.

Command:

php artisan migrate:reset

This command rolls back all migrations in reverse application order, restoring the database to its original state before any migrations were run.

Refreshing Migrations

Refreshing migrations combines rolling back and re-running all migrations. This is useful when you want to reset the database schema and reapply all migrations without manually rolling back each migration. It can be particularly helpful during development to quickly test changes in your migration files.

Command:

php artisan migrate:refresh

By default, this command rolls back all migrations and then re-runs them. You can also use the –seed option to run database seeders after refreshing the migrations, which will populate your database with test data.

php artisan migrate:refresh --seed

This command is often used during development to reset the database and start with fresh data.

Seeding with Migrations

Laravel provides built-in seeding functionality to populate your database with test data. Seeding is often used alongside migrations to populate the database with default or mock data after a migration has run.

You can seed your database manually using the db:seed Artisan command:

Command:

php artisan db:seed

This will run the DatabaseSeeder class located in the database/seeders directory. Inside this class, you can define seeders for each of your models.

If you want to run the seeder automatically after running migrations, you can use the –seed option with the migrate:refresh command:

php artisan migrate:refresh --seed

This will first rollback and then reapply all migrations, followed by seeding the database with data defined in the seeder classes.

Verify Laravel Installation

Before you begin, ensure that Laravel is properly installed on your system. You can verify this by running the following command in your terminal:

php artisan --version

If you see the Laravel version printed out, you’re all set.

Configure Database Connection in .env

To make sure your application can connect to your database, configure your .env file with the correct database connection details. Open the .env file in the root of your Laravel project and set the database parameters as follows:

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=your_database_name
DB_USERNAME=your_database_user
DB_PASSWORD=your_database_password

After updating the .env file, run the following command to clear the configuration cache:

php artisan config:clear

Create a New Migration File

To create a new migration file, you can use the Artisan make:migration command. This command will generate a new file where you can define your schema changes.

Here’s how you can create a migration for a table:

php artisan make:migration create_users_table

This will generate a file in the database/migrations directory with a name like create_users_table.php. The timestamp in the file name ensures that migrations are run in the correct order.

Define Schema Inside the Migration

Inside your migration file, you’ll define the structure of your new database table. This is done inside the up() method. You can use Laravel’s Schema Builder to define columns for the table.

Example of defining a users table with name, email, and password columns:

public function up()
{
Schema::create('users', function (Blueprint $table) {
$table->id(); // auto-incrementing primary key
$table->string('name');
$table->string('email')->unique();
$table->timestamp('email_verified_at')->nullable();
$table->string('password');
$table->rememberToken();
$table->timestamps();
});
}

In the above given example:

• $table->id() adds an auto-incrementing primary key.
• $table->string(‘name’) adds a column named name with a VARCHAR data type.
• $table->timestamps() automatically adds created_at and updated_at columns.

Run the php artisan migrate Command

Once your migration file is ready, you can apply the migration to your database by running the following Artisan command:

php artisan migrate

This command will run all pending migrations that have not been applied yet, creating or modifying tables in your database as specified in the migration files.

After running the migration, you can check your database to confirm that the table has been created.

Rollback Changes (If Required)

If you need to undo a migration, you can use the migrate:rollback command. This will roll back the last batch of migrations that were run.

To rollback the last migration:

php artisan migrate:rollback

To rollback all migrations (i.e., revert everything back to the initial state), use:

php artisan migrate:reset

If you need to refresh the database (rollback and re-run all migrations), use:

php artisan migrate:refresh

Test Your Database Schema

Once you’ve run your Laravel database migration, you should test your database schema to ensure that everything has been created correctly.

• Test with Tinker: You can use Laravel’s Tinker to interact with the database. Run php artisan tinker and try querying the table:

$users = DB::table('users')->get();
dd($users);

• Test Manually: You can also check the database directly using a database management tool (like phpMyAdmin, DBeaver, or MySQL Workbench) to verify that the schema and data have been correctly applied.

Deploy Migrations in Production Safely

When deploying migrations to production, exercise caution as migrations can affect critical data. Here are key tips for the safe deployment process:

• Back up the Database: Always back up the production database before running migrations.
• Use Migration Squashing: For large projects, squash older migrations to simplify the process.
• Test in Staging: Run migrations on a staging environment that mirrors production.
• Use –force Flag: In production, use php artisan migrate –force to bypass confirmation prompts.
• Monitor for Issues: After deployment, monitor the app for any issues related to schema changes.

SQL Error / Invalid Syntax

An invalid SQL syntax error can occur if there is a mistake in the migration script, particularly in the structure of SQL commands that Laravel tries to generate.

How to Fix:

• Check the Migration File: Verify that the syntax is correct. If you’re using raw SQL queries, double-check their structure.
• Database Compatibility: Ensure you use the correct column types for your database (MySQL, PostgreSQL, SQLite, etc.).
• Debugging: Try running the SQL query directly in the database to check for any syntax errors Laravel might be generating.

Foreign Key Constraint Errors

Foreign key constraints are often violated during migrations if the referenced column or table does not exist, or if the data types of the related columns do not match.

How to Fix:

• Ensure proper relationships: Double-check that all referenced tables exist and that the columns involved in the foreign key relationship are of the same type.
• Correct migration order: Ensure the table containing the referenced column is created before the table containing the foreign key.
• Check for foreign key constraints: Some databases may have them disabled, especially MySQL.

Migration Not Found

This error occurs when Laravel can’t find the migration file, either because it’s missing or incorrectly named.

How to Fix:

• Ensure the migration file exists: verify that it is present in the database/migrations/ directory.
• Check the naming conventions: the migration class name must match the filename, and the date in the filename must be accurate to ensure migrations run in the correct order.
• Look for typos in the class name or filename to ensure they match exactly.

Migration Table Missing (migrations table issues)

Laravel tracks the migrations that have been run in a special table called migrations. If this table is missing or corrupted, migrations will fail.

How to Fix:

• Recreate the migrations table: If the migrations table has been deleted, run the migration install command to create it again.
• Check the database connection: Ensure your .env file contains correct database settings and that Laravel can connect to the database.
• Clear cache: Clearing the configuration and cache can resolve issues with database connections.

Duplicate Column / Table Errors

This error occurs when a migration attempts to add a column or table that already exists in the database, often due to previous migrations or incomplete rollback.

How to Fix:

• Check the database: Before running the migration, confirm that the table or column doesn’t already exist.
• Rollback migrations: If the migration has already run, roll it back and rerun it to resolve any conflicts.
• Manually adjust the database: In some cases, you may need to remove or modify a column or table before rerunning the migration.

Permission or Environment Errors

Permission errors typically occur when the database user lacks sufficient privileges to perform the migration actions. Similarly, environment errors can occur when configuration settings are mismatched.

How to Fix:

• Verify user permissions: Ensure the database user has the appropriate permissions to create tables, alter columns, and perform related operations.
• Check .env configuration: Ensure your database credentials (host, username, password) are correctly set in your .env file.
• File permissions: If you’re working on a shared server or a system with strict file permissions, ensure that your Laravel project files are accessible to the web server or user running the migrations.