Postgres Notes

What is Postgres?

PostgreSQL is an advanced, enterprise class open source relational database that supports both SQL (relational) and JSON (non-relational) querying. It is a highly stable database management system, backed by more than 20 years of community development which has contributed to its high levels of resilience, integrity, and correctness. PostgreSQL is used as the primary data store or data warehouse for many web, mobile, geospatial, and analytics applications

Documentation

• NPM Package here
• PostgreSQL Favorite YouTube Tutorial here

Installation

1npm install postgres
2

Usage

Create your sql database instance

1import postgres from 'postgres';
2const sql = postgres({
3  /* options */
4}); // will use psql environment variables
5export default sql;
6

Simply Import for use elsewhere

1import sql from './db.js';
2
3async function getUsersOver(age) {
4  const users = await sql`
5    select
6      name,
7      age
8    from users
9    where age > ${age}
10  `;
11  // users = Result [{ name: "Walter", age: 80 }, { name: 'Murray', age: 68 }, ...]
12  return users;
13}
14
15async function insertUser({ name, age }) {
16  const users = await sql`
17    insert into users
18      (name, age)
19    values
20      (${name}, ${age})
21    returning name, age
22  `;
23  // users = Result [{ name: "Murray", age: 68 }]
24  return users;
25}
26

Connection

• postgres([url], [options]) You can use either a postgres:// url connection string or the options to define your database connection properties. Options in the object will override any present in the url. Options will fall back to the same environment variables as psql.

1
2const sql = postgres('postgres://username:password@host:port/database', {
3  host                 : '',            // Postgres ip address[s] or domain name[s]
4  port                 : 5432,          // Postgres server port[s]
5  database             : '',            // Name of database to connect to
6  username             : '',            // Username of database user
7  password             : '',            // Password of database user
8  ...and more
9})
10

Noteables

• A Free Open Source Relational Database Management System or RDBMS
• Emphasized extensibility and SQL Compliance
• Postgres features transactions with ACID pipelines.

Learn more about it here