How does local development work?

[barbalex]

Before writing what could seem to be a rant I must thank you for building this extremely useful tool. It is truly amazing.

I have read https://supabase.com/docs/guides/local-development a few times.
And tried to implement it.
But I seem to have not understood the gist of it. It seems that is not explained (well).

Symptoms of my bad understanding are:

• The first time I installed the local database, I expected to be able to use what I had built on https://app.supabase.io. But that seems not to be a straightforward task, at least not without the neccessary understanding of how things work (there are separate discussions dealing with the same)
• Every (?) time I stop, then restart my local supabase (supabase stop, supabase start), I have to rebuild the entire database from null because it seems to be empty 🤔
• From looking at the available commands it seems that supabase db is some sort of git like thing. But the combination of that seeming not to be documented and me having primarily used ui tools to interact with git probably leads to me not understanding it
• I do not understand the relation between the supabase migration and supabase db commands. It seems that is not explained anywhere either. It makes me wonder that in the docs under the migration title supabase migration is not even used/demonstrated
• Every time I supabase start I now get an error (it worked for a few days): Error: Error starting database: ERROR: type "row_revs" does not exist. I have absolutely no idea how to get rid of it. supabase db reset did not help. I had to nuke the supabase folder and re-init. But it seems this happens again after running supabase db commit 😢

What I ask for is a better explanation of:

• what local db and migration are for
• how they are designed
• and how they work deriving from the design principles

Or maybe just point me to the part of the docs I missed 🙄.

[Anan7Codes]

I'm interested to know more about this as well. Specifically, If I can pull an existing project's DB schema locally and then make changes and push it back.

[Theiaz]

Thats my question too! Docs could be improved!

[clemwo]

I'm trying to do the same and get an error.
The general setup is like this:
My colleagues invited me into an already existing supabase project as a developer.
We then created a small basic expo application we want to connect to supabase remote which already works.
Now I'm trying to setup local development with the following steps:

1. npx supabase login
2. npx supabase init
3. npx supabase link --project <PROJECT_ID>
   Here I get the error

Local config differs from linked project. Try updating supabase/config.toml
and a list of values in the config.toml that apparently aren't in sync with the remote database.
As a next step I tried to pull the database schema from remote to local with

4. npx supabase db remote commit and I get the error:

ERROR: must be owner of event trigger pgsodium_trg_mask_update (SQLSTATE 42501)

Does anyone have a clue how to pull the remote database to local?
My next step would be to try to set up an empty new supabase project and see if that works.

Anyway, help is greatly appreciated and I will keep you up to date if I dont forget

[keitanamazue]

+1

[Gladministrator]

Seeing as this question is left unanswered and I was also looking for information on this today, here's what I know:

If you want to make local changes and push it to the supabase remote db you need to do a couple of things.

1. Create the local repository using the supabase init command then supabase start
2. You need to use supabase login command (you have to generate a token on the portal)
3. Next you should use the command supabase link --project-ref <project-id> this is also found from portal
4. Once linked you can make changes to the local database, say for example create a new table using pgadmin or other methods
5. Next, if you run the command supabase db diff you should see the result showing what changed and hasn't been migrated
6. Use the command supabase db diff -f <file-name>, this will output a sql file your supabase local repo in the migrations folder
7. Now you can push this file to the remote using supabase db push, this should create the table (in this case) on the remote.
8. You can also put data and have it saved when you restart by using supabase stop --backup

If you made changes on the Remote and need to "pull" those changes to local, you need to to use the supabase db remote commit command. Worth noting I had some issues if I was in a "merge conflict" scenario since the tool doesn't seem to let you resolve conflicts. You need to do some similar to a rebase but manually

You can confirm if this was successful by checking the supabase portal for your app if the table got pushed.

• The docker container restarting will cause the database to reset to its initial state that it was in when you started if it wasn't migrated
• supabase db is kind of similar to a lightweight git. You can use -h flag for some information it
• not familiar with supabase migration commands

I don't see this command supabase db commit, is there some discrepancy between your remote and local?

Hopefully someone from the supabase team can help as it's a fairly new product; still in public beta

[Anan7Codes]

I believe their partnership with Snaplet will make local dev seamless pretty soon.

[juanvilladev]

Snaplet simply clones the DB that you pass into it. Once you get into the flow of doing local development with the Supabase CLI, you'll realize it's much better. The learning curve can be rough IMO the docs aren't very good yet.

[khera]

Any advice on how to have multiple people develop the project at the same time? Is it just highly coordinated pushes to production and corresponding sync back?

[colestriler]

Things to add to this:

You should only run supabase db remote commit once. If you need to run it again, delete the /migrations folder locally and delete all the migrations in the supabase_migrations table on the supabase dashboard (see attached img).

Once you have migration files in the /migrations folder, the way to apply these to your local db is by running supabase db reset. If this runs without failure, your local schema should now match your production schema.

One error I kept getting when running supabase db reset:

Error: ERROR: there is no unique constraint matching given keys for referenced table "users" (SQLSTATE 42830)
At statement 56: --                                                                                           
-- Name: user user_email_fkey; Type: FK CONSTRAINT; Schema: public; Owner: supabase_admin    
--                                                                                           
                                                                                             
ALTER TABLE ONLY "public"."user"                                                             
    ADD CONSTRAINT "user_email_fkey" FOREIGN KEY ("email") REFERENCES "auth"."users"("email")

The fix was to remove the foreign key relationship on email between my user table and the auth users table. Reason is explained here: https://discord.com/channels/839993398554656828/1065605053328666674/1071090254339125248.

[lucksp]

What if I want to promote changes from local to staging, prior to production. Such as, I want to have a different user DB between dev vs production. I will need to test out 1) app subscription permissions. So I want to test out that a user can only see the app if they have paid for it, not just authorized a session.

[ChuckJonas]

does anyone know how you get your supabase docker containers to update to the latest image? Is it just updating the supabase cli?

[jacktho]

Yeah, just update the cli and supabase stop and supabase start.