row-mapper
v1.0.1
Published
Efficiently move/map rows between PostgreSQL database tables
Downloads
10
Maintainers
Readme
row-mapper
Efficiently move/map rows between PostgreSQL database tables.
Install
$ npm i row-mapper
Use case
Assume you have a table named users
and you want to move all it's rows
to another table named archived_users
- which could reside in another
database.
While you move the data you might also need to convert each row's data to another format.
Here's how you would do it:
'use strict'
const RowMapper = require('row-mapper')
const mapper = new RowMapper({
// The source database
source: {
// DB settings
client: 'pg',
connection: {
host: '127.0.0.1',
user: 'foo',
password: 'bar_pass',
database: 'foo_db'
},
// The table to get data from.
table: {
name: 'users',
primaryKey: 'id_user'
}
},
/*
The target database, where data would be moved to.
- This can be the same as the source DB.
*/
target: {
// DB settings
client: 'pg',
connection: {
host: '127.0.0.2',
user: 'foo',
password: 'foo_pass',
database: 'bar_db'
},
// The table to insert data to.
table: {
name: 'archived_users',
primaryKey: 'id_user',
/*
Name of the sequence associated with the primary key.
- Upon succesful completion, this sequence will be
set to the max value of the primary key column of the target table.
*/
primaryKeySequence: 'user_id_user_seq'
}
},
// How many rows to process, per chunk. Defaults to 2000.
chunkSize: 3000,
/*
This mapper function will run for *each* row of the source table.
- You can convert each row here before you insert it to your target table.
*/
mapperFn: (row, i) => {
return Object.assign(row, {
archived_date: new Date(),
archiver: 'John Doe'
}
}
})
// Then just run the mapper
mapper.start().then(result => {
console.log('Success')
})
.catch(err => {
console.error(err)
})
Features
You can convert huge tables. This module processes rows in chunks, so it avoids a lot of network roundtrips whilst keeping memory usage low since we it doesn't load the whole table in-memory.
Successfully inserted/mapped rows are saved in a cache. When you start processing again, the module will skip processed rows and continue from the last known succesful row.
Tests
$ npm test
Important: Tests are run against an in-memory SQLite database and do not reset the table PK sequences after they are done.
Authors
License
MIT