Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

tnort's avatar
Level 4

Sqlite Laravel SESSION sql required primary key=0

Hi,

I am trying to build some tests and use an in_memory sqlite database. As I run my first test I get the following error on my last table

  SQLSTATE[HY000]: General error: 1 near "SET": syntax error (SQL: SET SESSION sql_require_primary_key=0)
.
.
.
  8   database/migrations/2022_09_12_153840_create_notifications_table.php:2

I've tried some workaround on my notifications table such as

Schema::create('notifications', function (Blueprint $table) {
            if (env('APP_ENV' == 'production')) {
                $table->uuid('id')->primary();
            } else {
                $table->id();
            }
            $table->string('type');
            $table->morphs('notifiable');
            $table->text('data');
            $table->timestamp('read_at')->nullable();
            $table->timestamps();
        });

But I still fail to run my tests.

My testing setup is:

		<server name="APP_ENV" value="testing"/>
        <server name="BCRYPT_ROUNDS" value="4"/>
        <server name="CACHE_DRIVER" value="array"/>
         <server name="DB_CONNECTION" value="sqlite_testing"/>
        <server name="MAIL_MAILER" value="array"/>
        <server name="QUEUE_CONNECTION" value="sync"/>
        <server name="SESSION_DRIVER" value="array"/>
        <server name="TELESCOPE_ENABLED" value="false"/>

	//database connection configuration 
	'sqlite_testing' => [
            'driver' => 'sqlite',
            'database' => ':memory:'
        ],

I am not sure what is going on. I looked on the web and tried different techniques but it still fails.

Thank you!

0 likes
4 replies
Tray2's avatar

I highly suggest that you don't use uuid as the primary key at all. A normal unisgned big integer is a much better choice. There is a big difference in performance as well.

1 like
secondman's avatar

I'm having the same issue.

@mvd How do I run SET SESSION sql_require_primary_key = 0; on an in memory Sqlite database?

I followed the advice on the ticket you referenced and added:

DB::statement('SET SESSION sql_require_primary_key=0');

to each of my migrations, but this did nothing at all.

Need some help getting this testing db set up.

mvd's avatar

@secondman ,

Apologies for my late response. I think my previous answers was not correct. Sqlite doesn't support native variable syntax.

I think you want to use DB::statement('SET SESSION sql_require_primary_key=0'); in Mysql but in the in memory Sqlite you get the error SQLSTATE[HY000]: General error: 1 near "SET": syntax error (SQL: SET SESSION sql_require_primary_key=0) ?

If this is correct, can you try this for every DB::statement('SET SESSION sql_require_primary_key=0'); in your migration files?

 if (env('APP_ENV') != 'testing') {
	DB::statement('SET SESSION sql_require_primary_key=0');
 }

Please or to participate in this conversation.