bulk insert using CSV file

[mizuochi-hikaru]

To initialize DB using CSV file, I'm trying bulk import like issue #830 .

import { Connection, createConnection } from 'mysql2/promise'
import * as fs from 'fs'
import * as csvSync from 'csv-parse/lib/sync'

type Table = {
    name: string,
    idColumnName: string
}

await function insert(filePath: string, table: Table): Promise<void> {
    const connection = createConnection({ host, port, user, password, database })
    const data: any[][] = csvSync(fs.readFileSync(filePath, 'utf-8'), {
        cast: (value, _) => (value.toLowerCase() === 'null' ? null : value),
    })
    const query = `INSERT INTO ${table.name} VALUES (?) ON DUPLICATE KEY UPDATE ${table.idColumnName}=${table.idColumnName}`
    // it doesn't work well
    await connection.query(query, data)
    // it works, but I feel it's verbose
    // await Promise.all(csv.data.map(row => connection.query(query, [row])))
}

As a result, only 1st record of the CSV file inserted into DB.

I put the data to array:

await connection.query(query, [data])

It become error. 'sql' has unnecessary brackets.

code:'ER_WRONG_VALUE_COUNT_ON_ROW'
errno:1136
message:'Column count doesn't match value count at row 1'
sql:'INSERT INTO administrators VALUES ((...record 1...), (...record 2...), (...record 3...)) ON DUPLICATE KEY UPDATE id=id'

For trial:

await connection.query(query, data.map(row => [row]))

and then, error was:

code:'ER_WRONG_VALUE_COUNT_ON_ROW'
errno:1136
message:'Column count doesn't match value count at row 1'
sql:'INSERT INTO administrators VALUES ((...record 1...)) ON DUPLICATE KEY UPDATE id=id'

The 1st record only was tried to insert (and failed).

How can I insert CSV file without shredding?

[mizuochi-hikaru]

"csv-parse": "4.16.3",
"mysql2": "^3.1.2",
"ts-node": "^8.7.0",
"typescript": "^3.8.3"

$ node -v                   
v19.6.0

[sidorares]

The easiest way to figure out how client side interpolation works is to simplify your example and 1) factor out CSV parsing ( just copy couple of rows of data and have them hardcoded and 2) factor out db communication. you can just use console.log(mysql.escape('sql, parameters)) for quicker iterations - play around until you get the correct valid sql syntax

If you can paste your starting point with hardcoded data and sql query I can try to help you along

[mizuochi-hikaru]

1. factor out CSV parsing
2. factor out db communication

I simplified the code like this:

const connection = createConnection({ host, port, user, password, database })
const data = [[/* record 1 */], [/* record 2 */], [/* record 3 */]]
console.log(connection.format(query, data))         // (A)
console.log(connection.format(query, [data]))       // (B)

as a result I got:

(A)
INSERT INTO administrators VALUES (...record 1...) ON DUPLICATE KEY UPDATE id=id

(B)
INSERT INTO administrators VALUES ((...record 1...), (...record 2...), (...record 3...)) ON DUPLICATE KEY UPDATE id=id

I cannot paste raw data here, so I try these with simple tables.

[mizuochi-hikaru]

DB:

create table users (id int, name varchar(64));

index.ts:

import { createConnection } from 'mysql2/promise'

async function main() {
    const connection = await createConnection({
        host: 'localhost',
        port: 3306,
        user: 'root',
        password: 'root',
        database: 'mysql2-test'
    })

    const data: any[][] = [['1', 'user 1'], ['2', 'user 2'], ['3', 'user 3']];

    const query = `INSERT INTO users VALUES (?) ON DUPLICATE KEY UPDATE id = id`

    console.error(connection.format(query, data))
    console.error(connection.format(query, [data]))
    await connection.query(query, [data])

    connection.destroy()
}

main().catch(e => {
    console.error(e)
})

result:

INSERT INTO users VALUES ('1', 'user 1') ON DUPLICATE KEY UPDATE id = id
INSERT INTO users VALUES (('1', 'user 1'), ('2', 'user 2'), ('3', 'user 3')) ON DUPLICATE KEY UPDATE id = id
Error: Column count doesn't match value count at row 1
    at PromiseConnection.query (....../mysql2-test/node_modules/mysql2/promise.js:93:22)
    at main (......mysql2-test/index.ts:18:22)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5) {message: 'Column count doesn't match value count at row 1', code: 'ER_WRONG_VALUE_COUNT_ON_ROW', errno: 1136, sql: 'INSERT INTO users VALUES (('1', 'user 1'), ('2…3', 'user 3')) ON DUPLICATE KEY UPDATE id = id', sqlState: '21S01', …}

What can I fix?

[mizuochi-hikaru]

ohhhhhh
I found this

// NG
const query = `INSERT INTO users VALUES (?) ON DUPLICATE KEY UPDATE id = id`
// OK
const query = `INSERT INTO users VALUES ? ON DUPLICATE KEY UPDATE id = id`

I am sorry for bothering you...

[sidorares]

I'm glad you found it yourself!