Supabase | Seeding data: The Problem (ERROR: SQLSTATE 22021) and Solutions

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.

If you know how to figure this out, please leave a comment!

Comments

[Dan Halis]

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