Drizzle Team•14mo ago

What is the Drizzle convention for storing the `db` object?

In the "Quick Start" guide, it showcases connecting to the database at the root of a module. However, in real code, this would likely be in a file called "db.ts", and it would not be at the root of the module, since the connection string would not yet be available. This is my naïve attempt to show what a more real-life code would look like:

export let db: PostgresJsDatabase;

export function databaseInit(): void {
  const config = getDatabaseConfig();
  const client = postgres(config);
  db = drizzle(client);
}

export let db: PostgresJsDatabase;

export function databaseInit(): void {
  const config = getDatabaseConfig();
  const client = postgres(config);
  db = drizzle(client);
}

And then, elsewhere in the code:

const user = await db.select().from(usersTable);

const user = await db.select().from(usersTable);

But here, I export a "let" variable, which flags ESLint and suggests that I am architecting my code incorrectly. Is there a more conventional way to handle the "db" object?

30 Replies

mr_pablo•14mo ago

no idea if it helps, but in my AWS Lambda functions, I simply do this, after my imports

const conn = mysql.createPool({
  host: RDS_HOSTNAME,
  user: RDS_USERNAME,
  password: RDS_PASSWORD,
  port: 3306,
  database: RDS_DATABASE_NAME,
  multipleStatements: true,
  enableKeepAlive: true,
});

const db = drizzle(conn, { schema: schema });

const conn = mysql.createPool({
  host: RDS_HOSTNAME,
  user: RDS_USERNAME,
  password: RDS_PASSWORD,
  port: 3306,
  database: RDS_DATABASE_NAME,
  multipleStatements: true,
  enableKeepAlive: true,
});

const db = drizzle(conn, { schema: schema });

then underneath I have a single class, with my various methods in it, which can all use db (we have a micro service style setup, where we have 1 lambda function per API endpoint)

Asseater Peter•14mo ago

I had the same problem, but I think I have a bit of a more elegant solution (Tho I did actually do exactly what @mr_pablo_85 suggested for a while) My current project has controllers for each table (UserController, MessageController), so I did this: I have an abstract class controller that looks like this:

const connection = connect({
  host: env.PLANETSCALE_HOST,
  username: env.PLANETSCALE_USER,
  password: env.PLANETSCALE_PASS,
})

const db = drizzle(connection, { schema })

export abstract class Controller {
  protected static readonly dbInstance = db
}

const connection = connect({
  host: env.PLANETSCALE_HOST,
  username: env.PLANETSCALE_USER,
  password: env.PLANETSCALE_PASS,
})

const db = drizzle(connection, { schema })

export abstract class Controller {
  protected static readonly dbInstance = db
}

And then whenever I need a new controller I extend the abstract class and go from there;

export class ChannelController extends Controller {
  static createChannel = async (name: string) => {
    await this.dbInstance.insert(ChannelTable).values({
      id: uuid(),
      name,
    })
  }

  static getChannels = (): Promise<Channel[]> =>
    this.dbInstance.select().from(ChannelTable)

  static getChannelByName = (name: string): Promise<Channel | undefined> =>
    this.dbInstance.query.ChannelTable.findFirst({
      where: eq(ChannelTable.name, name),
    })
}

export class ChannelController extends Controller {
  static createChannel = async (name: string) => {
    await this.dbInstance.insert(ChannelTable).values({
      id: uuid(),
      name,
    })
  }

  static getChannels = (): Promise<Channel[]> =>
    this.dbInstance.select().from(ChannelTable)

  static getChannelByName = (name: string): Promise<Channel | undefined> =>
    this.dbInstance.query.ChannelTable.findFirst({
      where: eq(ChannelTable.name, name),
    })
}

Example use:

import { ChannelController } from '../db'
await ChannelController.getChannels()

import { ChannelController } from '../db'
await ChannelController.getChannels()

mr_pablo•14mo ago

What is eslint saying exactly? You can just ignore it you know...

Zamiel•14mo ago

What is eslint saying exactly? You can just ignore it you know...

To be clear, I'm manually suppressing the error. But usually, when you are manually suppressing ESLint errors, it means you are doing something wrong. Not always, but as a general rule of thumb. You just connect at the top level / root level of the module? That won't work for me because 1) it is importing for side effects, which is generally considered to be nasty 2) settings such as RDS_HOSTNAME and RDS_USERNAME are coming from environment variables and are not yet ready to be used when the module is imported and the top-level code is first executed, which is why it needs to be wrapped inside of an init function.

mr_pablo•14mo ago

Re point 2, the env vars should be ready by time the code executes... why wouldn't they be?

Zamiel•14mo ago

Because they come from an .env file, so for example if you are using the dotenv library then you have to execute dotenv.config() earlier on in the execution path. Merely invoking dotenv.config() at all in the top-level of any file would have the same problem, you are just importing code for side effects.

mr_pablo•14mo ago

Just call that first? Stuff like that should be available from the get go Or get them from process.env

Zamiel•14mo ago

It shouldn't be called first in the db.ts file because other files need environment variables other than just the db.ts file, which should only handle the database. We don't want to call it more than one time.

mr_pablo•14mo ago

I'd say look at this env var issue really. Use process.env so they are available right away. You should know the db connection details so no reason why you have to "wait" for them

Zamiel•14mo ago

I am using process.env. dotenv loads them from disk. See: https://github.com/motdotla/dotenv Pretty sure that using .env files is best-practice.

mr_pablo•14mo ago

Can you not load them into the actual running env? Either way I think the issue here is how you are using env vars and initialising the drizzle orm

Asseater Peter•14mo ago

So, two things: First, I highly recommend giving https://env.t3.gg/docs/introduction a look, it is excellent Second, @zamiel did you give my solution a look? It's been in prod for me for about a month with no issues, and the DX is pretty solid

Introduction ⋅ T3 Env

Forgetting environment variables during build can be a hassle and difficult to debug if a bug is caused by a missing environment variable. This package provides a simple way to define environment variables validation for your app.

Zamiel•14mo ago

Nice, I'll take a look at T3 Env. Yes, I gave your solution a look, but I'm not quite sure I understand. It just looks like you are using the singleton class pattern to manage the db: https://refactoring.guru/design-patterns/singleton But in the context of my question, it would be kind of the same thing, e.g.:

export let db: PostgresJsDatabase;

export let db: PostgresJsDatabase;

-->

export let db: MyClassAbstractionOnTopOfTheBaseDrizzleConnectionObject;

export let db: MyClassAbstractionOnTopOfTheBaseDrizzleConnectionObject;

I guess my question is more general than just Drizzle, it would extend to any TypeScript server variable that is initialized on server boot and is intended to be used in many other files. Like a logger or a database connection or a redis client. (I'm new to writing TypeScript servers, I have typically used Golang in the past.)

Asseater Peter•14mo ago

Sort of? It's a singleton, but every one of my controllers is a separate singleton inheriting a specific parent's connection So I'm actually never directly interfacing with the connection variable outside of my classes, and the classes themselves inherit it from the abstract class which is only responsible for that one thing. Type inference is therefore super easy and the errors are quite useful I came up with this solution tho, so there might be a better pattern available. However, from fiddling around and thinking about it (quite a lot, actually) I haven't come up with any better ideas

Zamiel•14mo ago

Oh, I see now. Thank you.

Gaming

Programming

What is the Drizzle convention for storing the `db` object?