Creating a new Supabase table

When creating a new feature in your app, you might need to create a new table in your Supabase database. In this guide, we'll learn how to create a new table in Supabase, manage migrations, and use the new table in your Next.js app.

1. Postgres Queries

Let's say you want to create a table to store a list of todos. Here's the PostgreSQL query to create a todos table:

CREATE TABLE todos (
  id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  title TEXT,
  is_completed BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::text, NOW()) NOT NULL
);

2. Approaches to Creating Tables

There are two main approaches to creating tables in Supabase:

1. Direct SQL Execution: Run the SQL query directly in the Supabase SQL editor (localhost:54323 for local development or in your production Supabase database).
2. Using Migrations: Create and manage database changes through migrations, which is the recommended approach for better tracking and version control.

While the first approach is quick, it's not ideal for maintaining and tracking changes over time. Let's focus on the migration approach.

3. Managing Migrations

Nextbase provides commands to create and manage migrations:

Creating a New Migration

To create a new migration:

pnpm supabase migration new create_todos_table

This command will create a new timestamped SQL file in the supabase/migrations directory. Open this file and add your SQL query:

-- This is a migration file (e.g., 20240101000000_create_todos_table.sql)
CREATE TABLE todos (
  id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  title TEXT,
  is_completed BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::text, NOW()) NOT NULL
);

Applying Migrations Locally

To apply new migrations to your local database without losing data:

pnpm supabase migration up

Resetting Local Database

If you need to reset your local database and apply all migrations from scratch:

pnpm supabase db reset

Pushing Migrations to Production

To push your migrations to the production Supabase database:

pnpm supabase db push

This command will apply any new migrations to your production database.

4. Generating Types Locally

After creating or modifying tables, you should regenerate TypeScript types for your Supabase tables. Run the following command:

pnpm generate:types:local

This command regenerates the types for your local database. The generated types are located at @/src/lib/database.types.ts.

5. Using Generated Types

Types are crucial for type checking and auto-completion in your IDE. You can use the generated types to type-check your database queries and auto-complete column names.

Example usage:

// The Database type is generated from your database schema by supabase.
import { Database } from "@/lib/database.types";
import { createClient } from "@supabase/supabase-js";

// A simple wrapper around the Supabase client that uses the service role key
// Since Database is passed as a generic, the client will be typed correctly.
export const supabaseAdminClient = createClient<Database>(
  process.env.NEXT_PUBLIC_SUPABASE_URL,
  process.env.SUPABASE_SERVICE_ROLE_KEY,
);

export async function getAllTodos() {
  const { data, error } = await supabaseAdminClient.from("todos").select("*");
  /**
   * The `data` will be typed as `Database['public']['Tables']['todos']['Row'][]`.
   * It will have the following shape:
   * {
   *   id: number;
   *   title: string;
   *   is_completed: boolean;
   *   created_at: string;
   * }[]
   */
  if (error) {
    throw error;
  }
  return data;
}

In this example, data will be an array of Todo objects, with types generated from your database schema.

Conclusion

By following this workflow, you can create new tables, manage database changes through migrations, and ensure type safety in your Nextbase project. This approach provides better version control, easier collaboration, and a more maintainable codebase.