@rajuvais03/mysql-orm

ORM is a easy way to build query for Mysql, Sqlite, PostgresSQL. compatible, Supportable for Node v10+ Version

Features

• Install @rajuvais03/orm package and use for sqlite, mysql, postgress database
• Compatible for Node v10+ version
• Easy build query and run Raw query also, it's like framework ORM

Markdown is a lightweight markup language based on the formatting conventions

And of course mysql orm is open source with a [public repository][dill] on GitHub.

Installation Steps

mysql ORM requires Node.js v10+ to run. for NPM

npm install @rajuvais03/mysql-orm

for YARN

yarn install @rajuvais03/mysql-orm

Install the dependencies and devDependencies and start the server.

dependencies need here for database connectivity. This is most important when you use [mysql-orm][dill]

npm install dotenv --save

First connect with database Need to configure [.env] file

• For Mysql

DB_TYPE=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_USER=root
DB_PASS=root
DB_NAME=abc
connectionLimit=

• For SQLITE

DB_TYPE=sqlite
DB_NAME=abc.db

• For Postgress

DB_TYPE=postgres
DB_HOST=127.0.0.1
DB_PORT=3306
DB_USER=root
DB_PASS=root
DB_NAME=abc
connectionLimit=

AFTER CONNECTION YOU HAVE TO IMPORT THIS IN YOUR package.json file into script

"make-migration": "node node_modules/@rajuvais03/mysql-orm/lib/create_migration.js",
"migrate": "node node_modules/@rajuvais03/mysql-orm/lib/Migrate.js",
"make-controller": "node node_modules//@rajuvais03/mysql-orm/lib/controller.js",
"make-model": "node node_modules//@rajuvais03/mysql-orm/lib/model.js"
"make-route": "node node_modules//@rajuvais03/mysql-orm/lib/route.js"

Check connection is established

[For Migrations][dill], you have run below command for making tables

npm run make-migration create_table_tableName

After that edit migration file from migration folder, Then run command for migrate

npm run migrate

[For Model][dill], You have to run command given below

npm run make-model modelName

[For Controller][dill], You have to run command given below

npm run make-controller controllerName

[For Routes][dill], You have to run command given below

npm run make-route routeName

Developer Query Guidance, Please call using [async/await] otherwise promissing error

[Select][dill] Query using find() method

await model.find();
await model.findOne({uid:uid});

[Select][dill] Query using find and sortBy() method for sorting

await model.sortBy({"uid":"asc","name":"desc"}).find();
await model.sortBy(["uid","name"]).find();
await model.sortBy(["uid","name"],"desc").find();
await model.sortBy("uid","desc").find();

[Select][dill] Query using find and limit and offset for paging

await model.sortBy(["uid","name"],"desc").limit(10).offset(0).find();

[Select][dill] Query using find and findOne method with particular field

await model.select(["uid","desc"]).limit(10).offset(0).find();
await model.select(["uid","desc"]).limit(10).offset(0).findOne({uid:uid});

[Select][dill] Query using find with [Where][dill] clause

await model.find({"name":"raju"});
await model.find({"name":"raju","email":"[e-mail]"});
await model.find({"name":{$eq:"raju"}}); // for Equal check
await model.find({"uid":{$gt:5}}); // for Greater check
await model.find({"uid":{$gte:5}}); // for Greater Equal check
await model.find({"uid":{$lt:5}}); // for Lesser check
await model.find({"uid":{$lte:5}}); // for Lesser Equal check
await model.find({"uid":{$ne:5}}); // for not equal check
await model.find({"uid":{$btw:[5,10]}}); // for between check
await model.find({"uid":{$in:[5,10]}}); // for checking data available into array using $in
await model.find({"uid":{$nin:[5,10]}}); // for checking data not available into array using $in
await model.find({"name":{$like:"raju"}}); // for searching data using $like
await model.find({$and:[{"name":"raju"},{"name":"rahul"}]}); // using $and operator
await model.find({$or:[{"name":{$eq:"raju"}},{"name":{$eq:"rahul"}}]}); // same as $and operator
await model.find({$nor:[{"name":"raju"},{"name":"rahul"}]}); // using $nor operator
await model.find({$not:[{"name":"raju"},{"name":"rahul"}]}); // using $not operator
await model.find({ "Age": { $ngt:"25"} }); // using $ngt not greater than operator
await model.find({$or: [{"by": "tutorials point"},{"title": "MongoDB Overview"}],$and: [{"by": "tutorials point"},{"title": "MongoDB Overview"}] } ); // using $or and $and operator 
await model.find({"likes": {$gt:10}, $or: [{"by": "tutorials point"},{"title": "MongoDB Overview"}]}); // mixed operator

