| 1 | # supercopy
|
| 2 | [](https://tymly.io/)
|
| 3 | [](https://www.npmjs.com/package/@wmfs/supercopy)
|
| 4 | [](https://circleci.com/gh/wmfs/supercopy)
|
| 5 | [](https://codecov.io/gh/wmfs/supercopy)
|
| 6 | [](https://www.codefactor.io/repository/github/wmfs/supercopy)
|
| 7 | [](https://dependabot.com/)
|
| 8 | [](http://commitizen.github.io/cz-cli/)
|
| 9 | [](https://standardjs.com)
|
| 10 | [](https://github.com/wmfs/tymly/blob/master/packages/pg-concat/LICENSE)
|
| 11 |
|
| 12 |
|
| 13 |
|
| 14 |
|
| 15 |
|
| 16 | > Takes a specifically-named directory structure of CSV files and conjures bulk insert, update and delete statements and applies them to a PostgreSQL database.
|
| 17 |
|
| 18 | ## <a name="install"></a>Install
|
| 19 | ```bash
|
| 20 | $ npm install supercopy --save
|
| 21 | ```
|
| 22 |
|
| 23 | ## <a name="usage"></a>Usage
|
| 24 |
|
| 25 | ```javascript
|
| 26 | const pg = require('pg')
|
| 27 | const supercopy = require('supercopy')
|
| 28 |
|
| 29 | // Make a new Postgres client
|
| 30 | const client = new pg.Client('postgres://postgres:postgres@localhost:5432/my_test_db')
|
| 31 |
|
| 32 | supercopy(
|
| 33 | {
|
| 34 | sourceDir: '/dir/that/holds/deletes/inserts/updates/and/upserts/dirs',
|
| 35 | headerColumnNamePkPrefix: '.',
|
| 36 | topDownTableOrder: ['departments', 'employees'],
|
| 37 | client: client,
|
| 38 | schemaName: 'my_schema',
|
| 39 | truncateTables: true,
|
| 40 | debug: true
|
| 41 | multicopy: false
|
| 42 | },
|
| 43 | function (err) {
|
| 44 | // Done!
|
| 45 | }
|
| 46 | )
|
| 47 |
|
| 48 | ```
|
| 49 |
|
| 50 | ## supercopy(`options`, `callback`)
|
| 51 |
|
| 52 | ### Options
|
| 53 |
|
| 54 | | Property | Type | Notes |
|
| 55 | | -------- | ---- | ------ |
|
| 56 | | `sourceDir` | `function` | An absolute path pointing to a directory containing action folders. See the [File Structure](#structure) section for more details.
|
| 57 | | `headerColumnNamePkPrefix` | `string` | When conjuring an `update` statement, Supercopy will need to know which columns in the CSV file constitute a primary key. It does this by expecting the first line of each file to be a header containing `,` delimited column names. However, column names prefixed with this value should be deemed a primary-key column. Only use in update CSV-file headers.|
|
| 58 | | `topDownTableOrder` | `[string]` | An array of strings, where each string is a table name. Table inserts will occur in this order and deletes in reverse - use to avoid integrity-constraint errors. If no schema prefix is supplied to a table name, then it's inferred from `schemaName`.
|
| 59 | | `client` | `client` | Either a [pg](https://www.npmjs.com/package/pg) client or pool (something with a `query()` method) that's already connected to a PostgreSQL database.
|
| 60 | | `schemaName` | `string` | Identifies a PostgreSQL schema where the tables that are to be affected by this copy be found.
|
| 61 | | `truncateTables` | `boolean` | A flag to indicate whether or not to truncate tables before supercopying into them
|
| 62 | | `debug` | `boolean` | Show debugging information on the console
|
| 63 | | `multicopy` | `boolean` | Enables 'sourceDir' to house many typical Supercopy 'sourceDir' shaped directories. Defaults to false.
|
| 64 |
|
| 65 | ### <a name="structure"></a>File structure
|
| 66 |
|
| 67 | The directory identified by the `sourceDir` option should be structured in the following way:
|
| 68 |
|
| 69 | ```
|
| 70 | /someDir
|
| 71 | /inserts
|
| 72 | table1.csv
|
| 73 | table2.csv
|
| 74 | /updates
|
| 75 | table1.csv
|
| 76 | table2.csv
|
| 77 | /upserts
|
| 78 | table1.csv
|
| 79 | table2.csv
|
| 80 | /deletes
|
| 81 | table1.csv
|
| 82 |
|
| 83 | OR IF USING MULTICOPY
|
| 84 |
|
| 85 | /manyDirs
|
| 86 | /someDir
|
| 87 | /inserts
|
| 88 | table1.csv
|
| 89 | table2.csv
|
| 90 | /someDir
|
| 91 | /inserts
|
| 92 | table1.csv
|
| 93 | table2.csv
|
| 94 |
|
| 95 | ```
|
| 96 |
|
| 97 | #### Notes
|
| 98 |
|
| 99 | * The sub-directories here refer to the type of action that should be performed using CSV data files contained in it. Supported directory names are `insert`, `update`, `upsert` (try to update, failing that insert) and `delete`.
|
| 100 | * The filename of each file should refer to a table name in the schema identified by the `schemaName` option.
|
| 101 | * The expected format of the .csv files is:
|
| 102 | * One line per record
|
| 103 | * The first line to be a comma delimited list of column names (i.e. a header record)
|
| 104 | * For update and upsert files, ensure columns-names in the header record that are part of the primary key are identified with a `headerColumnNamePkPrefix` character.
|
| 105 | * All records to be comma delimited, and any text columns containing a `,` should be quoted with a `"`. The [csv-string](https://www.npmjs.com/package/csv-string#stringifyinput--object-separator--string--string) package might help.
|
| 106 | * Note that only primary key values should be provided in a 'delete' file.
|
| 107 |
|
| 108 | ## <a name="test"></a>Testing
|
| 109 |
|
| 110 | Before running these tests, you'll need a test PostgreSQL database available and set a `PG_CONNECTION_STRING` environment variable to point to it, for example:
|
| 111 |
|
| 112 | ```PG_CONNECTION_STRING=postgres://postgres:postgres@localhost:5432/my_test_db```
|
| 113 |
|
| 114 |
|
| 115 | ```bash
|
| 116 | $ npm test
|
| 117 | ```
|
| 118 |
|
| 119 |
|
| 120 | ## <a name="license"></a>License
|
| 121 | [MIT](https://github.com/wmfs/supercopy/blob/master/LICENSE)
|