pg-types

What is pg-types?

The pg-types package is a collection of type parsers and formatters for use with the node-postgres family of packages. It allows for the customization of how database types are parsed into JavaScript types and vice versa, enhancing the flexibility and control over data serialization and deserialization processes when interacting with PostgreSQL databases.

What are pg-types's main functionalities?

Custom Type Parsing

This feature allows for the customization of how specific PostgreSQL types are parsed into JavaScript types. In the code sample, the setTypeParser method is used to parse PostgreSQL numeric types (type OID 1700) into JavaScript floats.

const { types } = require('pg');
types.setTypeParser(1700, (val) => parseFloat(val));

Array Parsing

Enables the parsing of PostgreSQL array types into JavaScript arrays. The code sample demonstrates how to parse an array of integers (type OID 1007) using a custom parsing function that utilizes the postgres-array package.

const { types } = require('pg');
const parseArray = require('postgres-array').parse;
types.setTypeParser(1007, (val) => parseArray(val, parseInt));

Other packages similar to pg-types

pg-promise

pg-promise is a PostgreSQL interface for Node.js. It offers more comprehensive features for database interaction, including connection pooling, query formatting, and transaction management. While it also allows for type parsing customization, pg-types is more focused and lightweight, specifically targeting type parsing and formatting.

sequelize

Sequelize is an ORM for Node.js that supports PostgreSQL, MySQL, MariaDB, SQLite, and Microsoft SQL Server. It provides a higher-level abstraction for database interactions, including type casting, but does not offer the same level of direct control over type parsing as pg-types. Sequelize is better suited for applications requiring an ORM rather than low-level type control.

README

pg-types

This is the code that turns all the raw text from postgres into JavaScript types for node-postgres

use

This module is consumed and exported from the root pg object of node-postgres. To access it, do the following:

var types = require('pg').types

Generally what you'll want to do is override how a specific data-type is parsed and turned into a JavaScript type. By default the PostgreSQL backend server returns everything as strings. Every data type corresponds to a unique OID within the server, and these OIDs are sent back with the query response. So, you need to match a particluar OID to a function you'd like to use to take the raw text input and produce a valid JavaScript object as a result. null values are never parsed.

Let's do something I commonly like to do on projects: return 64-bit integers (int8) as JavaScript integers. Because JavaScript doesn't have support for 64-bit integers node-postgres cannot confidently parse int8 data type results as numbers because if you have a huge number it will overflow and the result you'd get back from node-postgres would not be the result in the datbase. That would be a very bad thing so node-postgres just returns int8 results as strings and leaves the parsing up to you. Let's say that you know you don't and wont ever have numbers greater than int4 in your database, but you're tired of recieving results from the COUNT(*) function as strings (because that function returns int8). You would do this:

var types = require('pg').types
types.setTypeParser(20, function(val) {
  return parseInt(val)
})

boom: now you get numbers instead of strings.

Just as another example -- not saying this is a good idea -- let's say you want to return all dates from your database as moment objects. Okay, do this:

var types = require('pg').types
var moment = require('moment')
var TIMESTAMPTZ_OID = 1184
var TIMESTAMP_OID = 1114
var parseFn = function(val) {
   return val === null ? null : moment(val)
}
types.setTypeParser(TIMESTAMPTZ_OID, parseFn)
types.setTypeParser(TIMESTAMP_OID, parseFn)

note: I've never done that with my dates, and I'm not 100% sure moment can parse all the date strings returned from postgres. It's just an example!

If you're thinking "gee, this seems pretty handy, but how can I get a list of all the OIDs in the database and what they correspond to?!?!?!" worry not:

$ psql -c "select typname, oid, typarray from pg_type order by oid"

If you want to find out the OID of a specific type:

$ psql -c "select typname, oid, typarray from pg_type where typname = 'daterange' order by oid"

:smile:

license

The MIT License (MIT)

Copyright (c) 2014 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.