Oraios Queries (formerly node-db-models) is a light-weighted project aims to provide class-based table representation and flexible query experience to help developers to avoid plain string queries that are error-prune.
Oraios Queries supports postgres and mysql2 packages.
The package is consistently getting enhanced and updated. Your contributions are always welcome. Here are the functionality that are developed/being developed:
- CRUD Ops: Insert, select, update & delete Data from Postgresql and MySQL with flexible nested WHERE conditions.
- ORM: Create class-based models for your tables with built-in features.
- Flexible Queries: Designed to perform flexible, nested WHERE statements, ordering and grouping.
- Model Settings: Specify certain fields to be selectable, allow HTML tags to be stored in database for certain fields, add default values on insert and update, and more.
- Pre-defined Query Executers: Extract data in various ways: list, select one column, first item, slicing, chunking, pagination and more.
- Light Weighted: This package is light and can be added on APIs, web workers, .. etc.
Install package using npm:
$ npm install --save oraios-queries
Connect to your database using pg or mysql2 package, then attach your connection with oraios-queries:
const Pg = require("pg");
const { Connection, Model } = require('oraios-queries');
let pgModConn = new Pg.Pool({
host: '127.0.0.1',
user: 'admin',
database: 'sampledb',
password: '*******',
port: 5432
});
let conn = new Connection({
connection: pgModConn,
type: 'pg'
});const mysql = require('mysql2');
const { Connection, Model } = require('oraios-queries');
const mysqlConn = mysql.createPool({
host: '127.0.0.1',
user: 'admin',
password: '*****',
database: 'sampledb',
waitForConnections: true,
connectionLimit: 10,
queueLimit: 0
});
let conn = new Connection({
connection: mysqlConn,
type: 'mysql'
});That's it. From now on everything will be the same across different connections.n rows in database.
- Create a Model:
const { Model, Util } = require('oraios-queries');
class Post extends Model {
tableName = 'posts';
allowHtml = ['body'];
selectable = ['title', 'body', 'author_id', 'created_at::date'];
//optional, default value is 'id'
primaryKey = 'uuid';
//the object created above
connection = conn;
//optional default value setup
defaultValue = {
onInsert: {
created_at: Util.timestamp(),
updated_at: Util.timestamp()
},
onUpdate: {
updated_at: Util.timestamp()
}
}
}- Inserting new row to database:
let insertedId = await post.set({title: 'blog post', body: '<p>Hello World</p>'}).insert();
if(insertedId){
//success
}- Inserting multiple rows to database:
let insertedRows = await post.setMany([
{title: 'blog post', body: '<p>Hello World</p>'},
{title: 'blog post 2', body: '<p>Hello Oraios</p>'}
]).insert();
if(insertedRows > 0){
//success
}- Updating certain rows in database:
let affectedRows = await post.set({title: 'another blog post'}).where(['id', '=', 25]).update();
if(affectedRows !== 0){
//update successful
}- Deleting a row in database:
let rowDeleted = await post.where(['id', '=', 25]).delete();
if(rowDeleted !== 0){
//delete successful
}- Find a row by id in database:
let row = await post.find(25);- Perform a query with joins:
let userJoinQuery = user.innerJoin(post, 'id', 'post_author').select(['user_email']);
let userEmails = await userJoinQuery.list();- Select query with conditions using AND & OR with grouping:
let post = new Post();
let conditions = nestedConditions = { cond: [] };
conditions.relation = 'AND';
conditions.cond.push(["created_at::date", ">", "2019-01-01" ]);
conditions.cond.push(["author_id", "=", 25 ]);
//include a nested condition
nestedConditions.relation = 'OR';
nestedConditions.cond.push(['created_at::date', ">", "2019-05-01"]);
nestedConditions.cond.push(['created_at::date', "<", "2019-10-01"]);
//add nested condition into the list of conditions
conditions.cond.push(nestedConditions);
let postQuery = post.select(['created_at::date', 'count(*) as posts'])
.where(conditions)
.groupBy(['created_at::date'])
.orderBy([{col: 'created_at::date', order: 'desc'}]);
let postRes = await postQuery.list();The previous statement will produce a query like this:
SELECT created_at::date, count(*) as posts
FROM posts
WHERE (
created_at::date > "2019-01-01" AND
author_id, "=", 25 AND
(
created_at::date > "2019-05-01" OR
created_at::date < "2019-10-01"
)
)
GROUP BY created_at::date
ORDER BY created_at::date desc;Copyright (c) 2019-2020 Ahmed Saad Zaghloul ([email protected]) MIT License
React
Typescript
Django
Laravel
Facebook
Microsoft
Google
Alibaba
D3
Tencent