MSSQL Support Fully Qualified Table Names

[chuck-flowers]

In MSSQL it is possible to reference tables from multiple databases within a single connection. This is accomplished by using a 3-part identifier like the following: MyDatabase.dbo.MyTable.

It appears that kysely doesn't currently support this. Is there a technical reason that prevents this from being supported or has it not yet been requested?

I'm more than happy to try to implement it if you're open to having the feature included in the project. After looking at the code, my first idea is to add an optional database property to the SchemableIdentifier type and support properly stringifying it in the MssqlCompiler if that property is present. If there's something I'm overlooking please let me know.

[koskimas]

This has come up, but implementing support for this would complicate the types a lot. It'd make the types more complex and considerably slower to compile for everyone. Not just mssql users.

You are overlooking types. The SQL part of it is relatively straight forward to implement. The types definitely aren't.

[chuck-flowers]

I totally understand.

Since this has come up before, are there any recommended work arounds? Unfortunately the way the DBs are organized for this application requires doing cross-db joins.

[igalklebanov]

Hey 👋

A possible workaround would be:

1. omitting dbo from the database interface table names.
2. a plugin that maps to/from 3 dot references from/to 2 dot references.

So when you're writing your queries, you don't write dbo, just [dbname].[tablename], but [dbname].dbo.[tablename] gets compiled.

[chuck-flowers]

Thank you for the suggestions. That sounds like a workable solution on my end.

I'll close this issue now

[chuck-flowers]

@igalklebanov I took a stab at creating the plugin as you described but I'm running into another issue now. After mapping from 2 to 3, it seems that kysely is double quoting the result to escape the extra dot. Is there a way to get kysely to not escape the table name with double quotes?

EDIT: Nevermind, I was able to accomplish it by extending/overriding the MssqlQueryCompiler. I was able to get a query to execute successfully.