Allow specifying database schema on migrations (MSSQL database)

[mkouhia]

I am developing a program deployed in Azure App services, and the program contacts Azure SQL database with managed identity authentication. There is no default schema set for the role, and therefore migrations will not succeed. Of course, good practice would be to set the default schema (see e.g. here), but for MSSQL a possibility to set the schema explicitly would be a good thing to have in every case.

Would it be possible to specify to Refinery, the database schema in addition to database table, where the migrations reside?

2024-05-10T15:34:48.861805281Z 2024-05-10T15:34:48.861617Z ERROR tiberius::tds::stream::token: The specified schema name "a2b3d93d-bc54-4fd3-80f6-f8e6ccf31d78@2ef3c4e1-5d07-493d-b7e6-6ef5ef5534c5" either does not exist or you do not have permission to use it. code=2760
2024-05-10T15:34:48.864015479Z 2024-05-10T15:34:48.863905Z ERROR tiberius::tds::stream::token: The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION. code=3903
2024-05-10T15:34:48.864032080Z 2024-05-10T15:34:48.863931Z ERROR refinery_core::drivers::tiberius: could not ROLLBACK transaction, Token error: 'The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.' on server <server name removed> executing  on line 1 (code: 3903, state: 1, class: 16)
2024-05-10T15:34:48.864038232Z 2024-05-10T15:34:48.863940Z ERROR modular_epd::errors: error=`error asserting migrations table`, `Token error: 'The specified schema name "a2b3d93d-bc54-4fd3-80f6-f8e6ccf31d78@2ef3c4e1-5d07-493d-b7e6-6ef5ef5534c5" either does not exist or you do not have permission to use it.' on server <server name removed> executing  on line 3 (code: 2760, state: 1, class: 16)` action="Database migration error"
2024-05-10T15:34:48.874574065Z Error: DatabaseError { action: "Database migration error", detail: "`error asserting migrations table`, `Token error: 'The specified schema name \"a2b3d93d-bc54-4fd3-80f6-f8e6ccf31d78@2ef3c4e1-5d07-493d-b7e6-6ef5ef5534c5\" either does not exist or you do not have permission to use it.' on server <server name removed> executing  on line 3 (code: 2760, state: 1, class: 16)`" }

For me, following attempt to define schema also resulted in problems:

let report = embedded::migrations::runner()
    .set_migration_table_name("dbo.refinery_schema_history")
    .run_async(client)
    .await?;

then error would be

2024-05-10T16:20:11.243413582Z 2024-05-10T16:20:11.228230Z ERROR refinery_core::drivers::tiberius: could not ROLLBACK transaction, Token error: 'The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.' on server <server name removed> executing  on line 1 (code: 3903, state: 1, class: 16)
2024-05-10T16:20:11.243419183Z 2024-05-10T16:20:11.228286Z ERROR modular_epd::errors: error=`error asserting migrations table`, `Token error: 'There is already an object named 'refinery_schema_history' in the database.' on server <server name removed> executing  on line 3 (code: 2714, state: 6, class: 16)` action="Database migration error"
2024-05-10T16:20:11.248482701Z Error: DatabaseError { action: "Database migration error", detail: "`error asserting migrations table`, `Token error: 'There is already an object named 'refinery_schema_history' in the database.' on server <server name removed> executing  on line 3 (code: 2714, state: 6, class: 16)`" }

[jxs]

Hi, yeah this makes sense, if you want to submit a PR I'll happily review it :)