Forem: poshiya parth s

Supabase Managing database migrations across multiple environments (Local, Staging, Production)

poshiya parth s — Sat, 04 Oct 2025 13:12:31 +0000

Quick Reference Table

#	Operation	Makefile Command	Direct Command (Shortened)	Safety	Confirmation
1	Copy staging → local	`make local-import-staging`	`npx supabase db reset --local` + dumps	✅	None
2	Copy production → local	`make local-import-production`	`npx supabase db reset --local` + dumps	✅	None
3	Create new migration	`make local-create-migration`	`npx supabase db diff -f name`	✅	None
4	Diff local to file	`make local-get-migration`	`npx supabase db diff -f name --local`	✅	None
5	Apply migrations locally	`make local-apply-migration`	`npx supabase db push --local`	✅	None
6	Reset local DB	`make local-reset`	`npx supabase db reset --local`	⚠️	`yes`
7	CLI authentication	`make supabase-login`	`npx supabase login`	✅	None
8	Diff staging to file	`make staging-get-migration`	`npx supabase db diff -f name --db-url "$STAGING_DB_URL"`	✅	None
9	Deploy to staging	`make staging-apply-migration`	`npx supabase db push --db-url "$STAGING_DB_URL"`	⚠️	`yes`
10	Reset staging DB	`make staging-reset`	`psql "$STAGING_DB_URL" -c "DROP SCHEMA..."`	🔶	`RESET-STAGING`
11	Diff production to file	`make prod-get-migration`	`npx supabase db diff -f name --db-url "$PROD_DB_URL"`	✅	None
12	Deploy to production	`make prod-apply-migration`	`npx supabase db push --db-url "$PROD_DB_URL"`	🔴	`APPLY-TO-PRODUCTION`
13	Promote staging → prod	`make prod-apply-staging-migration`	`npx supabase db dump` + `psql "$PROD_DB_URL"`	🔴	`APPLY-TO-PRODUCTION`
14	Reset production DB	`make prod-reset`	`psql "$PROD_DB_URL" -c "DROP SCHEMA..."`	🔴🔴	`DELETE-ALL-PRODUCTION-DATA`
15	Verify tools installed	`make check-deps`	`node --version && npx supabase --version`	✅	None
16	Remove dump files	`make clean`	`rm -f *.sql`	✅	None
17	Show DB URLs	`make check-env`	`echo $STAGING_DB_URL`	✅	None
18	Clear migration history	`make delete-migrations`	`psql -c "TRUNCATE supabase_migrations..."`	⚠️	`yes`

Introduction

Managing database migrations across multiple environments (Local, Staging, Production) can be challenging. This comprehensive guide shows you two approaches: using a Makefile-based workflow system for automation and safety, or running direct commands for more control.

Choose your approach:

🔧 With Makefile: Automated, safe, with built-in confirmations (requires setup files)
⚡ Direct Commands: Manual, flexible, no setup required (jump to the "Direct Commands" section)

Why Use a Migration Management System?

Traditional database migration management often leads to:

🔴 Accidental production changes
🔴 Manual command errors
🔴 Inconsistent environments
🔴 Lost migration history

A structured approach provides:

✅ Safety confirmations for destructive operations
✅ Automated workflows
✅ Clear environment separation
✅ Version-controlled migrations

Prerequisites

Before getting started, ensure you have:

Node.js and npm installed
Supabase CLI installed:

   npm install supabase --save-dev

Docker Desktop running (required for local Supabase)
PostgreSQL client tools (psql) - optional but recommended

Initialize your project:

npx supabase init
npx supabase start

If using the Makefile approach, additionally run:

