Get Started

Oraios Queries is a light-weighted project aims to create an ORM for Databases queries (especially Relational Databases that performs flexible WHERE statements) to help developers create model classes for tables and query them using functions rather than plain string that is error-prune once the query start to gets a little long.

Currenlty, the package supports Postgres and Mysql

Installation

            
              $ npm install --save oraios-queries
            
          

Configuration

Connect to your database using pg or mysql2 package, then attach your connection with node-db-models:

Postgres

            
              const Pg = require("pg");
              const { Connection } = require('oraios-queries');
              
              //pg package init
              let pgModConn = new Pg.Pool({
                host: '127.0.0.1',
                user: 'admin',
                database: 'sampledb',
                password: '*******',
                port: 5432
              });

              //oraios init :)
              let conn = new Connection({
                connection: pgModConn,
                type: 'pg'
              });
            
          

MySQL

              
                const mysql = require('mysql2');
                const { Connection } = require('oraios-queries');

                //mysql2 package init
                const mysqlConn = mysql.createPool({
                  host: '127.0.0.1',
                  user: 'admin',
                  password: '*****',
                  database: 'sampledb',
                  waitForConnections: true,
                  connectionLimit: 10,
                  queueLimit: 0
                });

                //oraios init :)
                let conn = new Connection({
                  connection: mysqlConn,
                  type: 'mysql'
                });
              
            

Creating an object for the connection enables multiple db connections.

Models

Let's create models for our databse tables that will extend Model class that we imported above:

          
            const { Model, Util } = require('oraios-queries');

            class Post extends Model {
              tableName = 'posts';
              allowHtml = ['body'];
              selectable = ['title', 'body', 'author_id', 'created_at::date'];
              connection = conn;
              primaryKey = 'uuid'; //optional, default value is 'id'
              
              //optional default values setup
              defaultValue = {
                onInsert: {
                    created_at: Util.timestamp(),
                    updated_at: Util.timestamp()
                },
                onUpdate: {
                    updated_at: Util.timestamp()
                }
              }
            }

            module.exports = new Post();
          
        

Query Builders

Now since you created your model(s). It's time to import the object of that class and start building queries:

          
            const post = require('./post.js');
            let postQuery = post.select(['title', 'body', 'created_at::date'])
                .where(["created_at", ">", "2019-01-01" ]);
            //some code ..
            postQuery = postQuery.orderBy([
                        {col: 'id', order: 'desc'}
                ]);
          
        

The above code builds your query, but does not execute it. You are free to chain to add to the query. You can use one of the following:

Query Executers

After the query is built, you are expected to chain a method that tells the query execution class how do you want the data to be returned.

          
            let postResults = await postQuery.list();
          
        

All the following methods return a promise:

Utilites

There are some utility functions that are provided to give your query more flexiblity:

          
            const { Util } = require('oraios-queries');

            let postQuery = post.select(['title', 'body', 'created_at::date'])
                .where(["created_at", ">", Util.raw("now() - INTERVAL '30 day'") ]);
          
        

The utilities is a new section that was just added in the latest version. More function is coming in the future.

Code Examples

Inserting new row to database:

          
            let insertedId = await post.set({title: 'blog post', body: 'Hello World'}).insert();
            if(insertedId){
                //success
            }
          
        

Inserting multiple rows to database:

          
            let insertedRows = await post.setMany([
                    {title: 'blog post', body: 'Hello World'},
                    {title: 'blog post 2', body: 'Hello Oraios'}
                ]).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);
          
        

Chunk the data to loop through groups of data:

          
            let row = await post.find(25);
          
        

Perform a query with joins:

          
            let postQuery = post.list();
            await postQuery.chunk(50, (posts) => {
              for(const post of posts){
                console.log(`This is post #${post.id}`);
              }
            });
          
        

Select query with conditions using AND & OR with grouping:

          
            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;