cli: CLI / local dev: "supabase db dump" creates broken SQL

Bug report

  • I confirm this is a bug with Supabase, not with my own application.
  • I confirm I have searched the Docs, GitHub Discussions, and Discord.

Describe the bug

I have inserted ~130k records into a hosted SB DB using the official JS client without any issues. When I then dump it into a seed.sql on local dev environment and try to reset the DB, it runs into an error.

To Reproduce

  1. Open a local development environment
  2. supabase db dump -f supabase/seed.sql --data-only:
Dumping data from remote database...
pg_dump: warning: there are circular foreign-key constraints among these tables:
Dumped schema to supabase/seed.sql.
  1. supabase db reset:
Resetting local database...
Initializing schema...
Applying migration 20230530183107_base.sql...
Applying migration 20230530184344_base.sql...
Applying migration 20230530185011_foreign_keys.sql...
Seeding data supabase\seed.sql...
Restarting containers...
Error: ERROR: syntax error at or near "Villa" (SQLSTATE 42601)
  1. Inspecting the related record in the DB shows that there is mulit-line TEXT value with some lines starting with --

EDIT: When I try supabase db reset --debug, it runs for much longer before it stops due to same error.

Expected behavior

The command supabase db reset must restore all records without any errors from seed.sql file that was generated by the dump command, esp. considering that they were originally inserted without any issues using the official SB JS client.

System information

  • OS: Windows 11
  • Version of supabase-js: n/a
  • Version of Node.js: v16.17.0
  • Version of Supabase CLI: 1.64.8

Additional context

A workaround to clean the current records of any “problematic” characters will be too much work. Also I don’t want to manipulate future inputs from my users, as we are talking about e.g. generic “Notes” fields here and they should be able to write whatever they like there.

Side note: supabase db reset is also very slow. It takes 36s to get to the line ~3000 which causes the error. So if I extrapolate it to ~130,000 lines, it will take around 25min. We haven’t gotten into a normal development cycle yet, so depending on how often we’ll need to run it, it can be a real issue.

About this issue

  • Original URL
  • State: closed
  • Created 3 years ago
  • Comments: 19 (9 by maintainers)

Most upvoted comments

so we “seed” all the DBs (incl. dev / staging) in same way as we will go live: uploading from a huge JSON file using Supabase JS client.

Yup, this is a missing feature of cli that we have punted for a while now. We will probably revisit this in a few weeks time.

It would be great if Supabase CLI had an option to anonymize data.

We have plans to add postgresql anonymizer extension to both our platform and local dev offering. It supports dynamic masking which allows you to configure anonymization rules when table columns are accessed by specific users. I don’t have an estimate on the timeline but you can follow that PR to know when it’s reopened.

I tried it. It was still running after 3+ hours xD The file has around 100k lines.

Let me know how else I can help.

FYI currently, while we are still migrating from the legacy system, I’m populating all environments in the same way - from a JSON file. But once we went live on SB, I plan to use your tooling for DevOps, e.g. regularly refreshing dev DBs with fresh Prod data, though I still need to do extra bits like anonymizing / obfuscating data, for which I might use Snaplet or end up using my own scripts after all 😦