@apparts/db

#+TITLE: @apparts/db #+DATE: [2021-03-25 Thu] #+AUTHOR: Philipp Uhl

A wrapper and query builder around the [[https://node-postgres.com/][pg]]. The API exposed by this package is meant to be usable with other database system (e.g. mongodb), too. Thus this package shall serve as an adapter between the database driver and your code.

• Usage

Install:

#+BEGIN_SRC sh npm i --save @apparts/db #+END_SRC

#+BEGIN_SRC js const connect = require("@apparts/db");

const DB_CONFIG = { "use": "postgresql", "postgresql": { // pg settings "host": "localhost", "port": 5432, "user": "postgres", "pw": "password", "db": "databasename", "maxPoolSize": 5, "connectionTimeoutMillis": 0, "idleTimeoutMillis": 1000,

// Use bigint as id instead of integer
"idsAsBigInt": false,

// Should bigint be returned as number? If false, a string will be returned
"bigIntAsNumber": true,

// Use json type when finding an array, defaults to false
"arrayAsJSON": true,

// Turn on logging on error. Default: no logging
"logs": "errors",

// Also log query parameters on error. Only effective if "logs" === "errors".
"logParams": true

} };

connect(DB_CONFIG, (e, dbs) => { if(e) { // handle error throw e; } // use dbs }); #+END_SRC

** Raw SQL queries

#+BEGIN_SRC js try { const { rows } = await dbs.raw( SELECT * FROM "testTable" WHERE a = $1 AND b = $2, [1, "test"]); // use data here } catch (e) { // handle error } #+END_SRC

** Query builder

#+BEGIN_SRC js // insert something const ids = await dbs.collection("testTable") .insert([{ number: 100 }, { number: 101 }]); // by default returns the "id" collumn // ids[0].id -> 1

// insert with custom return values const ids = await dbs.collection("testTable") .insert([{ number: 102 }, { number: 103 }], returning = ["number"]); // ids === [ { number: 102 }, { number: 103 } ]

// retrieve values const filter = { id: { op: "in", val: [2, 3] }}; // see below for everything you can stick into filter and into order const limit = 10, offset = 0, order = [{ key: "id", dir: "ASC" }]; await dbs.collection("testTable").find(filter, limit, offset, order);

// retrieve values by ids, easier await dbs.collection("testTable") .findByIds({ id: [ 2, 3 ]}, limit, offset, order);

// update values const newContent = { number: 1000 }; await dbs.collection("testTable").update(filter, newContent);

// DEPRICATED, same as update: await dbs.collection("testTable").updateOne(filter, newContent);

// delete values await dbs.collection("testTable").remove(filter);

// drop table await dbs.collection("testTable").drop();

#+END_SRC

*** Order

The order is given as an array of objects. The order is established, using the first array element. If two elements are equal according to that order, the next array element is used for ordering (and so on).

The order array takes this form:

#+BEGIN_SRC js [{ key: "", dir: "ASC" | "DESC" }, ... ] #+END_SRC

The object can contain these keys:

• key :: (required) The field that should be ordered
• dir :: (required) The direction into which is ordered (ascending or descending).
• path :: (opitonal) If =key= describes a JSON field, =path= can be used to specify by which element within an JSON object should be ordered. Path is then an array of strings (keys) that define the path within the nested JSON object.

*** Filters

The filter is given as an object. The keys represent the column that the filter should be applied against. The value is either a value or an object that has op and val keys. op can be one of

• in :: val then must be an array of possible values. The filter accepts values that appear in the val array.
• notin :: val then must be an array of values. The filter accepts values that do not appear in the val array.
• of :: val is an object with the keys
  • path (an array of keys for the nested json)
  • value, either a value to directly compare to, or another filter
  • cast (optional), a casting operator (Postgresql only returns strings for values from JSON. For numeric comparison you need to cast to number). Can be "number" or "boolean". #+BEGIN_SRC json { "op": "of", "val": { "path": ["<key 1>"], "value": // value or filter operator here, "cast": "number" | "boolean" | undefined } } #+END_SRC
• lte :: Less than, or equals. val must be a number, against the value is combined.
• lt :: Less than. val must be a number, against the value is combined.
• gte :: Greater than, or equals. val must be a number, against the value is combined.
• gt :: Greater than. val must be a number, against the value is combined.
• like :: Compares strings with the SQL like operator.
• ilike :: Compares strings with the SQL like operator, case insensitive.
• and :: Combines multiple filters and-wise. val must be an array of objects, that have val and op keys of the form described here.
• exists :: Checks if an optional field is null or not. If val is true, than the field must be not null. If val is false, than the field must be null;
• oftype :: Checks the type of a JSON property. Requires a JSONB column in Postgresql to work.
  • path (an array of keys for the nested json)
  • value, one of "object", "array", "string", "number", "boolean", and "null". #+BEGIN_SRC json { "op": "of", "val": { "path": ["<key 1>"], "value": "object" // or any other of the valid types } } #+END_SRC

**** Filter Grammar

The filter syntax is like this:

#+BEGIN_SRC js const filter = { : , ...}; // where is a key from the type and // where matcher is = | { op: , val: } | { op: , val: } | { op: "and", val: } // logical and for all subconditions | { op: "in", val: [] } // one of the values | { op: "of", path: [], value: } // match the prop of a nested JSON object | { op: "exists", val: }

= lte // less than or equals | lt // less than | gte // greater than or equals | gt // greater than = like // sql like, a string comparison where the "%" character // will be matched against anything. E.g. "bread%crumb" // matches "bread crumb" or "bread eating crumb". = | | | null = , | // nothing #+END_SRC