pg-copy-streams

What is pg-copy-streams?

The pg-copy-streams npm package provides a way to perform high-performance data import and export operations with PostgreSQL using the COPY command. It allows you to stream data to and from PostgreSQL databases efficiently.

What are pg-copy-streams's main functionalities?

COPY FROM

This feature allows you to import data into a PostgreSQL table from a file using the COPY FROM command. The code sample demonstrates how to stream data from a CSV file into a PostgreSQL table.

const { Client } = require('pg');
const copyFrom = require('pg-copy-streams').from;
const fs = require('fs');

const client = new Client();
client.connect();

const stream = client.query(copyFrom('COPY my_table FROM STDIN'));
const fileStream = fs.createReadStream('data.csv');
fileStream.pipe(stream).on('finish', () => {
  console.log('Data imported successfully');
  client.end();
});

COPY TO

This feature allows you to export data from a PostgreSQL table to a file using the COPY TO command. The code sample demonstrates how to stream data from a PostgreSQL table into a CSV file.

const { Client } = require('pg');
const copyTo = require('pg-copy-streams').to;
const fs = require('fs');

const client = new Client();
client.connect();

const stream = client.query(copyTo('COPY my_table TO STDOUT'));
const fileStream = fs.createWriteStream('data.csv');
stream.pipe(fileStream).on('finish', () => {
  console.log('Data exported successfully');
  client.end();
});

Other packages similar to pg-copy-streams

pg

The 'pg' package is the official PostgreSQL client for Node.js. While it does not provide streaming capabilities for the COPY command out of the box, it can be used in conjunction with other libraries to achieve similar functionality. It is a more general-purpose library for interacting with PostgreSQL databases.

pg-promise

The 'pg-promise' package is a PostgreSQL interface for Node.js that provides a more powerful and flexible API compared to the 'pg' package. It supports a wide range of features, including transactions and connection pooling, but does not natively support streaming for the COPY command. However, it can be extended to work with streaming libraries.

pg-boss

The 'pg-boss' package is a job queue built on top of PostgreSQL. While it is not directly related to streaming data with the COPY command, it provides a way to manage background jobs and tasks using PostgreSQL as the backend. It is useful for scenarios where you need to process large amounts of data asynchronously.

README

pg-copy-streams

COPY FROM / COPY TO for node-postgres. Stream from one database to another, and stuff.

how? what? huh?

Did you know the all powerful PostgreSQL supports streaming binary data directly into and out of a table? This means you can take your favorite CSV or TSV or whatever format file and pipe it directly into an existing PostgreSQL table. You can also take a table and pipe it directly to a file, another database, stdout, even to /dev/null if you're crazy!

What this module gives you is a Readable or Writable stream directly into/out of a table in your database. This mode of interfacing with your table is very fast and very brittle. You are responsible for properly encoding and ordering all your columns. If anything is out of place PostgreSQL will send you back an error. The stream works within a transaction so you wont leave things in a 1/2 borked state, but it's still good to be aware of.

If you're not familiar with the feature (I wasn't either) you can read this for some good helps: http://www.postgresql.org/docs/9.3/static/sql-copy.html

examples

pipe from a table to stdout

var pg = require('pg');
var copyTo = require('pg-copy-streams').to;

pg.connect(function(err, client, done) {
  var stream = client.query(copyTo('COPY my_table TO STDOUT'));
  stream.pipe(process.stdout);
  stream.on('end', done);
  stream.on('error', done);
});

pipe from a file to table

var fs = require('fs');
var pg = require('pg');
var copyFrom = require('pg-copy-streams').from;

pg.connect(function(err, client, done) {
  var stream = client.query(copyFrom('COPY my_table FROM STDIN'));
  var fileStream = fs.createReadStream('some_file.tsv')
  fileStream.on('error', done);
  fileStream.pipe(stream).on('finish', done).on('error', done);
});

install

$ npm install pg-copy-streams

notice

This module only works with the pure JavaScript bindings. If you're using require('pg').native please make sure to use normal require('pg') or require('pg.js') when you're using copy streams.

Before you set out on this magical piping journey, you really should read this: http://www.postgresql.org/docs/9.3/static/sql-copy.html, and you might want to take a look at the tests to get an idea of how things work.

contributing

Instead of adding a bunch more code to the already bloated node-postgres I am trying to make the internals extensible and work on adding edge-case features as 3rd party modules. This is one of those.

Please, if you have any issues with this, open an issue.

Better yet, submit a pull request. I love pull requests.

Generally how I work is if you submit a few pull requests and you're interested I'll make you a contributor and give you full access to everything.

Since this isn't a module with tons of installs and dependent modules I hope we can work together on this to iterate faster here and make something really useful.

license

The MIT License (MIT)

Copyright (c) 2013 Brian M. Carlson

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.