Menu

Supabase Database Development Examples

For those unfamiliar with server-side development, even with a complete full-stack boilerplate, they might still be confused about database creation, updates, and other operations.

This chapter shows how to update existing data tables and create new ones when developing products with the Nexty boilerplate.

Good to know

  • As of Nexty v2.1.0 and nexty-flux-kontext v1.2.0, database sql files are unified under the supabase/migrations/ folder, and all update and creation operations can be completed through simple commands.
  • For upgrade instructions, see Database Operations Upgrade

Initialize Database

After cloning the Nexty repository, you'll see the data table definition files in the supabase/migrations/ folder:

migrations files

Now you need to connect to the Supabase database and initialize the database definitions in supabase/migrations/:

## Login to Supabase locally. Press Enter when prompted, copy the code from the webpage, paste it into the command line, and press Enter to complete login
npm run db:login
 
## Connect to database, you'll be asked to enter the database password. Enter the database password you set when creating your Supabase project. Note that the password won't be visible as you type - just press Enter after typing it.
npm run db:link
 
## Automatically initialize database and initial data
npm run db:update

Good to know

npm run db:login and npm run db:link must be executed during initialization. For future operations like adding fields or new tables, you don't need to execute these two commands unless prompted to re-login.

On Windows, if you encounter an "Invalid project ref format" error when running npm run db:update:

update error

Please open package.json and modify the db:update command:

  "scripts": {
    // "db:update": "npm run db:push && npm run db:gen-types" // Delete this line
    "db:update": "npm run db:push ; npm run db:gen-types" // Change to this
  }

You can now view all the initialized database tables in your Supabase dashboard:

supabase tables
supabase database overview

Adding Fields to Existing Tables

Let's say you want to track user registration sources based on URL parameters, so the users table needs a new field referral.

First, create a new migration file through the command:

## npm run db:new-migration <new_feature_name>
 
npm run db:new-migration users_add_referral

After executing the command, a new empty file will be created in supabase/migrations with the filename structure: <timestamp>_<new_feature_name>.sql. For example, the file created in this example would be named 20250802164211_users_add_referral.sql.

Start vibe coding by selecting the existing users table sql file and the newly created empty file in the Cursor dialog box, and tell the AI: I need to add a field to record user source in the users table, the field name should be referral, please write the table update statement into this new file.

After the AI generates the code, the 20250802164211_users_add_referral.sql file will contain the SQL statements:

supabase database overview

Push the new sql definition to Supabase through the command:

npm run db:update

You can now verify the changes by checking the users table in your Supabase dashboard - the referral field should be successfully added.

This demonstrates how to add table fields. You can use the same process for other schema changes like deleting fields or updating field definitions.

Adding New Tables

First, create a new migration file through the command:

## npm run db:new-migration <new_table_name>
 
npm run db:new-migration add_orders_tables

After executing the command, a new empty file will be created in supabase/migrations with the filename structure: <timestamp>_<new_table_name>.sql. Next, open the newly created file in Cursor and prompt the AI to generate the table creation SQL.

Push the new sql definition to Supabase through the command:

npm run db:update

This completes the database update for creating new tables.

Syncing Nexty Database Updates

If you need to sync Nexty's database updates and features, you can refer to the corresponding changelog for synchronization.

Database updates only require two steps:

  • Copy the new sql files to your project's supabase/migrations
  • Execute npm run db:update

New Feature Development

For new feature development, the vibe coding workflow is:

  1. Discuss the feature design with AI through multiple iterations to clarify requirements, then document them in Notion
  2. Provide the confirmed requirements to AI and request a comprehensive requirements document that includes both feature and database design
  3. Send the requirements document to AI, requesting complete sql statements according to requirements, including field definitions, necessary indexes, RLS policies, RPC functions, and triggers
  4. Create a new database migration file through npm run db:new-migration <new_feature> command, and copy the sql provided by AI to the new file
  5. Push to Supabase and update types through npm run db:update command
  6. Have AI start working based on the requirements document and sql files

If you're a programming beginner or a developer unfamiliar with Supabase, RLS policies, RPC functions, and triggers might seem abstract to you. However, if you can spend some time mastering this knowledge, future feature development will become much easier, because Supabase's unique design allows you to handle most business logic at the database level, reducing complex interactions between frontend and backend while ensuring data security and consistency. Once you master these core concepts, you'll be able to quickly build feature-complete, secure, and reliable applications.