RefQL
A Node.js and Deno library for composing and running SQL queries.
Installation
yarn install refql
# or
npm install refql
Getting started
import postgres from "https://deno.land/x/postgresjs/mod.js";
import { rql, sql } from "https://deno.land/x/refql/mod.ts";
const playerQuery = sql`
select id, first_name, last_name
from player
`;
const byId = sql<{id: number}>`
where id = ${p => p.id}
`;
const getPlayerById =
playerQuery.concat (byId);
const pool = postgres ({
// ...pool options
});
// query: select id, first_name, last_name from player where id = $1
// values: [1]
const querier = <T>(query: string, values: any[]) =>
pool.query (query, values).then (({ rows }) => rows as T[]);
getPlayerById.run<Player> (querier, { id: 1 }).then (console.log);
// [ { id: 1, first_name: 'Estelle', last_name: 'Vangelisti' } ]
// alternative (including team)
const alternative = rql<{id: number}>`
player (id: ${p => p.id}) {
id
first_name
last_name
- team {
name
}
}
`;
alternative.run<Player> (querier, { id: 1 }).then (console.log);
// [ { id: 1, first_name: 'Estelle', last_name: 'Vangelisti', team: { name: 'FC Mezujfo' } } ]
Table of contents
- Querier
- Composition by Fantasy Land
- Composition by placeholders
- Raw
- In
- Table
- RQLTag
- Function placeholder
Querier
The querier should be passed as the first argument to the run
function. It should have the type signature <T>(query: string, values: any[]) => Promise<T[]>
. This function is a necessary in-between piece to make RefQL independent from database clients. This allows you to choose your own client.
import * as mySQL from "https://deno.land/x/mysql2/mod.ts";
const mySqlPool = mySQL.createPool ({
// ...pool options
});
const mySQLQuerier = <T>(query: string, values: any[]): Promise<T[]> =>
new Promise ((res, rej) => {
pool.query (query.replace (/\$\d/g, "?"), values, (error, rows) => {
if (error) {
rej (error);
return;
}
res (rows as T[]);
});
});
sql`select * from player`.run<Player> (mySQLQuerier, {});
Composition by Fantasy Land
SQLTag implements 2 algebraic structures specified by Fantasy Land: Semigroup, Functor.
Semigroup
Compliant implementation of fantasy-land/concat
.
const playerQuery = sql`
select id, first_name, last_name
from player
`;
const paginate = sql<{limit: number; offset: number}>`
limit ${p => p.limit}
offset ${p => p.offset}
`;
const getPlayerPage =
// or player.concat (paginate)
playerQuery["fantasy-land/concat"] (paginate);
getPlayerPage.run<Player> (querier, { limit: 3, offset: 5 }).then (console.log);
// [
// { id: 6, first_name: 'Nicholas', last_name: 'Ortiz' },
// { id: 7, first_name: 'Leila', last_name: 'Leclerc' },
// { id: 8, first_name: 'David', last_name: 'Sassi' }
// ]
Functor
Compliant implementation of fantasy-land/map
.
const playerQuery = sql`
select id, first_name, last_name
from player
`;
const orderByLastName = (values: any[]) =>
values.concat ("order by last_name limit 2");
const orderedPlayers =
// or player.map (orderByLastName)
playerQuery["fantasy-land/map"] (orderByLastName);
orderedPlayers.run<Player> (querier, {}).then (console.log);
// [
// { id: 326, first_name: 'Lucy', last_name: 'Acciai' },
// { id: 6, first_name: 'Katie', last_name: 'Adam' }
// ]
Composition by placeholders
const paginate = sql<{limit: number; offset: number}>`
limit ${p => p.limit}
offset ${p => p.offset}
`;
const orderByLastName = sql`
select id, first_name, last_name
from player
${sql`order by last_name`}
${paginate}
`;
orderByLastName.run<Player> (querier, { limit: 2, offset: 5 }).then (console.log);
// [
// { id: 368, first_name: 'Fanny', last_name: 'Aguilar' },
// { id: 508, first_name: 'Ruth', last_name: 'Albers' }
// ]
Raw
With the Raw data type it's possible to inject values as raw text into the query.
import { Raw, sql } from "https://deno.land/x/refql/mod.ts";
// dynamic properties
const idField = "id";
const bdField = "birthday";
const getPlayerById = sql<{ id: number }>`
select id, last_name, age (${Raw (bdField)})::text
from player where ${Raw (idField)} = ${p => p.id}
`;
// query: select id, last_name, age (birthday)::text from player where id = $1
// values: [1]
getPlayerById.run<Player> (querier, { id: 1 }).then (console.log);
// [ { id: 1, last_name: 'Cecchini', age: '30 years 4 mons 14 days' } ]
In
import { In, sql } from "https://deno.land/x/refql/mod.ts";
const getFirstThree = sql`
select id, last_name from player
where id ${In ([1, 2, 3])}
`;
// query: select id, last_name from player where id in ($1,$2,$3)
// values: [1, 2, 3]
getFirstThree.run<Player> (querier, {}).then (console.log);
// [
// { id: 1, last_name: 'Cecchini' },
// { id: 2, last_name: 'Schultz' },
// { id: 3, last_name: 'Scheffer' }
// ]
Table
Table (name, as, schema) can be used dynamically inside a SQLTag.
const select = (tableName: string, columns: string[] = []) => sql`
select ${Raw (columns.map (c => `t.${c}`).join (", "))} from ${Table (tableName, "t", "public")}
`;
const selectPlayers = select ("player", ["id", "last_name"]).concat (paginate);
// query: select t.id, t.last_name from public.player t limit $1 offset $2
// values: [2. 3]
selectPlayers.run<Player> (querier, { limit: 2, offset: 3 }).then (console.log);
// [ { id: 4, last_name: 'Tapinassi' }, { id: 5, last_name: 'Freeman' } ]
RQLTag
To include referenced data and end up with an aggregated result without having to write joins.
Belongs to
Useful when you're dealing with a n:1
relationship. The symbol for this type is a dash sign -
.
const getPlayerById = rql<{ id: number }>`
player (id: ${p => p.id}) {
id
last_name
- team {
id
name
}
}
`;
getPlayerById.run<Player> (querier, { id: 1 }).then (console.log);
// [ { id: 1, last_name: 'Cecchini', team: { id: 1, name: 'FC Ocvila' } } ]
Has many
Useful when you're dealing with a 1:n
relationship. The symbol for this type is a less-than sign <
.
const getTeamById = rql<{ id: number }>`
team (id: ${p => p.id}) {
id
name
< player: players {
id
first_name
last_name
}
}
`;
getTeamById.run<Player> (querier, { id: 1 }).then (console.log);
// {
// id: 1,
// name: 'FC Wuharazi',
// players: [
// { id: 1, first_name: 'Mike', last_name: 'Buckley' },
// { id: 2, first_name: 'Lela', last_name: 'Morales' },
// { id: 3, first_name: 'Delia', last_name: 'Brandt' },
// ...
// ]
// }
Many to many
Useful when you're dealing with a n:m
relationship and a junction table like player_game. The symbol for this type is the letter x sign x
.
const getPlayerById = rql<{ id: number }>`
player (id: ${p => p.id}) {
id
first_name
last_name
x game: games {
id
result
}
}
`;
getPlayerById.run<Player> (querier, { id: 1 }).then (console.log);
// {
// id: 1,
// first_name: 'Anne',
// last_name: 'Herrmann',
// games: [
// { id: 1, result: '4 - 0' },
// { id: 2, result: '1 - 0' },
// { id: 3, result: '1 - 4' }
// ]
// };
RQLTag keywords
Keywords can be passed as arguments after a table declaration.
limit and offset (Number)
To limit the number of rows returned and skip rows, ideal for paging.
const playerQuery = rql`
player (limit: 3, offset: 0) {
id
first_name
last_name
}
`;
playerQuery.run<Player> (querier, {}).then (console.log);
// [
// { id: 1, first_name: 'Logan', last_name: 'Groen' },
// { id: 2, first_name: 'Hannah', last_name: 'Boretti' },
// { id: 3, first_name: 'Robert', last_name: 'Da SilvaSilva' }
// ]
id (Number|String)
To easily retrieve a row by its id.
const getPlayerById = rql<{ id: number }>`
player (id: ${p => p.id}) {
id
first_name
last_name
}
`;
getPlayerById.run<Player> (querier, { id: 1 }).then (console.log);
// [ { id: 1, first_name: 'Logan', last_name: 'Groen' } ]
lref and rref (comma-separated String)
To provide refs between two tables. When these aren't provided, RefQL tries to guess them.
const getPlayers = rql`
player (id: 1) {
id
first_name
last_name
- team (lref: "team_id", rref: "id") {
id
name
}
}
`;
getPlayers.run<Player> (querier, {}).then (console.log);
// [
// {
// id: 1,
// first_name: 'Logan',
// last_name: 'Groen',
// team: { id: 1, name: 'FC Dezrano' }
// }
// ]
lxref, rxref (comma-separated String) and xtable (String)
To provide refs between the junction table (xtable) and the two involved tables.
const getPlayers = rql`
player (id: 1) {
id
first_name
last_name
x game: games (
lref: "id", lxref: "player_id",
rref: "id", rxref: "game_id",
xtable: "player_game"
) {
id
result
}
}
`;
getPlayers.run<Player> (querier, {}).then (console.log);
// {
// id: 1,
// first_name: 'Logan',
// last_name: 'Groen',
// games: [
// { id: 1, result: '5 - 5' },
// { id: 2, result: '1 - 4' },
// { id: 3, result: '3 - 1' },
// ...
// ]
// }
All
To select all columns.
const getPlayer = rql`
player (id: 1) {
*
}
`;
getPlayer.run<Player> (querier, {}).then (console.log);
// [
// {
// id: 1,
// first_name: 'Logan',
// last_name: 'Groen',
// birthday: 1989-06-26T22:00:00.000Z,
// team_id: 1,
// position_id: 1
// }
// ]
Aliases
Column names and function names can be aliased by placing 1 colon :
after the name followed by the alias.
const getPlayer = rql`
player (id: 1) {
id: identifier
concat: fullName (last_name, ' ', first_name)
}
`;
getPlayer.run<Player> (querier, {}).then (console.log);
// [ { identifier: 1, fullname: 'Groen Logan' } ]
Casts
Column names, function names and variables can be cast to another type by placing 2 colons ::
after the name, or if you are already using an alias then you must place them after the alias.
const getPlayer = rql`
player (id: 1) {
id::text
substring: birthYear::int (birthday::text, 0, ${"5"}::int)
}
`;
getPlayer.run<Player> (querier, {}).then (console.log);
// [ { id: '1', birthyear: 1989 } ]
Subselects
To include a nested select expression. A subselect must be a SQLTag.
const goalCount = sql`
select count (*)
from goal
where player_id = ${(_p, t) => t}.id
`;
const getPlayer = rql`
player (id: 9) {
id
first_name
last_name
${goalCount}:goal_count::int
}
`;
getPlayer.run<Player> (querier, {}).then (console.log);
// [ { id: 9, first_name: 'Lydia', last_name: 'Graham', goal_count: 4 } ]
Functions
Running functions is not difficult at all and the example below is quite self-explanatory.
const position =
sql`select name from position where id = ${(_p, t) => t}.position_id`;
const getPlayer = rql`
player (id: 1) {
id
date_part: age ("year", age (birthday))
concat: fullNameAndPosition (upper (first_name), " ", upper (lower (last_name)), ", ", ${position})
}
`;
getPlayer.run<Player> (querier, {}).then (console.log);
// [ { id: 1, age: 33, fullnameandposition: 'LOGAN GROEN, Goalkeeper' } ]
Literals
The following literals are supported: Boolean, String, Number, and Null.
const getPlayer = rql`
player (id: 1) {
"age": numberOfYears
concat: literals(true, "_", false, "_", 5)
null: nothing
true: correct
false
}
`;
getPlayer.run<Player> (querier, {}).then (console.log);
// [
// {
// numberofyears: 'age',
// literals: 't_f_5',
// nothing: null,
// correct: true,
// bool: false
// }
// ]
Composition by Fantasy Land
RQLTag implements only 1 algebraic structure specified by Fantasy Land: Functor. RQLTag doesn't implement fantasy-land/concat
because the associativity law wouldn't hold.
Functor
Compliant implementation of fantasy-land/map
.
import { Root, rql, RQLTag, sql } from "https://deno.land/x/refql/mod.ts";
const playerQuery = rql`
player (id: 9) { * }
`;
const teamQuery = rql`
team { * }
`;
const goalQuery = rql`
goal { * }
`;
const belongsTo = <Params>(tag: RQLTag<Params>) => <Params2>(node: Root<Params2>) => {
return node.addMember (tag.node.toBelongsTo ());
};
const hasMany = <Params>(tag: RQLTag<Params>, as: string) => <Params2>(node: Root<Params2>) => {
return node.addMember (tag.node.toHasMany ().setAs (as));
};
const getPlayer = playerQuery
.map (belongsTo (teamQuery))
.map (hasMany (goalQuery, "goals"));
getPlayer.run<Player> (querier, {}).then (console.log);
// [
// {
// id: 9,
// first_name: 'Lydia',
// last_name: 'Graham',
// birthday: 2004-12-04T23:00:00.000Z,
// team_id: 1,
// position_id: 9,
// team: { id: 1, name: 'FC Dezrano', league_id: 1 },
// goals: [
// { id: 23, game_id: 4, player_id: 9, own_goal: false, minute: 45 },
// { id: 30, game_id: 5, player_id: 9, own_goal: false, minute: 43 },
// { id: 38, game_id: 7, player_id: 9, own_goal: false, minute: 4 },
// { id: 39, game_id: 7, player_id: 9, own_goal: false, minute: 20 }
// ]
// }
// ]
Composition by placeholders
const teamQuery = rql`
team { * }
`;
const goalQuery = rql`
goal { * }
`;
const getPlayerById = rql<{ id: number }>`
${Table ("player")} {
*
- ${teamQuery}
< ${goalQuery}:goals
${(p, t) => sql`
where ${t}.id = ${p.id}
`}
}
`;
getPlayerById.run<Player> (querier, { id: 9 }).then (console.log);
// [
// {
// id: 9,
// first_name: 'Lydia',
// last_name: 'Graham',
// birthday: 2004-12-04T23:00:00.000Z,
// team_id: 1,
// position_id: 9,
// team: { id: 1, name: 'FC Dezrano', league_id: 1 },
// goals: [
// { id: 23, game_id: 4, player_id: 9, own_goal: false, minute: 45 },
// { id: 30, game_id: 5, player_id: 9, own_goal: false, minute: 43 },
// { id: 38, game_id: 7, player_id: 9, own_goal: false, minute: 4 },
// { id: 39, game_id: 7, player_id: 9, own_goal: false, minute: 20 }
// ]
// }
// ]
Function placeholder
If you use a function placeholder inside sql
or rql
, the first parameter of that function will be the object that you pass as the second argument to run
. Inside rql
, u can also access the table that you're working on through the second parameter of the function placeholder.
const getPlayer = rql<{ limit: number }>`
player (limit: ${p => p.limit}){
*
${sql`
order by ${(_p, t) => t}.last_name desc
`}
}
`;
const equivalent = rql<{ limit: number }>`
player (limit: ${p => p.limit}){
*
${(_p, t) => sql`
order by ${t}.last_name desc
`}
}
`;
getPlayer.run<Player> (querier, { limit: 1 }).then (console.log);
// [
// {
// id: 217,
// first_name: 'Hallie',
// last_name: 'Zoppi',
// birthday: 1996-09-05T22:00:00.000Z,
// team_id: 20,
// position_id: 8
// }
// ]