A repository has one job: turn a question about the domain into SQL, run it, and turn the rows back into your own types.
Two rules follow from that, and both are load-bearing.
It returns domain objects, not driver rows
The moment a raw result set escapes into the service layer, the database stops being swappable. Not because an ORM would have saved you, but because row.course_id is now spelled that way in forty places, and the column name is a public interface nobody declared.
function toCourse(row) {
if (!row) return null;
return {
id: row.id,
slug: row.slug,
title: row.title,
priceCents: row.price_cents,
publishedAt: row.published_at,
};
}
export async function findBySlug(slug) {
return toCourse(await one(
'SELECT * FROM catalog_courses WHERE slug = ?',
[slug],
));
}
The mapping function is boring, and that is the point. It is the one place the schema's naming meets the application's naming, so renaming a column is a one-line change rather than a search.
Every query is parameterised
// Wrong, and no amount of validation upstream makes it safe:
const found = await query(`SELECT * FROM users WHERE email = '${email}'`);
// Right:
const found = await query('SELECT * FROM users WHERE email = ?', [email]);
The difference is not escaping. With a placeholder the value never becomes part of the statement at all: the database is handed a query and, separately, a value, so there is nothing a quote character could break out of.
People sometimes argue that a validated input is safe to interpolate. It is not an argument worth having, because the parameterised version is shorter.
What belongs here, and what does not
In: the SQL, the mapping, the transaction boundary when several statements must succeed together, and the translation of a constraint violation into a domain error that means something ("that slug is taken" rather than ER_DUP_ENTRY).
Out: anything that decides. If a method is called createCourseIfInstructorHasQuota, the quota rule is a service rule that leaked downwards.
The test for the boundary is simple. Read the repository with the schema in front of you, and the service with the product brief in front of you. If you need the other document open to understand either file, something is in the wrong layer.