sql.ts

export type QueryTemplateParam = string | number | undefined;
export type QueryFragmentValue = QueryFragment | QueryTemplateParam;

export type QueryFragment = [
  { fragment: string },
  ReadonlyArray<QueryTemplateParam>
];

/**
 * You can use tagged template literals to build "fragments" of SQL queries
 *
 * ```ts
 * const [query, params] = sql`
 *   SELECT * FROM examples
 *   WHERE groupId = ${groupId}
 *   ORDER BY timestamp ${asc ? sqlFragment`ASC` : sqlFragment`DESC`}
 * `;
 * ```
 *
 * SQL Fragments can contain other SQL fragments, but must be finalized with
 * `sql` before being passed to `Database#prepare`.
 *
 * The name `sqlFragment` comes from several editors that support SQL syntax
 * highlighting inside JavaScript template literals.
 */
export function sqlFragment(
  strings: TemplateStringsArray,
  ...values: ReadonlyArray<QueryFragmentValue>
): QueryFragment {
  let query = '';
  const params: Array<string | number | undefined> = [];

  strings.forEach((string, index) => {
    const value = values[index];

    query += string;

    if (index < values.length) {
      if (Array.isArray(value)) {
        const [{ fragment }, fragmentParams] = value;
        query += fragment;
        params.push(...fragmentParams);
      } else {
        query += '?';
        params.push(value);
      }
    }
  });

  return [{ fragment: query }, params];
}

/**
 * Like `Array.prototype.join`, but for SQL fragments.
 */
export function sqlJoin(
  items: ReadonlyArray<QueryFragmentValue>,
  separator: string
): QueryFragment {
  let query = '';
  const params: Array<string | number | undefined> = [];

  items.forEach((item, index) => {
    const [{ fragment }, fragmentParams] = sqlFragment`${item}`;
    query += fragment;
    params.push(...fragmentParams);

    if (index < items.length - 1) {
      query += separator;
    }
  });

  return [{ fragment: query }, params];
}

export type QueryTemplate = [
  string,
  ReadonlyArray<string | number | undefined>
];

/**
 * You can use tagged template literals to build SQL queries
 * that can be passed to `Database#prepare`.
 *
 * ```ts
 * const [query, params] = sql`
 *   SELECT * FROM examples
 *   WHERE groupId = ${groupId}
 *   ORDER BY timestamp ASC
 * `;
 * db.prepare(query).all(params);
 * ```
 *
 * SQL queries can contain other SQL fragments, but cannot contain other SQL
 * queries.
 *
 * The name `sql` comes from several editors that support SQL syntax
 * highlighting inside JavaScript template literals.
 */
export function sql(
  strings: TemplateStringsArray,
  ...values: ReadonlyArray<QueryFragment | string | number | undefined>
): QueryTemplate {
  const [{ fragment }, params] = sqlFragment(strings, ...values);
  return [fragment, params];
}