Laravel migration with SQLite 'Cannot add a NOT NULL column with default value NULL'

Question

Why am I getting this warning when using the SQLite driver? I have no problems with the MySQL driver but SQLite is throwing this error.

It does not make sense to me since I understood the seeding happens after all the migrations are completed so why is it complaining about this issue which would only arise if data was already present in the database.

My two migrations are

FIRST MIGRATION

  public function up() {
    Schema::create('users', function($table) {
      $table->increments('id');
      $table->string('username');
      $table->string('email');
      $table->string('password');
    });
  } 

SECOND MIGRATION

public function up() {
    Schema::table('users', function(Blueprint $table) {
        $table->date('birthday')->after('id');
        $table->string('last_name')->after('id');
        $table->string('first_name')->after('id');
    });
}

ERROR

Exception: SQLSTATE[HY000]: General error: 1 Cannot add a NOT NULL column with default value NULL (SQL: alter table "users" add column "birthday" date not null)

Answer 1

It looks like this is a SQLite oddity. According to a Laracast forum thread about the same issue:

When adding a table from scratch, you can specify NOT NULL. However, you can't do this when adding a column. SQLite's specification says you have to have a default for this, which is a poor choice.

Looking further to the SQLite ALTER TABLE docs, I found:

If a NOT NULL constraint is specified, then the column must have a default value other than NULL.

I suppose in the world of SQLite, not providing a default value is the same thing as saying the default value should be NULL (as opposed to meaning there is no default value for this non-nullable column, so a value must be provided for it on each insert).

It seems SQLite simply leaves you in a bad state if you need to add a non-nullable column to an existing table, which column should also not have a default value.

Answer 2

A workaround I've used successfully is to check which database driver is being used and slightly modify the migration for SQLite.

For example:

class MyMigration extends Migration
{
    public function up()
    {
        $driver = Schema::connection($this->getConnection())->getConnection()->getDriverName();

        Schema::table('invoices', function (Blueprint $table) use ($driver) {
            $table->string('stripe_invoice')->nullable()->change();

            if ('sqlite' === $driver) {
                $table->string('stripe_invoice_number')->default('');
            } else {
                $table->string('stripe_invoice_number')->after('stripe_invoice');
            }
        });
    }
}

Answer 3

If you don't want the column to be nullable - then you need to let Laravel know what the default should be.

One option is an empty string "" like this

public function up() {
    Schema::create('users', function($table) {
      $table->date('birthday')->after('id')->default('');
      $table->string('last_name')->after('id')->default('');
      $table->string('first_name')->after('id')->default('');

    });
  } 

Answer 4

All the folks solutions are good, but I wanted to find a reusable and readable way to do this, so I made a trait and hope it can help you remove some boilerplate codes out of your migrations.

The method is $this->databaseDriverIs("driver_name_here");.

Here is how I use it in a typical table creation migration:

<?php

use App\Traits\WithDatabaseDriver;
use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateCountryTable extends Migration
{
  use WithDatabaseDriver;

  public function up()
  {
    Schema::create("country", function (Blueprint $table) {
      $table->increments("id");
      $table->string("name");

      $table->unique("name", "unique_country_name");
    });

    Schema::table("continent", function (Blueprint $table) {
      $column = $table->unsignedInteger("countryId");

      // This is where we apply the "fix" if 
      // the driver is SQLite
      if ($this->databaseDriverIs("sqlite")) {
        $column->nullable();
      }

      $table->foreign("countryId")->references("id")->on("country");
    });
  }

  public function down()
  {
    Schema::dropIfExists("country");
  }
}

And this is the trait that does all the job:

<?php

namespace App\Traits;

trait WithDatabaseDriver
{
  /**
   * @var string
   */
  private $driver;

  public function __construct()
  {
    $this->driver = config("database.default");
  }

  public function databaseDriverIs(string $driver): bool
  {
    return $this->driver === $driver;
  }
}