DEV Community

Cover image for How to bulk insert your data to Supabase with CSV
Raziq Din
Raziq Din

Posted on

How to bulk insert your data to Supabase with CSV

#programming #database #webdev #tutorial

If you’re working with Supabase and want to import data from a CSV file, the key to a smooth import is proper CSV structure. A poorly structured CSV can lead to errors, rejected rows, or data mismatches. This article walks you through everything you need to know to prepare your CSV for Supabase.

 

Supabase CSV

 

For clarification, Supabase provides a built-in feature that lets you bulk insert data into a database table using a CSV file, making it easy to import large amounts of data without writing any code. This is especially useful when inserting data one by one is impractical and when you need to ensure that each record is linked to the correct foreign key in one whole insert chunk.

 

The use case might differ , but this method is very efficient and could be useful to some of the readers out there!

 

Drag and Drop CSV to supabase

 

To go through this tutorial , we will bring you to a situation , lets say if you are building a CMS that also stores quizzes for users , there are many situations we can think about simply by going through this task. To simplify this , we will separate the csv column and data structure in two situations :

 

1.Questions stored in a single table ( No FK required )

Let's say we have a table column defined in our Supabase like this:

quiz_questions
--------------
id (uuid, pk)
question_text (text)
option_a (text)
option_b (text)
option_c (text)
option_d (text)
correct_option (text)
Enter fullscreen mode Exit fullscreen mode

there are no foreign keys involved, to do this , you can construct your CSV files according to these columns below:

question_text,option_a,option_b,option_c,option_d,correct_option
What is the capital of France?,Berlin,Madrid,Paris,Rome,C
Which language runs in the browser?,Java,Python,JavaScript,C++,C
Enter fullscreen mode Exit fullscreen mode

notice that I did not put the id values in my csv structure , this is because you don't have to provide it in the first place! You can simply leave it and let Supabase generate the id for you.

 

Disclaimer : ensure that your ID in Supabase is auto generated by the system itself to ensure non existence error occurence when submitting your csv file to Supabase.

 

2.Questions and options stored in separate tables (FK required)

This structure is used when questions and options are normalized into different tables and linked using foreign keys. In this case , our questions and options are connected using a foreign key (FK).

Questions Table

questions
---------
id (uuid, pk)
question_text (text)
Enter fullscreen mode Exit fullscreen mode

Options Table

id (uuid, pk)
question_id (uuid, fk → questions.id)
option_text (text)
is_correct (boolean)
Enter fullscreen mode Exit fullscreen mode

To put the data in your csv in case like this , we will put it based on this structure:

Questions.csv

question_text
What is the capital of France?
Which language runs in the browser?
Enter fullscreen mode Exit fullscreen mode

Options.csv

question_id,option_text,is_correct
4f9e1a2b-3c4d-4e5f-8a9b-0c1d2e3f4a5b,Berlin,false
4f9e1a2b-3c4d-4e5f-8a9b-0c1d2e3f4a5b,Madrid,false
4f9e1a2b-3c4d-4e5f-8a9b-0c1d2e3f4a5b,Paris,true
4f9e1a2b-3c4d-4e5f-8a9b-0c1d2e3f4a5b,Rome,false
d9876543-210e-4fba-9c8d-7e6d5c4b3a21,Java,false
d9876543-210e-4fba-9c8d-7e6d5c4b3a21,Python,false
d9876543-210e-4fba-9c8d-7e6d5c4b3a21,JavaScript,true
d9876543-210e-4fba-9c8d-7e6d5c4b3a21,C++,false
Enter fullscreen mode Exit fullscreen mode

Notice that we have unique ids in our question_id , in order to get these ids , you need to ensure to grab the question id from Supabase tables data and paste it respectively to your CSV files and its related data. Check out the note below for more clear clarification!

 

Note: For columns that have foreign key constraints, you need to provide the related primary key values in your CSV file so that Supabase can correctly associate the records and successfully insert the bulk data.

 

So there you have it! To wrap this up , here is a little trick and tips for you when doing this task:

  1. Use snake_case for headers (eg , course_title, created_at) for consistency between your Supabase DB and CSV files

  2. Keep text simple: avoid emojis or formatting.

  3. Treat the CSV like a database migration: structured, clean, and precise.

  4. Test a small CSV first before importing thousands of rows.

 

For more info , simply refer to Supabase's official documentation on this task! : Supabase CSV Data Insert

 

Conclusion

 
Getting your CSV right makes everything with Supabase so much easier. When your headers match your table, your data types make sense, and your formatting is clean, the import process becomes quick and painless. A little prep goes a long way; double-check your file, test with a small batch first, and fix any issues early. Once that’s done, you’ll be able to bulk insert data confidently without the usual headaches.

 

Thank you everyone! Happy weekend!

Top comments (0)