As Supabase official site suggests: @snaplet/seed is a good tool to help seeding.
Yes, it is a very professional seeding tool.
But I got constantly fatal error:
supabase db reset
Resetting local database...
Recreating database...
Setting up initial schema...
Seeding globals from roles.sql...
Applying migration 20240930022453_create_tables.sql...
Seeding data from seed.sql...
failed to send batch: ERROR: invalid byte sequence for encoding "UTF8": 0xff (SQLSTATE 22021)
Try rerunning the command with --debug to troubleshoot the error.
TL;DR:
supabase/see.sql
was UTF-8
encoded at creation as VS Code status bar shows:
But, after running npx tsx seed.ts > supabase/seed.sql
, the encoding becomes UTF-16LE
:
Solution
Just click UTF-16LE
in VS Code status bar, and choose Save with encoding
:
Then select UTF-8
:
This fixed my issue. But it is a little bit tedious.
Question
Why don't write some commands to do fetching seeding data and change encoding in one go?
Tryed npx tsx seed.ts > supabase/seed.sql; Get-Content -Path "supabase/seed.sql" -Encoding Unicode | Set-Content -Path "supabase/seed.sql" -Encoding UTF8
(PowerShell) but error occurred, said the file is being used by other processes.
Yes, the VS Code opened the whole project folder.
Then try another more complex way:
# PowerShell
npx tsx seed.ts > supabase/seed.sql
# Step 0: Get the absolute path of the current folder
$basePath = Get-Location
# Step 1: Read the content of the original file
$content = Get-Content -Path (Join-Path $basePath "supabase/seed.sql") -Encoding Unicode
# Step 2: Write the content to a new temporary file with UTF-8 encoding
$tempFilePath = Join-Path $basePath "supabase/temp_seed.sql"
Set-Content -Path $tempFilePath -Value $content -Encoding UTF8
# Step 3: Remove the original file
Remove-Item -Path (Join-Path $basePath "supabase/seed.sql")
# Step 4: Rename the temporary file to the original file name
Rename-Item -Path $tempFilePath -NewName (Join-Path $basePath "supabase/seed.sql")
This worked, but, the file ended with UTF-8 BOM
, which would cause error: failed to send batch: ERROR: syntax error at or near "INSERT" (SQLSTATE 42601)
.
I pasted the content to SQL Editor, no error at all. This proved that it is due to the encoding problem. As an answer from StackOverflow mentioned:
There is no official difference between UTF-8 and BOM-ed UTF-8
A BOM-ed UTF-8 string will start with the three following bytes. EF BB BF
Those bytes, if present, must be ignored when extracting the string from the file/stream.
Top comments (1)
If you are using Powershell 5, the default encoding is UTF-8 (no BOM) so you can run the command below:
npx tsx seed.ts | Out-File -Encoding default supabase/seed.sql
Don't use
utf8
because it's gonna give UTF-8 BOM.Available encodings (version 5)
unknown,string,unicode,bigendianunicode,utf8,utf7,utf32,ascii,default,oem
If you are using Powershell 6 or higher, I think you can use
utf8NoBOM
instead.Available encodings (version >=6)
ascii,ansi,bigendianunicode,bigendianutf32,oem,unicode,utf7,utf8,utf8BOM,utf8NoBOM,utf32