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:

``sh
npm install interact
`

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

`sh

Required for Postgres support

npm install interact-with-postgres

Required for SQLite support

npm install interact-with-sqlite
`

Getting started

$3

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

$3

`typescript
import { defineTable } from '@fairscript/interact'

const employees = defineTable(
'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.

$3

`typescript
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:

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

Table definition

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

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

const companies = defineTable(
'companies',
{
id: 'integer',
name: 'string'
})
`

Supported databases

$3

`typescript
const context = createSqliteInMemoryContext()
`

$3

`typescript
const context = createSqliteOnDiskContext(filename)
`

$3

`typescript
import {Client} from 'pg'

const pg = new Client(...)

await pg.connect()

const context = createPostgresContext(pg)

await pg.end()
`

Selection

$3

`typescript
employees.get(e => e.id)
`

$3

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

$3

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

$3

`typescript
employees.select()
`

$3

`typescript
employees
.select()
.limit(n)
`

$3

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

$3

`typescript
employees
.select()
.distinct()
`

Aggregation

$3

`typescript
employees.count()
`

$3

`typescript
employees.min(e => e.salary)
`

$3

`typescript
employees.max(e => e.salary)
`

$3

`typescript
employees.sum(e => e.salary)
`

$3

`typescript
employees.sum(e => e.average)
`

$3

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

$3

`typescript
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

$3

`typescript
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)
`

$3

`typescript
employees.filter(e => e.fulltime)

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

$3

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

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

$3

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

$3

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

$3

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

$3

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

$3

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

Sorting features

$3

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

$3

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

$3

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

Joins

$3

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

$3

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

$3

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

$3

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

Subqueries

$3

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

$3

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

$3

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

$3

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

$3

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

Parallel queries

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