Query SQL database through an array-like API.
✅ Expressive AND high-level query builder
✅ TypeScript support
✅ SQL databases:
✅ PostgreSQL
✅ SQLite
✅ MySQL
✅ MariaDB
⬜ SQL Server
⬜ Oracle
✅ Navigation properties
✅ Codegen free
✅ Surprise free, all queries produce 100% SQL
✅ Raw SQL
⬜ Migrations
⬜ Transactions
To start using qustar with PostgreSQL (the list of all supported data sources is available below) run the following command:
npm install qustar qustar-pg pgHere an example usage of qustar:
import {PgConnector} from 'qustar-pg';
import {Q} from 'qustar';
// specify a schema
const users = Q.table({
name: 'users',
schema: {
// generated is not required during insert
id: Q.i32().generated(), // 32 bit integer
firstName: Q.string(), // any text
lastName: Q.string(),
age: Q.i32().null(), // nullable integer
},
});
// compose a query
const query = users
.orderByDesc(user => user.createdAt)
// map will be translated into 100% SQL, as every other operation
.map(user => ({
name: user.firstName.concat(' ', user.lastName),
age: user.age,
}))
.limit(3);
// connect to your database
const connector = new PgConnector('postgresql:https://qustar:passwd@localhost:5432');
// run the query
console.log('users:', await query.fetch(connector));Output:
{ age: 54, name: 'Linus Torvalds' }
{ age: 29, name: 'Clark Kent' }
{ age: 18, name: 'John Smith' }The query above will be translated to:
SELECT
"s1"."age",
concat("s1"."firstName", ' ', "s1"."lastName") AS "name"
FROM
users AS "s1"
ORDER BY
("s1"."createdAt") DESC
LIMIT
3Insert/update/delete:
// insert
await users.insert({firstName: 'New', lastName: 'User'}).execute(connector);
// update
await users
.filter(user => user.id.eq(42))
.update(user => ({age: user.age.add(1)}))
.execute(connector);
// delete
await users.delete(user => user.id.eq(42)).execute(connector);To execute query against a database you need a connector. There are many ready to use connectors that wrap existing NodeJS drivers:
- PostgreSQL
- SQLite
- qustar-better-sqlite3 (recommended)
- qustar-sqlite3
- MySQL
- MariaDB
If you implemented your own connector, let me know and I will add it to the list above!
Any query starts from a table or a raw sql. We will talk more about raw queries later, for now the basic usage looks like this:
import {Q} from 'qustar';
const users = Q.table({
name: 'users',
schema: {
id: Q.i32(),
age: Q.i32().null(),
// ...
},
});In qustar you compose a query by calling query methods like .filter or .map:
const young = users.filter(user => user.age.lt(18));
const youngIds = young.map(user => user.id);
// or
const ids = users.filter(user => user.age.lt(18)).map(user => user.id);Queries are immutable, so you can reuse them safely.
For methods like .filter or .map you pass a callback which returns an expression. Expression represents a condition or operation you wish to do. Expressions are build using methods like .add or .eq:
// for arrays you would write: users.filter(x => x.age + 1 === x.height - 5)
const a = users.filter(user => user.age.add(1).eq(user.height.sub(5)));
// you can also use Q.eq to achieve the same
import {Q} from 'qustar';
const b = users.map(user => Q.eq(user.age.add(1), user.height.sub(5));We can't use native operators like + or === because JavaScript doesn't support operator overloading. You can find full list of supported expression operations here.
Now lets talk about queries and expressions.
const adults = users
// users with age >= 18
.filter(user => /* any expression */ user.age.gte(18));const userIds = users.map(user => user.id);
const user = users
// you can map to an object
.map(user => ({id: user.id, name: user.name}));
const userInfo = users
// you can map to nested objects
.map(user => ({
id: user.id,
info: {
adult: user.age.gte(18),
nameLength: user.name.length(),
},
}));const users = users
// order by age in ascending order
.orderByAsc(user => user.age)
// then order by name in descending order
.thenByDesc(user => user.name);const users = users
.orderByAsc(user => user.id)
// skip first ten users
.drop(10)
// then take only five
.limit(5);You can also use .slice method to achieve the same:
const users = users
// start = 10, end = 15
.slice(10, 15);Qustar supports .innerJoin, .leftJoin, .rightJoin and .fullJoin:
const bobPosts = posts
.innerJoin({
right: users,
condition: (post, user) => post.authorId.eq(user.id),
select: (post, author) => ({
text: post.text,
author: author.name,
}),
})
.filter(({author}) => author.like('bob%'));You can select distinct rows using .unique method:
const names = users.map(user => user.name).unique();const stats = users.groupBy({
by: user => user.age,
select: user => ({
age: user.age,
count: Expr.count(1),
averageTax: user.salary.mul(user.taxRate).mean(),
}),
});const studentNames = students.map(student => student.name);
const teacherNames = teachers.map(teacher => teacher.name);
const uniqueNames = studentNames.union(teacherNames);const studentNames = students.map(student => student.name);
const teacherNames = teachers.map(teacher => teacher.name);
const peopleCount = studentNames.unionAll(teacherNames).count();const studentNames = students.map(student => student.name);
const teacherNames = teachers.map(teacher => teacher.name);
// concat preserves original ordering
const allNames = studentNames.concat(teacherNames);const studentNames = students.map(student => student.name);
const teacherNames = teachers.map(teacher => teacher.name);
const studentAndTeacherNames = studentNames.intersect(teacherNames);const studentNames = students.map(student => student.name);
const teacherNames = teachers.map(teacher => teacher.name);
const studentOnlyNames = studentNames.except(teacherNames);const postsWithAuthor = users.flatMap(user =>
posts
.filter(post => post.authorId.eq(user.id))
.map(post => ({text: post.text, author: user.name}))
);const userExists = users.map(user => user.id).includes(42);The list of supported column types:
- boolean: true or false
- i8: 8 bit integer
- i16: 16 bit integer
- i32: 32 bit integer
- i64: 64 bit integer
- f32: 32 bit floating point number
- f64: 64 bit floating point number
- string: variable length string
You can use raw SQL like so:
import {Q, sql} from 'qustar';
const users = Q.rawQuery({
sql: sql`SELECT * from users`,
// we must specify schema so qustar knows how to compose a query
schema: {
id: Q.i32(),
age: Q.i32().null(),
},
})
.filter(user => user.age.lte(25))
.map(user => user.id);You can also use aliases in a nested query like so:
const postIds = users.flatMap(user =>
Q.rawQuery({
sql: sql`
SELECT
id
FROM
posts p
WHERE p.authorId = ${user.id}'
})`,
schema: {
id: Q.i32(),
},
});
);You can use Q.rawExpr for raw SQL in a part of an operation:
const halfIds = users.map(user => ({
halfId: Q.rawExpr({sql: sql`CAST(${user.id} as REAL) / 2`, schema: Q.f32()}),
name: user.name,
}));The query above will be translated to:
SELECT
"s1"."name",
(CAST(("s1"."id") as REAL) / 2) AS "halfId"
FROM
users AS "s1"MIT License, see LICENSE.