initial commit

1 files changed, 286 insertions, 0 deletions

diff --git a/db/utils.ts b/db/utils.ts
new file mode 100644
index 00000000..3c788dfa
--- /dev/null
+++ b/db/utils.ts
@@ -0,0 +1,286 @@
+/**
+ * @see https://gist.github.com/rphlmr/0d1722a794ed5a16da0fdf6652902b15
+ */
+
+import { type QueryBuilderOpts } from "@/types/table"
+import {
+  and,
+  is,
+  not,
+  sql,
+  type AnyColumn,
+  type Column,
+  type GetColumnData,
+  type SelectedFields,
+  type SQL,
+  type Table,
+} from "drizzle-orm"
+import { pgTableCreator, PgTimestampString } from "drizzle-orm/pg-core"
+import { type SelectResultFields } from "drizzle-orm/query-builders/select.types"
+
+import { databasePrefix } from "@/lib/constants"
+
+/**
+ * This lets us use the multi-project schema feature of Drizzle ORM. So the same
+ * database instance can be used for multiple projects.
+ *
+ * @see https://orm.drizzle.team/docs/goodies#multi-project-schema
+ */
+export const pgTable = pgTableCreator((name) => `${databasePrefix}_${name}`)
+
+/**
+ * Takes the first item from an array.
+ *
+ * @param items - The array to take the first item from.
+ * @returns The first item from the array.
+ */
+export function takeFirst<TData>(items: TData[]) {
+  return items.at(0)
+}
+
+/**
+ * Takes the first item from an array or returns null if the array is empty.
+ *
+ * @param items - The array to take the first item from.
+ * @returns The first item from the array or null.
+ */
+export function takeFirstOrNull<TData>(items: TData[]) {
+  return takeFirst(items) ?? null
+}
+
+/**
+ * Takes the first item from an array or throws an error if the array is empty.
+ *
+ * @param items - The array to take the first item from.
+ * @returns The first item from the array.
+ * @throws An error if the array is empty.
+ */
+export function takeFirstOrThrow<TData>(items: TData[]) {
+  const first = takeFirst(items)
+
+  if (!first) {
+    throw new Error("First item not found")
+  }
+
+  return first
+}
+
+/**
+ * Coalesces a value to a default value.
+ *
+ * @param value - The value to coalesce.
+ * @param defaultValue - The default value to return if the value is null.
+ * @returns A sql coalesce statement.
+ */
+export function coalesce<TData>(
+  value: SQL.Aliased<TData> | SQL<TData>,
+  defaultValue: SQL<TData>
+) {
+  return sql<TData>`coalesce(${value}, ${defaultValue})`
+}
+
+/**
+ * Builds a json object from a shape.
+ *
+ * @param shape - The shape of the object to build.
+ * @returns A sql json_build_object statement.
+ */
+export function jsonBuildObject<TFields extends SelectedFields<Column, Table>>(
+  shape: TFields
+) {
+  const chunks: SQL[] = []
+
+  Object.entries(shape).forEach(([key, value]) => {
+    if (chunks.length > 0) {
+      chunks.push(sql.raw(`,`))
+    }
+
+    chunks.push(sql.raw(`'${key}',`))
+
+    // json_build_object formats to ISO 8601 ...
+    if (is(value, PgTimestampString)) {
+      chunks.push(sql`timezone('UTC', ${value})`)
+    } else {
+      chunks.push(sql`${value}`)
+    }
+  })
+
+  return sql<
+    SelectResultFields<TFields>
+  >`json_build_object(${sql.join(chunks)})`
+}
+
+/**
+ * ggregates an array of values into a single json array.
+ *
+ * @param shape - The shape of the object to aggregate.
+ * @param options - The options to pass to the aggregate function.
+ * @returns A SQL json_agg statement.
+ */
+export function jsonAgg<Column extends AnyColumn>(
+  column: Column,
+  opts?: QueryBuilderOpts
+) {
+  const orderBy = opts?.orderBy ? sql` order by ${opts.orderBy}` : sql``
+
+  const aggregateFunction = opts?.distinct
+    ? sql`json_agg(distinct ${sql`${column}`} ${orderBy})`
+    : sql`json_agg(${sql`${column}`} ${orderBy})`
+
+  const where = opts?.nullish ? sql`true` : sql`${column} is not null`
+
+  return coalesce<GetColumnData<Column, "raw">[]>(
+    sql`${aggregateFunction} filter (where ${where})`,
+    sql`'[]'::json`
+  )
+}
+
+/**
+ *  Aggregates an array of objects into a single json object.
+ *
+ * @param shape - The shape of the object to build.
+ * @param options - The options to pass to the aggregate function.
+ * @returns A SQL json_agg statement.
+ */
+export function jsonAggBuildObject<
+  TFields extends SelectedFields<Column, Table>,
+  Column extends AnyColumn,
+>(shape: TFields, opts?: Omit<QueryBuilderOpts, "distinct">) {
+  const nullishWhere = opts?.nullish
+    ? sql`true`
+    : sql`${sql.join(
+        Object.values(shape).map((value) => sql`${value} is not null`),
+        sql` and `
+      )}`
+
+  const orderBy = opts?.orderBy ? sql` order by ${opts.orderBy}` : sql``
+  const where = opts?.where ? and(opts.where, nullishWhere) : nullishWhere
+
+  return coalesce<SelectResultFields<TFields>[]>(
+    sql`json_agg(${jsonBuildObject(shape)}${orderBy}) filter (where ${where})`,
+    sql`'[]'::json`
+  )
+}
+
+/**
+ * Aggregates an array of values into a single array.
+ *
+ * @param column - The column to aggregate.
+ * @param opts - The options to pass to the aggregate function.
+ * @returns A SQL array_agg statement.
+ */
+
+export function arrayAgg<Column extends AnyColumn>(
+  column: Column,
+  opts?: QueryBuilderOpts
+) {
+  const orderBy = opts?.orderBy ? sql` order by ${opts.orderBy}` : sql``
+
+  const aggregateFunction = opts?.distinct
+    ? sql`array_agg(distinct ${sql`${column}`} ${orderBy})`
+    : sql`array_agg(${sql`${column}`} ${orderBy})`
+
+  return sql`coalesce(nullif(${aggregateFunction}, '{}'), array[]::${column.dataType}[])`
+}
+
+/**
+ *  Combines array_agg with jsonb_build_object for more flexible aggregation.
+ *
+ * @param shape - An object representing the shape of the jsonb to build
+ * @param opts - Options for aggregation (distinct, where, orderBy)
+ * @returns A SQL statement for array_agg with jsonb_build_object
+ */
+export function arrayAggBuildObject<
+  TFields extends Record<string, AnyColumn | SQL>,
+>(fields: TFields, options?: QueryBuilderOpts) {
+  const jsonbBuildObject = sql`jsonb_build_object(${sql.join(
+    Object.entries(fields).flatMap(([key, value]) => [
+      sql.raw(`'${key}'`),
+      value,
+    ]),
+    sql`, `
+  )})`
+
+  const distinctClause = options?.distinct ? sql`distinct ` : sql``
+  const orderByClause = options?.orderBy
+    ? sql` order by ${options.orderBy}`
+    : sql``
+  const whereClause = options?.where
+    ? sql` filter (where ${options.where})`
+    : sql``
+
+  return sql<SelectResultFields<TFields>[]>`
+    coalesce(
+      array_agg(${distinctClause}${jsonbBuildObject}${orderByClause})${whereClause},
+      array[]::jsonb[]
+    )
+  `
+}
+
+/**
+ * Creates a SQL case statement for provided cases and an else value.
+ *
+ * @param cases - An array of objects with a `when` property and a `then` property.
+ * @param elseValue - The value to return if no case matches.
+ * @returns A SQL case statement.
+ */
+export function caseWhen<TColumn extends Column>(
+  cases: { when: SQL | undefined; then: Column }[],
+  elseValue: TColumn
+) {
+  const chunks: SQL[] = []
+
+  cases.forEach(({ when, then }) => {
+    chunks.push(sql`when ${when} then ${then}`)
+  })
+
+  chunks.push(sql`else ${elseValue}`)
+
+  return sql<TColumn["_"]["dataType"]>`case ${sql.join(chunks)} end`
+}
+
+/**
+ * Composes an array of columns into a single st.
+ *
+ * @param values - The values to compose.
+ * @param separator - The separator to use between the ids (default: "").
+ * @returns A sql statement representing the composite column.
+ */
+export function compose<TColumn extends Column>(
+  columns: TColumn[],
+  separator = ""
+) {
+  const chunks = columns.map((column) => sql`${column}::text`)
+
+  return sql<
+    TColumn["_"]["dataType"]
+  >`(${sql.join(chunks, sql` || ${separator} || `)})`
+}
+
+/**
+ * Checks if a value is empty (null, undefined, empty string, empty array, or empty object).
+ *
+ * @param value - The value to check.
+ * @returns A SQL expression that evaluates to true if the value is empty, false otherwise.
+ */
+export function isEmpty<TColumn extends AnyColumn>(column: TColumn) {
+  return sql<boolean>`
+    case
+      when ${column} is null then true
+      when ${column} = '' then true
+      when ${column}::text = '[]' then true
+      when ${column}::text = '{}' then true
+      else false
+    end
+  `
+}
+
+/**
+ * Checks if a value is not empty (not null, not undefined, not empty string, not empty array, and not empty object).
+ *
+ * @param value - The value to check.
+ * @returns A SQL expression that evaluates to true if the value is not empty, false otherwise.
+ */
+export function isNotEmpty<TColumn extends AnyColumn>(column: TColumn) {
+  return not(isEmpty(column))
+}