Seed Database with Data from CSV

web-application-framework laravel

This article is how to seed a database with data from a CSV file in Laravel.

What’s Seeder?

Seeder in Laravel is an ability to insert any data into a database.

Reference

Environment

Laravel 11
SQLite 3

Workflow

Create Table
Create CSV File
Create Seeder
Run Seeder

1. Create Table

Create a tests table. In this time, I used SQLite.
Execute the following Artisan to create a Test Model and Migration.

php artisan make:model Test --migration

Edit the created Migration file as follows.

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        Schema::create('tests', function (Blueprint $table) {
            $table->id('id');
            $table->string('name');
        });
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        Schema::dropIfExists('tests');
    }
};

Execute a migration command.

php artisan migrate

2. Create CSV File

Create a CSV file named tests.csv in the database/data directory for data in the tests table.
The first column is for the id column, the second column is for the name column.

1,test1
2,test2
3,test3

3. Create Seeder

Using Artisan, create a new Seeder. It should be stored in the database/seeders directory.

php artisan make:seeder TestSeeder

Edit the created Seeder file as follows.
It reads data from the CSV file and insert it into the tests table.

<?php

namespace Database\Seeders;

use App\Models\Test;
use Illuminate\Database\Console\Seeds\WithoutModelEvents;
use Illuminate\Database\Seeder;

abstract class TestSeeder extends Seeder
{
    /**
     * Run the database seeds.
     */
    public function run(): void
    {
        $values = [];
        if (($handle = fopen(database_path('data/tests.csv'), 'r')) !== false) {
            while (($data = fgetcsv($handle, null, ',')) !== false) {
                $values[] = [
                    'id' => $data[0],
                    'name' => $data[1],
                ];
            }

            fclose($handle);
        }

        Test::upsert($values, ['id']);
    }
}

Also edit the DatabaseSeeder as follows.

<?php

namespace Database\Seeders;

use Database\Seeders\TestSeeder;
// use Illuminate\Database\Console\Seeds\WithoutModelEvents;
use Illuminate\Database\Seeder;

class DatabaseSeeder extends Seeder
{
    /**
     * Seed the application's database.
     */
    public function run(): void
    {
        $this->call([
            TestSeeder::class,
        ]);
    }
}

4. Run Seeder

Execute a seed command.

php artisan db:seed

Check the tests table. You can see data inserted from the CSV file.

SELECT * FROM `tests`;
test1
test2
test3