@fairscript/interact-with-postgres

Interact

A database interaction library for node.js/JavaScript/TypeScript that uses code reflection to maximize type safety and minimize friction. Supports PostgreSQL and SQLite.

Installation

Interact can be installed from npm:

npm install interact

There is one additional module for each of the three supported databases:

# Required for Postgres support
npm install interact-with-postgres

# Required for SQLite support
npm install interact-with-sqlite

Getting started

Step 1: Define a type

interface Employee {
    id: number,
    firstName: string,
    lastName: string,
    title: string,
    salary: number,
    departmentId: string
    fulltime: boolean
}

Step 2: Define a table

import { defineTable } from '@fairscript/interact'

const employees = defineTable<Employee>(
    'employees',
    {
        id: 'number',
        firstName: 'string',
        lastName: 'string',
        title: 'string',
        salary: 'integer',
        departmentId: 'string',
        fulltime: 'boolean'
    })

defineTable is a generic function that expects two arguments: the database table name and a record specifying the column types for the specified type.

Step 3) Create a database context and start running queries

const dbContext = createSqliteContext(filename)

const query = employees
    .filter(e => e.id === 1)
    .map(e => ({ first: e.firstName, last: e.lastName }))

const namesOfEmployees = dbContext.run(query)

This generates the following SQL query:

SELECT t1.first_name AS first, t1.last_name AS last
FROM employees t1
WHERE t1.id = 1

Table definition

const employees = defineTable<Employee>(
    'employees',
    {
        id: 'integer',
        firstName: 'string',
        lastName: 'string',
        title: 'string',
        salary: 'integer',
        departmentId: 'integer',
        fulltime: 'boolean'
    })

const departments = defineTable<Department>(
    'departments',
    {
        id: 'integer',
        name: 'string',
        companyId: 'integer'
    })

const companies = defineTable<Company>(
    'companies',
    {
        id: 'integer',
        name: 'string'
    })

Supported databases

In-memory SQLite

const context = createSqliteInMemoryContext()

On-disk SQLite

const context = createSqliteOnDiskContext(filename)

Postgres

import {Client} from 'pg'

const pg = new Client(...)
                      
await pg.connect()

const context = createPostgresContext(pg)

await pg.end()

Selection

Single column

employees.get(e => e.id)

Single row

employees
    .filter(e => e.id === 1)
    .single()

Map over rows

employees
    .map(e => ({ firstName: e.firstName, lastName: e.lastName }))

Single table

employees.select()

Limited number of rows

employees
    .select()
    .limit(n)

Limited number of rows, with an offset

employees
    .select()
    .limit(m)
    .offset(n)

Distinct rows

employees
    .select()
    .distinct()

Aggregation

Number of rows

employees.count()

Minimum value in a column

employees.min(e => e.salary)

Maximum value in a column

employees.max(e => e.salary)

Sum of values in a column

employees.sum(e => e.salary)

Average column value

employees.sum(e => e.average)

Selecting multiple aggregations

employees
    .aggregate((e, count) => ({
        lowestSalary: e.salary.min(),
        highestSalary: e.salary.max(),
        totalSalaries: e.salary.sum(),
        averageSalary: e.salary.average(),
        numberOfEmployees: count()
    }))

Aggregating groups

employees
    .groupBy(e => e.departmentId)
    .aggregate((key, e, count) => ({
        lowestSalary: e.salary.min(),
        highestSalary: e.salary.max(),
        totalSalaries: e.salary.sum(),
        averageSalary: e.salary.average(),
        employeesInDepartment: count()
    }))

Filtering

Comparison

employees.filter(e => e.id == 1)
employees.filter(e => e.id === 1)

employees.filter(e => e.id != 1)
employees.filter(e => e.id !== 1)

employees.filter(e => e.salary > 10000)
employees.filter(e => e.salary >= 10000)
employees.filter(e => e.salary < 10000)
employees.filter(e => e.salary <= 10000)

Evaluating a Boolean column

employees.filter(e => e.fulltime)

employees.filter(e => !e.fulltime)

Conjunction

employees.filter(e => e.firstName === 'John' && e.lastName === 'Doe')

employees
    .filter(e => e.firstName === 'John')
    .filter(e => e.lastName === 'Doe')

Disjunction

employees.filter(e => e.firstName === 'Jim' && e.firstName === 'James')

Conjunction of disjunctions

employees.filter(e => (e.firstName === 'John' || e.firstName === 'Richard') && (e.firstName === 'Doe' || e.firstName === 'Roe'))

Disjunction of conjunctions

employees.filter(e => (e.firstName = 'John' && e.firstName = 'Doe') || (e.firstName = 'Richard' || e.firstName = 'Roe'))

User-provided value

employees.filter(1, (id, e) => e.id === 1)

User-provided object

employees
    .filter(
        { firstName: 'John', lastName: 'Doe' },
        (search, e) => e.firstName === search.firstName, e.lastName === search.lastName)
    )

Sorting features

Ascending order

employees
    .sortBy(e => e.id)
    .select()

Descending order

employees
    .sortDescendinglyBy(e => e.salary)
    .select()

Multiple orders

employees
    .sortBy(e => e.departmentId)
    .thenDescendinglyBy(e => e.salary)
    .select()

Joins

Joining tables

employees
    .join(departments, e => e.departmentId, d => d.id)
    .join(departments, e => e.companyId, c => c.id)

Column from a joined table

employees
    .join(departments, e => e.departmentId, d => d.id)
	.get((e, d) => d.name)

Map over rows

employees
    .join(departments, e => e.departmentId, d => d.id)
    .get((e, d) => {
        firstName: e.firstName,
    	lastName: e.lastName,
        department: d.name
    })

Selecting multiple tables

employees
    .join(departments, e => e.departmentId, d => d.id)
    .join(companies, d => d.companyId, c => c.id)
    .select('employee', 'department', 'company')

Subqueries

Number of rows

employees.map(
     employees,
     (subtable, e) => ({
         id: e.id,
         departmentSize: subtable
             .filter(se => se.departmentId === e.departmentId)
             .count()
     }))

Minimum value in a column

employees.map(
     employees,
     (subtable, e) => ({
         id: e.id,
         lowestSalaryInDepartment: subtable
             .filter(se => se.departmentId === e.departmentId)
             .min(se => se.salary)
     }))

Maximum value in a column

employees.map(
     employees,
     (subtable, e) => ({
         id: e.id,
         highestSalaryInDepartment: subtable
             .filter(se => se.departmentId === e.departmentId)
             .max(se => se.salary)
     }))

Sum of values in a column

employees.map(
     employees,
     (subtable, e) => ({
         id: e.id,
         totalSalariesInDepartment: subtable
             .filter(se => se.departmentId === e.departmentId)
             .sum(se => se.salary)
     }))

Average column value

employees.map(
     employees,
     (subtable, e) => ({
         id: e.id,
         averageSalaryInDepartment: subtable
             .filter(se => se.departmentId === e.departmentId)
             .average(se => se.salary)
     }))

Parallel queries

const promiseOfResults: Promise = context
	.parallelRun({
        numberOfEmployees: employees.count(),
        numberOfDepartments: departments.count(),
        numberOfCompanies: companies.count()
	})
    .then(res => {
        { numberOfEmployees, numberOfDepartments, numberOfCompanies } = res
        [...]
    })