Table prefix not used as expected in Laravel 9

Question

I'm working on a laravel app that used to work, but I don't know why after a few modifications on different setups, I cannot make it work as expected locally.

Symptoms

Queries from models accessing incorrect table names

My DB contains tables prefixed by sub_ but queries made from controllers are accessing non-prefixed tables.

For instance User::where(User::EMAIL_ADDRESS, "=", mb_convert_case($validated[User::EMAIL_ADDRESS], MB_CASE_LOWER))->first(); gives an error (table users does not exist).

Migrations accessing prefixed table names

Migrations are applied on the right table names

Model->getTable() returns names without prefix

When executing this code with php artisan tinker, the returned table name does not contain the prefix, even though it is correctly retrieved from the config:

php artisan tinker
> dd(['config_prefix' => config('database.connections.sqlite.prefix'), 'db_prefix' => \Illuminate\Support\Facades\DB::connection()->getTablePrefix(), 'env_prefix' => env('DB_PREFIX'), 'model_
table' => (new App\Season)->getTable()]);
array:5 [ // vendor\psy\psysh\src\ExecutionLoopClosure.php(52) : eval()'d code:1
  "config_prefix" => "sub_"
  "db_prefix" => "sub_"
  "env_prefix" => "sub_"
  "model_table" => "seasons"
]

For information, none of my models contain a property that would override the table name (no $table or $prefix property).

Query dump: from seems correct, but querying does not work

 php artisan tinker
Psy Shell v0.12.8 (PHP 8.2.8 — cli) by Justin Hileman
> dd([
.   'config_prefix' => config('database.connections.mysql.prefix'),
.   'db_prefix' => \Illuminate\Support\Facades\DB::connection()->getTablePrefix(),
.   'env_prefix' => env('DB_PREFIX'),
.   'model_table' => (new App\Season)->getTable(),
.   'query' => (new App\Season)->query()
. ]);
array:5 [ // vendor\psy\psysh\src\ExecutionLoopClosure.php(52) : eval()'d code:1
  "config_prefix" => "sub_"
  "db_prefix" => "sub_"
  "env_prefix" => "sub_"
  "model_table" => "sub_seasons"
  "query" => Illuminate\Database\Eloquent\Builder^ {#6141
    #query: Illuminate\Database\Query\Builder^ {#5340
      +connection: Illuminate\Database\SQLiteConnection^ {#5411 …24}
      +grammar: Illuminate\Database\Query\Grammars\SQLiteGrammar^ {#5412 …4}
      +processor: Illuminate\Database\Query\Processors\SQLiteProcessor^ {#5413}
      +from: "sub_seasons"
    // [...]
    }
    #model: App\Season^ {#5381
      #connection: null
      #table: null
      #primaryKey: "id"
      // [...]
    }
    // [...]
  }
]

Query from tinker works

When running App\Season::first() on tinker, the query is valid and it returns something.

The same code within a controller, throws an error about the table name being incorrect.

Failed Attempts

Cache clearing: php artisan config:clear, cache:clear, etc.
Override getTable(): Returns correct value but still ignored
AppServiceProvider boot(): Various connection-level fixes

Environment:

Laravel 9
on Windows 11
php 8.2.8

Dependencies:

"require": {
        "php": "^8.1.0",
        "bjeavons/zxcvbn-php": "^1.1",
        "doctrine/dbal": "^2.10",
        "fruitcake/laravel-cors": "^3.0",
        "goldspecdigital/laravel-eloquent-uuid": "^9.0",
        "guzzlehttp/guzzle": "^7.0.1",
        "laravel/framework": "^9.0",
        "laravel/tinker": "^2.0",
        "phpoffice/phpspreadsheet": "1.27",
        "sentry/sentry-laravel": "3.1.3",
        "tymon/jwt-auth": "^2.0"
    },

Config

# .env file
DB_PREFIX=sub_
DB_CONNECTION=sqlite

# config/database.php
'connections' => [
    'sqlite' => [
        'driver' => 'sqlite',
        'database' => env('DB_DATABASE', database_path('database.sqlite')),
        'prefix' => env('DB_PREFIX', ''),
        // ...
    ],
    // ...
],

Does anyone has a clue on what's going on with my setup?

Thanks

I can't see anything wrong with the config, I tried setting a prefix in the config of my local Laravel app and it worked immediately. The part of your question I would focus on is the first sentence, you say the "... laravel app that used to work, but ... after a few modifications on different setups, I cannot make it work as expected locally." Do you know what has changed? It would seem you need to undo those changes until it starts working again to find out what's going wrong. — Tony
– Tony, Commented Jun 4 at 11:03
@Tony I reverted the code back to previous versions (mostly minor updates on dependencies), but I cannot make the project work locally anymore even with previous revisions — ghusse
– ghusse, Commented Jun 4 at 12:02
@Don'tPanic clearing the cache does not fix the issue, unfortunately. I updated the question to mention that I tested this. Thanks. — ghusse
– ghusse, Commented Jun 4 at 12:02

Tony · Accepted Answer · 2025-06-04 13:05:47Z

0

If you look at the implementation of Model::getTable() it does not use the prefix from the config:

    public function getTable()
    {
        return $this->table ?? Str::snake(Str::pluralStudly(class_basename($this)));
    }

However, as soon as you query the table the prefix is applied. If I set a prefix of myprefix_ in the database config, and then dump a query, this is what I get:

dd([
  'config_prefix' => config('database.connections.mysql.prefix'),
  'db_prefix' => \Illuminate\Support\Facades\DB::connection()->getTablePrefix(),
  'env_prefix' => env('DB_PREFIX'),
  'model_table' => (new App\Booking)->getTable(),
  'query' => (new App\Booking)->query()
]);

Output:

array:5 [
  "config_prefix" => "myprefix_"
  "db_prefix" => "myprefix_"
  "env_prefix" => null
  "model_table" => "bookings"
  "query" => App\QueryBuilders\BookingQueryBuilder {#3254
    #query: Illuminate\Database\Query\Builder {#3255
      +connection: Illuminate\Database\MySqlConnection {#548
        #pdo: Closure() {#549
          class: "Illuminate\Database\Connectors\ConnectionFactory"
          this: Illuminate\Database\Connectors\ConnectionFactory {#83 …}
          use: {
            $config: array:15 [
              "driver" => "mysql"
              "host" => "127.0.0.1"
              "port" => "55000"
              "database" => "ucg_prod_250602"
              "username" => "root"
              "password" => "root"
              "unix_socket" => ""
              "charset" => "utf8mb4"
              "collation" => "utf8mb4_unicode_ci"
              "prefix" => "myprefix_"
              "prefix_indexes" => true
              "strict" => true
              "engine" => null
              "options" => []
              "name" => "mysql"
            ]
          }
        }
...

You can see in the $config array the prefix has been loaded, and if I execute Booking::find(1) I get:

Base table or view not found:   1146 Table 'myprefix_bookings' doesn't exist (SQL: select * from 'myprefix_bookings'

Where are you using Model::getTable() and expecting it to prefix the name? You might need to come up with a different solution.

answered Jun 4 at 13:05

Tony

10.4k3 gold badges51 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

ghusse Jun 4 at 14:39

Calling getTable was just a way to test the origin of my real issue: calling a simple route on a controller that calls User::where(User::EMAIL_ADDRESS, "=", mb_convert_case($validated[User::EMAIL_ADDRESS], MB_CASE_LOWER))->first(); throws an error because of a missing table users instead of sub_users. As you point out, this is not relevant to fix my issue ; thanks for clarifying this. I'll try to do the same dump on my project to see what is returned in the query's config.

ghusse Jun 4 at 16:36

I've completed my question with the following info: I tested your dump and it appears that the from property is correct when dumped from php artisan tinker. Executing a query from php artisan tinker actually works as expected, but I still have the issue when calling the exact same code from a controller.

Tony Jun 4 at 20:26

If the configuration is working in Tinkerwell that means the prefix in your sqlite connection is working as intended. Can you add the code from the controller that fails? Does it use a different connection? E.g. Season::on('connection_name')->where(...) or DB::connection('connection_name')->where(...)

Collectives™ on Stack Overflow

Table prefix not used as expected in Laravel 9

Symptoms

Queries from models accessing incorrect table names

Migrations accessing prefixed table names

Model->getTable() returns names without prefix

Query dump: from seems correct, but querying does not work

Query from tinker works

Failed Attempts

Environment:

Config

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Symptoms

Queries from models accessing incorrect table names

Migrations accessing prefixed table names

Model->getTable() returns names without prefix

Query dump: from seems correct, but querying does not work

Query from tinker works

Failed Attempts

Environment:

Config

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related