A database interaction library for Node.js/JavaScript/TypeScript that uses code reflection to maximize type safety and minimize friction. Supports PostgreSQL and SQLite.
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
[...]
})
``