Next JS Drizzle Supabase: A Comprehensive Guide

Next JS Drizzle Supabase is a powerful combination that can help you build fast and scalable applications. It's a great choice for developers who want to create dynamic and data-driven user interfaces.

Drizzle is an open-source, real-time data synchronization library that helps you manage data across multiple sources. It's designed to work seamlessly with Next.js and Supabase.

Supabase is a PostgreSQL-based database-as-a-service that provides a simple and intuitive API for storing and retrieving data. With Supabase, you can create a robust and scalable database that integrates well with your Next.js application.

By using Next JS Drizzle Supabase, you can create applications that are both fast and scalable, with real-time data synchronization and a robust database backend.

Clone the Drizzle repository in a folder of your choice and follow the steps to get started.

Open the drizzle-demo starter app in your code editor, which has a Next.js app router configured, along with TailwindCSS, DaisyUI, Heroicons, and date-fns added to package.json.

Install all packages to set up the environment.

You should have the starter app running on http://localhost:3000.

Start a local instance of PostgreSQL server, which is used in this demo with Drizzle.

Supabase is an open-source alternative to Firebase, providing a suite of APIs and services for building scalable and secure applications.

It's built on top of PostgreSQL, a powerful and flexible database management system.

Supabase has a simple and intuitive API, making it easy to integrate with your Next.js application.

With Supabase, you can manage users, authentication, and authorization with ease.

Supabase provides a built-in authentication system, allowing users to sign up and log in to your application securely.

The Supabase API is designed to be extensible, making it easy to add custom functionality to your application.

Supabase also provides a robust set of APIs for managing data, including CRUD (Create, Read, Update, Delete) operations.

By using Supabase with Next.js, you can create scalable and secure applications with ease.

Supabase is designed to work seamlessly with Next.js, allowing you to focus on building your application rather than worrying about the underlying infrastructure.

Drizzle ORM has a core package that's considered headless, supporting various SQL databases like PostgreSQL, MySQL, and SQLite, along with their different modes.

The core package is a foundation for Drizzle ORM, allowing it to connect to multiple databases. For example, the drizzle-orm/pg-core subpackage is used to connect to an externally running PostgreSQL database.

Drizzle also offers opt-in packages for different kinds of adapters, such as the drizzle-orm/node-postgres driver for running Postgres connections in a Node.js environment. This driver support exists for MySQL and SQLite as well.

Drizle ORM has a core package that serves as the headless part, supporting different SQL databases like PostgreSQL, MySQL, and SQLite, as well as their various modes.

The core package is considered the foundation of Drizle, and it's what allows you to connect to an externally running PostgreSQL database, for example.

You can choose from various opt-in packages that provide adapters for different databases and backend services built from them.

One such adapter is the drizzle-orm/pg-core subpackage, which is used to connect to an externally running PostgreSQL database.

Drizle also supports a range of drivers for different databases, including Postgres, MySQL, and SQLite, with options like PostgresJS, Neon, and Supabase available for Postgres.

You can find the full list of supported dialects and drivers on the Drizzle website.

Drizzle enhances HTTP request data validations in the frontend and backend with its support for Zod in the drizzle-zod package.

We can apply indexes and constraints to our column types, just like we do with indexes and constraints in SQL.

Indexes are applied using the index() and uniqueIndex() functions, and then chained with the on() function to specify the target table column.

Drizzle has complete SQL-like support for constraints, including default(), notNull(), and unique().

Data Management is a breeze with Supabase and Drizzle. Supabase's Data APIs are the easiest way to get started, offering REST, GraphQL, and Realtime APIs to suit your preferences.

To interact with your database, you can choose from REST, GraphQL, or Realtime APIs, each providing a unique way to manage your data.

Drizzle Queries take it to the next level with a convenient object-oriented SQL-like querying API. This API maps select queries to an object prototype represented by a schema entity, making it easier to query your data.

Here are the types of APIs offered by Supabase:

With Drizzle Queries, you can fetch related table data as nested objects, making it easier to navigate complex data relationships. For example, you can fetch all categories with their related posts in a single query.

Aggregating with GroupBy can be a powerful tool in data management. Drizzle makes it easy to do aggregations like count, sum, and average with its SQL GROUP BY implementation in the groupBy() method.

Drizzle's groupBy() method allows us to group aggregated data for further analysis. This is especially useful when dealing with large datasets.

We can use the groupBy() method to filter grouped data using having(). This helps us narrow down our results to specific criteria.

Joins are an essential part of data management, and Drizzle ORM has got you covered.

Drizzle supports joins for each individual SQL dialect, so you can use leftJoin(), rightJoin(), innerJoin(), and fullJoin() depending on your needs.

