Copying data from one DB to another with node-sqlite - formatting the 'insert' statement

Question

I'm writing a small utility to copy data from one sqlite database file to another. Both files have the same table structure - this is entirely about moving rows from one db to another.

My code right now:

let tables: Array<string> = [
        "OneTable", "AnotherTable", "DataStoredHere", "Video"
    ]

tables.forEach((table) => {
    console.log(`Copying ${table} table`);

    sourceDB.each(`select * from ${table}`, (error, row) => {
        console.log(row);
        destDB.run(`insert into ${table} values (?)`, ...row) // this is the problem
    })
})

row here is a js object, with all the keyed data from each table. I'm certain that there's a simple way to do this that doesn't involve escaping stringified data.

Answer 1

You could iterate over the row and setup the query with dynamically generated parameters and references.

let tables: Array<string> = [
        "OneTable", "AnotherTable", "DataStoredHere", "Video"
    ]

tables.forEach((table) => {
    console.log(`Copying ${table} table`);

    sourceDB.each(`select * from ${table}`, (error, row) => {
        console.log(row);
        const keys = Object.keys(row); // ['column1', 'column2']
        const columns = keys.toString(); // 'column1,column2'
        let parameters = {};
        let values = '';

        // Generate values and named parameters
        Object.keys(row).forEach((r) => {
          var key = '$' + r;
          // Generates '$column1,$column2'
          values = values.concat(',', key);
          // Generates { $column1: 'foo', $column2: 'bar' }
          parameters[key] = row[r];
        });

        // SQL: insert into OneTable (column1,column2) values ($column1,$column2)
        // Parameters: { $column1: 'foo', $column2: 'bar' }
        destDB.run(`insert into ${table} (${columns}) values (${values})`, parameters);
    })
})