Join types supported in Drizzle ORM
Drizzle ORM supports four join types: INNER JOIN, LEFT JOIN, RIGHT JOIN, and CROSS JOIN. The join syntax in Drizzle ORM balances SQL-likeness with type safety.
drizzle-orm/joins
11 notes, read out of this brain and free to use. Each one was extracted from a source and is re-checked against its exam.
RIGHT JOIN syntax and type inference
Use db.select().from(users).rightJoin(pets, eq(users.id, pets.ownerId)) to perform a right join. The resulting type makes the left table nullable. For example, the user field becomes {id: number; name: string} | null, while the pets field remains non-nullable.
INNER JOIN syntax and type inference
Use db.select().from(users).innerJoin(pets, eq(users.id, pets.ownerId)) to perform an inner join. Both tables in the result are non-nullable. For example, both user and pets fields have their full types without null.
CROSS JOIN syntax and type inference
Use db.select().from(users).crossJoin(pets) to perform a cross join. No join condition is specified. Both tables in the result are non-nullable.
Type casting with sql operator in joins
When using the sql operator for partial selection fields in joins, explicitly specify the type as sql<type | null> for proper result type inference. For example, use sql<string | null>`upper(${pets.name})` when the field might be null due to a left or right join. Without the explicit type, the field defaults to unknown type.
Nested select object in joins to avoid nullable field proliferation
When joining tables with many columns, use nested select object syntax to make the entire joined object nullable instead of making all individual fields nullable. For example: { userId: users.id, userName: users.name, pet: { id: pets.id, name: pets.name } } results in the pet object being {id, name} | null rather than having individual nullable fields.
Aggregating join results into hierarchical structures
Drizzle ORM returns name-mapped results from the driver. Results can be aggregated using reduce to transform flat join results into hierarchical structures, such as mapping many-to-one relationships by grouping pets under their users.
Many-to-one join example
For many-to-one relationships, join the parent table with the child table. Example: db.select().from(cities).leftJoin(users, eq(cities.id, users.cityId)).all() retrieves all cities with their associated users.
Many-to-many join example
For many-to-many relationships, use a junction table and chain multiple joins. Example: db.select().from(usersToChatGroups).leftJoin(users, eq(usersToChatGroups.userId, users.id)).leftJoin(chatGroups, eq(usersToChatGroups.groupId, chatGroups.id)).where(eq(chatGroups.id, 1)).all() retrieves a chat group and all its participants.
Join subquery with main table
Subqueries can be joined with tables: `const sq = db.select().from(users).where(eq(users.id, 42)).as('sq'); db.select().from(users).leftJoin(sq, eq(users.id, sq.id))`.
Select with joins, grouping, and pagination with Drizzle
Use db.select() with custom column selection via getColumns helper, leftJoin() for relationships, groupBy() for aggregation, orderBy() for sorting, limit() and offset() for pagination. Example: db.select({ ...getColumns(usersTable), postsCount: count(postsTable.id) }).from(usersTable).leftJoin(postsTable, eq(usersTable.id, postsTable.userId)).groupBy(usersTable.id).orderBy(asc(usersTable.id)).limit(pageSize).offset((page - 1) * pageSize). getColumns is available from drizzle-orm@1.0.0-beta.2; use getTableColumns for earlier versions.