RefQL

A Node.js and Deno library for composing and running SQL queries.

Installation

yarn install refql
# or
npm install refql

Getting started

import { Pool } from "pg";
import { 
  BelongsTo, NumberProp, 
  StringProp, Table 
} from "refql";

// id Prop
const id = NumberProp ("id");

// Tables
const Player = Table ("player", [
  id,
  StringProp ("firstName", "first_name"),
  StringProp ("lastName", "last_name"),
  BelongsTo ("team", "team")
]);

const Team = Table ("team", [
  id,
  StringProp ("name")
]);

// select components
const playerById = Player ([
  id,
  "firstName",
  "lastName",
  Team ([
    id,
    "name"
  ]),
  id.eq<{ id: number }> (p => p.id)
]);

const pool = new Pool ({
  // ...pool options
});

const querier = async (query: string, values: any[]) => {
  const { rows } = await pool.query (query, values);

  return rows;
};

playerById ({ id: 1 }, querier).then(console.log);

// [
//   {
//     id: 1,
//     firstName: "Christine",
//     lastName: "Hubbard",
//     team: { id: 1, name: "FC Agecissak" }
//   }
// ];

Tables and References

The example below shows how to define tables and describe their references to other tables. From then on, these references can be used in a RQLTag. Relationships are created by passing the table name as a string instead of passing a Table object. This is to avoid circular dependency problems. Tables are uniquely identifiable by the combination schema and tableName (<schema>.<tableName>).

import { Pool } from "pg";
import { 
  BelongsTo, BelongsToMany, HasMany,
  HasOne, Limit, NumberProp, Offset,
  StringProp, Table
} from "refql";

const pool = new Pool ({
  // ...pool options
});

const querier = async (query: string, values: any[]) => {
  const { rows } = await pool.query (query, values);

  return rows;
};

// construct RQLTag
const Player = Table ("player", [
  NumberProp ("id"),
  StringProp ("firstName", "first_name"),
  StringProp ("lastName", "last_name"),
  BelongsTo ("team", "public.team"),
  HasOne ("rating", "rating"),
  HasMany ("goals", "goal"),
  BelongsToMany ("games", "game")
]);

const Team = Table ("public.team", [
  StringProp ("name")
]);

const Rating = Table ("rating", [
  NumberProp ("finishing"),
  NumberProp ("dribbling"),
  NumberProp ("tackling")
]);

const Game = Table ("game", [
  StringProp ("result")
]);

const Goal = Table ("goal", [
  NumberProp ("minute")
]);

const fullPlayer = Player ([
  "id",
  "firstName",
  "lastName",
  Team (["name"]),
  Goal (["minute"]),
  Rating (["*"]),
  Game (["result"]),
  Limit (1),
  Offset (8)
]);

fullPlayer ({}, querier).then (console.log);

// [
//   {
//     id: 9,
//     firstName: "Leah",
//     lastName: "Kennedy",
//     team: { name: "FC Agecissak" },
//     goals: [{ minute: 36 }, { minute: 20 }, { minute: 87 }, ...],
//     rating: { finishing: 82, dribbling: 48, tackling: 47 },
//     games: [{ result: "5 - 4" }, { result: "4 - 0" }, { result: "4 - 5" }, ...]
//   }
// ];

Ref info

RefQL tries to link 2 tables based on logical column names, using snake case. You can always point RefQL in the right direction if this doesn't work for you.

const playerBelongsToManyGames = BelongsToMany ("games", "game", {
  lRef: "id",
  rRef: "id",
  lxRef: "playerId",
  rxRef: "gameId",
  xTable: "gamePlayer"
});

Querier

The querier 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. This is also the place where you can debug or transform a query before it goes to the database or when the result is obtained. Example of a querier for mySQL:

import mySQL from "mysql2";

const mySQLPool = mySQL.createPool ({
  // ...pool options
});

const mySQLQuerier = <T>(query: string, values: any[]): Promise<T[]> =>
  new Promise ((res, rej) => {
    mySQLPool.query (query.replace (/\$\d/g, "?"), values, (error, rows) => {
      if (error) {
        rej (error);
        return;
      }
      res (rows as T[]);
    });
  });

Set a default querier

import { setDefaultQuerier } from "refql";

const querier = async (query: string, values: any[]) => {
  const { rows } = await pool.query (query, values);

  return rows;
};

setDefaultQuerier (querier);

const firstTeam = Player ([
  id,
  "firstName",
  "lastName",
  Limit (10),
  id.asc ()
]);

// no need to provide a querier anymore
firstTeam ({ limit: 10 }).then (console.log);

Convert Promise output to something else

