Database Connection Prefixes behave unexpectedly

[bluehaoran]

• Laravel Version: 5.5.43
• PHP Version: 7.1
• Database Driver & Version: MySQL 5.7.24 / Sqlite3 3.11.0

Description:

Database connection prefixes behave inconsistently and unexpectedly.

Steps To Reproduce:

Configure database in config/database.php thusly:

'connections' => [
    'alpha' => [
        'driver'   => 'sqlite',
        'database' => ':memory:',
        'prefix'   => 'a_',
    ]);
    'beta' => [
        'driver'   => 'sqlite',
        'database' => ':memory:',
        'prefix'   => 'b_',
    ]);
]

Create a basic migration file:

<?php
use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreatePostsTable extends Migration
{
    public function up()
    {
        Schema::create('posts', function (Blueprint $table) {
            $table->increments('ID');
            $table->longText('content');
        });
    }
}

If we generate the table, these connections perform as expected.

        $this->artisan('migrate', [
            '--database' => 'alpha',
            '--path' => '/tests/database/migrations',
        ]);
        $this->artisan('migrate', [
            '--database' => 'beta',
            '--path' => '/tests/database/migrations',
        ]);

Two tables, a_posts and b_posts are created.

If we an Eloquent Post model, or use the Query Builder syntax, they perform as expected.

DB::connection('alpha')->table('posts')->insert(['content' => 'xyzzy']);
// Writes to a_posts as one would expect.

DB::connection('beta')->table('posts')->insert(['content' => 'xyzzy']);
// Writes to b_posts as one would expect.

However for more 'raw' queries, the prefix fails to fire:

DB::connection('alpha')->insert('INSERT INTO posts (content) VALUES (?)', ['xyzzy']);
// Produces this error:
// Illuminate\Database\QueryException: SQLSTATE[HY000]: General error: 1 no such table: posts (SQL: INSERT INTO posts (content) values (xyzzy))
DB::connection('beta')->insert('INSERT INTO posts (content) VALUES (?)', ['xyzzy']);
// Produces this error:
// Illuminate\Database\QueryException: SQLSTATE[HY000]: General error: 1 no such table: posts (SQL: INSERT INTO posts (content) values (xyzzy))

What this means is, database connection prefixes are inconsistent, and mistakenly suggest that one can be swapped out for the another, and that code can run without knowing what connection-specific name of each table is.

Also, it seems like factory methods for unit testing use the latter format to construct queries.

Why would you want to use connections in this way? One use case is retrofitting an app to a multi-site Wordpress database. Instead of having an AlphaPost and BetaPost and so on for every single site, we want to build a single Post object to generically handle an unknown, application-specific number of post tables. Using connections to separate the different sections of the database means we can use connection('alpha') or Post::on('alpha') to ensure our Post is accessing the right set of database tables.

[laurencei]

I kind of feel that a raw query has no way to know to use a prefix. You could write anything in that raw query, so it would be your responsibility to handle any prefixing.

Seems kind of expected to me...?

[driesvints]

@laurencei is right. For raw queries this is the expected way. There's no way to format the queries with the prefix. Raw queries are meant to just accept the input provided and not modify it.

[bluehaoran]

@driesvints , @laurencei absolutely correct. But in this case, database prefixing is failing to provide connection-agnostic code. If this was an application, that's okay, but as a framework, it's providing something unexpected and undocumented, which leads to unexpected application faults.

[laurencei]

Only because of your use of “DB::raw()” to do connection related queries. The whole point of DB::raw() is to allow you to type anything you want n there, and Laravel won’t touch it. Connection/DB agnostic support only applies to the normal queries, which would all work for your examples above.

[bluehaoran]

But you'd need to have knowledge of internals to know which of the documented DB methods use raw() and which don't. Indeed, the example in the docs seems to indicate switching connections with DB::connection() is completely fine and transparent for a raw method such as select().

In fact, after further inspection select(), insert() etc don't actually use raw(), right? But they do make a direct PDO call without any prefix auto-magic being applied.

[bluehaoran]

Personally, I'd be happy if the action for this ticket was: "update the documentation with a disclaimer that prefixes don't work for Raw SQL Queries."