@bezql/query

Query builder and executioner.

Supports MySQL and Postgresql.

Basic usage

Connect to Database

Connect to the database using one of the available drivers. Pass in an appropriate config for the driver being used.

The MySQL driver uses the mysql2 package under the hood, the PostGres driver uses pg.

A connection pool will be generated, with each query using a single connection from the pool.

import { Drivers } from @bezql/query;

//pass a mysql2 pool options object
const db = new Drivers.MySQL({
    host: "localhost",
    port: 3306,
    database: "myDb",
    user: "root",
    password: "MySecurePassword",
    connectionLimit: 100,
});

Build a Query

The inbuilt query builder is used to build a query step by step.

import { Query, Drivers } from @bezql/query;

const db = new Drivers.MySQL(config);

const query = db.query();

query.select("id", "name").from("User");

Execute Query

Use the appropriate function to execute a query.

Select

The fetch function accepts a generic type to define the structure of the items that will be returned.

query.select("id", "name").from("User");

const results = await query.fetch<{
  id: string;
  name: string;
}>();
results.rows.forEach((row) => {
  const { id, name } = row;
});

Sub Query Select

You can alternatively pass a query and alias to fetch from a sub query.

const subSelect = db.query();
subSelect.select("id", "name").from("User");

query.select("id", "name").from(subSelect, "SubUser");

Stream Select Results

Select results can be streamed to prevent holding too much data in memory at once.

query.select("id", "name").from("User");

await query.stream<{
  id: string;
  name: string;
}>(100, async (batch) => {
  batch.forEach((row) => {
    console.log(row.id, row.name);
  });

  //return false to exit the stream early
  return true;
});

Aggregate Results

Select results can be aggregated using the aggregate function, followed by the required aggregation.

query.select("id").from("userLogins");

query
  .aggregate()
  .count("*", "totalLogins")
  .max("loginDate", "lastLogin")
  .min("loginDate", "firstLogin")
  .avg("weekDay", "averageWeekday")
  .countDistinct("weekDay", "distinctWeekdays");

query.groupBy("id");

Insert

query
  .insert(
    {
      name: "Robert",
    },
    true
  )
  .into("User");

const result = await query.save();
console.log("INSERTED: ", result.rows_affected);

Insert multiple rows

Simply pass an array of values to insert multiple records.

query
  .insert(
    [
      {
        name: "Robert",
      },
      {
        name: "Tim",
      },
    ],
    true
  )
  .into("User");

Last Insert ID

For tables with an autogenerated ID, it is possible to return the ID of the first record inserted by the query.

This is done automatically for MySQL queries, with the first ID being returned in the results.

query
  .insert(
    [
      {
        name: "Robert",
      },
      {
        name: "Tim",
      },
    ],
    true
  )
  .into("User");

const result = await query.save();

const insertId = result.insert_id;
console.log(`FIRST INSERTED ID: ${insertId}`);

For Postgresql queries, you must first set the auto insert ID field.

query.setAutoInsertId("id");
query
  .insert(
    [
      {
        name: "Robert",
      },
      {
        name: "Tim",
      },
    ],
    true
  )
  .into("User");

const result = await query.save();

const insertId = result.insert_id;
console.log(`FIRST INSERTED ID: ${insertId}`);

Update

query.update("User").set(
  {
    name: "Bobby",
  },
  true
);

const constraints = query.constraint();
constraints.where("id", "=", 1, true);

const updateResult = await query.save();
console.log("UPDATED: ", updateResult.rows_changed);

Delete

query.delete().from("User");

const constraints = query.constraint();
constraints.where("id", "=", 1, true);

const updateResult = await query.save();
console.log("UPDATED: ", updateResult.rows_changed);

Constraints

Constraints can be used to restrict which records are returned when fetching, or which records are affected by an update or delete query.

To start adding constraints, the constraint manager must first be fetched from the query.

const query = db.query();
query.select("id", "name").from("User");

const constraints = query.constraints();

The constraint manager contains many constraining functions.

// basic where with parameterised and injected value
constraints.where("id", "=", 1, true);
//basic where with non parameterised value.
constraints.where("id", "=", 1, false);

// where in
constraints.whereIn("id", [1, 2], true);

// where not in
constraints.whereNotIn("id", [1, 2], true);

// where null
constraints.whereNull("isDeleted");

// where not null
constraints.whereNotNull("isDeleted");

Where logic

Logic can be applied to queries using the bracket and logic functions.

constraints
  .where("id", "=", 1, true)
  .and()
  .openBracket()
  .whereNull("isDeleted")
  .or()
  .where("isUnique", "=", 1, true)
  .closeBracket();

Joins

Two join functions are available for joining tables together, join and leftJoin.

query
  .select("User.id", "User.name", "UserType.type")
  .from("User")
  .join("UserType", "User.id", "UserType.id", "User.TypeId");

query
  .select("User.id", "User.name", "UserType.type")
  .from("User")
  .leftJoin("UserType", "User.id", "UserType.id", "User.TypeId");

More complex joins can also be handled by passing in a contraint function instead of the fields.

query
  .select("User.id", "User.name", "UserType.type")
  .from("User")
  .leftJoin("UserType", (contraints) => {
    constraints
      .on("UserType.id", "=", "User.id", false)
      .and()
      .on("UserType.type", "=", "member", true);
  });

Sub Query Joins

A query and alias can be passed into the join functions to join a sub query instead of a table name.

const joinQuery = db.query();
joinQuery.select("id", "group").from("UserGroup");

query.join(joinQuery, "groups", "User.groupId", "groups.id");

Disconnect

To disconnect, call the disconnect function on the database.

const db = new Drivers.MySQL({
  host: "localhost",
  port: 3306,
  database: "myDb",
  user: "root",
  password: "MySecurePassword",
  connectionLimit: 100,
});

db.disconnect();