Tables
This guide will help you to create and manage Supabase tables both locally and remotely, with examples and steps for migrating 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:
-
Ensure that you have the Supabase CLI installed. Initialize and start Supabase locally. Refer Supabase setup for more details.
-
Create a New Table: Once Supabase is running locally, you can create tables using SQL queries. Open the Supabase Studio (http://localhost:54323)
Example Query:
This query creates a
profiles
table with four columns:id
,name
,email
, andcreated_at
. Theid
column serves as the primary key.
2. Creating a Table Remotely in Supabase
To create tables on a remote Supabase project:
-
Log in to Supabase: Go to Supabase Dashboard and log in to your account.
-
Access Your Project: Choose your project from the dashboard.
-
Navigate to Table Editor: From the left sidebar, go to the Table Editor.
-
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 keyname
: String, requiredemail
: String, required, uniquecreated_at
: Timestamp with timezone, default value as current timestamp
OR using sql:
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
-
Generate Types Locally: You can generate types from your local Supabase database by running the following command:
This will create a
database.types.ts
file in yourtypes
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
-
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:
Replace
YOUR_PROJECT_ID
with your actual Supabase project ID. -
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:
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:
-
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.
This generates an SQL file containing all table creation scripts.
-
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:
Afterward, you can perform operations like reading or writing data to the tables you’ve created: