Allow importing CSV files into existing DB

[avinassh]

Currently, we don't support .import statements. So, to import CSV we have to do a workaround:

1. Create a temporary DB and import CSV
2. Export the db dump
3. Import that using shell: $ turso db shell funky-falcon < my-csv-table.sql

[savokiss]

Hi, thank you very much for providing this workaround. I used the command turso db shell funky-falcon < my-csv-table.sql to import a 50MB SQL file, but it resulted in a "Transaction timed out" error. So what is the timeout limit for this type of import operation?

[billythedummy]

Hi, thank you very much for providing this workaround. I used the command turso db shell funky-falcon < my-csv-table.sql to import a 50MB SQL file, but it resulted in a "Transaction timed out" error. So what is the timeout limit for this type of import operation?

@savokiss Faced the same issue. This is a python script we had to hack together in order to get a bulk import of a csv of similar scale to work with our turso DB.

# Usage: TURSO_DB_URL=“<libsql://…>” TURSO_DB_TOKEN=“<some-jwt>” python this-script.py <path-to-csv>.csv

import libsql_experimental as libsql # pip install libsql_experimental
import os
import sys

url = os.getenv("TURSO_DB_URL")
auth_token = os.getenv("TURSO_DB_TOKEN")
path = sys.argv[1]

def to_rowstr(entries):
    in_parens = ", ".join([f"'{col}'" for col in entries])
    return f"({in_parens}),\n"

s = "INSERT INTO users (id, created_at, code) VALUES\n"
with open(path) as csvfile:
    rdr = csv.reader(csvfile)

    # maybe header row
    first = next(rdr)
    if first[0].find("id") == -1:
        s += to_rowstr(first)
    
    for row in rdr:
        s += to_rowstr(row)
    
    # replace last ',' with ';'
    last_i = s.rfind(",")
    s = s[:last_i] + ";"

conn = libsql.connect(database=url, auth_token=auth_token)
conn.executescript(s)
conn.commit()

[savokiss]

@billythedummy Thanks you very much. I have used the similar workaround in Node.js. I use readline to read the SQL file, and then execute each line in it.

[Fryuni]

A possibly more efficient workaround would be:

• Create a new DB using turso db create temp-db --from-csv
• Allow the new DB to be attached turso db config attach allow temp-db
• Connect to the DB where you want to load the data with attach permission to the temp db turso db shell --attach temp-db <target db>
• Attach the db ATTACH "temp-db" as tmp;
• Migrate the data
  • INSERT INTO ... SELECT * FROM tmp.table
  • CREATE TABLE new_table AS SELECT * FROM tmp.table
• Delete the temp DB turso db destroy temp-db