SNOWFLAKE-ORM

Installation

$ npm i snowflake-orm

Documentation

Connecting to Snowflake DB

For creating the connection you have to Write this bellow code

const snowflakeOrm = require('snowflake-orm');

const dbConfig = {
    username: 'Your Username',
    password: 'Your Password',
    account: 'Your Account Name',
    warehouse: 'Your Warehouse Name',
    database: 'Your Database Name',
    schema: 'Your Schema Name',
    role: 'Your Role Name'
};
snowflakeOrm.connect(dbConfig);

Data Type

NUMBER

NUMBER,
INT,
INTEGER,
FLOAT,
DOUBLE,

Text

STRING(length),
VARCHAR(length),
CHAR(length),

Date Time

DATE,
DATETIME,
TIMESTAMP(),	// (), (LTZ) & (NTZ)
NOW()

OTHERS

BINARY => "BINARY",
BOOLEAN => "BOOLEAN"

Model Create Example

const SnowflakeOrm = require('snowflake-orm');
const Init = SnowflakeOrm.Init;
const user = new Init("user", {
    id: {
        type: SnowflakeOrm.VARCHAR(50),
        require: true
    },
    // This option still has present. But should not use, because Snowflake doesn't support primary key. 
    // ** Instead of using Id field & 'require: true'. Its automatically create a Unique Key field for you.
    //After thet when You insert any record, Automaticly unique key value insert into your Id field. Like Mongo DB using Mongoose.
    // You can enter Id value manually. That time you have to mentation 'required: false'.
    // id: {
    //     type: SnowflakeOrm.INT,
    //     primaryKey: true,			// Primary Key
    //     autoIncrement: true			// Auto Increment
    // },
    fname: SnowflakeOrm.VARCHAR(50),
    lname: SnowflakeOrm.VARCHAR(50),
    username: {
        type: SnowflakeOrm.VARCHAR(70),
        unique: true,				// Unique Key
        allowNull: true				// Allow Null Value
    },
    email: SnowflakeOrm.VARCHAR(70),
    password: SnowflakeOrm.VARCHAR(50),
    age: SnowflakeOrm.INT,
    status: {
        type: SnowflakeOrm.INT,
        defaultValue: 1			    // Default Value = 1
    },
    createdAt: {
        type: SnowflakeOrm.TIMESTAMP('LTZ'),
        defaultValue: SnowflakeOrm.NOW()	// Default Value = Current Time
    }
});

// Without Using Primary Key & Foreign Key. Because Snowflake doesn't support Primary Key & Foreign Key
const userDetails = new Init("userdetails", {
    id: {
        type: SnowflakeOrm.VARCHAR(50),
        require: true
    },
    userId: {
        type: SnowflakeOrm.VARCHAR(50),
        allowNull: false
    },
    phone: SnowflakeOrm.INT,
    gender: SnowflakeOrm.VARCHAR(10)
});


// Using Primary Key & Foreign Key
const userDetails = new Init("userdetails", {
    id: {
        type: SnowflakeOrm.INT,
        primaryKey: true,			// Primary Key
        autoIncrement: true			// Auto Increment
    },
    userId: {
        type: SnowflakeOrm.INT,
        allowNull: false,			// Do Not Allow Null Value
        references: {				// Foreign Key
            model: 'user', 			// 'user' refers to table name
            column: 'id', 			// 'id' refers to column name in user table
        }
    },
    phone: SnowflakeOrm.INT,
    gender: SnowflakeOrm.VARCHAR(10)
});

Create dynamic table using Model

Model.create().then(data => {
    res.send(data);
}).catch(err => {
    console.log(err);
});

Snowflake doesn't support Primary Key, Foreign Key, Unique Key constraint. For that reason don't use these constraint. But constraint are still present in this ORM.

Fetch Data

Getting data from database.

Find All

Model.find({}).then(res => {
    res.send(res);
}).catch(err => {
    res.send(err);
});

Column List  (With All Column)

Model.find({
    column: []
}).then(res => {
    res.send(res);
});

Column List  (With Specific Column)

Model.find({
    column: [column1, column2, column3]
}).then(res => {
    res.send(res);
});

Where Clause  (With Equal)

Model.find({
    where: {
        condition: {
            fname: 'Swarup',
            lname: 'Saha',
        }
    }
}).then(res => {
    res.send(res);
}).catch(err => {
    res.send(err);
});

Where Clause  (With Operator)

LIKE

where: {
    operator: {
        LIKE: [
            {
                filed: 'fname',
                value: 'A%'
            }
        ]
    }
}

NOT LIKE

operator: {
    NOTLIKE: [
        {
            filed: 'fname',
            value: 'A%'
        }
    ]
}

BETWEEN

operator: {
    BETWEEN: ['age', 25, 28]
}

NOT BETWEEN

operator: {
    NOTBETWEEN: ['age', 25, 28]
}

IN

operator: {
    IN: ['age', 24, 26, 28]
}

