Sqlify
Yet another SQL query builder.
There are many sql query builders out there. But this one makes more sense to me :wink:.
Install
npm install --save sqlify
Why
-
This package is a wrapper around squel module to make it more friendly. (Check that package to know its maintenance status)
-
Helps you to build dynamic sql queries.
-
Example use case: suppose, you are getting a POST request to insert some data to your SQL database.
You'll get the data in req.body
as {name: "Swat", age: 22, address: "ND"}
.
Now make the query like:
const resource = {
set: req.body
where: {
id: 5
}
}
sqlify(chain, resource);
Warning ⚠️: Do not ever pass queries generated on the client side to your web server for execution. The above example is only a use case. Do NOT copy paste as such.
Examples
SELECT
const { squel, sqlify } = require('sqlify');
const resource = {
field: ['name', 'age', 'address'],
where: {
name: 'Swat',
age: 22,
},
};
const chain = squel.select().from('users');
sqlify(chain, resource);
chain.toString();
Starter Guide For TypeScript
import { squel, sqlify, Resource } from 'sqlify'
const resource :Resource = {
field: ['name', 'age', 'address'],
where: {
name: 'Swat',
age: 22,
},
};
SELECT with a simple JOIN
const resource = {
field: ['user.*', 'hobbies.hobby', 'colors.favorite'],
where: {
name: 'Swat',
age: 22,
},
join: [
['hobbies', null, 'hobbies.id = user.id'],
['colors', null, 'colors.user_id = user.id'],
];
}
const chain = squel.select().from('Hero');
sqlify(chain, resource);
chain.toString();
Read the JOIN section of squel docs for more.
INSERT
const { squel, sqlify } = require('sqlify');
const resource = {
set: {
name: 'Swat',
age: 22,
},
};
const chain = sql.insert().into('users');
sqlify(chain, resource);
chain.toString();
How?
sqlify
exposes a function, module (squel) and a Resource
type (for using with TypeScript).
The function receives 2 arguments. They are:
Step 1: Require the package
const { squel, sqlify } = require('sqlify');
Step 2: Initialize chain
and resource
chain
is an instance of squel.
For example,
const chain = squel.select().from('users');
resource
is an object which contains the data to build the query.
Example:
const resource = {
field: ['name', 'age', 'address'],
where: {
name: 'Swa',
age: 22
}
};
Where, the properties of resource
object (in the above case, field
and where
) are taken from the chain function names of the squel. There are more. Refer their docs and use them accordingly.
When used with TypeScript, you should mark type of resource
with the import
ed Resource
class.
Like const resource:Resource = {...}
.
Step 3: Sqlify
sqlify(chain, resource);
sqlify
function wont return anything. It simply do things in in-place.
Step 4: Watch stuff
const query = chain.toString();
console.log(query);
Unclear about something here? Feel free to rise an issue..
Also,
Since sqlify
takes in and out chain functions, you can modify it even after sqlify
ing it.
Example:
const chain = squel.select().from('users');
sqlify(chain, resource);
chain.limit(10);
chain.toString();
Supported Squel Functions
The following fields can be used inside the resource
object. Logic behind the usage of these functions can be found at squel docs.
| | | | |
---|
cross_join | field | join | left_join | outer_join |
returning | right_join | set | where | group |
order | | | | |
| | | | |
Contributors
v1 to v2 migration guide
-
change the way you require
the package:
- in v1, you required
sqlify
along with squel
as:
const sqlify = require('sqlify');
const squel = require('squel');
- in v2 you've to change that code into:
const { sqlify, squel } = require('sqlify');
-
change in function name: change fields:[]
to field:[]
in the resource
object.
Oh yes! it's that simple.
Change log
- v2.5.0, v2.5.1, v2.5.2
- v2.4.0
- TypeScript support and definitions
- Better docs
- v2.3.1
- enabling Greeenkeeper, better docs
- v2.3.0
- adds better error handling: (if an unsupported method is used, sqlify throws an err)
- v2.2.0
- v2.1.1
- v2.0.0
- fixing #5 and #2.
- more squel functions
- v1.0.4
- bug fix with 's in select queries
- v1.0.1, 1.0.2, 1.0.3
- bug fix (in
package.json
) - better docs
- v1.0.0
Licence
MIT © Vajahath Ahmed