chmod +x scripts/*.sh  # Make scripts executable

Two Approaches to Choose From

Approach 1: Using Makefile (Requires Setup Files)

If you have access to the Makefile and helper scripts, this approach provides automation and safety features.

Pros:

✅ Simple, memorable commands
✅ Built-in safety confirmations
✅ Automated workflows
✅ Less chance of errors

Cons:

❌ Requires Makefile and script files
❌ Less flexibility for custom operations

Continue reading the sections below for Makefile commands.

Approach 2: Direct Commands (No Setup Required)

If you don't have the Makefile or prefer manual control, you can run Supabase CLI and psql commands directly.

Pros:

✅ No setup files needed
✅ Full control over operations
✅ Easy to integrate into CI/CD
✅ Works anywhere

Cons:

❌ More verbose commands
❌ Manual safety checks
❌ Higher chance of typos

Skip to the "Direct Commands (Without Makefile)" section below.

Project Structure (For Makefile Approach)

your-project/
├── Makefile                           # Main command interface
├── .env                               # Environment variables (gitignored)
├── scripts/
│   ├── apply-to-local.sh             # Apply dumps to local
│   ├── dump-staging-migrations.sh    # Export staging schema
│   ├── apply-migrations-to-prod.sh   # Push to production
│   └── reset-remote-db.sh            # Reset remote databases
└── supabase/
    └── migrations/                    # Migration files

Note: If you don't have these files, use the Direct Commands approach instead.

Environment Setup

Create a .env file in your project root:

# Local Environment (default)
LOCAL_DB_URL=postgresql://postgres:postgres@localhost:54322/postgres

# Staging Environment
STAGING_DB_URL=postgresql://postgres:YOUR_PASSWORD@db.YOUR_STAGING_REF.supabase.co:5432/postgres
STAGING_PROJECT_REF=your-staging-ref

# Production Environment
PROD_DB_URL=postgresql://postgres.YOUR_PROD_REF:YOUR_PASSWORD@aws-1-ap-south-1.pooler.supabase.com:6543/postgres
PROD_PROJECT_REF=your-production-ref

⚠️ Security Note: Never commit .env to version control! Add it to .gitignore.

Complete Command Reference

🖥️ Local Environment Commands

1. Import Staging to Local

With Makefile:

make local-import-staging

Direct Command:

# Set staging URL
STAGING_DB_URL="postgresql://postgres:YOUR_PASSWORD@db.YOUR_STAGING_REF.supabase.co:5432/postgres"

# Reset and import
npx supabase db reset --local
npx supabase db dump --db-url "$STAGING_DB_URL" --schema public > staging_schema.sql
npx supabase db dump --db-url "$STAGING_DB_URL" --data-only -f staging_data.sql
psql "postgresql://postgres:postgres@localhost:54322/postgres" < staging_schema.sql
psql "postgresql://postgres:postgres@localhost:54322/postgres" < staging_data.sql

What it does:

Resets local database (drops existing data)
Dumps staging schema and data
Applies both to local environment
Perfect for testing staging data locally

Use case: You want to debug an issue reported in staging with real data.

2. Import Production to Local

With Makefile:

make local-import-production

Direct Command:

# Set production URL
PROD_DB_URL="postgresql://postgres.YOUR_PROD_REF:YOUR_PASSWORD@aws-1-ap-south-1.pooler.supabase.com:6543/postgres"

# Reset and import
npx supabase db reset --local
npx supabase db dump --db-url "$PROD_DB_URL" --schema public > production_schema.sql
npx supabase db dump --db-url "$PROD_DB_URL" --data-only -f production_data.sql
psql "postgresql://postgres:postgres@localhost:54322/postgres" < production_schema.sql
psql "postgresql://postgres:postgres@localhost:54322/postgres" < production_data.sql

What it does:

Resets local database
Dumps production schema and data
Applies both to local environment

Use case: Investigating a production bug or testing a hotfix with production data.

⚠️ Warning: Production data may contain sensitive information. Handle with care!

3. Create New Migration

With Makefile:

make local-create-migration
# Enter migration name when prompted

Direct Command:

npx supabase db diff -f add_user_profiles_table

What it does:

Creates a new timestamped migration file in supabase/migrations/
Captures your local database changes

Example workflow:

# Make changes to your local database via Supabase Studio
# Then capture them:
npx supabase db diff -f add_user_profiles
# Creates: 20251004123045_add_user_profiles.sql

4. Get Migration from Local Changes

With Makefile:

make local-get-migration
# Enter migration name when prompted

Direct Command:

npx supabase db diff -f capture_all_changes --local

What it does:

Diffs your local database against the migration history
Generates a new migration file with the differences

Use case: You've made multiple changes and want to capture them all as one migration.

5. Apply Local Migrations

With Makefile:

make local-apply-migration

Direct Command:

npx supabase db push --local

What it does:

Applies all pending migrations to local database

Use case: After pulling new migrations from Git, sync your local database.

6. Reset Local Database

With Makefile:

make local-reset
# Type 'yes' to confirm

Direct Command:

npx supabase db reset --local

What it does:

Drops all local data
Reapplies all migrations from scratch

Use case: Clean slate for testing or recovering from a corrupted local state.

7. Supabase Login

With Makefile:

make supabase-login

Direct Command:

npx supabase login

What it does:

Authenticates with Supabase CLI
Required before running remote operations

🔶 Staging Environment Commands

8. Get Migration from Staging

With Makefile:

make staging-get-migration
# Enter migration name when prompted

Direct Command:

STAGING_DB_URL="postgresql://postgres:YOUR_PASSWORD@db.YOUR_STAGING_REF.supabase.co:5432/postgres"
npx supabase db diff -f migration_from_staging --db-url "$STAGING_DB_URL"

What it does:

Diffs staging database against local migrations
Creates a migration file capturing staging-specific changes

Use case: Someone made manual changes in staging that need to be migrated to code.

9. Apply Migration to Staging

With Makefile:

make staging-apply-migration
# Type 'yes' to confirm

Direct Command:

STAGING_DB_URL="postgresql://postgres:YOUR_PASSWORD@db.YOUR_STAGING_REF.supabase.co:5432/postgres"
npx supabase db push --db-url "$STAGING_DB_URL"

What it does:

Pushes local migrations to staging

Use case: Deploying tested changes from local to staging for QA testing.

Safety level: ⚠️ Medium - affects staging environment

10. Reset Staging Database

With Makefile:

make staging-reset
# Type 'RESET-STAGING' to confirm

Direct Command:

STAGING_DB_URL="postgresql://postgres:YOUR_PASSWORD@db.YOUR_STAGING_REF.supabase.co:5432/postgres"

# Execute reset
psql "$STAGING_DB_URL" -c "DROP SCHEMA IF EXISTS public CASCADE; CREATE SCHEMA public; GRANT ALL ON SCHEMA public TO postgres; GRANT ALL ON SCHEMA public TO public;"

What it does:

Drops public schema
Recreates empty schema

Use case: Starting fresh after testing or clearing corrupted staging data.

Safety level: 🔶 High - destroys staging data

🔴 Production Environment Commands

11. Get Migration from Production

With Makefile:

make prod-get-migration
# Enter migration name when prompted

Direct Command:

PROD_DB_URL="postgresql://postgres.YOUR_PROD_REF:YOUR_PASSWORD@aws-1-ap-south-1.pooler.supabase.com:6543/postgres"
npx supabase db diff -f migration_from_production --db-url "$PROD_DB_URL"

What it does:

Diffs production database against local migrations
Creates migration file capturing production state

Use case: Documenting manual production hotfixes in code.

12. Apply Migration to Production

With Makefile:

make prod-apply-migration
# Type 'APPLY-TO-PRODUCTION' to confirm

Direct Command:

PROD_DB_URL="postgresql://postgres.YOUR_PROD_REF:YOUR_PASSWORD@aws-1-ap-south-1.pooler.supabase.com:6543/postgres"
npx supabase db push --db-url "$PROD_DB_URL"

What it does:

Pushes local migrations to production

Use case: Final deployment after successful staging tests.

Safety level: 🔴 Critical - affects production

13. Apply Staging Migration to Production

With Makefile:

make prod-apply-staging-migration
# Type 'APPLY-TO-PRODUCTION' to confirm

Direct Command:

STAGING_DB_URL="postgresql://postgres:YOUR_PASSWORD@db.YOUR_STAGING_REF.supabase.co:5432/postgres"
PROD_DB_URL="postgresql://postgres.YOUR_PROD_REF:YOUR_PASSWORD@aws-1-ap-south-1.pooler.supabase.com:6543/postgres"

# Dump staging
npx supabase db dump --db-url "$STAGING_DB_URL" --schema public > staging_to_prod_$(date +%Y%m%d_%H%M%S).sql

# Review the file first!
cat staging_to_prod_*.sql

# Apply to production
psql "$PROD_DB_URL" < staging_to_prod_*.sql

What it does:

Dumps current staging schema
Creates timestamped migration file
Applies staging state to production

Use case: Promoting a tested staging environment to production.

Safety level: 🔴 Critical - affects production

14. Reset Production Database

With Makefile:

make prod-reset
# Type 'DELETE-ALL-PRODUCTION-DATA' to confirm

Direct Command:

PROD_DB_URL="postgresql://postgres.YOUR_PROD_REF:YOUR_PASSWORD@aws-1-ap-south-1.pooler.supabase.com:6543/postgres"

# ⚠️⚠️⚠️ EXTREMELY DANGEROUS ⚠️⚠️⚠️
psql "$PROD_DB_URL" -c "DROP SCHEMA IF EXISTS public CASCADE; CREATE SCHEMA public; GRANT ALL ON SCHEMA public TO postgres; GRANT ALL ON SCHEMA public TO public;"

What it does:

Drops public schema in production

Use case: Rare! Only for complete environment resets or catastrophic recovery.

Safety level: 🔴🔴 Extremely Dangerous - destroys all production data

🔧 Utility Commands

15. Check Dependencies

With Makefile:

make check-deps

Direct Command:

node --version
npx --version
npx supabase --version
psql --version

What it does:

Verifies Node.js, npm, and Supabase CLI are installed
Confirms all required tools are available

16. Clean Dump Files

With Makefile:

make clean

Direct Command:

rm -f staging_schema.sql staging_data.sql production_schema.sql production_data.sql staging_migration_*.sql

What it does:

Removes generated SQL dump files

17. Check Environment Configuration

With Makefile:

make check-env

Direct Command:

echo "Local: postgresql://postgres:postgres@localhost:54322/postgres"
echo "Staging: $STAGING_DB_URL"
echo "Production: $PROD_DB_URL"

What it does:

Displays configured database URLs
Helps verify environment setup

18. Delete Migration Records

With Makefile:

make delete-migrations
# Type 'yes' to confirm

Direct Command:

# View current migrations
psql "postgresql://postgres:postgres@localhost:54322/postgres" -c "SELECT * FROM supabase_migrations.schema_migrations;"

# Delete all migration records
psql "postgresql://postgres:postgres@localhost:54322/postgres" -c "TRUNCATE supabase_migrations.schema_migrations;"

# Or delete specific migration
psql "postgresql://postgres:postgres@localhost:54322/postgres" -c "DELETE FROM supabase_migrations.schema_migrations WHERE version = '20251004123045';"

What it does:

Clears migration history from database

Use case: When migration history is corrupted or needs cleanup.

Real-World Workflows

Workflow 1: Feature Development

With Makefile:

# 1. Start with fresh staging data
make local-import-staging

# 2. Develop your feature locally
# Make database changes via Supabase Studio or SQL

# 3. Create migration
make local-create-migration
# Enter: add_comments_feature

# 4. Test locally
npm run dev

# 5. Deploy to staging
make staging-apply-migration

# 6. After QA approval, deploy to production
make prod-apply-migration

Direct Commands:

# 1. Import staging data
STAGING_DB_URL="postgresql://postgres:YOUR_PASSWORD@db.YOUR_STAGING_REF.supabase.co:5432/postgres"
npx supabase db reset --local
npx supabase db dump --db-url "$STAGING_DB_URL" --schema public > staging_schema.sql
npx supabase db dump --db-url "$STAGING_DB_URL" --data-only -f staging_data.sql
psql "postgresql://postgres:postgres@localhost:54322/postgres" < staging_schema.sql
psql "postgresql://postgres:postgres@localhost:54322/postgres" < staging_data.sql

# 2. Develop feature (make changes)

# 3. Create migration
npx supabase db diff -f add_comments_feature

# 4. Test locally
npm run dev

# 5. Deploy to staging
npx supabase db push --db-url "$STAGING_DB_URL"

# 6. Deploy to production
PROD_DB_URL="postgresql://postgres.YOUR_PROD_REF:YOUR_PASSWORD@aws-1-ap-south-1.pooler.supabase.com:6543/postgres"
npx supabase db push --db-url "$PROD_DB_URL"

Workflow 2: Hotfix in Production

With Makefile:

# 1. Import production data locally
make local-import-production

# 2. Reproduce and fix the issue

# 3. Create migration for the fix
make local-create-migration
# Enter: hotfix_user_permissions

# 4. Test thoroughly locally

# 5. Apply directly to production (after approval)
make prod-apply-migration

Direct Commands:

# 1. Import production data
PROD_DB_URL="postgresql://postgres.YOUR_PROD_REF:YOUR_PASSWORD@aws-1-ap-south-1.pooler.supabase.com:6543/postgres"
npx supabase db reset --local
npx supabase db dump --db-url "$PROD_DB_URL" --schema public > prod_schema.sql
npx supabase db dump --db-url "$PROD_DB_URL" --data-only -f prod_data.sql
psql "postgresql://postgres:postgres@localhost:54322/postgres" < prod_schema.sql
psql "postgresql://postgres:postgres@localhost:54322/postgres" < prod_data.sql

# 2. Fix the issue

# 3. Create migration
npx supabase db diff -f hotfix_user_permissions

# 4. Test thoroughly

# 5. Apply to production
npx supabase db push --db-url "$PROD_DB_URL"

Workflow 3: Environment Synchronization

With Makefile:

# Sync staging to production
make prod-apply-staging-migration

# Or sync production back to staging
make staging-reset
make prod-get-migration
make staging-apply-migration

Direct Commands:

# Sync staging to production
STAGING_DB_URL="postgresql://postgres:YOUR_PASSWORD@db.YOUR_STAGING_REF.supabase.co:5432/postgres"
PROD_DB_URL="postgresql://postgres.YOUR_PROD_REF:YOUR_PASSWORD@aws-1-ap-south-1.pooler.supabase.com:6543/postgres"

# Dump staging
npx supabase db dump --db-url "$STAGING_DB_URL" --schema public > staging_to_prod.sql

# Apply to production
psql "$PROD_DB_URL" < staging_to_prod.sql

# Or sync production to staging
npx supabase db dump --db-url "$PROD_DB_URL" --schema public > prod_to_staging.sql
psql "$STAGING_DB_URL" < prod_to_staging.sql

Workflow 4: Debugging with Real Data

With Makefile:

# Import production data for investigation
make local-import-production

# Investigate the issue
# No changes needed? Just clean up
make local-reset

Direct Commands:

# Import production data
PROD_DB_URL="postgresql://postgres.YOUR_PROD_REF:YOUR_PASSWORD@aws-1-ap-south-1.pooler.supabase.com:6543/postgres"
npx supabase db reset --local
npx supabase db dump --db-url "$PROD_DB_URL" --schema public > prod_schema.sql
npx supabase db dump --db-url "$PROD_DB_URL" --data-only -f prod_data.sql
psql "postgresql://postgres:postgres@localhost:54322/postgres" < prod_schema.sql
psql "postgresql://postgres:postgres@localhost:54322/postgres" < prod_data.sql

# Investigate

# Clean up
npx supabase db reset --local

Script Deep Dive (For Makefile Approach)

Note: This section is only relevant if you're using the Makefile approach with helper scripts. If you're using direct commands, skip this section.

apply-to-local.sh

This script intelligently applies SQL files to your local database:

Features:

Accepts multiple SQL files as arguments
Defaults to staging_schema.sql and staging_data.sql
Prefers local psql if available
Falls back to Docker exec if psql isn't installed
Applies files in order with error handling

Usage examples:

# Apply default files
./scripts/apply-to-local.sh

# Apply specific files in order
./scripts/apply-to-local.sh schema.sql data.sql

# Apply single migration
./scripts/apply-to-local.sh supabase/migrations/20251004_my_migration.sql

dump-staging-migrations.sh

Exports staging database schema to a timestamped file:

What it captures:

Public schema structure
Tables, columns, constraints
Indexes and foreign keys
Functions and triggers

Output example:

staging_migration_20251004_143052.sql

apply-migrations-to-prod.sh

Safely applies staging migrations to production:

Safety features:

Finds latest staging migration file
Displays file information
Requires explicit confirmation
Uses PostgreSQL transaction handling
Reports success/failure clearly

reset-remote-db.sh

Resets remote databases with extreme caution:

How it works:

Accepts DB URL and environment name
Drops public schema entirely
Recreates empty public schema
Restores default permissions
Uses Node.js pg client for reliability

Safety Features Explained

Confirmation Levels

Safety Level	Confirmation Required	Use Case
✅ Safe	None	Read-only or local operations
⚠️ Medium	Type `yes`	Staging operations
🔶 High	Type `RESET-STAGING`	Destructive staging operations
🔴 Critical	Type `APPLY-TO-PRODUCTION`	Production modifications
🔴🔴 Extreme	Type `DELETE-ALL-PRODUCTION-DATA`	Production destruction

Best Practices

Always Test Locally First

   make local-apply-migration  # Test here first
   make staging-apply-migration  # Then here
   make prod-apply-migration  # Finally here

Review Migrations Before Applying

   # Check what will be applied
   cat supabase/migrations/20251004_my_migration.sql

Backup Before Major Changes

   # Makefile creates backups automatically during imports
   # But you can also manually dump production
   make prod-get-migration

Version Control Everything

   git add supabase/migrations/
   git commit -m "feat: add user profiles table"

Document Database Changes
- Use descriptive migration names
- Add comments in migration files
- Update README with schema changes

Troubleshooting

Common Issues

1. "Permission denied" on scripts

chmod +x scripts/*.sh

2. "psql: command not found"

# macOS
brew install postgresql

# Ubuntu/Debian
sudo apt-get install postgresql-client

# Windows
# Download from postgresql.org

3. "Connection refused" to local DB

# Check if Supabase is running
npx supabase status

# Start if not running
npx supabase start

4. "Docker container not found"

# Ensure Docker Desktop is running
# Restart Supabase
npx supabase stop
npx supabase start

5. Migration conflicts

# Reset local and reimport
make local-reset
make local-import-staging

# Or manually resolve conflicts in migration files

6. ".env variables not loading"

# Check file exists
ls -la .env

# Check variable names match exactly
make check-env

Advanced Tips

Custom Migrations

Create complex migrations with multiple statements:

-- supabase/migrations/20251004_custom.sql
BEGIN;

-- Add new table
CREATE TABLE user_profiles (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES auth.users(id),
  display_name TEXT,
  avatar_url TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Add RLS policies
ALTER TABLE user_profiles ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can view all profiles"
  ON user_profiles FOR SELECT
  TO authenticated
  USING (true);

CREATE POLICY "Users can update own profile"
  ON user_profiles FOR UPDATE
  TO authenticated
  USING (auth.uid() = user_id);

-- Add indexes
CREATE INDEX idx_user_profiles_user_id ON user_profiles(user_id);

COMMIT;

Selective Data Import

Modify apply-to-local.sh to exclude specific tables:

# Dump without sensitive tables
npx supabase db dump \
  --db-url "$STAGING_DB_URL" \
  --exclude-table=user_secrets \
  --exclude-table=payment_info \
  -f staging_data_safe.sql

Automated Backups

Add to your deployment pipeline:

# Before production deployment
timestamp=$(date +%Y%m%d_%H%M%S)
npx supabase db dump \
  --db-url "$PROD_DB_URL" \
  -f "backups/prod_backup_${timestamp}.sql"

Quick Reference Table

#	Command	Description	Safety	Confirmation
1	`local-import-staging`	Copy staging → local	✅	None
2	`local-import-production`	Copy production → local	✅	None
3	`local-create-migration`	Create new migration	✅	None
4	`local-get-migration`	Diff local to file	✅	None
5	`local-apply-migration`	Apply migrations locally	✅	None
6	`local-reset`	Reset local DB	⚠️	`yes`
7	`supabase-login`	CLI authentication	✅	None
8	`staging-get-migration`	Diff staging to file	✅	None
9	`staging-apply-migration`	Deploy to staging	⚠️	`yes`
10	`staging-reset`	Reset staging DB	🔶	`RESET-STAGING`
11	`prod-get-migration`	Diff production to file	✅	None
12	`prod-apply-migration`	Deploy to production	🔴	`APPLY-TO-PRODUCTION`
13	`prod-apply-staging-migration`	Promote staging → prod	🔴	`APPLY-TO-PRODUCTION`
14	`prod-reset`	Reset production DB	🔴🔴	`DELETE-ALL-PRODUCTION-DATA`
15	`check-deps`	Verify tools installed	✅	None
16	`clean`	Remove dump files	✅	None
17	`check-env`	Show DB URLs	✅	None
18	`delete-migrations`	Clear migration history	⚠️	`yes`

Conclusion

This Makefile-based system provides a robust, safe, and efficient way to manage Supabase databases across multiple environments. Key benefits:

Safety First: Multiple confirmation levels prevent accidents
Automation: Reduces manual errors and saves time
Clarity: Clear command names and workflows
Flexibility: Supports complex migration scenarios
Team-Friendly: Consistent commands across team members

Next Steps

Set up your .env file with connection strings
Make scripts executable: chmod +x scripts/*.sh
Start with local operations to familiarize yourself
Gradually adopt staging and production workflows
Customize scripts to fit your specific needs

Resources

Direct Commands Reference (Complete List)

Below is a comprehensive reference of all commands you can use without the Makefile. These commands give you full control and can be used in any environment.

Setup & Authentication

# Install Supabase CLI
npm install supabase --save-dev

# Initialize Supabase
npx supabase init

# Start local Supabase
npx supabase start

# Stop local Supabase
npx supabase stop

# Check status
npx supabase status

# Login to Supabase
npx supabase login

# Check Supabase version
npx supabase --version

Local Database Operations

# Reset local database (applies all migrations from scratch)
npx supabase db reset --local

# Push local migrations to local database
npx supabase db push --local

# Create a new migration file
npx supabase db diff -f add_user_profiles

# Generate migration from local database changes
npx supabase db diff -f capture_changes --local

# Apply a specific migration file
psql "postgresql://postgres:postgres@localhost:54322/postgres" < supabase/migrations/20251004_your_migration.sql

# Dump local database schema
npx supabase db dump --local --schema public > local_schema.sql

# Dump local database data
npx supabase db dump --local --data-only -f local_data.sql

# Dump specific tables only
npx supabase db dump --local --table=users --table=posts > specific_tables.sql

Staging Database Operations

# Set your staging DB URL (do this once per session)
export STAGING_DB_URL="postgresql://postgres:YOUR_PASSWORD@db.YOUR_STAGING_REF.supabase.co:5432/postgres"

# Dump staging schema
npx supabase db dump --db-url "$STAGING_DB_URL" --schema public > staging_schema.sql

# Dump staging data
npx supabase db dump --db-url "$STAGING_DB_URL" --data-only -f staging_data.sql

# Dump staging schema and data together
npx supabase db dump --db-url "$STAGING_DB_URL" > staging_full.sql

# Generate migration from staging database
npx supabase db diff -f migration_from_staging --db-url "$STAGING_DB_URL"

# Push local migrations to staging
npx supabase db push --db-url "$STAGING_DB_URL"

# Apply specific migration to staging
psql "$STAGING_DB_URL" < supabase/migrations/20251004_your_migration.sql

# Reset staging database (⚠️ drops all data)
psql "$STAGING_DB_URL" -c "DROP SCHEMA IF EXISTS public CASCADE; CREATE SCHEMA public; GRANT ALL ON SCHEMA public TO postgres; GRANT ALL ON SCHEMA public TO public;"

# Backup staging before changes
npx supabase db dump --db-url "$STAGING_DB_URL" > staging_backup_$(date +%Y%m%d_%H%M%S).sql

Production Database Operations

# Set your production DB URL (do this once per session)
export PROD_DB_URL="postgresql://postgres.YOUR_PROD_REF:YOUR_PASSWORD@aws-1-ap-south-1.pooler.supabase.com:6543/postgres"

# Dump production schema
npx supabase db dump --db-url "$PROD_DB_URL" --schema public > production_schema.sql

# Dump production data
npx supabase db dump --db-url "$PROD_DB_URL" --data-only -f production_data.sql

# Dump production schema and data together
npx supabase db dump --db-url "$PROD_DB_URL" > production_full.sql

# Generate migration from production database
npx supabase db diff -f migration_from_production --db-url "$PROD_DB_URL"

# Push local migrations to production (⚠️ CRITICAL)
npx supabase db push --db-url "$PROD_DB_URL"

# Apply specific migration to production (⚠️ CRITICAL)
psql "$PROD_DB_URL" < supabase/migrations/20251004_your_migration.sql

# Reset production database (⚠️⚠️⚠️ EXTREMELY DANGEROUS)
psql "$PROD_DB_URL" -c "DROP SCHEMA IF EXISTS public CASCADE; CREATE SCHEMA public; GRANT ALL ON SCHEMA public TO postgres; GRANT ALL ON SCHEMA public TO public;"

# Always backup production before changes!
npx supabase db dump --db-url "$PROD_DB_URL" > prod_backup_$(date +%Y%m%d_%H%M%S).sql

Import Database to Local

# Import staging to local
npx supabase db reset --local
npx supabase db dump --db-url "$STAGING_DB_URL" --schema public > staging_schema.sql
npx supabase db dump --db-url "$STAGING_DB_URL" --data-only -f staging_data.sql
psql "postgresql://postgres:postgres@localhost:54322/postgres" < staging_schema.sql
psql "postgresql://postgres:postgres@localhost:54322/postgres" < staging_data.sql

# Import production to local
npx supabase db reset --local
npx supabase db dump --db-url "$PROD_DB_URL" --schema public > production_schema.sql
npx supabase db dump --db-url "$PROD_DB_URL" --data-only -f production_data.sql
psql "postgresql://postgres:postgres@localhost:54322/postgres" < production_schema.sql
psql "postgresql://postgres:postgres@localhost:54322/postgres" < production_data.sql

# Import with error handling (stops on first error)
psql "postgresql://postgres:postgres@localhost:54322/postgres" -v ON_ERROR_STOP=1 < your_file.sql

Using Docker (if psql not installed locally)

# Get Docker container ID for Supabase DB
DB_CONTAINER_ID=$(docker ps -q --filter "label=com.docker.compose.service=db" --filter "name=supabase-db" | head -n 1)

# Check if container is found
echo $DB_CONTAINER_ID

# Apply SQL file via Docker
cat your_file.sql | docker exec -i $DB_CONTAINER_ID psql -U postgres -d postgres -v ON_ERROR_STOP=1

# Execute SQL command via Docker
docker exec -i $DB_CONTAINER_ID psql -U postgres -d postgres -c "SELECT * FROM your_table;"

# Dump database via Docker
docker exec $DB_CONTAINER_ID pg_dump -U postgres -d postgres > dump.sql

# Interactive psql session via Docker
docker exec -it $DB_CONTAINER_ID psql -U postgres -d postgres

Advanced Operations

# Dump specific tables only
npx supabase db dump --db-url "$STAGING_DB_URL" --table=users --table=posts -f specific_tables.sql

# Exclude specific tables from dump (useful for sensitive data)
npx supabase db dump --db-url "$STAGING_DB_URL" --exclude-table=sensitive_data --exclude-table=secrets --data-only -f safe_data.sql

# Dump with custom schema (not just public)
npx supabase db dump --db-url "$STAGING_DB_URL" --schema public --schema auth --schema storage > full_schema.sql

# Create migration comparing two databases
npx supabase db diff --linked --schema public

# View migration history in database
psql "postgresql://postgres:postgres@localhost:54322/postgres" -c "SELECT * FROM supabase_migrations.schema_migrations ORDER BY version;"

# Manually add migration record (if migration was applied outside Supabase)
psql "postgresql://postgres:postgres@localhost:54322/postgres" -c "INSERT INTO supabase_migrations.schema_migrations (version) VALUES ('20251004123045');"

# Delete specific migration record
psql "postgresql://postgres:postgres@localhost:54322/postgres" -c "DELETE FROM supabase_migrations.schema_migrations WHERE version = '20251004123045';"

# Clear all migration records (⚠️ use with caution)
psql "postgresql://postgres:postgres@localhost:54322/postgres" -c "TRUNCATE supabase_migrations.schema_migrations;"

# View all migration records
psql "$STAGING_DB_URL" -c "SELECT version, inserted_at FROM supabase_migrations.schema_migrations ORDER BY version DESC LIMIT 10;"

Database Inspection Commands

# Test database connection
psql "$STAGING_DB_URL" -c "SELECT version();"

# List all databases
psql "$STAGING_DB_URL" -c "\l"

# List all tables in public schema
psql "$STAGING_DB_URL" -c "\dt public.*"

# Describe table structure
psql "$STAGING_DB_URL" -c "\d public.users"

# Get database size
psql "$STAGING_DB_URL" -c "SELECT pg_size_pretty(pg_database_size('postgres'));"

# Get table sizes (sorted by size)
psql "$STAGING_DB_URL" -c "
SELECT 
  schemaname, 
  tablename, 
  pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size,
  pg_total_relation_size(schemaname||'.'||tablename) AS bytes
FROM pg_tables 
WHERE schemaname = 'public' 
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;
"

# Count rows in all tables
psql "$STAGING_DB_URL" -c "
SELECT 
  schemaname,
  tablename,
  n_live_tup AS row_count
FROM pg_stat_user_tables
WHERE schemaname = 'public'
ORDER BY n_live_tup DESC;
"

# View active connections
psql "$STAGING_DB_URL" -c "
SELECT 
  datname, 
  usename, 
  client_addr, 
  state
FROM pg_stat_activity 
WHERE datname = 'postgres';
"

# Check PostgreSQL version
psql "$STAGING_DB_URL" -c "SHOW server_version;"

Utility Commands

# Check if required tools are installed
node --version
npm --version
npx --version
npx supabase --version
psql --version
docker --version

# Set environment variables temporarily
export LOCAL_DB_URL="postgresql://postgres:postgres@localhost:54322/postgres"
export STAGING_DB_URL="postgresql://postgres:YOUR_PASSWORD@db.YOUR_STAGING_REF.supabase.co:5432/postgres"
export PROD_DB_URL="postgresql://postgres.YOUR_PROD_REF:YOUR_PASSWORD@aws-1-ap-south-1.pooler.supabase.com:6543/postgres"

# Display current environment variables
echo "Local: $LOCAL_DB_URL"
echo "Staging: $STAGING_DB_URL"
echo "Production: $PROD_DB_URL"

# Clean up dump files
rm -f staging_schema.sql staging_data.sql production_schema.sql production_data.sql staging_migration_*.sql

# Create backup directory
mkdir -p backups

# Backup with timestamp
npx supabase db dump --db-url "$PROD_DB_URL" > backups/prod_backup_$(date +%Y%m%d_%H%M%S).sql

Complete Workflow Examples (No Makefile)

Example 1: Create and Deploy a Migration

# 1. Make changes locally (via Supabase Studio or direct SQL)
# For example, create a table in Supabase Studio

# 2. Create migration from your changes
npx supabase db diff -f add_user_profiles

# 3. Review the generated migration file
cat supabase/migrations/*_add_user_profiles.sql

# 4. Apply to local (test it works)
npx supabase db push --local

# 5. Verify it worked
psql "postgresql://postgres:postgres@localhost:54322/postgres" -c "\d public.user_profiles"

# 6. Apply to staging
export STAGING_DB_URL="postgresql://postgres:YOUR_PASSWORD@db.YOUR_STAGING_REF.supabase.co:5432/postgres"
npx supabase db push --db-url "$STAGING_DB_URL"

# 7. Test in staging environment
# Run your application tests

# 8. Apply to production (after thorough testing)
export PROD_DB_URL="postgresql://postgres.YOUR_PROD_REF:YOUR_PASSWORD@aws-1-ap-south-1.pooler.supabase.com:6543/postgres"
npx supabase db push --db-url "$PROD_DB_URL"

Example 2: Import Production Data Locally

# 1. Set production URL
export PROD_DB_URL="postgresql://postgres.YOUR_PROD_REF:YOUR_PASSWORD@aws-1-ap-south-1.pooler.supabase.com:6543/postgres"

# 2. Reset local database
npx supabase db reset --local

# 3. Dump production schema
npx supabase db dump --db-url "$PROD_DB_URL" --schema public > prod_schema.sql

# 4. Dump production data
npx supabase db dump --db-url "$PROD_DB_URL" --data-only -f prod_data.sql

# 5. Apply schema to local
psql "postgresql://postgres:postgres@localhost:54322/postgres" < prod_schema.sql

# 6. Apply data to local
psql "postgresql://postgres:postgres@localhost:54322/postgres" < prod_data.sql

# 7. Verify import
psql "postgresql://postgres:postgres@localhost:54322/postgres" -c "SELECT COUNT(*) FROM users;"

# 8. Clean up dump files
rm prod_schema.sql prod_data.sql

Example 3: Sync Staging to Production

# 1. Set environment variables
export STAGING_DB_URL="postgresql://postgres:YOUR_PASSWORD@db.YOUR_STAGING_REF.supabase.co:5432/postgres"
export PROD_DB_URL="postgresql://postgres.YOUR_PROD_REF:YOUR_PASSWORD@aws-1-ap-south-1.pooler.supabase.com:6543/postgres"

# 2. Backup production first!
npx supabase db dump --db-url "$PROD_DB_URL" > prod_backup_$(date +%Y%m%d_%H%M%S).sql

# 3. Dump staging schema
npx supabase db dump --db-url "$STAGING_DB_URL" --schema public > staging_to_prod.sql

# 4. Review the file (IMPORTANT!)
cat staging_to_prod.sql
# or use a text editor
# code staging_to_prod.sql

# 5. Apply to production (⚠️ CRITICAL OPERATION)
psql "$PROD_DB_URL" < staging_to_prod.sql

# 6. Verify production
psql "$PROD_DB_URL" -c "\dt public.*"

# 7. Clean up
rm staging_to_prod.sql

Example 4: Hotfix Production Issue

# 1. Import production data locally to reproduce issue
export PROD_DB_URL="postgresql://postgres.YOUR_PROD_REF:YOUR_PASSWORD@aws-1-ap-south-1.pooler.supabase.com:6543/postgres"
npx supabase db reset --local
npx supabase db dump --db-url "$PROD_DB_URL" --schema public > prod_schema.sql
npx supabase db dump --db-url "$PROD_DB_URL" --data-only -f prod_data.sql
psql "postgresql://postgres:postgres@localhost:54322/postgres" < prod_schema.sql
psql "postgresql://postgres:postgres@localhost:54322/postgres" < prod_data.sql

# 2. Reproduce the bug locally

# 3. Fix the issue (modify database via Studio or SQL)

# 4. Create migration
npx supabase db diff -f hotfix_user_permissions

# 5. Test the fix locally
npm run dev

# 6. Review migration file
cat supabase/migrations/*_hotfix_user_permissions.sql

# 7. Apply to production (after approval)
npx supabase db push --db-url "$PROD_DB_URL"

# 8. Verify fix in production
psql "$PROD_DB_URL" -c "SELECT * FROM your_affected_table LIMIT 5;"

# 9. Apply same fix to staging for consistency
export STAGING_DB_URL="postgresql://postgres:YOUR_PASSWORD@db.YOUR_STAGING_REF.supabase.co:5432/postgres"
npx supabase db push --db-url "$STAGING_DB_URL"

Environment Variables Setup

For easier command usage, create a .env file or add to your shell profile:

Option 1: Using .env file

Create .env in your project root:

# Local Environment
LOCAL_DB_URL=postgresql://postgres:postgres@localhost:54322/postgres

# Staging Environment
STAGING_DB_URL=postgresql://postgres:YOUR_PASSWORD@db.YOUR_STAGING_REF.supabase.co:5432/postgres
STAGING_PROJECT_REF=your-staging-ref

# Production Environment
PROD_DB_URL=postgresql://postgres.YOUR_PROD_REF:YOUR_PASSWORD@aws-1-ap-south-1.pooler.supabase.com:6543/postgres
PROD_PROJECT_REF=your-production-ref

Then load it:

# Load .env file
export $(cat .env | xargs)

# Or use in a script
source .env

Option 2: Add to shell profile

Add to ~/.bashrc or ~/.zshrc:

# Supabase Database URLs
export LOCAL_DB_URL="postgresql://postgres:postgres@localhost:54322/postgres"
export STAGING_DB_URL="postgresql://postgres:YOUR_PASSWORD@db.YOUR_STAGING_REF.supabase.co:5432/postgres"
export PROD_DB_URL="postgresql://postgres.YOUR_PROD_REF:YOUR_PASSWORD@aws-1-ap-south-1.pooler.supabase.com:6543/postgres"

Reload your shell:

source ~/.bashrc
# or
source ~/.zshrc

Tips for Direct Commands

Always use full DB URLs - Don't rely on shortcuts or aliases
Backup before destructive operations:

   npx supabase db dump --db-url "$PROD_DB_URL" > backup_$(date +%Y%m%d).sql

Use transactions for multiple changes:

   BEGIN;
   -- Your changes here
   ALTER TABLE users ADD COLUMN age INTEGER;
   UPDATE users SET age = 25 WHERE age IS NULL;
   COMMIT;
   -- Or ROLLBACK; if something goes wrong

Check migration history before applying:

   psql "$DB_URL" -c "SELECT * FROM supabase_migrations.schema_migrations ORDER BY version DESC LIMIT 5;"

Review migration files before applying:

   cat supabase/migrations/20251004_your_migration.sql

Use error handling:

   psql "$DB_URL" -v ON_ERROR_STOP=1 < your_migration.sql

Test in local first, then staging, then production:

   # Local
   npx supabase db push --local

   # Staging
   npx supabase db push --db-url "$STAGING_DB_URL"

   # Production (after testing)
   npx supabase db push --db-url "$PROD_DB_URL"

Create aliases for common commands (add to shell profile):

   alias sp-local="npx supabase db push --local"
   alias sp-staging="npx supabase db push --db-url \$STAGING_DB_URL"
   alias sp-prod="npx supabase db push --db-url \$PROD_DB_URL"
   alias sp-diff="npx supabase db diff -f"

Quick Command Cheatsheet

Task	Command
Start Supabase	`npx supabase start`
Stop Supabase	`npx supabase stop`
Check status	`npx supabase status`
Login	`npx supabase login`
Reset local	`npx supabase db reset --local`
Create migration	`npx supabase db diff -f name`
Apply locally	`npx supabase db push --local`
Apply to staging	`npx supabase db push --db-url "$STAGING_DB_URL"`
Apply to prod	`npx supabase db push --db-url "$PROD_DB_URL"`
Dump schema	`npx supabase db dump --db-url "$URL" --schema public > file.sql`
Dump data	`npx supabase db dump --db-url "$URL" --data-only -f file.sql`
Apply SQL	`psql "$DB_URL" < file.sql`
Execute SQL	`psql "$DB_URL" -c "SQL"`
View migrations	`psql "$DB_URL" -c "SELECT * FROM supabase_migrations.schema_migrations;"`

--- Manually add migration record
psql "postgresql://postgres:postgres@localhost:54322/postgres" -c "INSERT INTO supabase_migrations.schema_migrations (version) VALUES ('20251004123045');"

Delete migration record

psql "postgresql://postgres:postgres@localhost:54322/postgres" -c "DELETE FROM supabase_migrations.schema_migrations WHERE version = '20251004123045';"

Clear all migration records

psql "postgresql://postgres:postgres@localhost:54322/postgres" -c "TRUNCATE supabase_migrations.schema_migrations;"


### Utility Commands

bash

Check PostgreSQL version

psql --version

Test database connection

psql "$STAGING_DB_URL" -c "SELECT version();"

List all databases

psql "$STAGING_DB_URL" -c "\l"

List all tables in public schema

psql "$STAGING_DB_URL" -c "\dt public.*"

Describe table structure

psql "$STAGING_DB_URL" -c "\d public.your_table"

Get database size

psql "$STAGING_DB_URL" -c "SELECT pg_size_pretty(pg_database_size('postgres'));"

Get table sizes

psql "$STAGING_DB_URL" -c "SELECT schemaname, tablename, pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size FROM pg_tables WHERE schemaname = 'public' ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;"


### Environment Variables for Scripts

Create a `.env` file or export these variables:

bash

Local

export LOCAL_DB_URL="postgresql://postgres:postgres@localhost:54322/postgres"

Staging

export STAGING_DB_URL="postgresql://postgres:YOUR_PASSWORD@db.YOUR_STAGING_REF.supabase.co:5432/postgres"
export STAGING_PROJECT_REF="your-staging-ref"

Production

export PROD_DB_URL="postgresql://postgres.YOUR_PROD_REF:YOUR_PASSWORD@aws-1-ap-south-1.pooler.supabase.com:6543/postgres"
export PROD_PROJECT_REF="your-production-ref"


### Quick Command Reference

| Operation | Command |
|-----------|---------|
| Start local Supabase | `npx supabase start` |
| Stop local Supabase | `npx supabase stop` |
| Check status | `npx supabase status` |
| Reset local DB | `npx supabase db reset --local` |
| Create migration | `npx supabase db diff -f name` |
| Apply migrations locally | `npx supabase db push --local` |
| Apply to staging | `npx supabase db push --db-url "$STAGING_DB_URL"` |
| Apply to production | `npx supabase db push --db-url "$PROD_DB_URL"` |
| Dump schema | `npx supabase db dump --db-url "$URL" --schema public > file.sql` |
| Dump data | `npx supabase db dump --db-url "$URL" --data-only -f file.sql` |
| Apply SQL file | `psql "$DB_URL" < file.sql` |
| Execute SQL | `psql "$DB_URL" -c "SQL COMMAND"` |

### Complete Workflow Examples (No Makefile)

**Example 1: Create and Deploy a Migration**

bash

1. Make changes locally (via Supabase Studio or SQL)

2. Create migration

npx supabase db diff -f add_user_profiles

3. Apply to local (test)

npx supabase db push --local

4. Apply to staging

npx supabase db push --db-url "$STAGING_DB_URL"

5. Apply to production (after testing)

npx supabase db push --db-url "$PROD_DB_URL"


**Example 2: Import Production Data Locally**

bash

1. Reset local database

npx supabase db reset --local

2. Dump production

npx supabase db dump --db-url "$PROD_DB_URL" --schema public > prod_schema.sql
npx supabase db dump --db-url "$PROD_DB_URL" --data-only -f prod_data.sql

3. Apply to local

psql "postgresql://postgres:postgres@localhost:54322/postgres" < prod_schema.sql
psql "postgresql://postgres:postgres@localhost:54322/postgres" < prod_data.sql

4. Verify

psql "postgresql://postgres:postgres@localhost:54322/postgres" -c "SELECT COUNT(*) FROM your_table;"


**Example 3: Sync Staging to Production**

bash

1. Dump staging schema

npx supabase db dump --db-url "$STAGING_DB_URL" --schema public > staging_to_prod.sql

2. Review the file

cat staging_to_prod.sql

3. Backup production first

npx supabase db dump --db-url "$PROD_DB_URL" --schema public > prod_backup_$(date +%Y%m%d).sql

4. Apply to production

psql "$PROD_DB_URL" < staging_to_prod.sql


### Tips for Direct Commands

1. **Always use full DB URLs** - Don't rely on shortcuts
2. **Test with --dry-run** when available
3. **Backup before destructive operations**
4. **Use transactions** for multiple changes:

sql
BEGIN;
-- Your changes here
COMMIT;
-- Or ROLLBACK; if something goes wrong

5. **Check migration history** before applying:

bash
psql "$DB_URL" -c "SELECT * FROM supabase_migrations.schema_migrations;"


---

## Conclusion

This guide provides two powerful approaches to managing Supabase databases across multiple environments:

### Choose Your Approach

**Use Makefile if you:**
- ✅ Want simplified, memorable commands
- ✅ Need built-in safety confirmations
- ✅ Prefer automation over manual control
- ✅ Work in a team with consistent workflows

**Use Direct Commands if you:**
- ✅ Don't have access to setup files
- ✅ Need maximum flexibility
- ✅ Integrate with CI/CD pipelines
- ✅ Prefer explicit control over each operation

### Key Benefits

**Safety First**: 
- Multiple confirmation levels prevent accidents (with Makefile)
- Explicit commands reduce ambiguity (with direct commands)

**Automation**: 
- Reduces manual errors and saves time
- Consistent workflows across team members

**Clarity**: 
- Clear command names and purposes
- Easy to understand what each operation does

**Flexibility**: 
- Supports complex migration scenarios
- Adaptable to different workflows

### Best Practices Summary

1. **Always Test Locally First**

bash
# Test here first
npx supabase db push --local

# Then staging
npx supabase db push --db-url "$STAGING_DB_URL"

# Finally production
npx supabase db push --db-url "$PROD_DB_URL"


2. **Backup Before Major Changes**

bash
npx supabase db dump --db-url "$PROD_DB_URL" > backup_$(date +%Y%m%d).sql


3. **Review Migrations Before Applying**

bash
cat supabase/migrations/20251004_your_migration.sql


4. **Version Control Everything**

bash
git add supabase/migrations/
git commit -m "feat: add user profiles table"
git push


5. **Document Database Changes**
   - Use descriptive migration names
   - Add comments in migration files
   - Update documentation with schema changes

6. **Use Transactions for Complex Changes**

sql
BEGIN;
-- Multiple operations
ALTER TABLE users ADD COLUMN status TEXT;
UPDATE users SET status = 'active';
COMMIT;


7. **Monitor Migration Status**

bash
psql "$DB_URL" -c "SELECT * FROM supabase_migrations.schema_migrations ORDER BY version DESC LIMIT 10;"


### Common Gotchas to Avoid

❌ **Don't skip environments** - Always test in local → staging → production order

❌ **Don't bypass confirmations** - They exist to prevent disasters

❌ **Don't forget backups** - Especially before production changes

❌ **Don't commit sensitive data** - Never commit `.env` files or connection strings

❌ **Don't apply untested migrations** - Review and test thoroughly

❌ **Don't ignore migration conflicts** - Resolve them properly

❌ **Don't modify existing migrations** - Create new ones instead

### Troubleshooting Quick Reference

| Problem | Solution |
|---------|----------|
| Permission denied on scripts | `chmod +x scripts/*.sh` |
| psql not found | Install PostgreSQL client tools |
| Connection refused | Check if Supabase is running: `npx supabase status` |
| Docker container not found | Ensure Docker Desktop is running |
| Migration conflicts | Reset and reimport: `npx supabase db reset --local` |
| .env not loading | Check file exists and variable names match |
| Slow migrations | Check database size and indexes |
| Authentication failed | Run `npx supabase login` |

### Next Steps

1. **Set up your environment**
   - Install required tools
   - Configure `.env` file (or export variables)
   - Initialize Supabase locally

2. **Choose your approach**
   - With Makefile: Set up project structure and scripts
   - Direct commands: Bookmark this guide for reference

3. **Start with local operations**
   - Practice creating and applying migrations
   - Get comfortable with the workflow

4. **Gradually adopt staging workflow**
   - Test deployments in staging first
   - Build confidence with the process

5. **Implement production workflow**
   - Always backup before changes
   - Follow the testing pyramid: local → staging → production

6. **Customize for your needs**
   - Adapt workflows to your team's requirements
   - Add custom scripts or aliases
   - Integrate with CI/CD pipelines

### Additional Resources

- **[Supabase CLI Documentation](https://supabase.com/docs/guides/cli)** - Official CLI reference
- **[PostgreSQL Documentation](https://www.postgresql.org/docs/)** - Database fundamentals
- **[Supabase Migration Guide](https://supabase.com/docs/guides/cli/local-development#database-migrations)** - Official migration docs
- **[Database Migration Best Practices](https://supabase.com/blog/database-migrations)** - Supabase blog
- **[GNU Make Manual](https://www.gnu.org/software/make/manual/)** - Makefile reference

### Getting Help

If you encounter issues:

1. **Check Supabase logs**:

bash
npx supabase logs


2. **Verify connection**:

bash
psql "$DB_URL" -c "SELECT version();"


3. **Check migration status**:

bash
psql "$DB_URL" -c "SELECT * FROM supabase_migrations.schema_migrations;"


4. **Review migration files**:

bash
ls -la supabase/migrations/




5. **Consult Supabase support**:
   - Discord: [https://discord.supabase.com](https://discord.supabase.com)
   - GitHub Discussions: [https://github.com/supabase/supabase/discussions](https://github.com/supabase/supabase/discussions)
   - Documentation: [https://supabase.com/docs](https://supabase.com/docs)

---

## Final Thoughts

Database migrations don't have to be scary. With the right tools and workflows, you can safely manage changes across multiple environments with confidence.

**Remember the golden rules:**

1. 🧪 **Test thoroughly** - Local → Staging → Production
2. 💾 **Backup always** - Before any production change
3. 📝 **Document everything** - Future you will thank you
4. 🔒 **Review carefully** - Double-check before applying
5. 🚀 **Deploy confidently** - With proper testing and backups

Whether you use the Makefile approach for convenience or direct commands for flexibility, the most important thing is consistency and safety in your workflow.

**With great power comes great responsibility. Always verify which environment you're working with, especially when running production commands!** 🚀

---

*Happy migrating! Have questions or suggestions? Feel free to adapt this workflow to your specific needs!*

---

## Appendix: Command Comparison Table

### Side-by-Side Comparison

| Operation | Makefile | Direct Command |
|-----------|----------|----------------|
| **Import Staging** | `make local-import-staging` | `npx supabase db reset --local && npx supabase db dump --db-url "$STAGING_DB_URL" --schema public > staging_schema.sql && npx supabase db dump --db-url "$STAGING_DB_URL" --data-only -f staging_data.sql && psql "postgresql://postgres:postgres@localhost:54322/postgres" < staging_schema.sql && psql "postgresql://postgres:postgres@localhost:54322/postgres" < staging_data.sql` |
| **Create Migration** | `make local-create-migration` | `npx supabase db diff -f migration_name` |
| **Apply Locally** | `make local-apply-migration` | `npx supabase db push --local` |
| **Apply to Staging** | `make staging-apply-migration` | `npx supabase db push --db-url "$STAGING_DB_URL"` |
| **Apply to Production** | `make prod-apply-migration` | `npx supabase db push --db-url "$PROD_DB_URL"` |
| **Reset Local** | `make local-reset` | `npx supabase db reset --local` |

### Pros and Cons

| Aspect | Makefile | Direct Commands |
|--------|----------|-----------------|
| **Ease of Use** | ✅ Very simple | ⚠️ More complex |
| **Setup Required** | ⚠️ Yes (files needed) | ✅ No setup |
| **Safety** | ✅ Built-in confirmations | ⚠️ Manual checks |
| **Flexibility** | ⚠️ Fixed workflows | ✅ Full control |
| **CI/CD Integration** | ⚠️ Requires files | ✅ Easy integration |
| **Learning Curve** | ✅ Low | ⚠️ Medium |
| **Customization** | ⚠️ Requires editing files | ✅ Highly customizable |
| **Team Consistency** | ✅ Enforced | ⚠️ Manual |

---

**That's it! You're now equipped with everything you need to manage Supabase migrations like a pro!** 🎉

dropdown open in portal for using table in react

poshiya parth s — Tue, 09 Jul 2024 05:42:53 +0000

import React, { useState, useRef, useEffect } from "react";
import ReactDOM from "react-dom";

const PortalWrapper = React.forwardRef(({ children }, ref) => {
  const [openDropdownMenu, setOpenDropdownMenu] = useState(false);
  const [position, setPosition] = useState({
    left: 0,
    top: 0,
    width: "100%",
  });

  const dropdownRef = useRef(null);

  const handleDropdDownClick = () => {
    setOpenDropdownMenu(!openDropdownMenu);
    if (ref && "current" in ref && ref.current) {
      const { top, left, height } = ref.current.getBoundingClientRect();
      setPosition({
        top: top + height,
        left,
        width: `${ref.current.clientWidth}px`,
      });
    }
  };

  useEffect(() => {
    if (openDropdownMenu && dropdownRef.current) {
      dropdownRef.current.style.top = `${position.top}px`;
      dropdownRef.current.style.left = `${position.left}px`;
      dropdownRef.current.style.display = "none";
    }
  }, [openDropdownMenu, position]);

  return (
    <div ref={ref} className="w-full" onClick={handleDropdDownClick}>
      {children(position)}
      {openDropdownMenu &&
        ReactDOM.createPortal(
          <div ref={dropdownRef} style={{ position: "absolute" }}>
            {children(position)}
          </div>,
          document.body
        )}
    </div>
  );
});
export default PortalWrapper;

 <PortalWrapper ref={dropdownRef}>
                      {() =>
                        (itIsOwner(user?.agent_role) ||
                          user?.platform_owner) && (
                          <Select
                            styles={CustomStyle}
                            onChange={(selectedOption) =>
                              handleActionChange(
                                selectedOption,
                                contactInfo,
                                index
                              )
                            }
                            options={agentData?.map((option) => ({
                              value: option,
                              label: `${option.first_name} `,
                            }))}
                            placeholder="Esther Howard"
                            className={clsx(
                              "dropdown_list !w-40 cursor-pointer userFilter mt-[0.4375rem]"
                            )}
                            classNamePrefix="dropdown_list-contacts"
                            menuPosition="fixed"
                            menuPlacement="auto"
                            menuShouldScrollIntoView={false}
                            menuPortalTarget={document.body}
                          />
                        )
                      }
                    </PortalWrapper>

Using React Portals to Render Elements Anywhere in the DOM

poshiya parth s — Tue, 09 Jul 2024 05:42:41 +0000

Introduction
React Portals provide a powerful way to render components outside the main DOM hierarchy, allowing for greater flexibility in placing elements. This is particularly useful for creating modals, tooltips, and other components that need to be rendered at a different location in the DOM. In this guide, we will demonstrate how to use React Portals to open and render elements anywhere in your application.

Prerequisites
Before we begin, ensure you have a basic understanding of React and functional components. Familiarity with hooks (useState, useEffect) will be beneficial.

Step 1: Setting Up the Project
First, create a new React project or navigate to your existing project:

npx create-react-app react-portal-tutorial
cd react-portal-tutorial
npm start

Step 2: Creating the Portal Component
Create a new file PortalComponent.js and add the following code to define a reusable portal component:

import React from 'react';
import ReactDOM from 'react-dom';

const PortalComponent = ({ children, containerId }) => {
  const container = document.getElementById(containerId);
  return container ? ReactDOM.createPortal(children, container) : null;
};

export default PortalComponent;

Step 3: Adding the Target Element in the DOM
Ensure you have an element in your index.html or another component where the portal can render its content. For instance, add this div in your public/index.html:

<body>
  <div id="root"></div>
  <div id="header-children"></div> <!-- Target Element for Portal -->
</body>

Step 4: Implementing the Portal in Your Component
Create a component, App.js, and use the PortalComponent to render content inside the target element (header-children):

import React, { useState } from 'react';
import PortalComponent from './PortalComponent';

const App = () => {
  const [isSearch, setIsSearch] = useState(false);
  const [isDelete, setIsDelete] = useState(false);

  const handleCancel = () => {
    setIsSearch(false);
    setIsDelete(false);
  };

  const onDeleteClick = () => setIsDelete(true);
  const onSearchClick = () => setIsSearch(true);

  return (
    <div>
      <h1>React Portal Example</h1>
      <button onClick={onSearchClick}>Search</button>
      <button onClick={onDeleteClick}>Delete</button>
      <div id="header-children"></div>
      <PortalComponent containerId="header-children">
        <div className="gap-5 flex">
          {isSearch || isDelete ? (
            <button
              className="text-xl leading-8 font-semibold text-caribbeangreen"
              onClick={handleCancel}
            >
              Cancel
            </button>
          ) : (
            <HistoryActions
              history={history}
              onDeleteClick={onDeleteClick}
              onSearchClick={onSearchClick}
            />
          )}
        </div>
      </PortalComponent>
    </div>
  );
};

const HistoryActions = ({ history, onDeleteClick, onSearchClick }) => (
  <div>
    <button onClick={onSearchClick}>Search History</button>
    <button onClick={onDeleteClick}>Delete History</button>
  </div>
);

export default App;

Step 5: Styling the Portal Content
Add necessary styles in your index.css or another CSS file:

.gap-5 {
  gap: 1.25rem;
}

.flex {
  display: flex;
}

.text-caribbeangreen {
  color: #00FF7F;
}

.text-xl {
  font-size: 1.25rem;
}

.leading-8 {
  line-height: 2rem;
}

.font-semibold {
  font-weight: 600;
}

Conclusion
In this tutorial, we've covered how to use React Portals to render components outside the main DOM hierarchy. By following these steps, you can create flexible and powerful UI elements that can be rendered anywhere in your application. This technique is especially useful for components like modals, tooltips, and context menus.

first file

const headerChildrenRenderer = document.getElementById("header-children");

  {createPortal(
          <div className="gap-5 flex">
            {isSearch || isDelete ? (
              <button
                className="text-xl leading-8 font-semibold text-caribbeangreen"
                onClick={handleCancel}
              >
                Cancel
              </button>
            ) : (
              <HistroyActions
                history={history}
                onDeleteClick={onDeleteClick}
                onSearchClick={onSearchClick}
              />
            )}
          </div>,
          headerChildrenRenderer as HTMLElement
        )}

second file

// ref for when we want to open above element
        <div id='header-children'></div>

Creating Modals in React: A Comprehensive Guide to Opening Elements in Modals

poshiya parth s — Tue, 09 Jul 2024 05:42:32 +0000

Introduction
Modals are an essential part of web applications, providing a way to display content over the current page without navigating away. This guide will walk you through the steps to create a modal in React, including how to use the React Portal for rendering modals and managing their visibility with state. By the end of this tutorial, you'll be able to implement modals that can open various elements dynamically.

Prerequisites
Before we dive into the code, make sure you have a basic understanding of React and functional components. Familiarity with hooks (useState, useEffect) will be beneficial.

Step 1: Setting Up the Project
First, create a new React project or navigate to your existing project:

npx create-react-app react-modal-tutorial
cd react-modal-tutorial
npm start

Step 2: Creating the Modal Component
Create a new file ModalPortal.js and add the following code:

import React, { useEffect } from 'react';
import ReactDOM from 'react-dom';

const ModalPortal = ({ open, children }) => {
  useEffect(() => {
    const rootElement = document.body;
    if (open) {
      rootElement.classList.add('overlay');
    } else {
      rootElement.classList.remove('overlay');
    }
  }, [open]);

  if (!open) return null;

  return ReactDOM.createPortal(
    <div className="modalContent">{children}</div>,
    document.body
  );
};

export default ModalPortal;

Step 3: Styling the Modal
In your index.css or a separate CSS file, add the following styles:

.modalContent {
  top: 50%;
  left: 50%;
  transform: translate(-50%, -50%);
  position: fixed;
  width: 100%;
  max-width: 500px;
  z-index: 300;
  overflow-y: auto;
  overflow-x: hidden;
}

@media (max-width: 550px) {
  .modalContent {
    left: 15px;
    right: 15px;
  }
}

.overlay {
  overflow: hidden;
}

@screen maxLg {
  .overlay {
    transition: all 0.3s ease-in-out;
    width: 100%;
    height: 100%;
    position: fixed;
  }
  .overlay::before {
    content: "";
    z-index: 1;
    background: rgba(0, 0, 0, 0.5);
    transition: all 0.3s ease-in-out;
    position: absolute;
    top: 0;
    left: 0;
    right: 0;
    bottom: 0;
  }
}

.overlay:before {
  content: "";
  background-color: rgba(0, 0, 0, 0.5);
  position: fixed;
  top: 0;
  left: 0;
  right: 0;
  bottom: 0;
  width: 100%;
  height: 100%;
  z-index: 200;
  backdrop-blur: 6px;
}

Step 4: Implementing the Modal in Your Component
Create a component, App.js, and use the ModalPortal to open a modal with some content:

import React, { useState } from 'react';
import ModalPortal from './ModalPortal';

const App = () => {
  const [isModalOpen, setIsModalOpen] = useState(false);

  const handleOpenModal = () => setIsModalOpen(true);
  const handleCloseModal = () => setIsModalOpen(false);

  return (
    <div>
      <button onClick={handleOpenModal}>Open Modal</button>
      <ModalPortal open={isModalOpen}>
        <div className="modalContent">
          <h2>Modal Title</h2>
          <p>This is the modal content.</p>
          <button onClick={handleCloseModal}>Close</button>
        </div>
      </ModalPortal>
    </div>
  );
};

export default App;

Step 5: Dynamically Rendering Elements in the Modal
To render different elements dynamically in the modal, you can pass the content as children to the ModalPortal component:

import React, { useState } from 'react';
import ModalPortal from './ModalPortal';

const App = () => {
  const [isModalOpen, setIsModalOpen] = useState(false);
  const [modalContent, setModalContent] = useState(null);

  const handleOpenModal = (content) => {
    setModalContent(content);
    setIsModalOpen(true);
  };
  const handleCloseModal = () => setIsModalOpen(false);

  return (
    <div>
      <button onClick={() => handleOpenModal(<p>Content 1</p>)}>Open Modal with Content 1</button>
      <button onClick={() => handleOpenModal(<p>Content 2</p>)}>Open Modal with Content 2</button>
      <ModalPortal open={isModalOpen}>
        <div className="modalContent">
          <h2>Modal Title</h2>
          {modalContent}
          <button onClick={handleCloseModal}>Close</button>
        </div>
      </ModalPortal>
    </div>
  );
};

export default App;

Conclusion
In this tutorial, we've covered how to create a modal in React using React Portal and manage its state with hooks. We've also learned how to dynamically render different elements within the modal. With these techniques, you can enhance the user experience of your web application by incorporating interactive and dynamic modals. Happy coding!

Additional Resources

1. React Documentation
2. CSS Tricks for Modals
3. React Portals

Implementing Autosize Textarea in React: A Complete Guide

poshiya parth s — Tue, 09 Jul 2024 05:42:21 +0000

Introduction
Creating a seamless user experience often hinges on the small details, such as a well-designed text area for user input. In this blog post, we’ll dive into the implementation of a dynamic text area using React, which adjusts its height based on the input content and provides a smooth user experience.

Component Breakdown
CommonTextArea Component
The CommonTextArea component is a reusable component that encapsulates the logic for a dynamic text area. Here’s a detailed look at its structure and functionality:

import clsx from "clsx";
import { useLayoutEffect, useState } from "react";

export default function CommonTextArea({
  inputRef,
  isFocuse,
  isInputError,
  onChangeInput,
  handleKeyDown,
  setIsFocuse,
  textBoxValue,
  parentClassName,
  textAreaClassName,
  placeHolder,
}: {
  inputRef: React.RefObject<HTMLTextAreaElement>;
  isFocuse?: boolean;
  isInputError?: boolean;
  onChangeInput: (event: React.ChangeEvent<HTMLTextAreaElement>) => void;
  handleKeyDown: (event: React.KeyboardEvent<HTMLTextAreaElement>) => void;
  setIsFocuse: React.Dispatch<React.SetStateAction<boolean>>;
  textBoxValue?: string;
  parentClassName?: string;
  textAreaClassName?: string;
  placeHolder?: string;
}) {
  const [scrollBar, setScrollBar] = useState<boolean>(false);

  useLayoutEffect(() => {
    const textArea: any =
      inputRef.current ?? document.getElementById("text_area_input");

    if (textArea) {
      textArea.style.height = "0px";
      const scrollHeight = textArea.scrollHeight;
      textArea.style.height = scrollHeight + "px";
      if (scrollHeight >= 200) {
        setScrollBar(true);
      } else {
        setScrollBar(false);
      }
    }
  }, [inputRef, textBoxValue]);

  return (
    <div
      className={clsx(
        {
          "form-input-error": isInputError,
          "ask-question-input-focus": isFocuse,
        },
        parentClassName
      )}
      onClick={() => {
        setIsFocuse(true);
      }}
    >
      <textarea
        id="text_area_input"
        ref={inputRef}
        className={clsx(textAreaClassName, "textarea_design", {
          "overflow-y-auto": scrollBar,
        })}
        placeholder={placeHolder}
        value={textBoxValue}
        onClick={() => {
          setIsFocuse(true);
        }}
        onKeyDown={handleKeyDown}
        style={{ height: "57px" }}
        onChange={onChangeInput}
      />
    </div>
  );
}

Key Features
Dynamic Height Adjustment: The text area dynamically adjusts its height based on the input content, ensuring that users have a comfortable and responsive typing experience.
Conditional Styling: The component uses the clsx library to conditionally apply styles based on the component's state, such as focus or error states.
Scrollable Content: When the content exceeds a certain height, a scrollbar appears, ensuring that the text area remains user-friendly and does not overflow.
App Component Integration
The CommonTextArea component can be easily integrated into any React application. Here’s how you can use it:

const inputRef = useRef<HTMLTextAreaElement | null>(null);
const [isFocuse, setIsFocuse] = useState<boolean>(false);
const [isInputError, setIsInputError] = useState<boolean>(false);

const onChangeInput = (e: React.ChangeEvent<HTMLTextAreaElement>) => {
  setChatPrompt(e.target.value);
  setIsInputError(false);
};

const handleKeyDown = (e: React.KeyboardEvent<HTMLTextAreaElement>) => {
  setIsFocuse(true);

  if (e.key === "Enter" && !e.shiftKey) {
    e.preventDefault();
    onSubmitdata(); // submit event
  }
};

useEffect(() => {
  inputRef?.current?.focus();
}, [inputRef]);

return (
  <CommonTextArea
    inputRef={inputRef}
    isFocuse={isFocuse}
    isInputError={isInputError}
    onChangeInput={onChangeInput}
    handleKeyDown={handleKeyDown}
    setIsFocuse={setIsFocuse}
    textBoxValue={"default text box value"}
    parentClassName={"text_area_parent "}
    textAreaClassName={"ask-question-input caret-raisinblack"}
  />
);

CSS Styling
Proper styling is crucial for a great user experience. Here are the styles used in this implementation:

.form-input-error {
  border-color: red;
  background-color: #f5f5f5;
}

.ask-question-input-focus {
  outline: none;
  border-color: #0f9d58;
  color: #2d2d2d;
  background-color: #e6f4ea;
}

.text_area_parent {
  width: 100%;
  background-color: #f5f5f5;
  display: flex;
  justify-content: start;
  align-items: center;
  padding: 5px;
  border-radius: 10px;
  font-size: 16px;
  border: 1px solid transparent;
  transition: border-color 0.3s, background-color 0.3s;
}

.ask-question-input {
  width: 100%;
  background-color: transparent;
  font-size: 16px;
  border: none;
  outline: none;
  resize: none;
  overflow: hidden;
  max-height: 230px;
  padding: 14px;
}

.ask-question-input::-webkit-scrollbar-thumb {
  background-color: #d9d9d9;
}

Conclusion
By implementing a dynamic text area in your React application, you can significantly enhance user experience, making text inputs more user-friendly and visually appealing. The CommonTextArea component is highly customizable and can be adapted to various use cases, ensuring a consistent and responsive design throughout your application.

[(https://andarist.github.io/react-textarea-autosize/)]
[https://www.npmjs.com/package/react-textarea-autosize]

also you dont want to custom then you can use this package

How To AutoScroll In React?Creating Smooth Auto-Scrolling Functionality in React

poshiya parth s — Tue, 09 Jul 2024 05:42:08 +0000

Are you looking to enhance the user experience in your React application by implementing smooth auto-scrolling functionality? Look no further! In this blog post, we'll guide you through how to create a custom React hook that automatically scrolls to the bottom of a container when new content is added. Let's dive in!

Implementing Auto-Scroll Hook
We'll start by creating a custom React hook named useAutoScroll.tsx that handles the auto-scrolling functionality. This hook will utilize the useEffect hook to trigger the scroll action whenever new content is added to the container.



import { useEffect } from "react";

function useScrollToBottom(element: HTMLDivElement | null, data: any) {
  const scrollToBottom = () => {
    if (element) {
      element.scrollIntoView({ behavior: "smooth" });
    }
  };

  useEffect(() => {
    scrollToBottom();
    // eslint-disable-next-line react-hooks/exhaustive-deps
  }, [data]);
}

export default useAutoScroll;

Using the Auto-Scroll Hook
Next, let's see how you can use the useAutoScroll hook in your component.

In your component file (demoFile.tsx), you can use the useAutoScroll hook by passing a reference to the target element and the dependency that triggers the scroll.



const scrollRef = useRef<HTMLDivElement | null>(null);
useAutoScroll(scrollRef.current, dependency);

// Ensure that this <div> is added after the scrolling content
return (
  <div ref={scrollRef} />
);

By following these steps and incorporating the useAutoScroll hook into your React components, you can achieve a seamless auto-scrolling experience for your users as new content is dynamically loaded.

Enhance your React application with this auto-scrolling functionality to keep your users engaged and provide a smooth browsing experience. Happy coding! 🚀

That's it for our blog post on creating smooth auto-scrolling functionality in React. We hope this guide helps you implement this feature seamlessly in your projects. Stay tuned for more frontend development tips and tricks!

iphone safari issue

poshiya parth s — Tue, 09 Jul 2024 05:41:03 +0000

Safari iphone input zoom , bottom scroll, scroll to bottom , chatgpt textarea

When we click on textbox that moment iPhone Safari that textbox zoom hear is the solution in react we use first and second for the next js

<meta
      name="viewport"
      content="width=device-width,initial-scale=1.0, maximum-scale=1, user-scalable=no"
    />

import { Viewport } from "next/dist/lib/metadata/types/extra-types";

export const viewport: Viewport = {
  width: "device-width",
  initialScale: 1,
  maximumScale: 1,
  userScalable: false,
  interactiveWidget: "overlays-content",
};

2.
also iphone safari bottom scroll is default behaviour

Kaboom 🔥! Supercharge Your React App: Saving Refetches with React Query Magic

poshiya parth s — Thu, 29 Feb 2024 05:08:42 +0000

Greetings, wonderful folks of the internet! 🌟 Today, we embark on a journey to explore a real-life application that not only saves precious API costs but also leverages the magic of optimistic updates in React Query. Now, you might be wondering, "How exactly do we achieve this feat?" Well, buckle up, because we're about to dive into some captivating insights.

In my pursuit of knowledge and inspiration, I stumbled upon a gem of a resource that fueled the spark for this blog. The blog post titled Effective React Query Keys caught my attention, particularly for its articulate explanation of caching. The way it demystifies the intricacies of caching is simply awe-inspiring.

Unveiling the Optimistic Updates Marvel
The Challenge: API Costs 📉
API costs can be a significant concern in any application. Fetching data from the server incurs expenses, and optimizing this process becomes paramount. This is where React Query and optimistic updates come into play, offering a strategic approach to minimize API costs while enhancing the user experience.

The Solution: Optimistic Updates in React Query 🚀
Optimistic updates allow us to update the UI optimistically, even before the server responds. This not only provides users with a seamless and responsive interface but also reduces the need for unnecessary API calls. The real question becomes, "How do we integrate this into our React Query workflow?"

Just have a look at the below code for a while and we will start tinkering the same in a couple of blog lines ahead.

import { useMutation, useQueryClient } from "@tanstack/react-query";
import { deleteEditFunction } from "../api";

const useDeleteEditCall = (userId) => {
  const queryClient = useQueryClient();

  return useMutation(
    (userId) => deleteEditFunction(userId),
    {
      onSuccess: () => {  
        queryClient.setQueryData(["get-previous-key"], (previousdata) => {
          const filterData = previousdata.data.body.filter((user) => {
            return user.userId !== userId;
          });
          previousdata.data.body = filterData;
          return previousdata;
        });
      },
      onError: (error) => {
        console.log(error);
      },
    }
  );
};

export default useDeleteEditCall;

Note:
In our system, we have implemented a caching key, identified as get-previous-key. This key serves a crucial role in optimizing data retrieval by enabling a caching experience. Here's a breakdown of how it works:

Caching Mechanism: When you initiate a request to fetch data, you include the caching key (get-previous-key) in the query. This key acts as a trigger for the caching mechanism.

Optimizing Data Retrieval: The caching response obtained through the key allows for manipulation of the data based on specific requirements. This manipulation can involve filtering, sorting, or modifying the response without the need for an additional API call.

Example Scenario : Todo List: Let's consider a practical example with a todo list. Assume the initial response contains four todo items. The power of the caching key lies in its ability to selectively modify this response.

Dynamic Data Modification: In the case of the todo response, if there's a need to delete an item from the result set, the caching key facilitates this modification. For instance, if the goal is to remove the last todo item, this can be achieved by manipulating the caching response without triggering a re-fetch.

Efficiency Gains: By utilizing this approach, unnecessary API calls are avoided, leading to improved efficiency. Additionally, it allows for optimistic updates, ensuring a seamless user experience.

Reducing Latency: The caching key contributes to reducing latency as it enables local manipulation of data, eliminating the need for repeated server requests for similar operations.

Customizable Optimization: The caching key provides a customizable way to optimize data handling. Depending on the specific use case, it allows for tailoring the caching response to suit the application's requirements.

In summary, the get-previous-key plays a pivotal role in enhancing the caching experience, providing flexibility in manipulating responses, and ultimately optimizing data retrieval processes in a way that aligns with the needs of the application.

Understanding React Query:
Brief overview of React Query and its key features.
Explanation of how React Query manages and caches API calls.

Setting Up React Query:
Installation and basic configuration of React Query in a React project.
Configuring a global QueryClient to manage API data.

Optimizing GET Requests:
How React Query automatically caches GET requests and eliminates redundant API calls.
Utilizing the useQuery hook for efficient data fetching.
Handling stale and fresh data scenarios.

Integration with DELETE, POST, and PUT Operations:
Introduction to the useMutation hook for managing non-GET operations.
Writing mutations for DELETE, POST, and PUT requests using React Query.
Maintaining data consistency across different API operations.

Saving GET Requests on Non-GET Operations:
Strategies for ensuring that GET requests are saved and optimized when performing DELETE, POST, and PUT operations.
Leveraging the onSuccess callback to update and invalidate cached data.

Handling Errors and Edge Cases:
Implementing error handling with React Query for a seamless user experience.
Addressing edge cases such as partial data updates and rollbacks.

Best Practices and Tips:
Best practices for structuring queries and mutations in complex applications.
Tips for optimizing performance and minimizing unnecessary API calls.

Real-world Examples:
Demonstrations of implementing React Query in real-world scenarios with various API interactions.
Code snippets and practical examples for better understanding.

Conclusion:
Our hero in this quest is the get-previous-key. This key unlocks the door to efficient data manipulation, enabling us to perform optimistic updates with finesse. As we explore its applications, we'll witness how it transforms the landscape of API cost optimization in React Query.

Journeying Together
Join me on this expedition as we decode the intricacies of effective React Query keys, delve into the world of caching, and master the art of optimistic updates. Together, we'll unravel the potential to save costs, enhance performance, and elevate the user experience.

In the spirit of knowledge sharing, let's make our React Query endeavors not just effective but downright spectacular! 🚀✨

Stay tuned for more insights, and don't forget to check out the blog that sparked this exciting exploration! Until then, happy coding! 💻🌈

That all the beautiful people on the internet , lets connect on twitter

Mastering Multi-Select Options with React-Select/Creatable

poshiya parth s — Mon, 26 Feb 2024 04:31:35 +0000

Content:

React-Select/Creatable is a powerful library that enhances the user experience when dealing with multi-select options in React applications. In this blog post, we will explore how to effectively implement and leverage React-Select/Creatable to create a seamless and intuitive multiple-selection component.

Understanding the Basics
React-Select/Creatable provides a flexible and customizable solution for handling multi-select options. The Creatable component, in particular, allows users to create new options on-the-fly, making it an excellent choice for scenarios where dynamic data entry is required.

Setting Up the Environment
To get started, make sure you have React and React-Select/Creatable installed in your project. You can install it using npm or yarn:

npm install react-select
or
yarn add react-select

Building the Multi-Select Component
The provided code showcases a React component (App) that utilizes React-Select/Creatable for multi-select functionality. Let's break down some key elements:

Filter Options: The filterOptions array defines the types of filters available for selection (e.g., State, Pincode, District).

State Management: The component uses React's useState hook to manage the state of selected parameters (paramsData) and whether the selection is complete (apply).

Dynamic Search Suggestions: The onInputChange function dynamically generates search suggestions based on user input and selected filter options.

Effect Hook: An useEffect hook monitors changes in the selected parameters and triggers the application of filters.

Customization and Styling
React-Select/Creatable provides various customization options to tailor the appearance and behavior of the multi-select component. The components prop is used to override the default behavior of the clear indicator.

Integration in Your Project
To integrate this multi-select component into your project, simply import and render the App component where needed. Customize the styles and appearance according to your project's design guidelines.

Conclusion
React-Select/Creatable simplifies the implementation of a feature-rich multi-select component in React applications. Whether you're managing tags, categories, or any other form of multiple selections, React-Select/Creatable provides an elegant and user-friendly solution.

Start incorporating React-Select/Creatable into your projects today to empower users with a seamless and efficient multi-select experience!

import { useState, useEffect } from "react";
import Creatable from "react-select/creatable";
import { intersection, isEmpty } from "lodash";

const Search = () => {
  let Placeholder = "Search ";
  const filterOptions = [
    {
      label: "State",
      value: "state",
    },
    {
      label: "Pincode",
      value: "pincode",
    },
    {
      label: "District",
      value: "district",
    },
  ];

  const [apply, setApply] = useState(false);

  const [paramsData, setParamsData] = useState([]);

  const [searchOptions, setSearchOptions] = useState([]);

  const onChange = (params) => {
    if (isEmpty(paramsData)) {
      setParamsData(params);
    } else if (
      disableInput &&
      isEmpty(params) &&
      Placeholder === "Search Admin"
    ) {
      setParamsData([]);
    } else if (
      disableInput &&
      !isEmpty(params) &&
      Placeholder === "Search Admin"
    ) {
      return;
    } else {
      setParamsData(params);
    }
  };

  const disableInput = paramsData.length === filterOptions.length;
  const onInputChange = (seachKey) => {
    if (!seachKey) {
      setSearchOptions([]);
      return;
    } else if (seachKey.length !== 0) {
      const paramsFieldValues = paramsData.map((param) => param.field);
      const filterOptionValues = filterOptions.map((option) => option.value);

      const commonValues = intersection(paramsFieldValues, filterOptionValues);

      const filteredFilterOptions = filterOptions.filter(
        (option) => !commonValues.includes(option.value)
      );
      const options = filteredFilterOptions.map((option) => {
        return {
          label: `${option.label} : ${seachKey}`,
          value: seachKey,
          field: option.value,
        };
      });

      setSearchOptions(options);
    } else {
      const options = filterOptions.map((option) => {
        return {
          label: `${option.label} : ${seachKey}`,
          value: seachKey,
          field: option.value,
        };
      });
      setSearchOptions(options);
    }
  };

  useEffect(() => {
    if (isEmpty(paramsData)) {
      setApply && setApply(!apply);
    }
  }, [paramsData]);
  return (
    <div className="!cursor-pointer">
      <Creatable
        classNamePrefix="multitag provider_modal"
        options={searchOptions}
        placeholder={Placeholder}
        isMulti
        value={!isEmpty(paramsData) ? paramsData : null}
        className=" w-[350px] maxSm:w-[290px]"
        onChange={onChange}
        onInputChange={onInputChange}
        isClearable
        components={{
          ClearIndicator: () => null,
        }}
      />
    </div>
  );
};

export default Search;

Creating Dynamic PDF Downloads with React: A Guide Using jsPDF and jspdf-autotable

poshiya parth s — Thu, 22 Feb 2024 04:42:56 +0000

Introduction:
Briefly introduce the concept of exporting tables to PDF from a React application.
Highlight the importance of providing users with the ability to download tabular data for offline use.

Section 1: Setting up the Project
1.1 Install Dependencies:
Guide readers on installing the necessary packages using npm.

npm install jspdf jspdf-autotable

1.2 Importing Libraries:
Demonstrate how to import jsPDF and jspdf-autotable in your React component.

Section 2: Creating a Sample Table
2.1 Define Table Structure:
Create a sample table with headers and dummy data.
Explain the importance of having a well-structured table for PDF generation.

Section 3: Generating PDF with jspdf-autotable
3.1 Writing the generatePDF Function:
Define the generatePDF function that utilizes jsPDF and jspdf-autotable.
Explain the purpose of each step in the function.

3.2 Customizing Table Styles:
Explore different styling options available in jspdf-autotable for customizing the appearance of the PDF table.

Section 4: Adding Page Numbers and Titles
4.1 Enhancing User Experience:
Show how to add page numbers and titles to each page of the generated PDF for better readability.

Section 5: Integrating with React
5.1 Button Component:
Create a React button component to trigger the PDF generation.
Utilize the onClick event to call the generatePDF function.

Section 6: Conclusion
Summarize the benefits of using jsPDF and jspdf-autotable for PDF generation in a React application.
Encourage readers to explore further customization options and integrate it into their projects.

Conclusion:
Express the importance of providing users with downloadable content.
Invite readers to share their experiences and feedback.
Feel free to customize the content and add more details based on your preferences and the level of depth you want to achieve in each section.

Section 7: Handling Large Tables
7.1 Pagination for Large Datasets:
Explain the limitations of handling a large number of rows in a single table.
Introduce the concept of paginating the data for more manageable PDF generation.

7.2 Implementing Pagination:
Demonstrate how to implement pagination for large tables using libraries like react-table or custom pagination logic.
Showcase how to generate PDFs for each page of the table separately.

7.3 Dynamic Loading of Data:
Explore the option of dynamically loading data as the user navigates through pages to optimize performance.
Discuss how lazy loading or infinite scrolling techniques can be beneficial.

Section 8: Performance Optimization
8.1 Virtualization Techniques:
Discuss the importance of virtualization to handle a large number of DOM elements efficiently.
Introduce libraries like react-virtualized that can be integrated to enhance performance.

8.2 Asynchronous PDF Generation:
Explain the benefits of asynchronous PDF generation to prevent blocking the UI during the process.
Utilize async/await to handle the generation process in the background.

Section 9: User Experience Considerations
9.1 Loading Indicators:
Implement loading indicators to inform users about the ongoing PDF generation process, especially for large datasets.

9.2 Providing Download Progress:
If feasible, demonstrate how to provide users with a download progress indicator to keep them informed about the PDF generation status.

Section 10: Conclusion and Future Considerations
10.1 Recap and Summary:
Summarize the key points discussed in handling large tables and optimizing PDF generation.

10.2 Future Considerations:
Encourage readers to explore other optimization techniques and libraries that may emerge in the future.
Discuss any potential updates or improvements in the jsPDF or jspdf-autotable libraries related to performance.
By addressing the challenges of handling large datasets in the blog post, you provide valuable insights to readers who may encounter similar issues in their projects. It also showcases your awareness of practical considerations when working with extensive data sets.

import React from 'react';

import jsPDF from "jspdf";
import "jspdf-autotable";

export function App(props) {
    const generatePDF = () => {
    const doc = new jsPDF("l", "mm", "legal");

    doc.text("left heading", 10, 10);
    doc.text("right header", doc.internal.pageSize.width - 10, 10, {
      align: "right",
    });
    doc.setFontSize(12);

    const jsonBody = [];
    const headerData = convertToFormDataArray(["EmployeeID","FirstName","LastName","DateOfBirth","Department","Salary"]);

  const dummyData = [
  [1, 'John', 'Doe', '1985-03-15', 'IT', 80000],
  [2, 'Jane', 'Smith', '1990-07-22', 'HR', 75000],
  [3, 'Bob', 'Johnson', '1988-11-10', 'Marketing', 85000],
  [4, 'Alice', 'Williams', '1995-05-03', 'Finance', 90000],
  [5, 'Charlie', 'Brown', '1980-09-28', 'Operations', 78000]
];

    jsonBody.push(...dummyData);

    function convertToFormDataArray(array) {
      return array.map((header) => ({ header }));
    }

    // doc.text(pdfHeader, centerX, 20);

    const addPageNumbersAndTitles = () => {
      const pageCount = doc.internal.getNumberOfPages();
      for (let i = 1; i <= pageCount; i++) {
        doc.autoTable({
          // head: [[pdfHeader], pdfField], // Use the first header for all pages
          head: [
            [
              {
                content: " table Heading 1",
                colSpan: headerData.length,
                styles: { halign: "center", fillColor: [22, 160, 133] },
              },
            ],
            [
              {
                content: " table heading 2",
                colSpan: headerData.length,
                styles: { halign: "center", fillColor: [22, 160, 133] },
              },
            ],

          ["EmployeeID","FirstName","LastName","DateOfBirth","Department","Salary"],
          ],
          body: jsonBody,
          startY: 20,
          columnStyles: { email: { cellWidth: "wrap" } },
          theme: "striped",
          didDrawPage: () => {
            const pageWidth = doc.internal.pageSize.width;
            const pageHeight = doc.internal.pageSize.height;
            doc.setFontSize(10);
            const pages = doc.internal.getNumberOfPages();
            doc.setFontSize(10);
            for (let j = 1; j < pages + 1; j++) {
              const horizontalPos = pageWidth / 2;
              const verticalPos = pageHeight - 10;
              doc.setPage(j);
              doc.text(`Page ${j}`, horizontalPos, verticalPos, {
                align: "center",
              });
            }
          },
          columns: headerData,
        });
      }
    };

    addPageNumbersAndTitles();

    doc.save(`${"pdfName"}.pdf`);
  };
   return (
    <>
      <button
        onClick={generatePDF}
        className={
          "btn-primary ml-auto flex justify-center items-center gap-2"
        }
      >

        <p>Download PDF</p>
      </button>
    </>
  );
}

Simplify Asynchronous State Management with React Query's useMutation Hook

poshiya parth s — Mon, 19 Feb 2024 04:42:44 +0000

What is useMutation?
useMutation is a hook provided by React Query that facilitates making asynchronous mutations, such as POST, DELETE, or PATCH requests to a server. It abstracts away much of the boilerplate code traditionally associated with managing asynchronous state in React applications.

Setting up useMutation
Setting up a mutation call with useMutation is straightforward. You define a mutation function that encapsulates the logic for your API call and specify callbacks for handling success and error scenarios. Let's take a look at an example:

Benefits of useMutation
Simplified Syntax: With useMutation, you can perform mutation calls with minimal code, reducing the complexity of managing asynchronous operations.

Automatic Caching and Invalidation: React Query automatically handles caching and invalidation of data, ensuring that your UI remains consistent and up-to-date.

Error Handling: The hook provides built-in error handling capabilities, allowing you to easily display error messages or handle retries.

Introduction to React Query: Provide a brief overview of React Query and its core features, such as data fetching, caching, and mutations.

Comparisons with Other State Management Solutions: Discuss how React Query's approach to handling asynchronous state differs from other solutions like Redux or traditional React state management.

Examples of Real-world Use Cases: Share examples of scenarios where useMutation can be particularly useful, such as user authentication, form submissions, or updating user profiles.

Optimistic Updates: Explain how React Query supports optimistic updates, allowing you to update the UI immediately while waiting for the mutation to complete.

Advanced Configuration Options: Dive into more advanced configuration options available with useMutation, such as specifying custom error handling logic or controlling the behavior of retries.

Testing: Discuss strategies for testing components that use useMutation, including mocking API calls and testing different mutation states.

Performance Considerations: Touch on performance considerations when using React Query, such as query and mutation caching strategies, and how to optimize performance for larger-scale applications.

Community and Resources: Highlight the active community around React Query and point readers to additional resources such as documentation, tutorials, and community forums for further learning.

A Comprehensive Guide to Using React Query's useQuery for API Calls

poshiya parth s — Thu, 15 Feb 2024 04:36:20 +0000

Introduction:
In modern React applications, managing asynchronous data fetching is crucial for delivering a seamless user experience. One powerful library that simplifies this process is react-query. This blog post will provide a comprehensive guide on how to use the useQuery hook from the react-query library for making API calls in a React application.

Setting Up the API Call Function:
Start by creating a function to handle the API call. In the provided code, getadmin is used to fetch admin information. Ensure that this function is capable of making the necessary API requests.

Creating a Custom Hook with useQuery:
Introduce a custom hook, such as useapiQuery, which utilizes useQuery from the react-query library. This hook encapsulates the logic for making API calls, managing loading states, and handling errors. Customize it based on your application's specific requirements.

Destructuring the Query Result:
Explore the various properties provided by the useQuery hook, such as data, isLoading, isFetching, error, and more. Destructuring these properties allows easy access to the current state of the data fetching process.

isLoading:
Indicates whether the query is currently in a loading state.
true if data is being fetched, false otherwise.
Useful for displaying loading indicators or conditional rendering during data retrieval.

isFetching:
Represents whether background refetching is currently occurring.
true if the query is actively fetching data in the background, false otherwise.
Useful for providing real-time updates or indicating ongoing background data refresh.

error:
Contains any error that occurred during the data fetching process.
When an error occurs, this property holds information about the error, enabling developers to handle and display error messages appropriately.

errorUpdatedAt:
Indicates the timestamp when the error state was last updated.
Useful for tracking when the error occurred and providing context on the timeline of error events.

fetchStatus:
Represents the overall status of the data fetching process.
Possible values include "idle," "loading," "success," or "error."
Offers a high-level overview of the current state, aiding in conditional rendering and logic handling.

isError:
A boolean flag indicating whether an error occurred during the data fetching process.
true if an error occurred, false otherwise.
Provides a simple way to check for errors and adjust the application's behavior accordingly.

isFetched:
A boolean flag indicating whether the data fetching process has been successfully completed.
true if the data was fetched successfully, false otherwise.
Helpful for determining if the data is available for rendering and performing subsequent actions.

Certainly! The react-query library's useQuery hook provides a variety of properties that offer insights into the status and behavior of the asynchronous query