BATCH Import CSV into Relational Database
This utility helps inserting big amount of relational data into RDB. This could be CSV or any other source. Usually to insert data into several related tables, you do this with this algorithm.
- Read CSV record
- Insert PARENT get ParentId
- Insert CHILDREN with ParentId specified
- If PARENT table has linkage to CHILDREN table as well, you need to make 3rd update query
- Repeat for all records in CSV file
With this algorithm it is slow to insert a lot of records.
This utility helps to do it, by specifing temporary keys, which then replaced with read ids when values are received.
- Read CSV record
- Fill Structure, add records for each table for one CSV row
- Repeat for all records in CSV file
- Exectute insert Function, which do it in 2 passes, make records inserts and updates references if two tables points to each other.
let knx = ;let insert knexInsert knexUpdate mapper duplicateChecker writeOutput getKey readFull = ; // 0. DECLARE STRUCTURE, keys are table names data to be inserted into// ===========================let structure = User: Phone: ; { let data = await ; // 1. FILL structure // =========================== data; console; // 2. VALIDATE DATA, check field unique // =========================== let dups = ; // log duplicates to duplicates.json await ; if dups console; return; console; // 3. PREPARE DATABASE connection and structure // tables are created just to show structure. // usually this is not needed, because you insert into existing database. // =========================== let knex = ; try await knex; catch e{} try await knex; catch e{} await knex; console; // 4. RUN INSERT PROCESS // =========================== try await ; catch e return false; finally knex; console; return true;}