This is primarily built around the NPM package pg. It also utilizes sql-template-strings.
This package is useful, primarily, when wanting to write raw SQL instead of using an ORM or some sort of query builder like Knex.js, and is sort of my version to the more popular package slonik.
Objectives of this package:
- Helpers to construct tedious queries (i.e. multiple record inserts, on conflicts, combining SQL Statements)
- Pre-written queries that can be ran to gain insight on the health of the database (e.g. find unused indexes, dead tuples, blocking queries)
- Handle connections, timeouts, log slow queries, return data with camel case keys, return records that have been inserts / updates without needing to do
RETURNING *
This is important because there are baked in assumptions when using this package.
Table names and column names are assumed to be in snake case and in all lower cases (e.g. table_names, col_name). Due to postgres being case insensitive, this is my preferred naming convention for identifiers within Postgres.
If you are using this package and don't strictly follow snake casing when it comes to identifers there might be some limitations such as using helper functions within the sqlBuilder
module.
To install the package, within the project directory, run:
npm i n96-postgres --save
To import in project
import * as anythingYouLike from "n96-postgres";
// or
import { conn, sqlBuilder } from "n96-postgres";
There are cases where it is nice to have some help when constructing a SQL Statement, such as when you have an array of objects that you want inserted into a table.
This module is use to query the database. By default, it will create a pool
and open and close a client
per query. There are times when you would not want that, and this package accounts for those situations.
The main function from the conn
module is query
. This allows a SQL Statement Query to be passed in and it then returns the results as an array of objects. The column names will be automatically converted from snake casing to camel casing, removing the need to alias the columns in the SELECT clause.
The pool
is also exposed in this module for the times when managing the client
is necessary such as managing a complete transaction.
import { conn } from "./../src/conn";
const connectionProps = {
user: process.env.APP_DB_USER,
password: process.env.APP_DB_PASSWORD,
host: process.env.APP_DB_HOST,
port: Number(process.env.APP_DB_PORT),
database: process.env.APP_DB_NAME,
schema: "public",
};
const db = conn(connectionProps);