What is this?
A library providing a simple way of accessing a database and moving soft deleted rows to automatically generated tables and prevent performance erosion.
The library also allows the migration to happen on a different database.
Currently supported RDBMS are: Mysql, Sqlite.
Currently supported Node.js clients: mysql
, mysql2
, sqlite3
.
How do I install it?
npm install soft-delete-migrator
How can I use it?
Use-case 1: Migrate soft deleted rows to the same database
Given a variable mySqlConn
containing an active connection to a MySQL database, the following code will migrate all soft deleted rows from the users
table to the users_deleted
table considering all the deleted items prior to the 2022-12-31:
import { migrate } from 'soft-delete-migrator';
migrate(
mySqlConn,
{
tableName: 'users',
slaveTableName: 'users_deleted',
softDeleteColumn: 'deleted_at',
migrateCondition: 'deleted_at < ?',
migrateConditionParams: ['2022-12-31'],
limit: 500,
chunkSize: 10,
safeExecution: false,
}
).then(() => {
console.log('Migration completed');
}).catch((err) => {
console.error('Migration failed', err);
});
Use-case 2: Migrate soft deleted rows to a different db connection/client
Given two variables, mySqlConn
containing an active connection to a MySQL database and sqliteConn
containing an active connection to a SQLite database, the following code will migrate all soft deleted rows from the users
table of mySqlConn
to the users_deleted
table of sqliteConn
considering all the deleted items prior to the 2022-12-31:
import { migrate } from 'soft-delete-migrator';
migrate(
mySqlConn,
{
tableName: 'users',
slaveTableName: 'users_deleted',
softDeleteColumn: 'deleted_at',
migrateCondition: 'deleted_at < ?',
migrateConditionParams: ['2022-12-31'],
limit: 500,
chunkSize: 10,
safeExecution: false,
},
sqliteConn
).then(() => {
console.log('Migration completed');
}).catch((err) => {
console.error('Migration failed', err);
});
API
A little introduction
When talking about a master
database, we mean the database containing the table where the soft deleted rows will be migrated from.
When talking about a slave
database, we mean the database containing the table where the soft deleted rows will be migrated to.
The slave
database is optional. If not given, the library will create a table with the same name as the master table but with the _
suffix and the given/default schema.
When talking about migrateCondition
, we mean the condition to apply to the query to select the rows to migrate.
This condition will be applied while running the following query (An example of what the library does internally):
SELECT * FROM <schema>.<tableName> WHERE <softDeleteColumn> IS NOT NULL AND (<migrateCondition>) LIMIT <limit>
migrate
The library exposes a migrate
function, which executes the migration process.
The function expects the following parameters:
-
masterConnection
(required): The connection to the master database. The connection must be an instance of the following classes:mysql.Connection
,mysql2.Connection
,sqlite3.Database
. The connection must be already connected as the library will not connect it. -
_config
(required): An object containing the following properties:-
tableName
(required): The name of the master table containing the soft deleted rows. -
schema
(optional): The schema containing the table to migrate. Defaults topublic
. -
softDeleteColumn
(optional): The name of the column containing the soft delete datetime(MySQL) or timestamp (Sqlite). Defaults todeleted_at
. -
migrateCondition
(optional): The condition to apply to the query to select the rows to migrate. Defaults to1=1
. -
migrateConditionParams
(optional): The parameters to use in themigrateCondition
query. -
limit
(optional): The maximum number of rows to migrate. Defaults to1000
. -
chunkSize
(optional): The number of rows to migrate at a time. Defaults to100
. -
filePaths
(optional): An array containing two file paths. The first file path is the path used to save the queries necessary for theDELETE
queries to launch on the master instance. The second file path is the path used to save the queries necessary for theINSERT
queries to launch on the slave instance. -
safeExecution
(optional): If set totrue
, the library will not execute theDELETE
andINSERT
queries but just write them to thefilePaths
, if given. -
slaveSchema
(optional): The schema containing the slave table. Defaults toundefined
. -
slaveTableName
(optional): The name of the slave table. Defaults toundefined
. If not set, the library will use thetableName
value with the_
suffix and the given/defaultschema
. -
closeConnectionOnFinish
(optional): If set totrue
, the library will close the connection to the involved database(s) after the migration is completed. -
onInsertedChunk
(optional): A callback function to be called after each chunk of rows is inserted on the slave table. -
onDeletedChunk
(optional): A callback function to be called after each chunk of rows is deleted from the master table. -
onInsertedChunkError
(optional): A callback function to be called after each chunk of rows fails to be inserted on the slave table. Defaults to a function whichthrows
the error. -
onDeletedChunkError
(optional): A callback function to be called after each chunk of rows fails to be deleted from the master table. Defaults to a function whichthrows
the error.
-
-
slaveConnection
(optional): The connection to the slave database. If not given, the library will use themasterConnection
for both the master and the slave database.
Other functions
The library also exposes two utility functions: getConnection
and closeConnection
.
The getConnection
function expects the following parameters:
-
client
: The client to use. Can be eithermysql
,mysql2
orsqlite
. -
config
: The configuration to use to create the connection.- In case of
mysql
ormysql2
, the configuration must be an instance ofmysql.ConnectionConfig
ormysql2.ConnectionOptions
. - In case of
sqlite
, the configuration must be a string containing the path to the SQLite database.
- In case of
FAQ
Why do I need to specify the schema
?
The library uses the schema
parameter to check, in case of MySql, if the table exists in the information schema tables.
How can you ensure data integrity?
The library does all of its work in a transaction.
If the migration fails, the transaction is rolled back and the data is not migrated.
If the migration succeeds, the transaction is committed and the data is migrated.
How can you ensure that the migration is not executed twice?
The library is idempotent in itself as it just considers the rows respecting the migrateCondition
, if passed, or a simple NOT NULL
condition on the softDeleteColumn
.
If you want to ensure that the migration is not executed twice, you can use the filePaths
configuration to save the queries necessary to execute the migration, set the safeExecution
configuration to true
and then execute them manually.
Tests
You can run the tests by using the following command:
npm test
As the tests are using a real database, you need to have a MySQL running on your machine.
You can configure the connection details in the test/shared.ts
file.
The MySQL instance must have two schemas already created: soft_delete_migrator
and soft_delete_migrator_slave
.
The SQLite instances are created in memory and do not need any configuration.
Top comments (0)