Let's Build a Supabase + Drizzle-orm + Express backend

Introduction

In this tutorial, we will walk through the process of building a backend using Supabase, Drizzle ORM, and Express. This combination allows for a robust and efficient backend solution, leveraging the power of PostgreSQL through Supabase along with type-safe database interactions using Drizzle ORM. By the end, you'll have a functional backend setup that you can customize further.

Step 1: Project Setup

• Start by creating a new directory for your project.
• Initialize a new Node.js project:

  npm init -y
  

• Install the necessary dependencies:

  npm install express supabase drizzle-orm dotenv
  

Step 2: Update the .env File

• Create a .env file in your project root directory.
• Add your Supabase credentials to the .env file:

  SUPABASE_URL=your_supabase_url
  SUPABASE_KEY=your_supabase_key
  

• Ensure you replace your_supabase_url and your_supabase_key with the actual values from your Supabase project.

Step 3: Introspect the Database

• Use the Supabase CLI or dashboard to introspect your existing database schema.
• This step will help you understand the structure of your database and what tables you will be working with.

Step 4: Reset the Database for New Migrations

• To start fresh, you may want to reset your database. This can typically be done via the Supabase dashboard or CLI.
• Ensure you back up any existing data if necessary.

Step 5: Build schema.ts

• Create a new file called schema.ts in your project.
• Define your database schema using Drizzle ORM. An example structure:

  import { pgTable, serial, varchar } from 'drizzle-orm/pg-core';
  
  export const users = pgTable('users', {
    id: serial('id').primaryKey(),
    name: varchar('name', { length: 255 }),
  });
  

• This example creates a simple users table with an id and name.

Step 6: Generate Migrations from schema.ts

• Use the Drizzle ORM CLI to generate migrations based on your schema.ts file.
• Run the appropriate command:

  drizzle-kit generate
  

Step 7: Run Migrations on the Database

• Apply the generated migrations to your Supabase database:

  drizzle-kit migrate
  

• This will update your database schema according to the definitions in schema.ts.

Step 8: Type Inference from Schema

• Leverage TypeScript's type inference capabilities to ensure type safety in your database interactions.
• For example, you can use the defined types in your queries:

  import { users } from './schema';
  
  const user = await db.select().from(users).where(users.id.equals(1));
  

Step 9: Build Queries

• Construct queries using Drizzle ORM to interact with your database efficiently.
• Example query to get all users:

  const allUsers = await db.select().from(users);
  

Step 10: Update Relations in Schema

• If you have relational data, update your schema.ts to define relationships.
• Example of adding a relation:

  const posts = pgTable('posts', {
    id: serial('id').primaryKey(),
    userId: serial('userId').references(() => users.id),
  });
  

Step 11: Build Relational Queries

• Use the defined relationships to create complex queries.
• Example to fetch a user with their posts:

  const userWithPosts = await db
    .select()
    .from(users)
    .innerJoin(posts)
    .where(users.id.equals(1));
  

Conclusion

You have now set up a basic backend using Supabase, Drizzle ORM, and Express. This tutorial covered the essential steps from project setup to building queries. You can expand upon this foundation by adding more features, improving error handling, or integrating additional services. Explore the GitHub repository linked in the video for more advanced examples and code snippets. Happy coding!