SQL helper

sql.helper.ts

export enum TextInputTypes {
  Raw,
  Parameter,
}

export type Primitive = string | number | boolean;

export interface TextInputObject<Type extends TextInputTypes> {
  type: Type;
  value: Primitive;
}

export type TextInputRaw = TextInputObject<TextInputTypes.Raw>;
export type TextInputParameter = TextInputObject<TextInputTypes.Parameter>;

export type TextInputArg = Primitive | TextInputRaw;
export type TextInput = TextInputRaw | TextInputParameter;

export function rawInput<T extends Primitive>(value: T): TextInputRaw {
  return {
    type: TextInputTypes.Raw,
    value,
  };
}

export function parameterInput<T extends Primitive>(
  value: T
): TextInputParameter {
  return {
    type: TextInputTypes.Parameter,
    value,
  };
}

interface CreateSelectOptions {
  alias?: string;
}

export function createSelect<T>({ alias }: CreateSelectOptions = {}) {
  return function select(...properties: (keyof T)[]): TextInputRaw {
    return rawInput(
      properties
        .map((property) => (alias ? `${alias}.${property}` : property))
        .join(", ")
    );
  };
}

export function table(name: string) {
  return rawInput(name);
}

export function mapArgToInput(arg: TextInputArg) {
  if (typeof arg === "object") {
    return arg;
  }

  return parameterInput(arg);
}

export function mapInputToValue(text: string, input: TextInput) {
  if (input.type === TextInputTypes.Raw) {
    return text + input.value;
  }

  if (input.type === TextInputTypes.Parameter) {
    return text + "?";
  }

  return text;
}

sql.test.ts

import { table, createSelect, sql } from ".";

describe("sql", () => {
  it("should generate basic query", () => {
    const query = sql`
        SELECT * FROM Table
    `;

    expect(query.sql).toEqual("SELECT * FROM Table");
  });

  it("should inline raw input", () => {
    const query = sql`
      SELECT * FROM ${table("Table")}
    `;

    expect(query.sql).toEqual("SELECT * FROM Table");
  });

  it("should replace parameter input with '?'", () => {
    const id = 5;
    const query = sql`
      SELECT * FROM Table WHERE id = ${id}
    `;

    expect(query.sql).toEqual("SELECT * FROM Table WHERE id = ?");
    expect(query.parameters).toEqual([5]);
  });

  interface UserModel {
    name: string;
    email: string;
  }

  it("should handle a complex query", () => {
    const select = createSelect<UserModel>({ alias: "u" });
    const query = sql`
      SELECT ${select("name", "email")}
      FROM ${table("Users")} u
      INNER JOIN Bookings b ON b.userId = u.id
      WHERE id = ${4}
      GROUP BY b.id
    `;

    expect(query.sql).toMatchInlineSnapshot(
      `"SELECT u.name, u.email FROM Users u INNER JOIN Bookings b ON b.userId = u.id WHERE id = ? GROUP BY b.id"`
    );
    expect(query.parameters).toEqual([4]);
  });
});

sql.ts

import {
  mapArgToInput,
  mapInputToValue,
  TextInputArg,
  TextInputTypes,
} from "./helpers";

export function sql(texts: TemplateStringsArray, ...args: TextInputArg[]) {
  const textInputs = args.map((arg) => mapArgToInput(arg));

  const escapedTexts = texts
    .filter((text) => text.trim().length > 0)
    .map((text) => text.replace(/\n/g, ""))
    .map((text) => text.replace(/\s+/g, " "));

  const mappedTexts = escapedTexts.map((text, i) => {
    const input = textInputs[i];
    if (!input) {
      return text;
    }

    return mapInputToValue(text, input);
  });

  const sqlStr = mappedTexts.join("").trim();

  const parameters = textInputs
    .filter((input) => input.type === TextInputTypes.Parameter)
    .map((input) => input.value);

  return { sql: sqlStr, parameters };
}