Drizzle Team•3mo ago

Does drizzle support querying across multiple mysql databases (same mysql server)?

Here's some 'rough' code to demonstrate what I'm attempting to do... basically I have tables in two mysql databases (same mysql server). I can write SQL to query data from tables in different databases, but am struggling to get drizzle to do it. I saw that drizzle allows defining the database with mysqlSchema(). Looking at the query that is being generated, it is not using the mysql database (mysqlSchema). When the query is generated, it thinks both tables in the same mysql database (mysqlSchema). Maybe the 'rqb' doesn't support multiple databases or does drizzle not support querying across them yet?

  const schema1 = mysqlSchema('schema1');
  const schema2 = mysqlSchema('schema2');

  const table_a = schema1.table('table_a', {
    id: int('id'),
  });
  const table_b = schema2.table('table_b', {
    id: int('id'),
    tblA_ref: int('a_ref').references(() => table_a.id),
  });

  const tbl_b_relations = relations(table_b, ({ one }) => ({
    tblA: one(...)
  });
 
  const schema = { table_a, table_b, tbl_b_relations };

  const poolConnection = mysql.createPool(...);

  const db = drizzle(poolConnection, {
    ...,
    schema
  });

  const results = db.query.table_b.findMany({
    with: {
      tblA: true
    },
  });

  const schema1 = mysqlSchema('schema1');
  const schema2 = mysqlSchema('schema2');

  const table_a = schema1.table('table_a', {
    id: int('id'),
  });
  const table_b = schema2.table('table_b', {
    id: int('id'),
    tblA_ref: int('a_ref').references(() => table_a.id),
  });

  const tbl_b_relations = relations(table_b, ({ one }) => ({
    tblA: one(...)
  });
 
  const schema = { table_a, table_b, tbl_b_relations };

  const poolConnection = mysql.createPool(...);

  const db = drizzle(poolConnection, {
    ...,
    schema
  });

  const results = db.query.table_b.findMany({
    with: {
      tblA: true
    },
  });

4 Replies

Mario564•3mo ago

RQB currently doesn't support multiple schemas. This will likely be addressed in RQB v2

mr_pablo•3mo ago

Any update on this? I have a use case for multiple databases on the same server. @asch where did you get up to without being able to use RQB?

aschOP•3mo ago

If you need to query across databases then I believe you have to use db.select(). If you just need to access both databases then just create multiple connections.

mr_pablo•3mo ago

yea, i need to query across the 2 DBs. Did you ever actually try it? Using db.select, how would I target the different databases?

Gaming

Programming

Does drizzle support querying across multiple mysql databases (same mysql server)?