//Or

operator: {
    IN: {
        filed: 'age',
        value: [24, 26, 28]
    }
}

NOT IN

operator: {
    NOTIN: ['age', 24, 26, 28]
}

//Or

operator: {
    NOTIN: {
        filed: 'age',
        value: [24, 26, 28]
    }
}

GREATER THEN

operator: {
    GT: ['age', 25]
}

GREATER THEN OR EQUAL

operator: {
    GTE: ['age', 25]
}

LESS THEN

operator: {
    LT: ['age', 27]
}

LESS THEN OR EQUAL

operator: {
    LTE: ['age', 27]
}

Order By

Model.find({
    order: {
        field: 'column',
        orderBy: 'DESC'	// For Descending order DESC & for Ascending Order ASC. Default is Ascending order
    }
}).then(res => {
    res.send(res);
});

Distinct

Model.find({
    column: [column1, column2, column3],
    distinct: true,
    where: {}
}).then(res => {
    res.send(res);
}).catch(err => {
    res.send(err);
});

Limit & Offset

Model.find({
    limit: [4, 1]	// 1st Parameter for Limit & 2nd Parameter for Offset
}).then(res => {
    res.send(res);
});

Only LIMIT

Model.find({
    limit: 4
}).then(res => {
    res.send(res);
});

Function

Count()

Model.findByFunction({
    functions: {
        name: 'COUNT',
        option: [{
            column: 'column1',
            as: 'count',
            distinct: true
        }]
    }
    where: {}		// Optional
}).then(res => {
    res.send(res);
});

Avg()

functions: {
    name: 'AVG',
    option: [{
        column: 'column1',
        as: 'Average',
        distinct: false
    }]
}

Max()

functions: {
    name: 'MAX',
    option: [{
        column: 'column1',
        as: 'maximum',
        distinct: true
    }]
}

Min()

functions: {
    name: 'MIN',
    option: [{
        column: 'column1',
        as: 'minimum',
        distinct: false
    }]
}

Sum()

functions: {
    name: 'SUM',
    option: [{
        column: 'column1',
        as: 'sum1',
        distinct: false
    }, {
        column: 'column2',
        as: 'sum2',
        distinct: false
    }]
}

CRUD

Insert

If you want primary key constraint. But Snowflake doesn't support Primary Key. It's accept duplicate value. So use ID object in Models. require: true. Its autometicly create unique id & insert into table.

Model.save(req.body).then(res => {
    res.send(res);
}).catch(err => {
    res.send(err);
});

Update

Model.update(req.body, {
    where: {
        condition: {
            fname: 'Swarup',
            lname: 'Saha',
        }
    }
}).then(res => {
    res.send(res);
}).catch(err => {
    res.send(err);
});

UpdateByID

Model.updateById(req.body, id).then(res => {
    res.send(res);
}).catch(err => {
    res.send(err);
});

Delete

Model.update({
    where: {
        condition: {
            fname: 'Swarup',
            lname: 'Saha',
        }
    }
}).then(res => {
    res.send(res);
}).catch(err => {
    res.send(err);
});

DeleteByID

Model.deleteById(id).then(res => {
    res.send(res);
}).catch(err => {
    res.send(err);
});

Raw Query

const Query = require('snowflake-orm').query;

With Params

let sql = "SELECT * FROM USER WHERE FNAME = ?";
Query(sql, ['Swarup']).then(data => {
    res.send(data);
}).catch(err => {
    console.log(err);
});

Without Params

let sql = "SELECT * FROM USER";
Query(sql).then(data => {
    res.send(data);
}).catch(err => {
    console.log(err);
});

Joining 

Inner Join

let obj = {
    column: ['fname'],
    eqColumn: 'id',
    include: [{
        model: Model2,
        column: ['homeTown'],
        eqcolumn: 'userId'
    }, {
        model: Model3,
        column: ['image'],
        eqColumn: 'userId'
    }],
    where: {
        operator: {
            GT: ['age', 26]
        }
    }
}

Model.innerJoin(obj).then(data => {
    res.send(data);
}).catch(err => {
    console.log(err);
});

Right Join

Model.rightJoin(obj).then(data => {
    res.send(data);
}).catch(err => {
	console.log(err);
});

Left Join

Model.leftJoin(obj).then(data => {
    res.send(data);
}).catch(err => {
    console.log(err);
});

Full Join

Model.fullJoin(obj).then(data => {
    res.send(data);
}).catch(err => {
    console.log(err);
});

SubQuery 

Condition

let obj = {
    column:  ['fname', 'lname'],
    where: {
        condition: {
            id: {
                subQuery: {
                    model: Model2,
                    column: ['userId'],
                    where: {
                        condition: {
                            image: 'Swarup Profile Pics.jpg'
                        }
                    }
                }
            }
        }
    }
}


Model.find(obj).then(data => {
    res.send(data);
}).catch(err => {
    console.log(err);
});