pglink-lite
Advanced tools
A library for Postgresql to use ORM on NodeJS with GraphQL
Via Janden Ma
MIT LICENCE
This library is built for who uses GraphQL on NodeJS, you can use model to operate data.
MultiUpdateWithConditions bug fixes.globalAutoSetTimeFieldstableName and returnTableName in Transaction to alias and returnWithAliasExecute function in dataAccess to run a single querypg package dependency.connectionTimeoutMillis and idleTimeoutMillis parameters.DataAccess.Disconnect() function (beta).findByPk function supports multiple primary keys.autoSetTimeFieldssslTransactionsortBy option for build querying sqlTransactionpg to support PostgreSQL 13preserveClient for leaving the client open for further operations in the transactiondeleteByConditionsIS and IS NOT operatorsSIMILAR TO operatorsnpm
npm i pglink-lite --save
yarn
yarn add pglink-lite --save
Instance (core/pglink.js)
// core/pgsqlize.js
const { PgLink } = require('pglink-lite')
const pglink = new PgLink({
host: 'http://192.168.1.100',
port: 5432,
userName: 'root',
password: '123456',
database: 'test',
connectionTimeoutMillis: 0,
idleTimeoutMillis: 60000,
globalAutoSetTimeFields: ['updatedAt'],
ssl: true
})
module.exports.default = pglink
Model (models/users.js)
// models/users.js
const pglink = require('../core/pglink')
class UserModel extends pglink.Model {
constructor() {
super({ tableName: 'users', pkName: 'userId'})
}
}
module.exports.default = UserModel
Schema (schemas/users.js)
// schemas/users.js
// you need to install something for gql first, we use apollo-server here
const { gql } = require('apollo-server')
const typeDefs = gql`
type User {
userId: ID!
userName: String
status: Boolean
}
input UserInsertInput {
userName!: String
}
input UserEditInput {
userId!: ID
userName: String
status: Boolean
}
type Query {
getUserById(userId: ID!): User
}
type Mutation {
insertUser(user: UserInsertInput): User
editUser(user: UserEditInput): User
}
`
module.exports.default = typeDefs
Resolver (resolvers/users.js)
// resolvers/users.js
const UserModel = require('../models/users.js')
const getUserById = async (_, args) => {
const inst = new UserModel()
const res = await inst.findByPk({ pk: args.userId })
return res
}
const insertUser = async (_, args) => {
const inst = new UserModel()
const res = await inst.insertOne({ params: args.user })
return res
}
const editUser = async (_, args) => {
const inst = new UserModel()
const res = await inst.updateByPk({ params: args.user })
return res
}
module.exports = {
getUserById,
insertUser,
editUser
}
Import library package
import { PgLink } from 'pglink-lite'
// or
import PgLink from 'pglink-lite'
// or
const { PgLink } = require('pglink-lite')
// or
const PgLink = require('pglink-lite')
Instantiate PgLink
export const pglink = new PgLink({
host: 'http://192.168.1.100',
port: 5432,
userName: 'root',
password: '123456',
database: 'test'
})
Props: object
| Key | Type | Introduction | Default value |
|---|---|---|---|
| host | string | Postgresql server host | "localhost" |
| port | number | Postgresql server port | 5432 |
| userName | string | Postgresql server user name | "postgres" |
| password | string | Postgresql server password | ""(empty) |
| database | string | Postgresql database name | "postgres" |
| connectionMax | number | Postgresql database max connection | 10 |
| connectionTimeoutMillis | number | Number of milliseconds to wait before timing out when connecting a new client, by default this is 0 which means no timeout | 0 |
| idleTimeoutMillis | number | Number of milliseconds a client must sit idle in the pool and not be checked out, before it is disconnected from the backend and discarded, default is 10000 (10 seconds) - set to 0 to disable auto-disconnection of idle clients | 10000 |
| globalAutoSetTimeFields | Array<string> | To define fields that should be automatically updated with a current timestamp | [] |
| ssl | boolean | To connect to pg using ssl | false |
Inherit and declare model
// example
class xxxModel extends pglink.Model {
constructor(params) {
super({
tableName: 'users',
pkName: 'No',
enumMapping: {
sex: { MALE: 1, FAMALE: 0 },
role: { STUDENT: 1, TEACHER: 2 }
}
})
}
// if you need rewrite inner funtions or add some functions, write here
}
object| Key | Type | Introduction | Required |
|---|---|---|---|
| tableName | string | the data table in postgresql you need to operate | true |
| pkName | string | the name of primary key in the data table, default id | false |
| enumMapping | object | to defined the key and value, key should be included in the fields, e.g. {role: {ADMIN: 0, USER: 1}} | false |
inner properties or functions
dataAccess
Introduction
A data table operator (CRUD)
see the details below
findAll
Introduction
A function for querying all rows from one table
Parameters
{
options: object, default {
sortBy: undefined,
limit: undefined,
offset: undefined
}
preserveClient: boolean
callback: function
}
Returns
(Promise) All rows data or error
findByPk
Introduction
A function for querying by primary key
Parameters
{
pkValue: string | number | object
selectFields: string, default *
callback: function
preserveClient: boolean
}
Returns
(Promise) One row data or error
findByConditions
Introduction
A function for querying by conditions
Parameters
{
whereClause: string. (e.g. ' name = "Tim" ')
selectFields: string, default *
options: object, default {
sortBy: undefined,
limit: undefined,
offset: undefined
}
preserveClient: boolean
callback: function
}
Returns
(Promise) Some rows data or error
InsertOne
Introduction
A function for inserting one row to a table
Parameters
{
params: object. (data from resolver)
callback: function
client: object
preserveClient: boolean
}
Returns
(Promise) Inserted row data or errors
multiInsert
Introduction
A function for inserting multi rows to a table
Parameters
{
items: Array<object>. (data from resolver)
forceFlat?: boolean (whether or not to force results into a single array)
callback: function
client: object
preserveClient: boolean
}
Returns
(Promise) Inserted rows data or errors
updateByPk
Introduction
A function for updating by primary key
Parameters
{
params: object. (data from resolver, have to include pkName and pkValue)
autoSetTimeFields: Those fields need to set time automatically, should be included in items
callback: function
client: object
preserveClient: boolean
}
Returns
(Promise) updated row data or errors
updateByConditions
Introduction
A function for updating by conditions
Parameters
{
params: object. (data from resolver)
whereClause: string. (e.g. ' name = "Tim" ')
autoSetTimeFields: Those fields need to set time automatically, should be included in items
callback: function
client: object
preserveClient: boolean
}
Returns
(Promise) updated rows data or errors
multiUpdateWithConditions
Introduction
A function for multi updating by conditions
Parameters
{
items: Array<object>. (data from resolver)
whereClause: string with replacements. (e.g. ' company = $1 ')
replacementFields: Array<string> (e.g. ['company'])
autoSetTimeFields: Those fields need to set time automatically, should be included in items
forceFlat?: boolean (if true, forces results into a single array)
callback: function
client: object
preserveClient: boolean
}
Returns
(Promise) Updated rows data or errors
deleteByConditions
Introduction
A function for deleting by conditions
Parameters
{
whereClause: string. (e.g. ' "companyId" = 1001 ')
returnSingleRecord?: boolean (if true, returns just one record)
callback: function
client: object
preserveClient: boolean
}
Returns
(Promise) Deleted rows data or errors
encodeFromEnum
Introduction
A function for encoding the enum to integer value
Parameters
data: object | Array. (input data)
Returns
(Object) Same structure of input data, with encoded enum
decodeToEnum
Introduction
A function for decoding the enum from integer value
Parameters
data: object | Array. (output data)
Returns
(Object) Same structure of output data, with decoded enum
dataAccess functions
Execute
Introduction
to run a single and simple sql
Parameters:
Returns
reponse from database
Example:
const sqlStatement = `SQL STATEMENT GOES HERE`
const res = await this.dataAccess.Execute(sqlStatement);
Transaction
Introduction
core function with transaction
Parameters:
args: {
params: Array<{
sql: string
replacements?: Array<any>
alias?: string // to distinguish responses
}>
client?: object
returnWithAlias?: boolean, // if true, return res with alias
returnSingleRecord?: boolean,
forceFlat?: boolean,
preserveClient?: boolean // Skips committing the operation, leaving the client open for further operations in the transaction
},
transaction: Function // callback function or Transaction
Returns
reponse from database
GenerateInsertSQL
Introduction
generate insert sql object
Parameters
params: object, //data from resolver, includes inserted fields and values
tableName: string //name of inserted table
Returns
{
sql: string
replacement: Array<any>
tableName: string
}
GenerateMultiInsertSQL
Introduction
generate bulk insert sql object
Parameters
insertFields: Array<string>,
params: object, //data from resolver, includes inserted fields and values
tableName: string //name of inserted table
Returns
{
sql: string
replacement: Array<any>
tableName: string
}
GenerateUpdateSQL
Introduction
generate update sql object
Parameters
{
/** an object includes the fields and values you want to update */
params: object
/** the name of table */
tableName: string
/** e.g. "employeeId" = '123' */
whereClause?: string
/** the name of primary key, default 'id' */
pkName?: string
/** those fields need to set time automatically, default value is from globalAutoSetTimeFields. We will check whether fields included in the table, if not, skip */
autoSetTimeFields?: Array<string>
}
Returns
{
sql: string
replacement: Array<any>
tableName: string
}
InsertExecutor
Introduction
execute insert sql
Parameters
params: object, //data from resolver, includes inserted fields and values
tableName: string, //name of inserted table
callback?: function, //function to run before committing the transaction
client?: object //the pg client to be used for the transaction
preserveClient?: boolean // skips comitting the client if true
Returns
response from database
MultiInsertToOneTableExecutor
Introduction
execute insert sqls to one table
Parameters
insertFields: Array<string>,
params: object, //data from resolver, includes inserted fields and values
tableName: string, //name of inserted table
callback?: function, //function to run before committing the transaction
client?: object //the pg client to be used for the transaction
preserveClient?: boolean // skips comitting the client if true
Returns
response from database
MultiInsertExecutor
Introduction
execute insert sqls to deferent tables
Parameters
Array<
{
params: object, //data from resolver, includes inserted fields and values
tableName: string //name of inserted table
}>,
forceFlat?: boolean, //if true, forces results into one array
callback?: function, //function to run before committing the transaction
client?: object //the pg client to be used for the transaction
preserveClient?: boolean // skips comitting the client if true
Returns
response from database
UpdateByPkExecutor
Introduction
execute update sql by primary key
Parameters
params: object, //data from resolver, includes updated fields and values
tableName: string, //name of inserted table
pkName?: string, //the name of primary key
autoSetTimeFields?: Array<string> ,//those fields need to set time automatically
callback?: function, //function to run before committing the transaction
client?: object //the pg client to be used for the transaction
preserveClient?: boolean // skips comitting the client if true
Returns
response from database
UpdateExecutor
Introduction
execute update sql by conditions
Parameters
params: object, //data from resolver, includes updated fields and values
tableName: string, //name of inserted table
whereClause?: string, //e.g. "employeeId" = '123'
autoSetTimeFields?: Array<string>, //those fields need to set time automatically
callback?: function, //function to run before committing the transaction
client?: object //the pg client to be used for the transaction
preserveClient?: boolean // skips comitting the client if true
Returns
response from database
MultiUpdateExecutor
Introduction
execute bulk update sqls by conditions
Parameters
Array<
{
params: object, //data from resolver, includes updated fields and values
tableName: string, //name of inserted table
whereClause?: string //e.g. "employeeId" = '123'
pkName: string, //the name of primary key
autoSetTimeFields?: Array<string>, //those fields need to set time automatically
client?: object //the pg client to be used for the transaction
}>,
forceFlat?: boolean //if true, forces results into a single array
callback?: function //function to run before committing the transaction
preserveClient?: boolean // skips comitting the client if true
Returns
response from database
DeleteExecutor
Introduction
execute delete sql by conditions
Parameters
tableName: string, //name of inserted table
whereClause?: string, //e.g. "employeeId" = '123'
returnSingleRecord?: boolean,//if true, returns one record instead of array
callback?: function, //function to run before committing the transaction
client?: object //the pg client to be used for the transaction
preserveClient?: boolean // skips comitting the client if true
Returns
response from database
SingleQueryExecutor
Introduction
execute query sql
Parameters
{
/** the name of table */
tableName: string
/** e.g. "employeeId" = '123' */
whereClause: string
/** the fields what you want to select, default * */
selectFields?: string
/** the field name for sorting, e.g.: [{field: 'id', sequence:'DESC'}] */
sortBy?: Array<{ field: String; sequence?: 'ASC' | 'DESC' }>
/** to limit the count of rows you want to query */
limit?: number
/** how many rows you want to skip */
offset?: number,
/** if true, return a single record instead of an array */
returnSingleRecord?: boolean,
client?: object //the pg client to be used for the transaction
preserveClient?: boolean // skips comitting the client if true
}
Returns
response from database
Disconnect [Beta Function]
Introduction
It will drain the pool of all active clients, disconnect them, and shut down any internal timers in the pool. It is common to call this at the end of a script using the pool or when your process is attempting to shut down cleanly.
FAQs
A lite library for Postgresql to use ORM on NodeJS with GraphQL
The npm package pglink-lite receives a total of 41 weekly downloads. As such, pglink-lite popularity was classified as not popular.
We found that pglink-lite demonstrated a not healthy version release cadence and project activity because the last version was released a year ago. It has 2 open source maintainers collaborating on the project.
Did you know?
Socket for GitHub automatically highlights issues in each pull request and monitors the health of all your open source dependencies. Discover the contents of your packages and block harmful activity before you install or update your dependencies.
Research
Four npm packages disguised as cryptographic tools steal developer credentials and send them to attacker-controlled Telegram infrastructure.
Security News
Ruby maintainers from Bundler and rbenv teams are building rv to bring Python uv's speed and unified tooling approach to Ruby development.
Security News
Following last week’s supply chain attack, Nx published findings on the GitHub Actions exploit and moved npm publishing to Trusted Publishers.