db import/export or dump?

[fritx]

Is there any method to import/export a db to any kind of dump file?
Related issue: mattn/go-sqlite3#305

I want to make a db migration with sqlcipher, and I also tried to include https://github.com/jayralencar/sqlite-cipher.js but it didn't work well with this one, probably due to the varied iv.

[tmcw]

Yep, answer is the same as over there: import & export commands are part of the sqlite3 binary, not of SQL.

[MartinMuzatko]

But is there a way to do this via node bindings? I don't want to execute the command via shell and then feed the output back into js

[fritx]

@MartinMuzatko hope that helps!

I had written a method to do the whole migration on the fly before:
https://gist.github.com/fritx/38609ea0590e4de532e25d1ecfd24b69

• db encryption in both win/mac
• one by one, tables and dbs
• conditional filters

async function migrate (from, to, filter) {
  // ...
  for (let { name, sql } of tables) {
    // ...
    // 避免大小超出限制 数据分批导入
    const pageSize = 1000
    /* eslint-disable no-constant-condition */
    for (let i = 0; true; i++) {
      const rows = await t2p(cb => {
        from.all(`SELECT * FROM "${name}"
          ${condition}
          LIMIT ${pageSize}
          OFFSET ${i * pageSize}`, cb)
      })
      if (rows.length <= 0) break

      const row0 = rows[0]
      const keys = rowKeys(row0)
      const keyHolder = keys.join(',')

      // 拼接成一条sql 速度快
      const valuesHolder = rows.reduce((m, row) => {
        const values = rowValues(keys, row)
        return m.push('(' + values.map(escape).join(', ') + ')'), m
      }, []).join(', ')
      sql = `INSERT OR REPLACE INTO "${name}" (${keyHolder}) VALUES ${valuesHolder}`
      await t2p(cb => to.run(sql, cb))
      console.log(`migrated table ${name} rows * ${rows.length}`)
    }
    console.log(`migrated table ${name} finish`)
  }
  // ...
}

[MartinMuzatko]

Thanks for the effort. Does this also create CREATE TABLE statements?

[fritx]

Yes, they were all clipped. It's hard to find the original full code, I will try ;)

[MartinMuzatko]

If anyone is interested. this is how we do schema dumps right now, to check in diffs of sqlite table changes:

https://gist.github.com/MartinMuzatko/e3a4fc3b9fc380e74dfdc376da67329d