U can use Module augmentation in TypeScript to register another container type.

import { setConvertPromise } from "refql";

declare module "refql" {
  interface RQLTag<TableId extends string = any, Params = any, Output = any> {
    (params?: Params, querier?: Querier): Task<Output>;
  }
}

class Task<Output> {
  fork: (rej: (e: any) => void, res: (x: Output) => void) => void;

  constructor(fork: (rej: (e: any) => void, res: (x: Output) => void) => void) {
    this.fork = fork;
  }
}

// natural transformation
const promiseToTask = <Output>(p: Promise<Output>) =>
  new Task<Output> ((rej, res) => p.then (res).catch (rej));

setConvertPromise (promiseToTask);

const firstTeam = Player ([
  id,
  "firstName",
  "lastName",
  Limit (10),
  id.asc ()
]);

// `fork` instead of `then`
firstTeam ().fork (console.error, console.log);

// [
//   { id: 1, firstName: "Christine", lastName: "Hubbard" },
//   { id: 2, firstName: "Emily", lastName: "Mendez" },
//   { id: 3, firstName: "Stella", lastName: "Kubo" },
//   { id: 4, firstName: "Celia", lastName: "Misuri" },
//   { id: 5, firstName: "Herbert", lastName: "Okada" },
//   { id: 6, firstName: "Terry", lastName: "Bertrand" },
//   { id: 7, firstName: "Fannie", lastName: "Guerrero" },
//   { id: 8, firstName: "Lottie", lastName: "Warren" },
//   { id: 9, firstName: "Leah", lastName: "Kennedy" },
//   { id: 10, firstName: "Lottie", lastName: "Giraud" },
//   { id: 11, firstName: "Marc", lastName: "Passeri" }
// ];

Fantasy Land Interoperability

Both RQLTag and SQLTag are Semigroup structures.

const part1 = Player ([
  id,
  "firstName",
  Team (["id"])
]);

const part2 = Player ([
  "lastName",
  Team (["name"]),
  id.eq<{ id: number }> (p => p.id)
]);

const playerById = part1.concat (part2);

playerById ({ id: 1 }).then (console.log);

// [
//   {
//     id: 1,
//     firstName: "Christine",
//     lastName: "Hubbard",
//     team: { id: 1, name: "FC Agecissak" }
//   }
// ];

Compare

import { NumberProp, sql, StringProp, Table } from "refql";

const Player = Table ("player", [
  NumberProp ("id"),
  StringProp ("firstName", "first_name"),
  StringProp ("lastName", "last_name"),
  NumberProp ("teamId", "team_id").nullable (),
  NumberProp ("goalCount", sql`
    select cast(count(*) as int) from goal
    where goal.player_id = player.id
  `)
]);

const { goalCount, lastName, teamId } = Player.props;

const strikers = Player ([
  "*",
  goalCount,
  teamId.eq (1),
  goalCount.gt (7),
  lastName.like ("Craw%")
]);

strikers ().then (console.log);

// [
//   {
//     id: 6,
//     firstName: "Verna",
//     lastName: "Crawford",
//     teamId: 1,
//     goalCount: 11
//   }
// ];

OrderBy, Limit and Offset

import { NumberProp, Limit, Offset, StringProp, Table } from "refql";

const Player = Table ("player", [
  NumberProp ("id"),
  StringProp ("firstName", "first_name"),
  StringProp ("lastName", "last_name")
]);

const { lastName } = Player.props;

const orderByLastName = Player ([
  "*",
  lastName.desc (),
  Limit<{ limit: number }> (p => p.limit),
  Offset<{ offset: number }> (p => p.offset)
]);

orderByLastName ({ limit: 5, offset: 30 }).then (console.log);

// [
//   { id: 410, firstName: "Marcus", lastName: "Volpe" },
//   { id: 248, firstName: "Clarence", lastName: "Vogt" },
//   { id: 615, firstName: "Daniel", lastName: "Vincent" },
//   { id: 228, firstName: "Lloyd", lastName: "Vidal" },
//   { id: 166, firstName: "Marian", lastName: "Vermeulen" }
// ];

When

When takes a predicate and a list of operations. If the predicate returns true, the operations will be applied.

import { Limit, When } from "refql";

const { id, lastName } = Player.props;

const searchPlayer = Player ([
  id,
  lastName,
  When (p => p.q != null, [
    lastName.like<{ q: string }> (p => p.q)
  ]),
  Limit (5)
]);

searchPlayer ({ q: "Ba" }).then (console.log);

