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.

score 3 · Answer 1 · answered Apr 05 '17 at 09:22

3

If your database driver has not blocked ATTACH, you can simply tell the database to copy everything:

ATTACH '/some/where/source.db' AS src;
INSERT INTO main.MyTable SELECT * FROM src.MyTable;

answered Apr 05 '17 at 09:22

CL.

173,858
17
217
259

score 2 · Accepted Answer · answered Apr 05 '17 at 07:53

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);
    })
})

score 0 · Answer 3 · answered Apr 10 '19 at 02:35

Tried editing the answer by @Cl., but was rejected. So, adding on to the answer, here's the JS code to achieve the same:

let sqlite3 = require('sqlite3-promise').verbose();
let sourceDBPath = '/source/db/path/logic.db';
let tables = ["OneTable", "AnotherTable", "DataStoredHere", "Video"];
let destDB = new sqlite3.Database('/your/dest/logic.db');
await destDB.runAsync(`ATTACH '${sourceDBPath}' AS sourceDB`);
await Promise.all(tables.map(table => {
    return new Promise(async (res, rej) => {
        await destDB.runAsync(`
            CREATE TABLE ${table} AS
            SELECT * FROM sourceDB.${table}`
        ).catch(e=>{
            console.error(e);
            rej(e);
        });
        res('');
    })
}));

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

3 Answers3