Drizzle Team•11mo ago

joining tables from different SQL Schemas MARIA Db

Hi, we have a multi-schema Maria Db and in the old codeignitor query language it was possible to join tables even though they were not part of the same sql schema. Does this work in drizzle as well? I tried to setup the multischema part like described in the docs, but i am struggling with the database connection.

const firstSchema = mysqlSchema("firstSchema")

const firstSchemaTable = firstSchema.table("table_one", {...})


const secondSchema = mysqlSchema("secondSchema")

const secondSchemaTable = secondSchema.table("table_one", {...})

const firstSchema = mysqlSchema("firstSchema")

const firstSchemaTable = firstSchema.table("table_one", {...})


const secondSchema = mysqlSchema("secondSchema")

const secondSchemaTable = secondSchema.table("table_one", {...})

The schema names are matching with the schemas/databases in the mariaDb The question now is: What do I need to provide as database name in order to make this work? At the end I want to be able to do something like this:

const myQueryResult = await db
.select()
.from(firstSchemaTable)
.leftJoin(secondSchemaTable, eq(firstSchemaTable.secondTableFk, secondSchemaTable.id))

const myQueryResult = await db
.select()
.from(firstSchemaTable)
.leftJoin(secondSchemaTable, eq(firstSchemaTable.secondTableFk, secondSchemaTable.id))

or in plain SQL

SELECT *
FROM "firstSchema".table_one to
JOIN "secondSchema".table_one tt ON to.secondTableFk = tt.id

SELECT *
FROM "firstSchema".table_one to
JOIN "secondSchema".table_one tt ON to.secondTableFk = tt.id

is that possible with drizzle?

0 Replies

No replies yetBe the first to reply to this messageJoin

Gaming

Programming

joining tables from different SQL Schemas MARIA Db