Many to many and transactions
Summary: Discover how to handle complex database relationships (many-to-many) and ensure data integrity using SQL Transactions, without relying on an ORM.
In StartER, the default item resource is simple: one user owns many items (a 1-to-N relationship). But real-world applications often require many-to-many (M:N) relationships.
Imagine we want to introduce a group resource.
- A
usercan belong to manygroup. - A
groupcan have manyuser.
Relational databases cannot store M:N relationships directly. Instead, you must use a third table called a join table (or junction table) that sits between the two entities.
Tip
Naming convention: it is a strong industry best practice to name join tables by combining the two singular entity names in alphabetical order. Because 'g' comes before 'u', the table should be named group_user (not user_group).
Our schema would look like this:
CREATE TABLE "group" (
id INTEGER PRIMARY KEY NOT NULL,
name VARCHAR(255) NOT NULL
);
CREATE TABLE group_user (
group_id INTEGER NOT NULL,
user_id INTEGER NOT NULL,
PRIMARY KEY (group_id, user_id),
FOREIGN KEY (group_id) REFERENCES "group" (id) ON DELETE CASCADE,
FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE CASCADE
);When a user creates a new group, you need to do two things:
- Insert the new group into the
grouptable. - Insert a link into the
group_usertable to make the creator a member of the group.
// 1. Create the group
const { lastInsertRowid: groupId } = database
.prepare('INSERT INTO "group" (name) VALUES (?)')
.run(name);
// 2. Add the creator to the group
const { lastInsertRowid: groupUserId } = database
.prepare("INSERT INTO group_user (group_id, user_id) VALUES (?, ?)")
.run(groupId, creatorUserId);What happens if the second query fails? (e.g., the user_id doesn't exist, or the server crashes exactly between the two queries).
The database will contain a group that has no members. This is called an "orphaned" record, and it breaks data integrity.
We need both queries to succeed completely, or fail completely. This is what ACID (Atomicity, Consistency, Isolation, Durability) properties guarantee, specifically Atomicity.
A transaction groups multiple SQL statements into a single, all-or-nothing operation.
Because StartER uses raw SQL (Zero-Magic) via node:sqlite, implementing a transaction is incredibly straightforward. You wrap your queries between BEGIN and COMMIT. If anything goes wrong, you catch the error and execute ROLLBACK to undo everything.
Here is how you would write the create function inside src/express/modules/group/groupRepository.ts:
create(name: string, creatorUserId: string) {
// 1. Start the transaction
database.prepare("BEGIN").run();
try {
// 2. Execute the queries
const { lastInsertRowid: groupId } = database
.prepare('INSERT INTO "group" (name) VALUES (?)')
.run(name);
const { lastInsertRowid: groupUserId } = database
.prepare("INSERT INTO group_user (group_id, user_id) VALUES (?, ?)")
.run(groupId, creatorUserId);
// 3. If everything succeeded, commit the changes to the database
database.prepare("COMMIT").run();
return { groupId, groupUserId };
} catch (error) {
// 4. If ANY query threw an error, cancel the entire transaction
database.prepare("ROLLBACK").run();
// Re-throw the error so the Express Action can catch it and return a 500
throw error;
}
}Important
Always put your ROLLBACK inside a catch block and re-throw the error. If you forget to ROLLBACK, the database connection might remain locked in the transaction state, causing your application to freeze on subsequent queries.
Creating records safely is only half the battle. Reading many-to-many data back out using raw SQL is where developers usually miss the magic of ORMs.
When you want to fetch a group and its users, you have two primary ways to do it in "Zero-Magic" SQL.
Note
For the sake of simplicity, the following examples rely on TypeScript's type inference for the return type. In a real application, you should explicitly define the return types (e.g., GroupWithUsers) in your types.ts file to ensure strict end-to-end type safety!
The cleanest and most readable way to structure this in your Repository is to just run two separate queries and combine the results in JavaScript.
getGroupWithUsers(groupId: number) {
// 1. Fetch the group
const group = database.prepare('SELECT * FROM "group" WHERE id = ?').get(groupId);
if (!group) return null;
// 2. Fetch the associated users using an INNER JOIN
const users = database.prepare(`
SELECT user.*
FROM user
JOIN group_user ON user.id = group_user.user_id
WHERE group_user.group_id = ?
`).all(groupId);
// 3. Assemble and return the complete object
return {
...group,
users
};
}Why this is best: It's incredibly easy to read. You don't get duplicate rows, and the users array is naturally formatted as a list of user objects.
If you must do it in a single trip to the database, modern SQLite has powerful built-in JSON functions (like json_group_array) that allow you to aggregate joined rows into a JSON array directly inside SQL.
getGroupWithUsers(groupId: number) {
const row = database.prepare(`
SELECT
"group".id,
"group".name,
json_group_array(
json_object('id', user.id, 'email', user.email)
) as users
FROM "group"
LEFT JOIN group_user ON "group".id = group_user.group_id
LEFT JOIN user ON group_user.user_id = user.id
WHERE "group".id = ?
GROUP BY "group".id
`).get(groupId);
// SQLite returns `users` as a JSON string, so you must parse it
if (row) {
row.users = JSON.parse(row.users);
}
return row;
}Why this is trickier: If the group has zero users, SQLite's LEFT JOIN might result in [{"id": null, "email": null}], which requires extra cleanup logic in JavaScript. This is why Approach 1 is usually the preferred "Zero-Magic" pattern!
By using BEGIN, COMMIT, and ROLLBACK, you ensure that complex, multi-table operations maintain perfect data integrity. You now have the tools to build teams, roles, collaborative projects, or any other many-to-many feature!
AI co-creation
Getting started
Explanations
How-To Guides
Reference
Digging deeper