Japper
Database library for PostgreSQL that doesn't get in your way but makes interacting with database more elegant.
It's a thin layer on top of node-postgres built with typescript in mind, heavily inspired by C# dapper library.
Writing raw SQL is the recommended way to do anything besides a couple goodies included like simple CRUD queries that are used always.
Be more productive when working with your database while still having all the control!
Documentation
Features
- Write plain SQL queries (no need to learn yet another "language" just to interact with a database)
- Generate INSERT/UPDATE queries based on object schema (making DTO classes single source of truth)
- Returns are key! Improve your code readability with getting the results back as needed (see examples below)
- Automatically open connection on first usage
- Everything great node-postgres already provides us with
Installation
$ npm install japper --save
Usage
All of the example are written with Typescript. Of course, this is usable without typescript, but I suggest you to try out Typescript :)
Express (connection pool example)
Usage with; // import Japper Pool; ;; // create a Japper pool of connections; ; app.get"/users", ; app.listenPORT,;
Usage without a connection pool
; ; try catch
API documentation
Main usage
query - execute query and return array of found rows (preferably typed if using typescript)
async queryquery: string, params?: any | undefined: Promise<T>
Example Usage:
;;
queryFirst - execute query and return a single row as object (preferably typed if using typescript)
async queryFirstquery: string, params?: any | undefined: Promise<T | null>
Example Usage:
;
executeScalar - execute query and return a single value as string
async executeScalarquery: string, params?: any: Promise<string | null>
Example Usage:
;;
execute - execute query and return number of changed rows
async executequery: string, params?: any: Promise<number>
Example Usage:
;console.log`Deleted users`;
CRUD helpers
insert - insert row based on schema (making DTOs single source of truth)
async inserttableName: string, obj: T, excludeFields: K | null = null: Promise<number>
Example Usage:
await db.insert"users", ; // or if we want to exclude some properties from inserting (in this example we don't insert id);await db.insert"users", newUser, ;
insertReturning - insert row based on schema and return a field as string
async insertReturningtableName: string, obj: T, returningPropertyName: R, excludeFields: E | null = null: Promise<string>
Example Usage:
// return id after inserting;
update - update an object based on schema
async updatetableName: string, obj: T, primaryKeyName: K, excludeFields: K | null = null: Promise<number>
Example Usage:
// update user that has this id with this schema;
delete - delete a row based on a single field
async delete
Example Usage:
// delete user with id 1;
JapperPool vs JapperConnection
JapperPool maintains a pool of connections that can be reused. So when you create a new JapperPool you can just issue queries on it whenever you want. The pool can remain opened for ever. When you want to close the pool you call
.close()
JapperConnection is a one time connection that cannot be reused. So use them wisely! Connections should be opened as short as possible but opening and closing the connection is expensive so you could do multiple queries using one..be smart!
Example Usage:
; ; //manually closing connection;await conn.execute"DELETE FROM users"; // first query will automatically open connectionawait conn.insert"users", ;await conn.close; //after we're done we close it // if we need to use db again, we create a new connection again! // better way (no need to manually close connection)new JapperConnectionconfig;
Accessing node-postgres
JapperConnection and JapperPoll have a property named adapter
which is a node-postgres object!
Support
japper is free software. If you encounter a bug with the library please open an issue on the GitHub repo.
When you open an issue please provide:
- version of Node
- version of Postgres
- smallest possible snippet of code to reproduce the problem
Contributing
❤️ contributions!
I will happily accept your pull request if it:
- has tests
- added or changed functionality is in the japper philosophy
- does not break backwards compatibility
If your change involves breaking backwards compatibility please please point that out in the pull request & we can discuss & plan when and how to release it and what type of documentation or communication it will require.
Setting up for local development
- Clone the repo
- From your workspace root run
yarn
- Ensure you have a PostgreSQL instance running and an empty database for tests
- Ensure you have the proper environment variables configured for connecting to the instance (see .sample-env and create .env file based on it)
- Run
yarn test
to run all the tests
License
Copyright (c) 2020 Eric Jardas (givemeurhats@gmail.com)
Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:
The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.
THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.