Tables

Overview

This documentation provides a comprehensive guide on creating and managing Supabase tables both locally and remotely. It covers step-by-step instructions for setting up tables, SQL examples, and migrating tables to a production. The guide ensures seamless integration with Supabase's database management tools.

What is a Table?

In Supabase, a table is a structured set of data organized into rows and columns, similar to a traditional relational database. Each row represents a record, and each column defines a specific attribute of the data. Tables in Supabase are stored in PostgreSQL, allowing you to perform SQL queries to manage and interact with your data.

How to Create a Table in Supabase

You can create tables in Supabase both locally (in a development environment) and remotely (on Supabase's hosted platform). Below are the steps for each approach.

1. Creating a Table Locally

To create a table in a local Supabase setup, follow these steps:

1. Ensure that you have the Supabase CLI installed. Initialize and start Supabase locally. Refer Supabase setup for more details.

2. Create a New Table: Once Supabase is running locally, you can create tables using SQL queries. Open the Supabase Studio.

   Example Query:

    create table "public"."profiles" (
        "id" uuid not null default gen_random_uuid(),
        "created_at" timestamp with time zone not null default now(),
        "name" text,
        "email" text not null,
    )

   This query creates a profiles table with four columns: id, name, email, and created_at. The id column serves as the primary key.

2. Creating a Table Remotely in Supabase

To create tables on a remote Supabase project:

1. Log in to Supabase: Go to Supabase Dashboard and log in to your account.

2. Access Your Project: Choose your project from the dashboard.

3. Navigate to Table Editor: From the left sidebar, go to the Table Editor.

4. Create a New Table: Click on the Create Table button and define the structure of your table by adding columns, setting data types, and defining constraints like primary keys and unique constraints.

   You can create the same profiles table using the graphical interface by adding the following fields:

   • id: Integer, primary key
   • name: String, required
   • email: String, required, unique
   • created_at: Timestamp with timezone, default value as current timestamp

   OR using sql:

    create table "public"."profiles" (
        "id" uuid not null default gen_random_uuid(),
        "created_at" timestamp with time zone not null default now(),
        "name" text,
        "email" text not null,
    )

3. Generating Types Locally and Remotely for Tables

Supabase allows you to generate TypeScript types based on your table schema, which helps ensure type safety in your Next.js application.

Local Type Generation

1. Generate Types Locally: You can generate types from your local Supabase database by running the following command:

   ./generate-types.sh

   This will create a database.types.ts file in your types folder, containing TypeScript types for your tables.

   Permission Issue

   If you encounter an issue with the ./generate-types.sh command, use the following command to grant the necessary permissions: chmod ug+x generate-types.sh command to give it proper access.

Remote Type Generation

1. Generate Remote Types: For remote tables, Supabase also provides the ability to generate types directly from your hosted project. Run this command after setting up your remote Supabase project:

   supabase gen types typescript --project-id YOUR_PROJECT_ID > ./types/database.types.ts

   Replace YOUR_PROJECT_ID with your actual Supabase project ID.

2. Using the Generated Types: Once the types are generated, you can import them into your Next.js application to ensure type safety when working with your Supabase tables:

   import { Database } from '../types/supabase';
    
   const { data, error } = await supabase
     .from<Database['public']['Tables']['profiles']['Row']>('profiles')
     .select('*');

This ensures that you have strongly typed interactions with your tables.

4. Creating Tables Using Supabase SQL Editor

For both local and remote projects, Supabase provides an SQL editor where you can run raw SQL queries to create tables. This allows you to copy your local SQL creation scripts and run them directly in a remote environment.

5. Migrating Tables Locally in a Next.js Application

When working with a Next.js application, you may want to migrate your locally created tables to your remote Supabase instance.

Step-by-Step Guide:

1. Export SQL Migration: If you’ve created tables locally using Supabase's PostgreSQL database, you can export your table creation SQL statements using the Supabase's built-in migration tools.

   npm run supabase:create-public-migration feat-adding-profile-table

   This generates an SQL file containing all table creation scripts.

2. Next.js Integration: Inside your application, you can use the Supabase client to interact with these tables once they've been migrated to the remote Supabase database. Initialize the Supabase client in your project:

   import { createSupabaseServerClient } from "@/lib/supabase/server";
    
   const supabase = createSupabaseServerClient();

   Afterward, you can perform operations like reading or writing data to the tables you’ve created:

   const { data, error } = await supabase
     .from('profiles')
     .select('*')