@ssibrahimbas/query
v0.0.4
Published
A fast, synchronized and dynamic query builder package.
Downloads
3
Maintainers
Readme
What is?
In short, the query builder. You can write complex and parameterized queries fast, plain and dynamically using the Query class, which uses the chain of responsibility pattern
!
Motivation
Generally, adding parameters to queries and parsing objects increases the distance of us as software developers to sql. This package was influenced by mongoose's Query class, aiming to make it usable in SQL databases, and that's what it ultimately does.
Run it on the drive you want! Sequelize can work with postgresql or any driver. All it needs is a driver that accepts sql code!
Looking forward to your pull requests and issues to make this package better.
Finally, all functions are gesture tested.
Installation
To include this package in your project, run the following command:
npm install @ssibrahimbas/query
or with yarn
yarn add @ssibrahimbas/query
And try this:
import { Query } from "@ssibrahimbas/query"
console.log(Query.table("users").getAll())
// SELECT * FROM users
Contributing
If you want to contribute to the project or play with the codes on your local machine, follow the steps below:
install dependencies:
npm install
or with yarn
yarn
run tests
npm run test
or with yarn
yarn test
Documentation
|Interface|
|---------|
|ISsiQuery
|
| Functions |
|--------------------------|
|table(...)
|
|select(...)
|
|groupConcat(...)
|
|least(...)
|
|max(...)
|
|min(...)
|
|sum(...)
|
|count(...)
|
|avg(...)
|
|innerJoin(...)
|
|leftJoin(...)
|
|rightJoin(...)
|
|fullOuterJoin(...)
|
|leftOuterJoin(...)
|
|rightOuterJoin(...)
|
|where(...)
|
|orWhere(...)
|
|notWhere(...)
|
|orNotWhere(...)
|
|whereNull(...)
|
|whereNotNull(...)
|
|grouped(...)
|
|in(...)
|
|notIn(...)
|
|orIn(...)
|
|orNotIn(...)
|
|findInSet(...)
|
|notFindInSet(...)
|
|orFindInSet(...)
|
|orNotFindInSet(...)
|
|between(...)
|
|notBetween(...)
|
|orBetween(...)
|
|orNotBetween(...)
|
|like(...)
|
|orLike(...)
|
|notLike(...)
|
|orNotLike(...)
|
|limit(...)
|
|pagination(...)
|
|orderBy(...)
|
|groupBy(...)
|
|having(...)
|
|query(...)
|
|get()
|
|getAll()
|
|insert(...)
|
|update(...)
|
|delete()
|
|analyze()
|
|check()
|
|checksum()
|
|optimize()
|
|repair()
|
|reset()
|
ISsiQuery
Abstract of the Query class. If you wish, you can use as follows:
import { ISsiQueue, SsiQuery } from "@ssibrahimbas/query"
const Query : ISsiQuery = new SsiQuery();
Although this doable, it is memory redundant as the new
key is constantly used. The Queue class has been developed to reset itself after each query. So you can use it with peace of mind as follows:
import { Query } from "@ssibrahimbas/query"
Functions
table
It is used to declare a table in SQL.
Abstract:
table(table: string | Array<string>) : ISsiQuery;
Example:
import { Query } from "@ssibrahimbas/query"
const query : string = Query.table("users").getAll();
// SELECT * FROM users
Using arrays:
import { Query } from "@ssibrahimbas/query"
const query : string = Query.table(["users", "products"]).analyze();
// ANALYZE TABLE users, products
select
Select the fields to use for the query
Abstract:
select(fields: Record<string,string> | Array<Record<string, string>> | string | Array<string>) : ISsiQuery;
Example:
import { Query } from "@ssibrahimbas/query"
const query : string = Query.table("users").select("id").getAll();
// SELECT id FROM users
Using arrays:
import { Query } from "@ssibrahimbas/query"
const query : string = Query.table("users").select(["id As userId", "name As userName"]).analyze();
// SELECT id As userId, name As userName FROM users
Using Records:
import { Query } from "@ssibrahimbas/query"
const data = { Id: "userId", FirstName: "firstName" };
const query : string = Query.table("users").select(data).getAll();
// SELECT Id AS userId, FirstName AS firstName FROM users
Using Records With Arrays:
import { Query } from "@ssibrahimbas/query"
const data: Array<Record<string, string>> = [
{ Id: "userId" },
{ FirstName: "firstName" },
];
const query : string = Query.table("users").select(data).getAll();
// SELECT Id AS userId, FirstName AS firstName FROM users
least
Abstract:
least(fields: Array<string>, name?: string | null) : ISsiQuery;
Example:
import { Query } from "@ssibrahimbas/query"
const query : string = Query.table("users").least(["point1", "point2", "point3"], "minPoint").getAll();
// "SELECT LEAST(point1, point2, point3) AS minPoint FROM users"
Single Parameter:
import { Query } from "@ssibrahimbas/query"
const query : string = Query.table("users").least(["point1", "point2", "point3"]).getAll();
// "SELECT LEAST(point1, point2, point3) FROM users"
groupConcat
Abstract:
groupConcat(fields: string, name?: string | null) : ISsiQuery;
Example:
import { Query } from "@ssibrahimbas/query"
const query : string = Query.table("users").groupConcat("UserId", "users").getAll();
// "SELECT GROUP_CONCAT(UserId) AS users FROM users"
Single Parameter:
import { Query } from "@ssibrahimbas/query"
const query : string = Query.table("users").groupConcat("UserId").getAll();
// "SELECT GROUP_CONCAT(UserId) FROM users"
max
Abstract:
max(fields: string, name?: string | null) : ISsiQuery;
Example:
import { Query } from "@ssibrahimbas/query"
const query : string = Query.table("users").max("id", "count").getAll();
// "SELECT MAX(id) AS count FROM users"
Single Parameter:
import { Query } from "@ssibrahimbas/query"
const query : string = Query.table("users").max("id").getAll();
// "SELECT MAX(id) FROM users"
min
Abstract:
min(fields: string, name?: string | null) : ISsiQuery;
Example:
import { Query } from "@ssibrahimbas/query"
const query : string = Query.table("users").min("id", "count").getAll();
// "SELECT MIN(id) AS count FROM users"
Single Parameter:
import { Query } from "@ssibrahimbas/query"
const query : string = Query.table("users").min("id").getAll();
// "SELECT MIN(id) FROM users"
sum
Abstract:
sum(fields: string, name?: string | null) : ISsiQuery;
Example:
import { Query } from "@ssibrahimbas/query"
const query : string = Query.table("users").sum("id", "count").getAll();
// "SELECT SUM(id) AS count FROM users"
Single Parameter:
import { Query } from "@ssibrahimbas/query"
const query : string = Query.table("users").sum("id").getAll();
// "SELECT SUM(id) FROM users"
count
Abstract:
count(fields: string, name?: string | null) : ISsiQuery;
Example:
import { Query } from "@ssibrahimbas/query"
const query : string = Query.table("users").count("id", "count").getAll();
// "SELECT COUNT(id) AS count FROM users"
Single Parameter:
import { Query } from "@ssibrahimbas/query"
const query : string = Query.table("users").count("id").getAll();
// "SELECT COUNT(id) FROM users"
avg
Abstract:
avg(fields: string, name?: string | null) : ISsiQuery;
Example:
import { Query } from "@ssibrahimbas/query"
const query : string = Query.table("users").avg("id", "avg").getAll();
// "SELECT AVG(id) AS avg FROM users"
Single Parameter:
import { Query } from "@ssibrahimbas/query"
const query : string = Query.table("users").avg("id").getAll();
// "SELECT AVG(id) FROM users"
innerJoin
Abstract:
innerJoin(table: string, field1: string, operator?: string, field2?: string) : ISsiQuery;
Example:
import { Query } from "@ssibrahimbas/query"
const query : string = Query.table("user_posts")
.select([
"user_posts.id as postId",
"user_posts.title as postTitle",
"users_username as username",
])
.innerJoin("users", "user_posts.userId", "users.id")
.getAll();
// "SELECT user_posts.id as postId, user_posts.title as postTitle, users_username as username FROM user_posts INNER JOIN users ON user_posts.userId = users.id"
leftJoin
Abstract:
leftJoin(table: string, field1: string, operator?: string, field2?: string) : ISsiQuery;
Example:
import { Query } from "@ssibrahimbas/query"
const query : string = Query.table("users")
.select(["users.id As userId", "user_posts.title As postTitle"])
.leftJoin("user_posts", "users.id", "user_posts.userId")
.getAll();
// "SELECT users.id As userId, user_posts.title As postTitle FROM users LEFT JOIN user_posts ON users.id = user_posts.userId"
rightJoin
Abstract:
rightJoin(table: string, field1: string, operator?: string, field2?: string) : ISsiQuery;
Example:
import { Query } from "@ssibrahimbas/query"
const query : string = Query.table("user_posts")
.select(["users.id As userId", "user_posts.title As postTitle"])
.rightJoin("users", "user_posts.userId", "users.id")
.getAll();
// "SELECT users.id As userId, user_posts.title As postTitle FROM user_posts RIGHT JOIN users ON user_posts.userId = users.id"
fullOuterJoin
Abstract:
fullOuterJoin(table: string, field1: string, operator?: string, field2?: string) : ISsiQuery;
Example:
import { Query } from "@ssibrahimbas/query"
const query : string = Query.table("users")
.select(["users.id As userId", "user_posts.title As postTitle"])
.fullOuterJoin("user_posts", "users.id", "user_posts.userId")
.getAll();
// "SELECT users.id As userId, user_posts.title As postTitle FROM users FULL OUTER JOIN user_posts ON users.id = user_posts.userId"
leftOuterJoin
Abstract:
leftOuterJoin(table: string, field1: string, operator?: string, field2?: string) : ISsiQuery;
Example:
import { Query } from "@ssibrahimbas/query"
const query : string = Query.table("users")
.select(["users.id As userId", "user_posts.title As postTitle"])
.leftOuterJoin("user_posts", "users.id", "user_posts.userId")
.getAll();
// "SELECT users.id As userId, user_posts.title As postTitle FROM users LEFT OUTER JOIN user_posts ON users.id = user_posts.userId"
rightOuterJoin
Abstract:
rightOuterJoin(table: string, field1: string, operator?: string, field2?: string) : ISsiQuery;
Example:
import { Query } from "@ssibrahimbas/query"
const query : string = Query.table("user_posts")
.select(["users.id As userId", "user_posts.title As postTitle"])
.rightOuterJoin("users", "user_posts.userId", "users.id")
.getAll();
// "SELECT users.id As userId, user_posts.title As postTitle FROM user_posts RIGHT OUTER JOIN users ON user_posts.userId = users.id"
where
Abstract:
where(
where: string | Array<string>,
operator?: string | Array<string> | boolean | null,
val?: string | null | number,
type?: string,
andOr?: string
) : ISsiQuery;
Example:
import { Query } from "@ssibrahimbas/query"
const query : string = Query.table("users")
.where("id", "=", "1")
.getAll();
// "SELECT * FROM users WHERE id = '1'"
orWhere
Abstract:
orWhere(
where: string | Array<string>,
operator?: string | Array<string> | boolean | null,
val?: string | null | number
) : ISsiQuery;
Example:
import { Query } from "@ssibrahimbas/query"
const query : string = Query.table("users")
.where("age", "20")
.orWhere("age", ">", "25")
.getAll();
// "SELECT * FROM users WHERE age = '20' OR age > '25'"
notWhere
Abstract:
notWhere(
where: string | Array<string>,
operator?: string | Array<string> | boolean | null,
val?: string | null | number
) : ISsiQuery;
Example:
import { Query } from "@ssibrahimbas/query"
const query : string = Query.table("users")
.where("age", "20")
.notWhere("age", ">", "25")
.getAll();
// "SELECT * FROM users WHERE age = '20' AND NOT age > '25'"
orNotWhere
Abstract:
orNotWhere(
where: string | Array<string>,
operator?: string | Array<string> | boolean | null,
val?: string | null | number
) : ISsiQuery;
Example:
import { Query } from "@ssibrahimbas/query"
const query : string = Query.table("users")
.where("age", "20")
.orNotWhere("age", ">", "25")
.getAll();
// "SELECT * FROM users WHERE age = '20' OR NOT age > '25'"
whereNull
Abstract:
whereNull(
where: string,
not?: boolean
) : ISsiQuery;
Example:
import { Query } from "@ssibrahimbas/query"
const query : string = Query.table("users").whereNull("email").getAll();
// "SELECT * FROM users WHERE email IS NULL"
whereNotNull
Abstract:
whereNotNull(
where: string
) : ISsiQuery;
Example:
import { Query } from "@ssibrahimbas/query"
const query : string = Query.table("users").whereNotNull("email").getAll();
// "SELECT * FROM users WHERE email IS NOT NULL"
grouped
Abstract:
grouped(
callback: (q: ISsiQuery) => any
) : ISsiQuery;
Example:
import { Query } from "@ssibrahimbas/query"
const query : string = Query.table("users")
.grouped((q) => {
q.where("country", "TURKEY").orWhere("country", "ENGLAND");
})
.getAll();
// "SELECT * FROM users WHERE (country = 'TURKEY' OR country = 'ENGLAND')"
in
Abstract:
in(
field: string,
keys: Array<string | number>,
type?: string,
andOr?: string
) : ISsiQuery;
Example:
import { Query } from "@ssibrahimbas/query"
const query : string = Query.table("users")
.in("state", [1, 2, 3, 4])
.getAll();
// "SELECT * FROM users WHERE state IN ('1', '2', '3', '4')"
notIn
Abstract:
notIn(
field: string,
keys: Array<string | number>
) : ISsiQuery;
Example:
import { Query } from "@ssibrahimbas/query"
const query : string = Query.table("test")
.where("active", "1")
.notIn("id", [1, 2, 3])
.getAll();
// "SELECT * FROM test WHERE active = '1' AND id NOT IN ('1', '2', '3')"
orIn
Abstract:
orIn(
field: string,
keys: Array<string | number>
) : ISsiQuery;
Example:
import { Query } from "@ssibrahimbas/query"
const query : string = Query.table("test")
.where("active", "1")
.orIn("id", [1, 2, 3])
.getAll();
// "SELECT * FROM test WHERE active = '1' OR id IN ('1', '2', '3')"
orNotIn
Abstract:
orNotIn(
field: string,
keys: Array<string | number>
) : ISsiQuery;
Example:
import { Query } from "@ssibrahimbas/query"
const query : string = Query.table("test")
.where("active", "1")
.orNotIn("id", [1, 2, 3])
.getAll();
// "SELECT * FROM test WHERE active = '1' OR id NOT IN ('1', '2', '3')"
findInSet
Abstract:
findInSet(
field: string,
key: string | number,
type?: string,
andOr?: string
) : ISsiQuery;
Example:
import { Query } from "@ssibrahimbas/query"
const query : string = Query.table("test")
.where("active", "1")
.findInSet("selected_tests", 1)
.getAll();
// "SELECT * FROM test WHERE active = '1' AND FIND_IN_SET (1, selected_tests)"
notFindInSet
Abstract:
notFindInSet(
field: string,
key: string | number
) : ISsiQuery;
Example:
import { Query } from "@ssibrahimbas/query"
const query : string = Query.table("test")
.where("active", "1")
.notFindInSet("selected_tests", 1)
.getAll();
// "SELECT * FROM test WHERE active = '1' AND NOT FIND_IN_SET (1, selected_tests)"
orFindInSet
Abstract:
orFindInSet(
field: string,
key: string | number
) : ISsiQuery;
Example:
import { Query } from "@ssibrahimbas/query"
const query : string = Query.table("test")
.where("active", "1")
.orFindInSet("selected_tests", 1)
.getAll();
// "SELECT * FROM test WHERE active = '1' OR FIND_IN_SET (1, selected_tests)"
orNotFindInSet
Abstract:
orNotFindInSet(
field: string,
key: string | number
) : ISsiQuery;
Example:
import { Query } from "@ssibrahimbas/query"
const query : string = Query.table("test")
.where("active", "1")
.orNotFindInSet("selected_tests", 1)
.getAll();
// "SELECT * FROM test WHERE active = '1' OR NOT FIND_IN_SET (1, selected_tests)"
between
Abstract:
between(
field: string,
value1: string | number,
value2: string | number,
type?: string,
andOr?: string
) : ISsiQuery;
Example:
import { Query } from "@ssibrahimbas/query"
const query : string = Query.table("test")
.where("active", "1")
.between("age", 12, 35)
.getAll();
// "SELECT * FROM test WHERE active = '1' AND (age BETWEEN '12' AND '35')"
notBetween
Abstract:
notBetween(
field: string,
value1: string | number,
value2: string | number
) : ISsiQuery;
Example:
import { Query } from "@ssibrahimbas/query"
const query : string = Query.table("test")
.where("active", "1")
.notBetween("age", 12, 35)
.getAll();
// "SELECT * FROM test WHERE active = '1' AND (age NOT BETWEEN '12' AND '35')"
orBetween
Abstract:
orBetween(
field: string,
value1: string | number,
value2: string | number
) : ISsiQuery;
Example:
import { Query } from "@ssibrahimbas/query"
const query : string = Query.table("test")
.where("active", "1")
.orBetween("age", 12, 35)
.getAll();
// "SELECT * FROM test WHERE active = '1' OR (age BETWEEN '12' AND '35')"
orNotBetween
Abstract:
orNotBetween(
field: string,
value1: string | number,
value2: string | number
) : ISsiQuery;
Example:
import { Query } from "@ssibrahimbas/query"
const query : string = Query.table("test")
.where("active", "1")
.orNotBetween("age", 12, 35)
.getAll();
// "SELECT * FROM test WHERE active = '1' OR (age NOT BETWEEN '12' AND '35')"
like
Abstract:
like(
field: string,
data: string,
type?: string,
andOr?: string
) : ISsiQuery;
Example:
import { Query } from "@ssibrahimbas/query"
const query : string = Query.table("test")
.where("active", "1")
.like("title", "%nodeJS%")
.getAll();
// "SELECT * FROM test WHERE active = '1' AND title LIKE '%nodeJS%'"
orLike
Abstract:
orLike(
field: string,
data: string
) : ISsiQuery;
Example:
import { Query } from "@ssibrahimbas/query"
const query : string = Query.table("test")
.where("active", "1")
.orLike("title", "%nodeJS%")
.getAll();
// "SELECT * FROM test WHERE active = '1' OR title LIKE '%nodeJS%'"
notLike
Abstract:
notLike(
field: string,
data: string
) : ISsiQuery;
Example:
import { Query } from "@ssibrahimbas/query"
const query : string = Query.table("test")
.where("active", "1")
.notLike("title", "%nodeJS%")
.getAll();
// "SELECT * FROM test WHERE active = '1' AND title NOT LIKE '%nodeJS%'"
orNotLike
Abstract:
orNotLike(
field: string,
data: string
) : ISsiQuery;
Example:
import { Query } from "@ssibrahimbas/query"
const query : string = Query.table("test")
.where("active", "1")
.orNotLike("title", "%nodeJS%")
.getAll();
// "SELECT * FROM test WHERE active = '1' OR title NOT LIKE '%nodeJS%'"
limit
Abstract:
limit(
limit: number,
limitEnd?: number | null
) : ISsiQuery;
Example:
import { Query } from "@ssibrahimbas/query"
const query : string = Query.table("test").where("active", "1").limit(20).getAll();
// "SELECT * FROM test WHERE active = '1' LIMIT 20"
offset
Abstract:
offset(
offset: number
) : ISsiQuery;
Example:
import { Query } from "@ssibrahimbas/query"
const query : string = Query.table("test")
.where("active", "1")
.limit(20)
.offset(200)
.getAll();
// "SELECT * FROM test WHERE active = '1' LIMIT 20 OFFSET 200"
pagination
Abstract:
pagination(
perPage: number,
page: number
) : ISsiQuery;
Example:
import { Query } from "@ssibrahimbas/query"
const query : string = Query.table("test")
.where("active", "1")
.pagination(20, 1)
.getAll();
// "SELECT * FROM test WHERE active = '1' LIMIT 20 OFFSET 20"
orderBy
Abstract:
orderBy(
orderBy: string,
orderDir?: string | null
) : ISsiQuery;
Example:
import { Query } from "@ssibrahimbas/query"
const query : string = Query.table("test")
.where("active", "1")
.orderBy("date", "desc")
.getAll();
// "SELECT * FROM test WHERE active = '1' ORDER BY date DESC"
groupBy
Abstract:
groupBy(
groupBy: string | Array<string>
) : ISsiQuery;
Example:
import { Query } from "@ssibrahimbas/query"
const query : string = Query.table("test").where("active", "1").groupBy("id").getAll();
// "SELECT * FROM test WHERE active = '1' GROUP BY id"
having
Abstract:
having(
field: string,
operator?: string | Array<string> | null,
val?: string | null
) : ISsiQuery;
Example:
import { Query } from "@ssibrahimbas/query"
const query : string = Query.table("test")
.where("active", "1")
.groupBy("place")
.having("AVG(salary)", ">=", "3000")
.getAll();
// "SELECT * FROM test WHERE active = '1' GROUP BY place HAVING AVG(salary) >= '3000'"
query
Abstract:
query(
query: string,
values?: Array<string | number> | null
) : ISsiQuery;
Example:
import { Query } from "@ssibrahimbas/query"
const _query = "SELECT * FROM users WHERE userId = ?";
const query = Query.query(_query, ["1"]).getQuery();
// "SELECT * FROM users WHERE userId = '1'"
get
Builds the select query with a limit of 1
Abstract:
get() : string;
Example:
import { Query } from "@ssibrahimbas/query"
const query = Query.table("users").select("id, firstName").get();
// "SELECT id, firstName FROM users LIMIT 1"
getAll
Builds the select query
Abstract:
getAll() : string;
Example:
import { Query } from "@ssibrahimbas/query"
const query = Query.table("users").select("id, firstName").getAll();
// "SELECT id, firstName FROM users"
insert
Builds the insert query
Abstract:
insert(data: object) : string;
Example:
import { Query } from "@ssibrahimbas/query"
const data = {
id: "3",
firstName: "John",
};
const query = Query.table("test").insert(data);
// "INSERT INTO test (id, firstName) VALUES ('3', 'John')"
update
Builds the update query
Abstract:
update(data: object) : string;
Example:
import { Query } from "@ssibrahimbas/query"
const data = {
firstName: "John",
age: 22,
};
const query = Query.table("test").where("id", "3").update(data);
// "UPDATE test SET firstName='John', age='22' WHERE id = '3'"
delete
Builds the delete query
Abstract:
delete() : string;
Example:
import { Query } from "@ssibrahimbas/query"
const query = Query.table("test").where("id", "54").delete();
// "DELETE FROM test WHERE id = '54'"
analyze
Builds the analyze query
Abstract:
analyze() : string;
Example:
import { Query } from "@ssibrahimbas/query"
const query = Query.table("test").analyze();
// "ANALYZE TABLE test"
check
Builds the check query
Abstract:
check() : string;
Example:
import { Query } from "@ssibrahimbas/query"
const query = Query.table("test").check();
// "CHECK TABLE test"
checksum
Builds the checksum query
Abstract:
checksum() : string;
Example:
import { Query } from "@ssibrahimbas/query"
const query = Query.table("test").checksum();
// "CHECKSUM TABLE test"
optimize
Builds the optimize query
Abstract:
optimize() : string;
Example:
import { Query } from "@ssibrahimbas/query"
const query = Query.table("test").optimize();
// "OPTIMIZE TABLE test"
repair
Builds the repair query
Abstract:
repair() : string;
Example:
import { Query } from "@ssibrahimbas/query"
const query = Query.table(["test", "test_2"]).repair();
// "REPAIR TABLE test, test_2"
reset
It clears all the values of the query in the class, it is not recommended to use it externally.
Abstract:
reset() : void;
Example:
import { Query } from "@ssibrahimbas/query"
Query.reset();