DEV Community

01kg
01kg

Posted on

1

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.
Enter fullscreen mode Exit fullscreen mode

TL;DR:

supabase/see.sql was UTF-8 encoded at creation as VS Code status bar shows:

Image description

But, after running npx tsx seed.ts > supabase/seed.sql, the encoding becomes UTF-16LE:

Image description

Solution

Just click UTF-16LE in VS Code status bar, and choose Save with encoding:

Image description

Then select UTF-8:

Image description

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")

Enter fullscreen mode Exit fullscreen mode

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!

AWS Security LIVE!

Tune in for AWS Security LIVE!

Join AWS Security LIVE! for expert insights and actionable tips to protect your organization and keep security teams prepared.

Learn More

Top comments (1)

Collapse
 
danhalis profile image
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

AWS Security LIVE!

Tune in for AWS Security LIVE!

Join AWS Security LIVE! for expert insights and actionable tips to protect your organization and keep security teams prepared.

Learn More