Added schemaname support for sqlserver driver

[jeff-99]

I've added support for the SchemaName config parameter for the SQL Server driver. This creates the opportunity to place the migrations table in a different schema. The same as the postgres driver.

[coveralls]

Pull Request Test Coverage Report for Build 366

• 5 of 5 (100.0%) changed or added relevant lines in 1 file are covered.
• No unchanged relevant lines lost coverage.
• Overall coverage remained the same at 57.418%

---

Totals
Change from base Build 361:	0.0%
Covered Lines:	3611
Relevant Lines:	6289

---

💛 - Coveralls

[jeff-99]

How the SQL Server driver is implemented now it's not necessary to configure the SchemaName of the database it's managing. Currently none of the methods uses the SchemaName to filter a specific part of the database (ex. "Drop" drops all tables regardless of the schema name).

That's why using the SchemaName to define the schema of the migrations table seems more logical then parsing the MigrationsTable name. I'm also not a fan of parsing the MigrationsTable string and requoting it in the queries. There are just too much ways you could define the object names, to name a few:

• "db_name"."schema_name"."table.name"
• db_name.schema_name.table_name
• [db_name].[schema_name].[table.name]
• db_name."schema.name".[table.name]
• "schema.name".[table.name]
• schema_name.table_name

I think it's best to either use the SchemaName or introduce a MigrationSchemaName config parameter, to clearly state the meaning and prevent all sorts of "weird" parsing behaviour.

[dhui]

I'm not familiar with SQLServer. How is the schema determined? Is there config similar to postgres' search_path? The postgres db driver went down the path of supporting quoted migration tables and parsing the quoted identifier to avoid conflicting config values from different config parameters.

[jeff-99]

In SQL Server you don't have a search_path like parameter. There is the default 'dbo' schema and there is a parameter to set a default schema for the logged in user (which is usually 'dbo'). Only for these schemas it's possible to identify a table with only a tablename.

For all other schema's the identifier for an object needs to have at least 2 parts. the schema name and the table name and can additionally also include the database name and the servername (for linked servers).

It's a best practice to always include the schemaname when writing queries. So to parse the MigrationTable name it could be made easier to always require these 2 parts like this: "[dbo].[schema_migrations]". No more and no less.

[dhui]

In SQL Server you don't have a search_path like parameter. There is the default 'dbo' schema and there is a parameter to set a default schema for the logged in user (which is usually 'dbo'). Only for these schemas it's possible to identify a table with only a tablename.

Is the schema name required for CREATE TABLE or CREATE VIEW or does it default to the default schema if a schema is not specified? Originally, Config.SchemaName was designed to run migrations in that schema, but that hasn't been implemented, so now it just reflects the current schema after connecting.

If a schema name is not required for CREATE TABLE or CREATE VIEW, then let's add a new config value MigrationsSchema to specify the schema for migrations. Note, if we go down this path, we should assume that Config.MigrationsTable unquoted and quote it when using it.

Parsing quotes for SQL Server seems more complicated since there are multiple ways to parse a quoted identifier. Note: the postgres db driver approach was to require a specific quoting structure and is naive in it's parsing. In hindsight, maybe adding a schema config value for migrations is simpler and more flexible than parsing a quoted identifier...

[RaMin0]

Any luck getting this PR merged? I faced the same problem in a project I'm working on. I prefer having control over which schema the migrations table is created in, instead of defaulting to dbo.

[convoliution]

@jeff-99 @dhui Out of curiosity, what do you feel is the current status of this PR?

I've been encountering what seems to be a requirement in ensureVersionTable() that the user's migration table be in the dbo schema

WHERE id = object_id(N'[dbo].[` + ss.config.MigrationsTable + `]')

and the proposed fix in this PR

WHERE id = object_id(N'[` + ss.config.SchemaName + `].[` + ss.config.MigrationsTable + `]')

would help immensely in unblocking cases where the user's default schema is not dbo.