Import & Export CSV Data

Whether you have a need to extract or insert data into your database with CSV, this guide will help you.

As a feature of StarbaseDB, we allow users to both import and export data into/from their database in the most convenient ways to them. This guide will walk you through how to do both of the operations with CSV file formats.

You can optionally import data from a file directly by uploading it (file size limits may apply) or by passing the CSV body into the API request.

URL Structure

To call the proper API route you can examine the following URL structure.

https://starbasedb.YOUR-IDENTIFIER.workers.dev/import/csv/{YOUR_TABLE_NAME}

Host part of the URL is where your database is accessible from, followed by /import/csv/ which begins routing your request to the application logic which will handle importing CSV data. The last path component is where you will put your specific table name (e.g. users) to indicate where you would like this data to be appended to.

Column Mapping

As you pass data into the API you can also tell the system to map your CSV column keys into different names that your database columns may refer to them as. For example, if your CSV has the column key fname but your database refers to that field as first_name then you would want to tell the API to map fname → first_name so it imports it into the table correctly.

Below is an example cURL:

curl --location 'https://starbasedb.YOUR-IDENTIFIER.workers.dev/import/csv/users' \
--header 'Authorization: Bearer ABC123' \
--header 'Content-Type: application/json' \
--data '{
  "data": "fname,lname\nJohn,Doe\nJane,Smith",
  "columnMapping": {
    "fname": "first_name",
    "lname": "last_name"
  }
}'

Import from Body

If you have a preference of sending in the CSV data you want appended to a table by passing it in as a CSV object to the API route (instead of a file) you can do so with a data param. Below is an example.

curl --location 'https://starbasedb.YOUR-IDENTIFIER.workers.dev/import/csv/users' \
--header 'Authorization: Bearer ABC123' \
--header 'Content-Type: application/json' \
--data '{
  "data": "name,email\nJohn Doe,john@example.com\nJane Smith,jane@example.com",
}'

The response you receive from the API endpoint in an ideal situation where no entry failed will look like this:

Copy{
    "result": {
        "message": "Imported 2 out of 2 records successfully. 0 records failed.",
        "failedStatements": []
    }
}

While a failing response of some, or all, entries may look like the following:

Copy{
    "result": {
        "message": "Imported 0 out of 2 records successfully. 2 records failed.",
        "failedStatements": [
            {
                "statement": "INSERT INTO users (database_column1, database_column2) VALUES (?, ?)",
                "error": "Unknown error"
            },
            {
                "statement": "INSERT INTO users (database_column1, database_column2) VALUES (?, ?)",
                "error": "Unknown error"
            }
        ]
    }
}

Import from File

You can also append new data into an existing SQL table from a CSV file by adding the file to your request instead of providing a data body object. Below is an example:

curl --location 'https://starbasedb.YOUR-IDENTIFIER.workers.dev/import/csv/users' \
--header 'Authorization: Bearer ABC123' \
--form 'file=@"/path/to/your/csvfile.csv"'

Export Table Data

It is quite simple to export all of your table data into a CSV file. By using the below cURL in your own command line tool such as Terminal and changing the last URL path component to be the table name you want to export data from, StarbaseDB will aggregate your data and output it into a local output.csv file wherever you initiate this command from on your machine.

Copycurl
--location 'https://starbasedb.YOUR-ID-HERE.workers.dev/export/csv/users' \
--header 'Authorization: Bearer ABC123'
--output output.csv
Updated on
JSON Data