# supercopy

> Takes a specifically-named directory structure of CSV files and conjures bulk insert, update and delete statements and applies them to a PostgreSQL database.

Latest version **0.0.6** (published 2017-10-06) · MIT license · 0 weekly downloads

> **Deprecated.** This package is deprecated.

## Install

```sh
npm install supercopy
pnpm add supercopy
yarn add supercopy
bun add supercopy
```

## Health

**Score 10/100 (F)** — status: deprecated.

Negative: deprecated.

## Facts

| | |
|---|---|
| Version | 0.0.6 |
| Published | 2017-10-06 |
| First published | 2017-07-08 |
| Weekly downloads | 0 |
| License | MIT |
| TypeScript types | none |
| Module format | CommonJS |
| Dependencies | 9 |
| Known vulnerabilities | 0 |
| Install scripts | no |
| GitHub stars | 121 |
| Author | Tim Needham |
| Maintainers | timneedham |
| Keywords | postgres, postgresql, copy, import, csv |

## Links

- npm: https://www.npmjs.com/package/supercopy
- Repository: https://github.com/wmfs/tymly
- Homepage: https://github.com/wmfs/tymly#readme
- Issues: https://github.com/wmfs/supercopy/issues
- npm.io page: https://npm.io/package/supercopy

## Dependencies (9)

- [pg](https://npm.io/package/pg.md) ^6.4.1
- [boom](https://npm.io/package/boom.md) ^5.2.0
- [async](https://npm.io/package/async.md) ^2.5.0
- [debug](https://npm.io/package/debug.md) ^2.6.8
- [upath](https://npm.io/package/upath.md) ^1.0.0
- [rimraf](https://npm.io/package/rimraf.md) ^2.6.2
- [csv-string](https://npm.io/package/csv-string.md) ^2.3.2
- [node-expat](https://npm.io/package/node-expat.md) ^2.3.16
- [pg-copy-streams](https://npm.io/package/pg-copy-streams.md) ^1.2.0

## Alternatives

- [csv-to-markdown-table](https://npm.io/package/csv-to-markdown-table.md) — 47.0K weekly downloads
- [@sapphire/ratelimits](https://npm.io/package/@sapphire/ratelimits.md) — 4.4K weekly downloads
- [js-csvparser](https://npm.io/package/js-csvparser.md) — 2.0K weekly downloads
- [@adadapted/js-sdk](https://npm.io/package/@adadapted/js-sdk.md) — 251 weekly downloads
- [@grapecity/spread-sheets-sparklines](https://npm.io/package/@grapecity/spread-sheets-sparklines.md) — 103 weekly downloads

## Recent versions

- 0.0.6 (latest) — 2017-10-06
- 0.0.4 — 2017-09-10
- 0.0.3 — 2017-08-19
- 0.0.2 — 2017-07-22
- 0.0.1 — 2017-07-08

## README

# supercopy

> Takes a specifically-named directory structure of CSV files and conjures bulk insert, update and delete statements and applies them to a PostgreSQL database. 

## <a name="install"></a>Install
```bash
$ npm install supercopy --save
```
Because Supercopy uses a native library please make sure you have windows-build-tools installed.
This must be done in Windows PowerShell as admin!!!
```bash
$ npm install -g windows-build-tools
```


## <a name="usage"></a>Usage

```javascript
const pg = require('pg')
const supercopy = require('supercopy')

// Make a new Postgres client
const client = new pg.Client('postgres://postgres:postgres@localhost:5432/my_test_db')

supercopy(
  {
    sourceDir: '/dir/that/holds/deletes/inserts/updates/and/upserts/dirs',
    headerColumnNamePkPrefix: '.',
    topDownTableOrder: ['departments', 'employees'],
    client: client,
    schemaName: 'my_schema',
    truncateTables: true,
    debug: true
  },
  function (err) {
    // Done!
  }
)

```
If you want to import data from an XML file, you will need to configure supercopy like this...
```javascript
supercopy(
  {
    sourceDir: '/dir/in/which/generated/csv/file/will/be/placed',
    topDownTableOrder: ['departments', 'employees'],
    client: client,
    schemaName: 'my_schema',
    truncateTables: true,
    debug: true,
    triggerElement: 'word-to-split-records-on',
    xmlSourceFile: '/path/to/target/xml/file'
  }
```
## supercopy(`options`, `callback`)

### Options

| Property              | Type       | Notes |
| --------              | ----       | ------ |
| `sourceDir`           | `function` | An absolute path pointing to a directory containing action folders. See the [File Structure](#structure) section for more details.
| `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.|
| `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`. 
| `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.
| `schemaName`          | `string`   | Identifies a PostgreSQL schema where the tables that are to be affected by this copy be found.
| `truncateTables`      | `boolean`  | A flag to indicate whether or not to truncate tables before supercopying into them
| `debug`               | `boolean`  | Show debugging information on the console

### <a name="structure"></a>File structure

The directory identified by the `sourceDir` option should be structured in the following way:

```
/someDir
  /inserts
    table1.csv
    table2.csv
  /updates
    table1.csv
    table2.csv
  /upserts
    table1.csv
    table2.csv  
  /deletes
    table1.csv
```

#### Notes

* 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`.
* The filename of each file should refer to a table name in the schema identified by the `schemaName` option. 
* The expected format of the .csv files is:
  * One line per record
  * The first line to be a comma delimited list of column names (i.e. a header record)
  * 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.
  * 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.
* Note that only primary key values should be provided in a 'delete' file.

## <a name="test"></a>Testing

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:

```PG_CONNECTION_STRING=postgres://postgres:postgres@localhost:5432/my_test_db```


```bash
$ npm test
```


## <a name="license"></a>License
[MIT](https://github.com/wmfs/supercopy/blob/master/LICENSE)

---
_Source: https://npm.io/package/supercopy · Machine-readable twin of the npm.io package page. Health data is recomputed on every publish._