// [
//   { id: 11, lastName: "Bardi" },
//   { id: 14, lastName: "Barchielli" },
//   { id: 22, lastName: "Baronti" },
//   { id: 23, lastName: "Baumann" },
//   { id: 72, lastName: "Barrett" }
// ];

Functions and subselects

U can pass a SQLTag as the second argument to a Prop builder to select functions and subselects.

import { NumberProp, Limit, Offset, StringProp, Table } from "refql";

const Player = Table ("player", [
  NumberProp ("id"),
  StringProp ("firstName", "first_name"),
  StringProp ("lastName", "last_name"),
  StringProp ("fullName", sql`
    concat (player.first_name, ' ', player.last_name)
  `),
  NumberProp ("goalCount", sql`
    select count(*) from goal
    where goal.player_id = player.id
  `)
]);

const strikers = Player ([
  "id",
  "fullName",
  "goalCount",
  Limit (3),
  Offset (8)
]);

strikers ().then (console.log);

// [
//   { id: 9, fullName: "Leah Kennedy", goalCount: 10 },
//   { id: 10, fullName: "Lottie Giraud", goalCount: 7 },
//   { id: 11, fullName: "Marc Passeri", goalCount: 5 }
// ]

SQLTag

If something can't be done by using the functions provided by RefQL, use sql.

import { NumberProp, sql, StringProp, Table } from "refql";

const Player = Table ("player", [
  NumberProp ("id"),
  StringProp ("firstName", "first_name"),
  StringProp ("lastName", "last_name"),
  NumberProp ("goalCount", sql`
    select count(*) from goal
    where goal.player_id = player.id
  `)
]);

const topScorers = Player ([
  "id",
  "firstName",
  "lastName",
  "goalCount",
  sql`
    and (
      select count(*) from goal
      where goal.player_id = player.id
    ) > 15
  `
]);

topScorers ().then (console.log);

// [
//   { id: 44, firstName: "Lester", lastName: "Rhodes", goalCount: 16 },
//   { id: 373, firstName: "Lucinda", lastName: "Moss", goalCount: 17 }
// ];

Raw

With the Raw data type it's possible to inject values as raw text into the query.

import { Raw, sql, Table } from "refql";

// dynamic properties
const idField = "id";
const bdField = "birthday";

const Player = Table ("player", []);

const playerById = 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]

playerById ({ id: 1 }).then (console.log);

// [ { id: 1, last_name: "Hubbard", age: "26 years 1 mon 15 days" } ];

Values

Useful when you want to create dynamic queries, such as inserts or queries with the in operator.

import { sql, Table, Values } from "refql";

const Player = Table ("player", []);

// select id, last_name from player where id in ($1, $2, $3)
const selectPlayers = sql<{ ids: number[]}>`
  select id, last_name
  from ${Player}
  where id in ${Values (p => p.ids)}
`;

selectPlayers ({ ids: [1, 2, 3] }).then (console.log);

// [
//   { id: 1, last_name: "Hubbard" },
//   { id: 2, last_name: "Mendez" },
//   { id: 3, last_name: "Kubo" }
// ];

Values2D

Useful for batch inserts.

import { Table, Raw, sql, Values2D } from "refql";

interface Player {
  first_name: string;
  last_name: string;
}

const Player = Table ("player", []);

const insertBatch = sql<{ fields: (keyof Player)[]; data: Player[] }, Player[]>`
  insert into ${Player} (${Raw (p => p.fields.join (", "))})
  values ${Values2D (p => p.data.map (x => p.fields.map (f => x[f])))}
  returning *
`;

insertBatch ({
  fields: ["first_name", "last_name"],
  data: [
    { first_name: "John", last_name: "Doe" },
    { first_name: "Jane", last_name: "Doe" },
    { first_name: "Jimmy", last_name: "Doe" }
  ]
}).then (console.log);

// [
//   { id: 1020, first_name: "John", last_name: "Doe" },
//   { id: 1021, first_name: "Jane", last_name: "Doe" },
//   { id: 1022, first_name: "Jimmy", last_name: "Doe" }
// ];

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

README.md

README.md

RefQL

Installation

Getting started

Table of contents

Tables and References

Ref info

Querier

Set a default querier

Convert Promise output to something else

Fantasy Land Interoperability

Compare

OrderBy, Limit and Offset

When

Functions and subselects

SQLTag

Raw

Values

Values2D

Files

README.md

Latest commit

History

README.md

File metadata and controls

RefQL

Installation

Getting started

Table of contents

Tables and References

Ref info

Querier

Set a default querier

Convert Promise output to something else

Fantasy Land Interoperability

Compare

OrderBy, Limit and Offset

When

Functions and subselects

SQLTag

Raw

Values

Values2D