Skip to main content

12.1 - Advanced Database Operations

Advanced Database Operations​

In this section, we'll explore some advanced database operations, including filtering, joining, grouping, aggregate functions, subqueries, Common Table Expressions (CTEs), data transactions, and set operations.

Filtering​

Filtering involves retrieving specific rows from a database table that meet certain criteria. This is done using the WHERE clause in SQL. In Drizzle, you can filter with where along with eq, ne, gt, lte, isNull, and/or, between, etc.

Use Case: Find products within a specific price range or with certain features.

export const getMenuItemById = async (db, id: number) => {
  return await db.select().from(menuItems).where(eq(menuItems.id, id));
};

Joining​

Joining combines rows from two or more tables based on a related column between them. The common types of joins are INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN.

Use Case: Combine data from the Customers table and the Orders table to get a list of all orders along with the customer details.

const userPosts = await db
  .select()
  .from(usersTable)
  .leftJoin(postsTable, eq(usersTable.id, postsTable.userId));

Grouping​

Grouping involves aggregating data based on one or more columns using the GROUP BY clause. This is often used with aggregate functions like SUM, AVG, COUNT, etc.

Use Case: Group sales data by product category to calculate the total sales for each category.

const salesByCategory = await db
  .select({
    category: salesTable.category,
    totalAmount: sum(salesTable.amount),
  })
  .from(salesTable)
  .groupBy(salesTable.category);

Aggregate Functions​

Aggregate Functions perform calculations on a set of values and return a single value. Common aggregate functions include SUM, AVG, MAX, MIN, and COUNT.

Use Case: Calculate the average order amount from the Orders table.

const salesByCategory = await db
  .select({
    category: salesTable.category,
    totalAmount: sum(salesTable.amount),
  })
  .from(salesTable)
  .groupBy(salesTable.category);

Subqueries​

Subqueries are queries nested inside another query. They are used to perform operations that require multiple steps.

Use Case: First find user ids of those who have made more than a certain number of purchases last month. And then use the user ids to find the corresponding user phone numbers.

const sq = db.select().from(users).where(eq(users.id, 42)).as("sq");
const result = await db.select().from(users).leftJoin(sq, eq(users.id, sq.id));

Common Table Expressions (CTEs)​

Common Table Expressions (CTEs) are temporary result sets that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement. They are defined using the WITH clause. Temporary result sets are defined within the scope of a single SQL statement and allow us to do further processing on this intermediary table.

Use Case: Create a temporary table with the customer id and total purchase amount for customers who have made purchases at the store for over 5 times in the past month. We then filter for customers whose purchase amount is greater than a certain value.

const sq = db.$with("sq").as(db.select().from(users).where(eq(users.id, 42)));
const result = await db.with(sq).select().from(sq);

Data Transactions​

Data Transactions are sequences of database operations that are treated as a single unit. Transactions ensure data integrity by making sure that all operations succeed or if we fail at any point, we can rollback the steps that have already been taken. Transactions are managed using BEGIN, COMMIT, and ROLLBACK.

Use Case: Ensure that all steps of a multi-step process (like transferring money between bank accounts) either complete successfully or none of them do.

await db.transaction(async (tx) => {
  await tx.insert(usersTable).values({ name: "Alice" });
  await tx.insert(accountsTable).values({ balance: 1000 });
});

Set Operations​

Set Operations are used to combine the results of two or more queries. The common set operations are UNION, INTERSECT, and EXCEPT.

Use Case: Combine results from two different queries to get a list of all unique customers who have made a purchase or have signed up for the newsletter.