How to use prepared statements in a repository class?

[paulwongx]

I want to use prepared statements in my repository class. Typically you would pass in the database object into the class and make calls there. I'm not sure whether the prepared statement should be outside of the class, or inside the class when using it in the context of aws lambas / server functions. Looking for another brain to share some thoughts...

Option 1 - Prepared statement outside the repository class

• The prepared statement calls the _db instatiation every time. Is this less efficient than passing it once into the UserRepository class to use since it needs to reinstantiate every time (or is the db connection done only once for the life of the server function?), or the same as passing it in once into the UserRepository?

import { db } from "@/src/connect";

const preparedCreateUser = db.insert(users).values({}).prepare("createUser");

class UserRepository {
  async createUser(user: NewUser) {
    await preparedCreateUser.execute({ user });
  }
}

Option 2 - Prepared statement inside the repository class

• This seems redundant (I think) since the class gets instantiated on every server function call so using prepared statements here seems pointless?

import { db as _db } from "@/src/connect";

class UserRepository {
  private db: typeof _db;

  constructor(db: typeof _db) {
    this.db = db;
  }

  async createUser(user: NewUser) {
    const preparedCreateUser = this.db.insert(users).values({}).prepare("createUser");
    await preparedCreateUser.execute({ user });
  }
}

My own answer: Based on the docs for prepared statements, it says if I want to reuse my db connection and prepared statements, it just has to be declared outside the handler, so in this case, I think option 1 is the correct solution right?

Next logical step...

So then if option #1 is right, the next logical step seems to be creating a class of prepared statements and a repository class to call that prepared statement class. Does this still get the benefit of prepared statements, or is this overcomplicating it?

• Should prepared statements be standalone declarations instead?

import { db as _db } from "@/src/connect";

class PreparedUserRepositoryStatements {
  // Should there be a constructor here?
   private db: typeof _db;

  constructor(db: typeof _db) {
    this.db = db;
  }

  createUser = db.insert(users).value({}).prepare("createUser");
  // deleteUser = ... 
}

class UserRepository extends PreparedUserRepositoryStatements {
  constructor(db: typeof _db) {
    super(db);
  } 

  async createUser(user: NewUser) {
    await this.createUser.execute({ user });
  }
  // async deleteUser 
}

[bpingris]

const getPreparedStatements = (db: DB) => {
	const getSomething = db.query.something
		.findFirst({
			where: eq(something.id, sql.placeholder('id')),
		})
		.prepare('getSomething')

	return { getSomething }
}

export class CustomerSchedulersRepository {
	#db: DB
	#statements: ReturnType<typeof getPreparedStatements>

	constructor(db: DB) {
		this.#db = db
		this.#statements = getPreparedStatements(db)
	}

	get(id: string) {
		return this.#statements.getSomething.execute({ id })
	}
}

here's how I tried to organise my code, I really have no idea if it's good or not 🤷‍♂️, but in my experience it works ok, I don't lose any type-safety anywhere, I don't find the api that awkward, having the #statement property denotes what we are accessing in the methods and so on.