To perform joins in Drizzle, you need to map the relations explicitly in the schema file using the relations() function. Only then can entity relations get mapped to the db.query prototype.

Drizzle's joins allow you to fetch related table data as nested objects, making it easier to access and manipulate your data.

In Drizzle, joins are not automatically called at the database server, so you need to define the relations in your schema file to make them work.

Drizzle implements SQL mutations with intuitive methods like insert(), update(), and delete() on the db connection instance.

These CUD (Create, Update, Delete) actions are pretty straightforward and easy to use.

To update rows, Drizzle's update() method is called on the db with a table schema, and you can chain SQL-like set() and where() methods.

We can also update with returning() chained to return a value after update, making it a powerful tool for data manipulation.

SQL Filtering Where can be a bit of a challenge, especially when you're dealing with multiple conditions. Drizzle ORM's where() method is used to invoke the SQL WHERE clause.

Drizzle has helper functions for all SQL filtering operations, including equal, not equal, greater than, and more. You can find the list of filter operators and their docs here.

Combining multiple filters in Drizzle can be a pain, especially in queries that ask for nested relational data. The and() and or() operators can lead to long and nested code.

Using the where() method with filtering helpers like gte() can help you filter queries, but it's not always the most readable solution.

Inserting rows in Drizzle ORM is a straightforward process, thanks to its intuitive CUD (Create, Update, Delete) methods.

To insert a row, you need to pass the table schema to the insert() method and the item values to the values() method chained to it.

Drizzle's mutation methods reflect the syntax in SQL, making it easy to understand and work with.

You can insert multiple rows at once by passing an array of objects to the values() method.

If you need to return a response after insertion, you can use the returning() method.

For conflicting insert actions in PostgreSQL, you can configure the desired steps using the onConflictDoNothing() or onConflictDoUpdate() methods chained to the operation.

Updating rows with Drizzle's db.update() is a straightforward process. You call the update() method on the db instance with a table schema.

The set() method is used to specify the columns to update, and it's chained to the update() method. This is similar to how you would write an update query in SQL.

You can also chain the where() method to specify the conditions for which rows to update. This allows you to select a specific number of rows that you want to update.

In some cases, you might need to return a value after updating a row. Drizzle's update() method allows you to do this by chaining the returning() method.

By using the db.update() method, you can update rows in your database with ease and precision.

In Drizzle ORM, schemas and migrations are crucial for defining database tables and setting up necessary client connections.

Drizzle schemas encompass the backbone of defining database tables, and for our app, we have schemas for three tables: posts, categories, and tops.

To define our schemas, we need to create a schema folder and define our schemas inside it, referencing relationships between tables at the database level.

We use the schema file to define entity relations at the application level with relations(), as seen in the postsRelations file.

Zod schemas and types are also created for insert actions and select queries on posts.

All schemas are exported in the index.ts file inside the ./src/drizzle/schema/ directory.

Drizzle schemas can generate TypeScript entity types using zod.infer<>.

This feature is particularly useful for deriving type definitions from entity table definitions. For example, the type definitions for posts are derived by passing the table definition to the createInsertSchema() and createSelectSchema() functions.

Drizzle schemas allow you to define entity relations declaratively with relations(). This is necessary for navigating entity relations at the application level, in addition to referencing them at the database level.

The posts schema references categoryId to categories.id, which is then used to define the posts relations with categories in postsRelations.

Drizzle schemas also enable you to create Zod schemas and types for insert actions and select queries on posts.

To integrate Next.js with Supabase, you'll need to set up middleware to refresh expired Auth tokens and store them. This involves refreshing the Auth token with a call to supabase.auth.getUser, passing the refreshed token to Server Components through request.cookies.set, and passing it to the browser with response.cookies.set.

Create a middleware.js file at the project root and another one within the utils/supabase folder, which contains the logic for updating the session. This is used by the middleware.js file, a Next.js convention.

To protect pages, use supabase.auth.getUser() to revalidate the Auth token, as it sends a request to the Supabase Auth server every time. Never trust supabase.auth.getSession() inside server code, as it isn't guaranteed to revalidate the Auth token.

You can also add a matcher to the middleware to run only on routes that access Supabase, as shown in the documentation.

Here's a step-by-step guide to setting up middleware for Next.js with Supabase:

1. Create a middleware.js file at the project root

2. Create another middleware.js file within the utils/supabase folder

3. Use supabase.auth.getUser() to revalidate the Auth token

4. Pass the refreshed token to Server Components through request.cookies.set

5. Pass the refreshed token to the browser with response.cookies.set