-
Notifications
You must be signed in to change notification settings - Fork 238
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Ms sql server - database / schema / tablename #875
Comments
Yes. See this recipe https://kysely.dev/docs/recipes/schemas |
Thanks, but unfortunately it only works with a schema but not with a database.schema |
Oh, that's true. I'm not sure if we can make that work without complicating everything too much. But let's take a look. For now it's impossible to do this without using raw SQL. // PersonTable is the table interface.
.selectFrom(sql<PersonTable>`foo.bar.baz.person`.as('person')) or something like: function table<T>(...t: string[]) {
return sql<T>`${sql.id(...t)}`
}
...
.selectFrom(table<PersonTable>("foo", "bar", "baz", "spam").as('person')) |
Just want to add this here: With some slight modifications, the solution above covers all of my needs for cross database joins. I have a bunch of databases that I do not have much control over and it's structured such that there are a few situations in which I need to join two tables across databases. I also have kysely-codegen writing all of my database schemas out to separate TypeScript files. I have some custom codegen alongside that which will create a TypeScript type that includes all of the databases from my server like so in file called schemas/index.ts: import { DB as DB1 } from "./db1";
import { DB as DB2 } from "./db2";
export type Server = {
DB1: DB1;
DB2: DB2;
}; where ./db1 is something like export interface DB {
person: {
id: Generated<string>
first_name: string
last_name: string | null
created_at: Generated<Date>
age: number
}
} and ./db2 is something like export interface DB {
pet: {
id: Generated<string>
name: string
owner_id: string
species: 'cat' | 'dog'
is_favorite: boolean
}
} DB1 and DB2 would be the name of my databases. All of my databases share the same schema name, so I can just do something like the following: import { Server } from "./schemas";
export function table<
DB extends keyof Server,
Table extends string & keyof Server[DB]
>(database: DB, table: Table) {
return sql<Server[DB][Table]>`${sql.id(database, "dbo", table)}`;
} With that, you can use it as such for a join: const result = await db1.selectFrom("person")
.innerJoin(table("DB2", "pet").as("pet"), "person.id", "pet.owner_id")
.selectAll()
.execute(); You can also use the json helpers as such: const result = await db1.selectFrom("person")
.selectAll("person")
.select((eb) =>
jsonArrayFrom(
eb
.selectFrom(table("DB2", "pet").as("pet"))
.select(['pet.id', 'pet.name'])
.whereRef("pet.owner_id", "=", "person.id")
).as("pets")
)
.execute(); Both of these keep the type safety without requiring explicit generics and allowing you to join across databases. Apologies if any of this syntax is slightly off, I more or less took the example from kysely's relations recipe, modified it with the tables helper function and to fit the schemas in my databases. Then I subbed the example table's names and schemas back in, but I think this covers the gist of it. Edit: here's a kysely playground link which might be a bit more digestible. |
Good morning,
I work with Sql server on several databases. When I do a select it is common for me to join tables between several databases. Is there a way on Kysely to specify the database/schema/tablename?
Ex:
Select * from dbname1.dbo.tablename1 T1 inner join dbname2.dbo.tablename2 T2 on T1.ID = T2.ID
tyvm
The text was updated successfully, but these errors were encountered: