0.0.7 • Published 5 months ago
@flunsi/query v0.0.7
flunsi/query
Description
A simple wrapper around pg.query() for simple use cases. If you don't like ORMs, but pg.query() is too painfull to work with, this package might be for you.
Features
- Every use case has it's own methode and return type (see list below)
- Parameters as an object (not an array)
- String parameters are automatically escaped
- Optionally typed return values
Methodes
methode | returns |
---|---|
query.field() | string or number |
query.row() | object (first row) |
query.rowAsArray() | array (first row) |
query.rows() | array of objects |
query.columnAsArray() | array (first column) |
query.update() | number (rowCount aka rowsAffected) |
Install
npm install @flunsi/query
Usage
import Pool from 'pg-pool'
import Query from '@flunsi/query'
const pool = new Pool({ connectionString: DB_URL })
const query = new Query(pool)
const userName = await query.field(
'SELECT userName FROM users WHERE userId = {userId}',
{ userId: 69 })
Definition
All methodes have the same input parameters. Only the return type differs.
field()
is used as an example:
field(statement: string, params?: Record<string, string | number>) : Promise<string | number>
Examples
Data
id | name | age |
---|---|---|
1 | Coco | 4 |
2 | Bibi | 3 |
Code
query.field()
// Select a stingle string value (untyped and with no parameters)
const name = await query.field(
'SELECT name FROM animals WHERE id = 1')
name = 'Coco'
// Select a single number value
const age = await query.field<number>(
'SELECT age FROM animals WHERE id = {id}',
{ id: 1 })
age = 4
query.row()
// Select a single row
type Animal = { name: string, age: number }
const animal = await query.row<Animal>(
'SELECT name, age FROM animals WHERE id = {id}',
{ id: 1 })
animal = { name: 'Coco', age: 4 }
query.rowAsArray()
// Select a single row
const animal = await query.rowAsArray(
'SELECT name, age FROM animals WHERE id = {id}',
{ id: 1 })
animal = ['Coco', 4]
query.rows()
// Select multiple rows
type Animals = Array<{ name: string, age: number }>
const animals = await query.rows<Animals>(
'SELECT name, age FROM animals WHERE name in ({name1}, {name2})',
{ name1: 'Coco', name2: 'Bibi' })
animals = [ { name: 'Coco', age: 4 }, { name: 'Bibi', age: 3 } ]
query.columnAsArray()
// Select a single column as an array
type Names = Array<string>
const names = await query.columnAsArray<Names>('SELECT name FROM animals')
names = ['Coco', 'Bibi']
query.update()
// Update with returning rowCount (aka: rowsAffected)
const rowCount = await query.update(
'UPDATE animals SET age = age + 1 WHERE id BETWEEN {idLow} AND {idHigh}',
{ idLow: 1, idHigh: 2 })
rowCount = 2
query.field()
// Insert with returning auto-incremented id
const newId = await query.field<number>(
'INSERT INTO animals (name, age) VALUES ({name}, {age}) RETURNING id',
{ name: 'Lulu', age: 1 })
newId = 3