Persist data during local development

[burmecia]

Feature request

Can we persist data when doing local development? Currently the user input data will be lost when supabase stop and start again. It will be really helpful if we can persist the data because stop&start cycle is quite often in local development.

It is even better if we can add an argument option so that user can choose to turn this switch on/off.

Is your feature request related to a problem? Please describe.

Users data should be able to persist between development sessions.

Describe the solution you'd like

Add a CLI argument option so that users can switch the data persistence on&off.

Describe alternatives you've considered

A clear and concise description of any alternative solutions or features you've considered.

Additional context

#35 is related to this, supabase/supabase#5725 is also related.

[burmecia]

I just found a workaround can achieve the same goal, you can use supabase db commit command to save changes made to a migration script, and when next time restart supabase services those changes will be automatically applied.

[samuba]

I'm using the supabase/seed.sql file for this which I populate regularly using pg_dump as a npm task.
You can also have an npm task for supabase stop which executes the pg_dump before calling supabase stop. You then never use supabase stop by itself but the npm task to make sure you never lose any changes.

My package.json looks like this:

{
	"scripts": {
		"sb:start": "supabase start",
		"sb:stop": "npm run seed:gen && supabase stop",
		"seed:gen": "dotenv -- bash -c 'pg_dump --data-only --schema=public --column-inserts --host=$DB_HOST --username=$DB_USER --port=$DB_PORT --no-password > supabase/seed.sql '",
                 
                 // Bonus: I also regularly dump the whole schema into a file to better visualize current state and changes over time: 
		"schema:gen": "dotenv -- bash -c 'pg_dump --schema-only --schema=public --host=$DB_HOST --username=$DB_USER --port=$DB_PORT  --no-password > supabase/schema.sql '"
	},
	"devDependencies": {
		"dotenv-cli": "^5.0.0",
	}
}

You need to have the pg_dump binary in your path for this to work. I'm using dotenv-cli here to load the DB credentials from a seperate .env file.

[radim10]

I agree. Much needed feature.

[sweatybridge]

related to #150

We have added supabase stop --backup flag to save a pg_dump of your local database before stopping the containers. The dumped file is stored at supabase/.branches/main/dump.sql and will be restored on supabase start.

Note that the --backup flag persists both data and schema changes between development sessions. Once you are done developing, you still want to commit them separately with schema changes in a new migration and test data in seed.sql.

[oliverbytes]

supabase stop --backup

Thanks. I just tried this. It successfully dumped but when supabase start I get "Error: ERROR: schema "auth" does not exist (SQLSTATE 3F000)"

the whole auth schema is now gone too. It happens when there's a primary key linked to auth schema.

So I just resarted the docker to fix everything again. I just thought I let you know about this.

[github-actions]

🎉 This issue has been resolved in version 1.8.5 🎉

The release is available on:

• npm package (@latest dist-tag)
• GitHub release

Your semantic-release bot 📦🚀

[sweatybridge]

Thank you for reporting this issue. I have updated the pg_dump script to include auth schemas in latest cli version.

[oliverbytes]

Thank you for reporting this issue. I have updated the pg_dump script to include auth schemas in latest cli version.

Awesome! Thank you

[dreinon]

It would be nice to persist the data also when we do a db reset. I imagine an option for the reset command to automatically populate seed.sql with the new data before reseting.