Sycamore Willow•7mo ago

How can I use multiple DBs with trpc in T3 app?

I'm cross posting to T3 Discord as well. Here's the Q: Hey all, I'm using T3 for a proof of concept at the startup I work at. I'm really really digging this stack and want to pitch it to our team to replace our current stack. I've chosen to work with Drizzle, can explain more why if necessary but will wave my hands at that for a moment. One key thing I need to demonstrate in the POC is that I can run queries on multiple DBs that live on different hosts. I did not think I'd get stuck on this bit! But I'm stumped. Here's what I've done: I have config and schema defined for two DBs. I'll call them DB1 and DB1. I'm able to connect to them separately with Drizzle Studio, so I know I have the connections working. I assumed I could just put DB2 into the TRPC context here (src/server/api) : const createInnerTRPCContext = (opts: CreateContextOptions) => { return { session: opts.session, db1, db2, }; }; Then in an rpc when I use db2 like so: const res = await ctx.db2.query.foo.findFirst(); I get the error relation "foo" does not exist. foo does exist on the schema for db2. Code completion knows about it and like I mentioned Drizzle Studio connects to db2 and I can see the foo table. One more weirdsie! Let's say there's a table called bar in db1. I can call this successfully: const data = await ctx.db2.select().from(bar).limit(10); Clearly I'm doing something wrong and it isn't obvious to me how I get the second DB into context! Thanks in advance!

3 Replies

Mika•7mo ago

@Sycamore Willow In this case I would try to understand if tRPC context is messing up something, so I'd try to remove the db from the context completely, and use the db by importing it from a file.

import { db1, db2 } from "~/db";

import { db1, db2 } from "~/db";

To avoid potentially creating multiple connections, I would put it into globalThis

// db.ts

function getDBs() {
   const g = globalThis as any;
   g.db1 ??= createDB1Connection();
   g.db2 ??= createDB2Connection();
 
   return { db1: g.db1, db2: g.db2 };
}

export const {db1, db2} = getDBs();

// db.ts

function getDBs() {
   const g = globalThis as any;
   g.db1 ??= createDB1Connection();
   g.db2 ??= createDB2Connection();
 
   return { db1: g.db1, db2: g.db2 };
}

export const {db1, db2} = getDBs();

I never quite understood the appeal of putting dbs in context, in all of my projects i follow a similar pattern above ^

Sycamore WillowOP•7mo ago

Hey! Thanks for the speedy reply! I will try this right now and let you know. Hell yeah, that worked @Mika ! Griacias por todo! It ends up ctx wasn't buggering up anything. It was the fact that I didn't quite understand how the connection as being added to the globalThis. Now I get it thanks to your help. I think I can put it back in context if I want now.

Mika•7mo ago

Awesome, good luck pitching tRPC to your startup!