• Related to [11.x] Non-default schema names #50019

This PR improves support for multi-database aka schemas (using a single connection) setups. Laravel's Eloquent, Query Builder, and most Schema Builder functions already support references in the schema_name.table_name format. Since all supported databases allow multi-schema functionality, this PR ensures consistent usage across all databases and adds full support for it in all Schema Builder methods.

Improvements

• Add new $schema argument to Schema::getTables(), Schema::getViews(), Schema::getTypes(), and Schema::getTableListing() methods.
  • The default value is $schema = null, returning all tables / views / types of all schemas.
  • Passing $schema as string or array will return the result for the given schemas only.
• Support inspecting other schemas as well on Schema::hasTable(), Schema::hasView(), Schema::getColumns(), Schema::getIndexes(), Schema::getForeignKeys(), Schema::hasColumn(), Schema::getColumnListing(), and Schema::hasIndex() methods utilizing MySQL, MariaDB, and SQLite, fallback to the current schema if not specified explicitly on the table name.
• Add new schema_qualified_name property to the returned results of Schema::getTables(), Schema::getViews(), Schema::getTypes() methods.
• Add new $schemaQualified argument to Schema::getTableListing() method:
  • The default value is $schemaQualified = true, returning the table names as schema_name.table_name format.
  • Passing schemaQualified = false value results in not schema-qualified table names.
• Add new Schema::getSchemas() method for all DB drivers to get schemas for the connection with these properties:
  • name (string): Name of the schema.
  • path (?string): Schema file path (SQLite only)
  • default (bool): The schema is the default one.
• Add new Schema::getCurrentSchemaName() method. This method has been used by the framework internally to determine the name of the current schema for the connection:
  • PostgreSQL: First value of the search_path config property (same as select current_schema()).
  • MySQL / MariaDB: The current database name (same as select schema()).
  • SQLite: Always 'main'.
  • SQL Server: The same as the result of the select schema_name() query.
• Add new Schema::getCurrentSchemaListing() method that returns names of schemas currently in-use for the connection. This method has been used by the framework internally, e.g. when dropping / truncating all tables are needed:
  • PostgreSQL: List parsed schema names of the search_path config property (same as select current_schemas()).
  • MySQL / MariaDB: Single value list of the current database name.
  • SQLite: Single value list of the current schema: ['main']
  • SQL Server: null, means all schemas.
• Set schema property on returned result of Schema::getTables() and Schema::getViews() on MySQL, MariaDB and SQLite (was always null previously).
• Set foreign_schema property on returned result of Schema::getForeignKeys() on SQLite (was always null previously).
• Make DB::withoutTablePrefix() method to return the result of the given callback.
• Fix creating temporary tables with prefix set on the connection utilizing SQL Server.
• Add support for modifying columns of a table on other schema when utilizing SQLite.
• Add support for creating, dropping and renaming indexes of tables on other schemas when utilizing SQLite.
• Add support for truncating tables on other schemas when utilizing SQLite DB::table('schema_name.table_name')->truncate()
• Cleanup db:show, db:table commands, Schema\Builder and Schema\Grammar classes, removing redundant codes (without any BC change).
• Add integration / unit tests for every single added / changed feature.

Behavioral Changes

• The DB::withoutTablePrefix($callback) method now returns the result of the given callback instead of void.
• The Schema::getTables(), Schema::getViews() and Schema::getTableListing() methods:
  • Now return all results from all the schemas. Consistent usage across all DB drivers. Accepting $schema argument to filter the result as desired.
  • Previous behavior was the same when utilizing PostgreSQL and SQL Server, but was returning only the results of the current schema on MySQL, MariaDB and SQLite.
• The Schema::getTableListing() method now returns schema-qualified table names by default. Accepting bool $schemaQualified argument to change the behavior as desired.
• The db:table and db:show command now outputs results of all schemas on MySQL, MariaDB and SQLite, just like PostgreSQL and SQL Server. Consistent usage across all DB drivers.

Context

Being able to categorize your database tables is useful, and this is already possible with all databases supported by Laravel. This means you can manage multiple databases with a single MySQL connection! For example, let’s assume you have this connection:

'connections' => [
    'mysql' => [
        'driver' => 'mysql',
        'database' => 'primary_db',
        'username' => 'root,
        'password' => 'password',
        // ...
    ],
    // ...
],

This mysql connection uses primary_db as the default "schema," which you already know how to use. But what if you want to interact with multiple MySQL databases? Laravel has you covered:

DB::statement('create schema `other_db`');

Schema::create('other_db.posts', function (Blueprint $table) {
    $table->id();
    // ...
});

DB::table('other_db.posts')->get();

class Post extends Model
{
    protected $table = 'other_db.posts';

   // ...
}

The same example works in MariaDB, PostgreSQL, SQL Server, and even SQLite (with a small change when creating the schema). You can use schema_name.table_name references almost everywhere, regardless of the database driver. However, each database driver handles "schemas" differently, so it's important to abstract these differences to ensure consistent usage at the application layer.

Let's compare databases:

This means:

• Multiple schema is supported on all DB drivers and Laravel can interact with tables on other schemas using schema_name.table_name reference.
• MySQL defines SCHEMA as a synonym for DATABASE (ref). MySQL connects with a database config property as the default schema / database and can switches between schemas / databases via USE schema_name)
• SQLite supports attaching other database files (or :memory:) as schemas (ref). SQLite connects to a database file as the default schema named "main".
• PostgreSQL supports multiple databases and multiple schemas, but it connects to a single database at a time (there is no USE db_name). It connects to a database config property as the default database and first schema name on the search_path config property as the default schema.
• SQL Server also supports multiple databases and multiple schemas. It connects to a server with database config property
  as the default database, and "dbo" as the default schema. Unlike PostgreSQL, it's possible to switch between databases via USE db_name and change the default schema (refer to [11.x] Use Default Schema Name on SQL Server #50855 for more). That's why sqlsrv has three-part references db_name.schema_name.table_name, but Laravel doesn't fully / officially supports three-part references for tables.

Let's compare "Current Schema" for each DB driver connection:

[
    'driver' => 'mysql', // 'mariadb'
    'database' => 'laravel',
]

[
    'driver' => 'sqlite',
    'database' => database_path('database.sqlite'),
]

[
    'driver' => 'pgsql',
    'database' => 'laravel',
    'search_path' => 'public,my_schema',
]

[
    'driver' => 'sqlsrv',
    'database' => 'laravel',
]