[FEATURE]: Join different databases

[Foahh]

Describe what you want

We are migrating from Knex to Drizzle, but it's unclear from the documentation whether Drizzle supports joins across multiple databases. This feature is crucial for our distributed data use cases.

[L-Mario564]

@Foahh What database are you using? There is support for joins across DBs in MySQL and across schemas in PG.

[L-Mario564]

Closing this due to inactivity.

[AlexisMunozC]

@Foahh What database are you using? There is support for joins across DBs in MySQL and across schemas in PG.

Hi, Where could I find an example of creating a query with joins across multiple DBs in MySQL? I have tried to create the tables using const mySchema = mysqlSchema("db_1") and then use mySchema.table, and this works for querying, but drizzle-kit doesn't support mysqlSchema tables to generate migrations anymore, so is there another way to use joins across DBs?

[L-Mario564]

@Foahh What database are you using? There is support for joins across DBs in MySQL and across schemas in PG.

Hi, Where could I find an example of creating a query with joins across multiple DBs in MySQL? I have tried to create the tables using const mySchema = mysqlSchema("db_1") and then use mySchema.table, and this works for querying, but drizzle-kit doesn't support mysqlSchema tables to generate migrations anymore, so is there another way to use joins across DBs?

Performing joins across DBs and managing said DBs are two completely separate things. The first is supported by Drizzle, you can declare those tables in your TS code, but you are responsible for managing migrations for that other DB.

[Foahh]

@Foahh What database are you using? There is support for joins across DBs in MySQL and across schemas in PG.

Thank you for your reply! I missed the email and just now saw this post. I am using MySQL. I couldn't find any documentation on how to perform joins across databases in a Drizzle statement, so I resolved this issue by using raw SQL.

[Foahh]

@Foahh What database are you using? There is support for joins across DBs in MySQL and across schemas in PG.

Hi, Where could I find an example of creating a query with joins across multiple DBs in MySQL? I have tried to create the tables using const mySchema = mysqlSchema("db_1") and then use mySchema.table, and this works for querying, but drizzle-kit doesn't support mysqlSchema tables to generate migrations anymore, so is there another way to use joins across DBs?

If your goal is to migrate across multiple databases using Drizzle Kit, my idea is creating a script to handle the migrations with the migrator.

Part of the migrate.ts:

import { drizzle } from "drizzle-orm/mysql2";
import { migrate } from "drizzle-orm/mysql2/migrator";
import { ConnectionOptions, createConnection } from "mysql2/promise";

export async function migrateDatabase(
  data: ConnectionOptions,
  migrationsFolder: string,
) {
  const { database, ...config } = data;
  const conn = await createConnection(config);

  try {
    await conn.execute(
      `CREATE DATABASE IF NOT EXISTS \`${database}\` CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;`,
    );

    await conn.changeUser({ database });

    await migrate(drizzle(conn, { logger: false }), {
      migrationsFolder,
    });
  } finally {
    await conn.end();
  }
}

In the package.json, I added "migrate": "pnpm exec ts-node src/scripts/migrate.ts"

[AlexisMunozC]

Oh, I didn't know the changeUser method, that helps a lot, thank you!