sql-partial-dump

Partial dumper for SQL

Usage no npm install needed!

<script type="module">
  import sqlPartialDump from 'https://cdn.skypack.dev/sql-partial-dump';
</script>

README

sql-partial-dump

This tool is used to produce coherent partial SQL dumps based on a list of "seed" SELECT queries, then by following foreign keys to ensure needed related data is also present in the dump.

Installation

npm install -g sql-partial-dump

Usage

A contextual help is available. Just type sql-partial-dump in a shell to see the available options.

Dump

The mostly used command is sql-partial-dump dump, which produces a SQL file containing a list of INSERT statements, based on a configuration file in javascript format.

An example file is provided in the examples folder of this project.

The output SQL script is intended to be executed on an empty database. You probably want to apply first a schema dump generated, for example, by mysqldump --no-data, which will ensure that the tables exist and are empty.

You can also use the sql-partial-dump empty command, which will empty all the tables in the given schema (see below).

Please note that this tool does not touch the database schema, indexes, views, stored procedures, etc. Only INSERT statments are generated.

The foreign key constraints should not be a problem, as this tool handles insertion ordering according to the configuration file and foreign keys defined in the source database.

All the command-line options can be passed as classic options (--the-option) or using environment variables prefixed with SPD_. Environment variables are strongly recommended for --password option, to prevent your database password to appear in your OS' processes list (and related logs !).

For example :

SPD_PASSWORD=myPassword sql-partial-dump dump /path/to/my/spd-config.yml -u myDbUser -h myDbHost -s myDbSchema > inserts.sql

Empty

This command generates DELETE or TRUNCATE statments for each table found in the provided schema. This is useful to make sure that the tables are empty before executing the INSERTs generated by sql-partial-dump dump (see above).

For example :

SPD_PASSWORD=myPassword sql-partial-dump empty -u myDbUser -h myDbHost -s myDbSchema > empty.sql