Creating a new Supabase table

When you are 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 and use the new table in your Next.js app.

Creating a table

Let us say, you want to create a table to store a list of todos. You can create a table in Supabase using the SQL editor.

1. Go to the SQL editor in your Supabase project.
2. Run the following SQL query to create a table named todos:

    create table todos (
      id bigint generated by default as identity primary key,
      title text,
      is_completed boolean default false
    );

3. You can also create a table using the Supabase UI. Go to the Tables tab in your Supabase project and click on the New table button and create the columns in the GUI editor.

Generate types for the table

Nextbase includes a CLI command to generate TypeScript types for your Supabase tables. You can run the following command to generate types for the newly modified database.

pnpm generate:types:local

This will regenerate the types for your local database. And the types are located at @/src/lib/database.types.ts

Why do we need to generate types?

Types are useful for type checking and auto-completion in your IDE. You can use the generated types to type check your database queries and also use them to auto-complete the column names in your queries. For example, if you want to query the todos table, you can use the generated types to type check the query and also auto-complete the column names.

import { supabaseAdminClient } from '@/supabase-clients/admin/supabaseAdminClient';
 
export async function getAllTodos() {
  const { data, error } = await supabaseAdminClient.from('todos').select('*');
 
  if (error) {
    throw error;
  }
  return data;
}

The above code returns data which will be an array of Todo objects. And the types will be generated within the @/src/lib/database.types.ts file.