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?

joining tables from different SQL Schemas MARIA Db

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", {...})

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?

joining tables from different SQL Schemas MARIA Db

Similar Threads

joining tables from different SQL Schemas MARIA Db

Similar Threads

Similar Threads

Similar Threads