[Select][dill] Query using JOIN QUERY

await model.join("user_roles","user_roles.user_id","=","users.uid").join("roles","roles.uid","=","user_roles.role_id").select(["users.name","users.email","users.uid","roles.name AS role"]).find();
await model.leftJoin("user_roles","user_roles.user_id","=","users.uid").leftJoin("roles","roles.uid","=","user_roles.role_id").select(["users.name","users.email","users.uid","roles.name AS role"]).find();
await model.rightJoin("user_roles","user_roles.user_id","=","users.uid").rightJoin("roles","roles.uid","=","user_roles.role_id").select(["users.name","users.email","users.uid","roles.name AS role"]).find();
await model.fullJoin("user_roles","user_roles.user_id","=","users.uid").fullJoin("roles","roles.uid","=","user_roles.role_id").select(["users.name","users.email","users.uid","roles.name AS role"]).find();

[Select][dill] Query using GROUP BY

await model.leftJoin("user_roles","user_roles.user_id","=","users.uid").leftJoin("roles","roles.uid","=","user_roles.role_id").select(["users.name","users.email","users.uid","roles.name AS role"]).groupBy("role").find();
await model.leftJoin("user_roles","user_roles.user_id","=","users.uid").leftJoin("roles","roles.uid","=","user_roles.role_id").select(["users.name","users.email","users.uid","roles.name AS role"]).groupBy(["role","users.name"]).find();

[Select][dill] Query using [min, max, count, sum, avg, case etc.]

await model.select(["uid","sum(status) AS status"]).find()
await model.select(["uid","avg(status) AS status"]).find();
await model.select(["uid","count(status) AS status"]).find();
await model.select(["uid","min(status) AS status"]).find();
await model.select(["uid","max(status) AS status"]).find();
await model.select(["uid","CASE WHEN emailON = 1 THEN 'true' ELSE 'false' END AS status"]).find();

[Select][dill] Query for Count using count method

await model.count();
await model.where("city","jabalpur").count();

[Select][dill] Query using raw query

const { DB } = require('@rajuvais03/mysql-orm')
await DB('raw query').exec()

[INSERT][dill] Query for single row insert using [insertOne] method

await model.insertOne({uid:"heladjlfaksdfka",name:"raju",email:"[e-mail]"});

[INSERT][dill] Query for multiple rows insert using [insertMany]/[insert] method

await model.insertMany([{uid:"heladjlfaksdfka",name:"raju",email:"[e-mail]"},{uid:"heladjlfaksdfkf",name:"raju1",email:"[e-mail]"},{uid:"heladjlfaksdfk1",name:"raju2",email:"[e-mail]"}]);
await model.inser({uid:"heladjlfaksdfka",name:"raju",email:"[e-mail]"});

[Condition] should be in json format like

{uid:"heladjlfaksdfkf"}

[UPDATE][dill] Query for single row insert using [updateOne] method

await model.updateOne(condtion, {uid:"heladjlfaksdfka",name:"raju",email:"[e-mail]"});

[UPDATE][dill] Query for multiple rows insert using [updateMany]/[update] method

await model.updateMany(condition,[{uid:"heladjlfaksdfka",name:"raju",email:"[e-mail]"},{uid:"heladjlfaksdfkf",name:"raju1",email:"[e-mail]"},{uid:"heladjlfaksdfk1",name:"raju2",email:"[e-mail]"}]);

[DELETE][dill] Query for single row insert using [deleteOne] method

await model.deleteOne({"uid":"how are you"});

[DELETE][dill] Query for multiple rows insert using [deleteMany]/[delete] method

await model.deleteMany({"uid":"how are you"});
await model.delete({"uid":"how are you"});

Development

mysql-orm uses for fast developing. Make a change in your file and instantaneously see your updates!

License

MIT