mongo2sql v1.0.4
Welcome to Mongo2Sql
Hello, this is Mongo2Sql Package for Translate MongoDB Like Work to MySQL. example: "MongoDB" db.collection("accounts").find({id:user.id}).toArray(function(err,result) { ... }); "MySQL": db.connection.query("SELECT FROM accounts WHERE id = ?", user.id, function(err,result) { ... }); To avoid that the script works like: "MySQL" db.collection('accounts').find({id:user.id}).toArray(function(err,result) { ... }); and all that translated to `SELECT FROM accounts WHERE id = ${user.id}`
that is how the script works
Install
npm install --save mongo2sql
Create Connection
var db = require('mongo2sql')({
host:'localhost',
user:'root',
pass:'',
db:'mongo2sql'
});
Functions
collection: Define the Table you want to select.
Find: Create Find Query but do not execute it.
toArray: execute Find query and return array value.
sort: JSON sort (ASC,DESC).
deleteOne/remove: Create Delete query and execute it.
insert: Insert ONE document to Database.
insertMany: insert Multiple Documents to database by array.
update: Create update Query and execute it.
Collection
Select Table
No individual Work
e.g. :
db.collection('account') ...
Find
Create Find query.
No individual Work
Note: You can't use it after toArray or after sort when run find query this functions shall be the first function
toArray
Execute Find Query.
No individual Work
Note: you can't run it before find function
e.g. :
db.collection('test').find({id:3}).toArray(function(err,result) { if(err) return handle(err); console.log(result) //=> {id:3,name:"Jamal Abo Mokh"} });
Sort
Create Sort Query.
The Query Executed in toArray function
No individual Work
Note: You can't use it after toArray function or before find function
e.g. :
db.collection('test').find({id:3}).sort({id:-1}).toArray(function(err,result) { if(err) return handle(err); console.log(result) //=> {id:6,name:"Jamal Abo Mokh2"},{id:3,name:"Jamal Abo Mokh"} });
deleteOne / remove
Delete All Rows that match the condition from Table.
Note: deleteOne deletes one document in MongoDB but in this functions delete every document match the condition.
e.g. :
db.collection('accounts').deleteOne({id:5}, function(err,result) { console.log(result); //=> {ok:1,nDeleted: 0 || "Deleted Rows" } })
insert
Insert One Document to table (database)
Note: JSON tree limits to 5. See JSON translate
e.g. :
db.collection('accounts').insert({id:5,name:"Jamal Abo Mokh"}, function(err,result) { console.log(result); //=> {ok:1,nInserted: 0 || "Inserted Rows" } })
insertMany
Insert Multiple Documents to table (database) by Array
Note: JSON tree limits to 5. See JSON translate
e.g. :
db.collection('accounts').insert({id:5,name:"Jamal Abo Mokh"},{id:3,name:"Jamal Abo Mokh2"}, function(err,result) { console.log(result); //=> {ok:1,ColumnInserted: 0 || "Inserted Columns (See Auto-Column)" } })
Update
Update All Rows that match the condition from Table.
Note: JSON tree limits to 5. See JSON translate ({'name.givenName':"Jamal"})
You have to add $set to query. Update Will Not Work without $set
e.g. :
db.collection('accounts').update({id:3},{$set:{address: "Main Street"}}, function(err,result) { console.log(result); // => {ok:1, nModified: 1||0, affectedRows: (number of rows got update)} })
JSON Translate
In MySQL you can't Save JSON like MongoDB:
{
name:{
givenName:"Jamal",
familyName: "Abo Mokh"
}
}
JSON Translate is function that convert JSON to "key.key2"
e.g. :
JSON :
{
name:{
givenName:"Jamal",
familyName: "Abo Mokh"
}
}
that contain 1 trees
Translated:
name.givenName = Jamal
name.familyName = Abo Mokh
they will be stored in individual on the database but when run find query it will reconvert it to JSON.
Note: this function translate up to 5 tree in JSON"
Auto-Column
In MySQL Database you have to create Column before insert data on it but in mongodb there is not limitation for this. so there is function that every time you run insert and insertMany will create column with JSON Translated Name even if the column exist.
e.g.
Translated JSON:
name.givenName
Runs ALTER TABLE ${table_name} ADD "name.givenName" ${typeof || TEXT || DOUBLE || INT}