Skip to content

Instantly share code, notes, and snippets.

@rphlmr

rphlmr/clear-db.ts

Last active August 24, 2025 11:07
Show Gist options
  • Save rphlmr/0d1722a794ed5a16da0fdf6652902b15 to your computer and use it in GitHub Desktop.
Save rphlmr/0d1722a794ed5a16da0fdf6652902b15 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. rphlmr revised this gist Oct 24, 2024. 1 changed file with 2 additions and 0 deletions.
    2 changes: 2 additions & 0 deletions jsonAgg_jsonAggBuildObject.ts
    View file
    Original file line number Diff line number Diff line change
    @@ -13,6 +13,8 @@ import {
    } from "drizzle-orm/pg-core";
    import { type SelectResultFields } from "node_modules/drizzle-orm/query-builders/select.types";

    // demo https://drizzle.run/se2noay5mhdu24va3xhv0lqo

    export function jsonBuildObject<T extends T extends SelectedFields<any, any>>(shape: T) {
    const chunks: SQL[] = [];

  2. rphlmr revised this gist Oct 24, 2024. 1 changed file with 2 additions and 2 deletions.
    4 changes: 2 additions & 2 deletions jsonAgg_jsonAggBuildObject.ts
    View file
    Original file line number Diff line number Diff line change
    @@ -13,7 +13,7 @@ import {
    } from "drizzle-orm/pg-core";
    import { type SelectResultFields } from "node_modules/drizzle-orm/query-builders/select.types";

    export function jsonBuildObject<T extends SelectedFields>(shape: T) {
    export function jsonBuildObject<T extends T extends SelectedFields<any, any>>(shape: T) {
    const chunks: SQL[] = [];

    Object.entries(shape).forEach(([key, value]) => {
    @@ -37,7 +37,7 @@ export function jsonBuildObject<T extends SelectedFields>(shape: T) {
    }

    export function jsonAggBuildObject<
    T extends SelectedFields,
    T extends SelectedFields<any, any>,
    Column extends AnyColumn,
    >(
    shape: T,
  3. rphlmr revised this gist Oct 24, 2024. 1 changed file with 5 additions and 0 deletions.
    5 changes: 5 additions & 0 deletions utils.ts
    View file
    Original file line number Diff line number Diff line change
    @@ -105,6 +105,9 @@ export function inJsonArray<T extends SQL.Aliased<unknown[]>>(
    }

    // Like getTableColumns but for sub queries https://orm.drizzle.team/docs/goodies#get-typed-table-columns
    /**
    * @deprecated - use subQuery._.selectedFields
    */
    export function getSubQueryColumns<
    S extends ColumnsSelection,
    A extends string,
    @@ -115,6 +118,8 @@ export function getSubQueryColumns<

    return selection;
    }
    // can now used like that
    subQuery._.selectedFields

    export type InferSubQueryModel<T extends SQL.Aliased> = T["_"]["type"];

  4. rphlmr revised this gist Oct 19, 2024. 1 changed file with 14 additions and 1 deletion.
    15 changes: 14 additions & 1 deletion utils.ts
    View file
    Original file line number Diff line number Diff line change
    @@ -148,4 +148,17 @@ function mergeJson<
    return sql`jsonb_set(${column}, '{${sql.raw(String(fieldOrData))}}', ${data ? sql`${column} -> '${sql.raw(String(fieldOrData))}' || ${JSON.stringify(data)}` : "null"})`;
    }
    return sql`coalesce(${column}, '{}') || ${fieldOrData ? JSON.stringify(fieldOrData) : null}`;
    }
    }

    // example
    await db.update(config).set({
    roles: mergeJson(config.roles, "president", {
    label: "new-P",
    }),
    });

    await db.update(config).set({
    roles: mergeJson(config.roles, {
    member: { color: "new-blue", label: "mbr" },
    }),
    });
  5. rphlmr revised this gist Oct 19, 2024. 1 changed file with 26 additions and 0 deletions.
    26 changes: 26 additions & 0 deletions utils.ts
    View file
    Original file line number Diff line number Diff line change
    @@ -123,3 +123,29 @@ export function isNotNullJsonb<T> (column: PgColumnJson<T>, key: keyof T): SQL |
    return and(isNotNull(column), isNotNull(sql`${column}->>${key}`));
    }
    // usage: https://drizzle.run/lm4zy2ohxoc1sxzizzgf1kt0 Author: nowifi4u (discord)


    // usage: https://drizzle.run/lkd38uqtk5broj117asmxkah
    function mergeJson<
    Colum extends PgColumn,
    CustomType extends Colum["_"]["data"] = Colum["_"]["data"],
    >(column: Colum, data: Partial<CustomType> | null): SQL;
    function mergeJson<
    Colum extends PgColumn,
    CustomType extends Colum["_"]["data"] = Colum["_"]["data"],
    Key extends keyof CustomType = keyof CustomType,
    >(column: Colum, field: Key, data: Partial<CustomType[Key]> | null): SQL;
    function mergeJson<
    Colum extends PgColumn,
    CustomType extends Colum["_"]["data"] = Colum["_"]["data"],
    Key extends keyof CustomType = keyof CustomType,
    >(
    column: Colum,
    fieldOrData: Key | Partial<CustomType>,
    data?: Partial<CustomType[Key]> | null,
    ) {
    if (typeof fieldOrData === "string") {
    return sql`jsonb_set(${column}, '{${sql.raw(String(fieldOrData))}}', ${data ? sql`${column} -> '${sql.raw(String(fieldOrData))}' || ${JSON.stringify(data)}` : "null"})`;
    }
    return sql`coalesce(${column}, '{}') || ${fieldOrData ? JSON.stringify(fieldOrData) : null}`;
    }
  6. rphlmr revised this gist Oct 2, 2024. 1 changed file with 5 additions and 4 deletions.
    9 changes: 5 additions & 4 deletions utils.ts
    View file
    Original file line number Diff line number Diff line change
    @@ -105,10 +105,11 @@ export function inJsonArray<T extends SQL.Aliased<unknown[]>>(
    }

    // Like getTableColumns but for sub queries https://orm.drizzle.team/docs/goodies#get-typed-table-columns
    function getSubQueryColumns<S extends ColumnsSelection, A extends string>(
    subQuery: SubqueryWithSelection<S, A> | WithSubqueryWithSelection<S, A>
    ) {
    const { selection } = (subQuery as any)[SubqueryConfig] as {
    export function getSubQueryColumns<
    S extends ColumnsSelection,
    A extends string,
    >(subQuery: SubqueryWithSelection<S, A> | WithSubqueryWithSelection<S, A>) {
    const { selection } = subQuery as unknown as {
    selection: (typeof subQuery)["_"]["selectedFields"];
    };

  7. rphlmr revised this gist Aug 8, 2024. 1 changed file with 20 additions and 13 deletions.
    33 changes: 20 additions & 13 deletions jsonAgg_jsonAggBuildObject.ts
    View file
    Original file line number Diff line number Diff line change
    @@ -31,27 +31,34 @@ export function jsonBuildObject<T extends SelectedFields>(shape: T) {
    }
    });

    return sql<SelectResultFields<T>>`coalesce(json_build_object(${sql.join(
    return sql<SelectResultFields<T>>`json_build_object(${sql.join(
    chunks
    )}), '{}')`;
    )})`;
    }

    export function jsonAggBuildObject<
    T extends SelectedFields,
    Column extends AnyColumn
    Column extends AnyColumn,
    >(
    shape: T,
    options?: { orderBy?: { colName: Column; direction: "ASC" | "DESC" } }
    options?: { orderBy?: { colName: Column; direction: "ASC" | "DESC" } },
    ) {
    return sql<SelectResultFields<T>[]>`coalesce(jsonb_agg(${jsonBuildObject(
    shape
    )}${
    options?.orderBy
    ? sql`order by ${options.orderBy.colName} ${sql.raw(
    options.orderBy.direction
    )}`
    : undefined
    }), '${sql`[]`}')`;
    return sql<SelectResultFields<T>[]>`coalesce(
    json_agg(${jsonBuildObject(shape)}
    ${
    options?.orderBy
    ? sql`ORDER BY ${options.orderBy.colName} ${sql.raw(
    options.orderBy.direction,
    )}`
    : undefined
    })
    FILTER (WHERE ${and(
    sql.join(
    Object.values(shape).map((value) => sql`${sql`${value}`} IS NOT NULL`),
    sql` AND `,
    ),
    )})
    ,'${sql`[]`}')`;
    }

    // with filter non-null + distinct
  8. rphlmr revised this gist Jul 31, 2024. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion utils.ts
    View file
    Original file line number Diff line number Diff line change
    @@ -121,4 +121,4 @@ type PgColumnJson<T> = PgColumn<ColumnBaseConfig<'json', string> & { data: T }>;
    export function isNotNullJsonb<T> (column: PgColumnJson<T>, key: keyof T): SQL | undefined {
    return and(isNotNull(column), isNotNull(sql`${column}->>${key}`));
    }
    // usage: https://drizzle.run/lm4zy2ohxoc1sxzizzgf1kt0
    // usage: https://drizzle.run/lm4zy2ohxoc1sxzizzgf1kt0 Author: nowifi4u (discord)
  9. rphlmr revised this gist Jul 31, 2024. 1 changed file with 6 additions and 0 deletions.
    6 changes: 6 additions & 0 deletions utils.ts
    View file
    Original file line number Diff line number Diff line change
    @@ -116,3 +116,9 @@ function getSubQueryColumns<S extends ColumnsSelection, A extends string>(
    }

    export type InferSubQueryModel<T extends SQL.Aliased> = T["_"]["type"];

    type PgColumnJson<T> = PgColumn<ColumnBaseConfig<'json', string> & { data: T }>;
    export function isNotNullJsonb<T> (column: PgColumnJson<T>, key: keyof T): SQL | undefined {
    return and(isNotNull(column), isNotNull(sql`${column}->>${key}`));
    }
    // usage: https://drizzle.run/lm4zy2ohxoc1sxzizzgf1kt0
  10. rphlmr revised this gist Jul 26, 2024. 1 changed file with 29 additions and 0 deletions.
    29 changes: 29 additions & 0 deletions sqlite.ts
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,29 @@
    import { SQL, sql } from "drizzle-orm";
    import { SelectResultFields } from "drizzle-orm/query-builders/select.types";
    import { SelectedFields } from "drizzle-orm/sqlite-core";

    export function jsonObject<T extends SelectedFields>(shape: T) {
    const chunks: SQL[] = [];

    Object.entries(shape).forEach(([key, value]) => {
    if (chunks.length > 0) {
    chunks.push(sql.raw(`,`));
    }

    chunks.push(sql.raw(`'${key}',`));

    chunks.push(sql`${value}`);
    });

    return sql<SelectResultFields<T>>`coalesce(json_object(${sql.join(
    chunks,
    )}), ${sql`json_object()`})`;
    }

    export function jsonAggObject<T extends SelectedFields>(shape: T) {
    return sql<SelectResultFields<T>[]>`coalesce(json_group_array(${jsonObject(
    shape,
    )}), ${sql`json_array()`})`.mapWith(
    (v) => JSON.parse(v) as SelectResultFields<T>[],
    );
    }
  11. rphlmr revised this gist May 24, 2024. 1 changed file with 2 additions and 0 deletions.
    2 changes: 2 additions & 0 deletions jsonAgg_jsonAggBuildObject.ts
    View file
    Original file line number Diff line number Diff line change
    @@ -3,11 +3,13 @@ import {
    type AnyColumn,
    type SQL,
    type InferSelectModel,
    is
    } from "drizzle-orm";
    import {
    type SelectedFields,
    type PgTable,
    type TableConfig,
    PgTimestampString
    } from "drizzle-orm/pg-core";
    import { type SelectResultFields } from "node_modules/drizzle-orm/query-builders/select.types";

  12. rphlmr revised this gist Feb 5, 2024. No changes.
  13. rphlmr revised this gist Feb 5, 2024. 1 changed file with 1 addition and 0 deletions.
    1 change: 1 addition & 0 deletions jsonAgg_jsonAggBuildObject.ts
    View file
    Original file line number Diff line number Diff line change
    @@ -102,6 +102,7 @@ await db
    .from(existingDiscussionQuery)
    .where(
    arrayContained(
    // from Drizzle
    existingDiscussionQuery.participants,
    toArray(
    [
  14. rphlmr revised this gist Feb 5, 2024. 1 changed file with 14 additions and 14 deletions.
    28 changes: 14 additions & 14 deletions jsonAgg_jsonAggBuildObject.ts
    View file
    Original file line number Diff line number Diff line change
    @@ -98,20 +98,20 @@ export function toArray<Values>(values: Values[], cast: PGArrayCastTypes) {
    }
    // exemple:
    await db
    .select()
    .from(existingDiscussionQuery)
    .where(
    arrayContained(
    existingDiscussionQuery.participants,
    toArray(
    [
    "c3b1399f-2c6b-40d7-9d37-cfaf9a7c6164",
    "77c75084-7123-481b-a326-49c9ebceb431",
    ],
    "uuid[]",
    ),
    ),
    ),
    .select()
    .from(existingDiscussionQuery)
    .where(
    arrayContained(
    existingDiscussionQuery.participants,
    toArray(
    [
    "c3b1399f-2c6b-40d7-9d37-cfaf9a7c6164",
    "77c75084-7123-481b-a326-49c9ebceb431",
    ],
    "uuid[]"
    )
    )
    );

    // you use it like that:
    const result = await db
  15. rphlmr revised this gist Feb 5, 2024. 1 changed file with 16 additions and 0 deletions.
    16 changes: 16 additions & 0 deletions jsonAgg_jsonAggBuildObject.ts
    View file
    Original file line number Diff line number Diff line change
    @@ -96,6 +96,22 @@ export function toArray<Values>(values: Values[], cast: PGArrayCastTypes) {

    return sql`array[${sql.join(chunks)}]::${sql.raw(cast)}`;
    }
    // exemple:
    await db
    .select()
    .from(existingDiscussionQuery)
    .where(
    arrayContained(
    existingDiscussionQuery.participants,
    toArray(
    [
    "c3b1399f-2c6b-40d7-9d37-cfaf9a7c6164",
    "77c75084-7123-481b-a326-49c9ebceb431",
    ],
    "uuid[]",
    ),
    ),
    ),

    // you use it like that:
    const result = await db
  16. rphlmr revised this gist Feb 5, 2024. 1 changed file with 22 additions and 0 deletions.
    22 changes: 22 additions & 0 deletions jsonAgg_jsonAggBuildObject.ts
    View file
    Original file line number Diff line number Diff line change
    @@ -75,6 +75,28 @@ export function arrayAgg<Column extends AnyColumn>(column: Column) {
    >`array_agg(distinct ${sql`${column}`}) filter (where ${column} is not null)`;
    }

    // To be completed
    type PGCastTypes = "uuid" | "uuid[]" | "text" | "text[]";

    type PGArrayCastTypes = {
    [P in PGCastTypes]: P extends `${infer _T}[]` ? P : never;
    }[PGCastTypes];

    // Transform an array of values (from a function params) into a postgres array
    export function toArray<Values>(values: Values[], cast: PGArrayCastTypes) {
    const chunks: SQL[] = [];

    values.forEach((column) => {
    if (chunks.length > 0) {
    chunks.push(sql.raw(`,`));
    }

    chunks.push(sql`${column}`);
    });

    return sql`array[${sql.join(chunks)}]::${sql.raw(cast)}`;
    }

    // you use it like that:
    const result = await db
    .select({
  17. rphlmr revised this gist Feb 5, 2024. 1 changed file with 7 additions and 0 deletions.
    7 changes: 7 additions & 0 deletions jsonAgg_jsonAggBuildObject.ts
    View file
    Original file line number Diff line number Diff line change
    @@ -68,6 +68,13 @@ export function jsonAgg<Column extends AnyColumn>(column: Column) {
    );
    }

    // Sometimes you want an array and not a json
    export function arrayAgg<Column extends AnyColumn>(column: Column) {
    return sql<
    GetColumnData<Column, "raw">[]
    >`array_agg(distinct ${sql`${column}`}) filter (where ${column} is not null)`;
    }

    // you use it like that:
    const result = await db
    .select({
  18. rphlmr revised this gist Feb 5, 2024. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion jsonAgg_jsonAggBuildObject.ts
    View file
    Original file line number Diff line number Diff line change
    @@ -55,7 +55,7 @@ export function jsonAggBuildObject<
    // with filter non-null + distinct
    export function jsonAgg<Column extends AnyColumn>(column: Column) {
    return coalesce<GetColumnData<Column, "raw">[]>(
    sql`json_agg(${sql`${column}`}) filter (where ${column} is not null)`,
    sql`json_agg(distinct ${sql`${column}`}) filter (where ${column} is not null)`,
    sql`'[]'`
    );
    }
  19. rphlmr revised this gist Feb 5, 2024. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion jsonAgg_jsonAggBuildObject.ts
    View file
    Original file line number Diff line number Diff line change
    @@ -52,7 +52,7 @@ export function jsonAggBuildObject<
    }), '${sql`[]`}')`;
    }

    // with filter non-null
    // with filter non-null + distinct
    export function jsonAgg<Column extends AnyColumn>(column: Column) {
    return coalesce<GetColumnData<Column, "raw">[]>(
    sql`json_agg(${sql`${column}`}) filter (where ${column} is not null)`,
  20. rphlmr revised this gist Jan 31, 2024. 1 changed file with 5 additions and 4 deletions.
    9 changes: 5 additions & 4 deletions jsonAgg_jsonAggBuildObject.ts
    View file
    Original file line number Diff line number Diff line change
    @@ -53,10 +53,11 @@ export function jsonAggBuildObject<
    }

    // with filter non-null
    export function jsonAgg<Table extends AnyTable<TableConfig>>(table: Table) {
    return sql<
    InferModel<Table>[]
    >`coalesce(json_agg(${table}) filter (where ${table} is not null), ${sql`[]`})`;
    export function jsonAgg<Column extends AnyColumn>(column: Column) {
    return coalesce<GetColumnData<Column, "raw">[]>(
    sql`json_agg(${sql`${column}`}) filter (where ${column} is not null)`,
    sql`'[]'`
    );
    }

    // generalist
  21. rphlmr revised this gist Jan 31, 2024. 1 changed file with 34 additions and 37 deletions.
    71 changes: 34 additions & 37 deletions utils.ts
    View file
    Original file line number Diff line number Diff line change
    @@ -1,13 +1,13 @@
    import {
    sql,
    type AnyColumn,
    type SQL,
    type InferSelectModel,
    sql,
    type AnyColumn,
    type SQL,
    type InferSelectModel,
    } from "drizzle-orm";
    import {
    type SelectedFields,
    type PgTable,
    type TableConfig,
    type SelectedFields,
    type PgTable,
    type TableConfig,
    } from "drizzle-orm/pg-core";
    import { type SelectResultFields } from "node_modules/drizzle-orm/query-builders/select.types";

    @@ -26,49 +26,48 @@ export function takeFirstOrThrow<T>(items: T[]) {
    }

    export function distinct<Column extends AnyColumn>(column: Column) {
    return sql<Column["_"]["data"]>`distinct(${column})`;
    return sql<Column["_"]["data"]>`distinct(${column})`;
    }

    export function distinctOn<Column extends AnyColumn>(column: Column) {
    return sql<Column["_"]["data"]>`distinct on (${column}) ${column}`;
    return sql<Column["_"]["data"]>`distinct on (${column}) ${column}`;
    }

    export function max<Column extends AnyColumn>(column: Column) {
    return sql<Column["_"]["data"]>`max(${column})`;
    return sql<Column["_"]["data"]>`max(${column})`;
    }

    export function count<Column extends AnyColumn>(column: Column) {
    return sql<number>`cast(count(${column}) as integer)`;
    return sql<number>`cast(count(${column}) as integer)`;
    }

    /**
    * Coalesce a value to a default value if the value is null
    * Ex default array: themes: coalesce(pubThemeListQuery.themes, sql`'[]'`)
    * Ex default number: votesCount: coalesce(PubPollAnswersQuery.count, sql`0`)
    */
    export function coalesce<T>(value: SQL.Aliased<T>, defaultValue: SQL) {
    return sql<T>`coalesce(${value}, ${defaultValue})`;
    export function coalesce<T>(value: SQL.Aliased<T> | SQL<T>, defaultValue: SQL) {
    return sql<T>`coalesce(${value}, ${defaultValue})`;
    }

    type Unit = "minutes" | "minute";
    type Operator = "+" | "-";

    export function now(interval?: `${Operator} interval ${number} ${Unit}`) {
    return sql<string>`now() ${interval || ""}`;
    return sql<string>`now() ${interval || ""}`;
    }


    //
    //
    // example where table.data type is { id: string; type: 'document' | 'image' }
    // eq(eqJsonb(table.data, {
    // id: 'some value',
    // type: "document",
    // }))
    export function eqJsonb<T extends PgColumn>(
    column: T,
    value: Partial<GetColumnData<T, "raw">>,
    column: T,
    value: Partial<GetColumnData<T, "raw">>
    ) {
    return sql`${column} @> ${value}`;
    return sql`${column} @> ${value}`;
    }

    // Select a JSONB field
    @@ -81,41 +80,39 @@ export function eqJsonb<T extends PgColumn>(
    // >(table.data, "fieldKey"),
    // })
    export function pickJsonbField<
    U,
    K extends keyof U,
    T extends PgColumn = PgColumn,
    U,
    K extends keyof U,
    T extends PgColumn = PgColumn
    >(column: T, field: K, cast?: "uuid") {
    return sql<U[K]>`((${column}->${field})${
    cast ? sql.raw(`::${cast}`) : undefined
    })`;
    return sql<U[K]>`((${column}->${field})${
    cast ? sql.raw(`::${cast}`) : undefined
    })`;
    }

    // .where(inJsonArray(subQueryWithJsonAggregate.anArray, "keyName", [valueFromParams]))
    export function inJsonArray<T extends SQL.Aliased<unknown[]>>(
    jsonArray: T,
    key: keyof T["_"]["type"][number],
    values: string[],
    jsonArray: T,
    key: keyof T["_"]["type"][number],
    values: string[]
    ) {
    const element = sql.raw(`${String(key)}_array_element`);
    const element = sql.raw(`${String(key)}_array_element`);

    return sql`EXISTS (
    return sql`EXISTS (
    SELECT 1
    FROM jsonb_array_elements(${jsonArray}) AS ${element}
    WHERE ${inArray(sql`${element}->>${key}`, values)}
    )`;
    }


    // Like getTableColumns but for sub queries https://orm.drizzle.team/docs/goodies#get-typed-table-columns
    function getSubQueryColumns<S extends ColumnsSelection, A extends string>(
    subQuery: SubqueryWithSelection<S, A> | WithSubqueryWithSelection<S, A>,
    subQuery: SubqueryWithSelection<S, A> | WithSubqueryWithSelection<S, A>
    ) {
    const { selection } = (subQuery as any)[SubqueryConfig] as {
    selection: (typeof subQuery)["_"]["selectedFields"];
    };
    const { selection } = (subQuery as any)[SubqueryConfig] as {
    selection: (typeof subQuery)["_"]["selectedFields"];
    };

    return selection;
    return selection;
    }

    export type InferSubQueryModel<T extends SQL.Aliased> = T["_"]["type"];

  22. rphlmr revised this gist Jan 31, 2024. 1 changed file with 5 additions and 2 deletions.
    7 changes: 5 additions & 2 deletions jsonAgg_jsonAggBuildObject.ts
    View file
    Original file line number Diff line number Diff line change
    @@ -60,8 +60,11 @@ export function jsonAgg<Table extends AnyTable<TableConfig>>(table: Table) {
    }

    // generalist
    export function jsonAgg<Table extends AnyTable<TableConfig>>(table: Table) {
    return sql<InferModel<Table>[]>`coalesce(json_agg(${table}), ${sql`[]`})`;
    export function jsonAgg<Column extends AnyColumn>(column: Column) {
    return coalesce<GetColumnData<Column, "raw">[]>(
    sql`json_agg(${sql`${column}`})`,
    sql`'[]'`
    );
    }

    // you use it like that:
  23. rphlmr revised this gist Jan 30, 2024. 1 changed file with 50 additions and 53 deletions.
    103 changes: 50 additions & 53 deletions jsonAgg_jsonAggBuildObject.ts
    View file
    Original file line number Diff line number Diff line change
    @@ -1,55 +1,55 @@
    import {
    sql,
    type AnyColumn,
    type SQL,
    type InferSelectModel,
    sql,
    type AnyColumn,
    type SQL,
    type InferSelectModel,
    } from "drizzle-orm";
    import {
    type SelectedFields,
    type PgTable,
    type TableConfig,
    type SelectedFields,
    type PgTable,
    type TableConfig,
    } from "drizzle-orm/pg-core";
    import { type SelectResultFields } from "node_modules/drizzle-orm/query-builders/select.types";

    export function jsonBuildObject<T extends SelectedFields>(shape: T) {
    const chunks: SQL[] = [];
    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 ((value as any)["columnType"] === "PgTimestampString") {
    chunks.push(sql`timezone('UTC', ${value})`);
    } else {
    chunks.push(sql`${value}`);
    }
    });
    Object.entries(shape).forEach(([key, value]) => {
    if (chunks.length > 0) {
    chunks.push(sql.raw(`,`));
    }

    return sql<SelectResultFields<T>>`coalesce(json_build_object(${sql.join(
    chunks,
    )}), '{}')`;
    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<T>>`coalesce(json_build_object(${sql.join(
    chunks
    )}), '{}')`;
    }

    export function jsonAggBuildObject<
    T extends SelectedFields,
    Column extends AnyColumn,
    T extends SelectedFields,
    Column extends AnyColumn
    >(
    shape: T,
    options?: { orderBy?: { colName: Column; direction: "ASC" | "DESC" } },
    shape: T,
    options?: { orderBy?: { colName: Column; direction: "ASC" | "DESC" } }
    ) {
    return sql<SelectResultFields<T>[]>`coalesce(jsonb_agg(${jsonBuildObject(
    shape,
    )}${
    options?.orderBy
    ? sql`order by ${options.orderBy.colName} ${sql.raw(
    options.orderBy.direction,
    )}`
    : undefined
    }), '${sql`[]`}')`;
    return sql<SelectResultFields<T>[]>`coalesce(jsonb_agg(${jsonBuildObject(
    shape
    )}${
    options?.orderBy
    ? sql`order by ${options.orderBy.colName} ${sql.raw(
    options.orderBy.direction
    )}`
    : undefined
    }), '${sql`[]`}')`;
    }

    // with filter non-null
    @@ -61,22 +61,19 @@ export function jsonAgg<Table extends AnyTable<TableConfig>>(table: Table) {

    // generalist
    export function jsonAgg<Table extends AnyTable<TableConfig>>(table: Table) {
    return sql<InferModel<Table>[]>`coalesce(json_agg(${table}), ${sql`[]`})`;
    return sql<InferModel<Table>[]>`coalesce(json_agg(${table}), ${sql`[]`})`;
    }

    // you use it like that:
    const result = await db
    .select({
    post,
    // keep only what you need from table theme
    themes: jsonAggBuildObject({
    id: theme.id,
    label: theme.label,
    }),
    })
    .leftJoin(
    postsThemes,
    eq(postsThemes.theme_id, post.theme_id)
    )
    .leftJoin(theme, eq(theme.id, postsThemes.theme_id))
    .groupBy(post.id);
    .select({
    post,
    // keep only what you need from table theme
    themes: jsonAggBuildObject({
    id: theme.id,
    label: theme.label,
    }),
    })
    .leftJoin(postsThemes, eq(postsThemes.theme_id, post.theme_id))
    .leftJoin(theme, eq(theme.id, postsThemes.theme_id))
    .groupBy(post.id);
  24. rphlmr revised this gist Jan 29, 2024. 1 changed file with 15 additions and 15 deletions.
    30 changes: 15 additions & 15 deletions clear-db.ts
    View file
    Original file line number Diff line number Diff line change
    @@ -1,25 +1,25 @@
    // Credits to Louistiti from Drizzle Discord: https://discord.com/channels/1043890932593987624/1130802621750448160/1143083373535973406

    import { sql } from 'drizzle-orm';
    import { sql } from "drizzle-orm";

    const clearDb = async (): Promise<void> => {
    const query = sql<string>`SELECT table_name
    const query = sql<string>`SELECT table_name
    FROM information_schema.tables
    WHERE table_schema = 'public'
    AND table_type = 'BASE TABLE';
    `;

    const tables = await db.execute(query);

    // @LauraKirby
    for (let table of tables.rows) {
    const query = sql.raw(`TRUNCATE TABLE ${table.table_name} CASCADE;`);
    await db.execute(query);
    }
    const tables = await db.execute(query);

    // previous version
    // for (let table of tables) {
    // const query = sql.raw(`TRUNCATE TABLE ${table.table_name} CASCADE;`);
    // await db.execute(query);
    // }
    }
    // @LauraKirby
    for (let table of tables.rows) {
    const query = sql.raw(`TRUNCATE TABLE ${table.table_name} CASCADE;`);
    await db.execute(query);
    }

    // previous version
    // for (let table of tables) {
    // const query = sql.raw(`TRUNCATE TABLE ${table.table_name} CASCADE;`);
    // await db.execute(query);
    // }
    };
  25. rphlmr revised this gist Jan 29, 2024. 1 changed file with 8 additions and 1 deletion.
    9 changes: 8 additions & 1 deletion jsonAgg_jsonAggBuildObject.ts
    View file
    Original file line number Diff line number Diff line change
    @@ -18,8 +18,15 @@ export function jsonBuildObject<T extends SelectedFields>(shape: T) {
    if (chunks.length > 0) {
    chunks.push(sql.raw(`,`));
    }

    chunks.push(sql.raw(`'${key}',`));
    chunks.push(sql`${value}`);

    // json_build_object formats to ISO 8601 ...
    if ((value as any)["columnType"] === "PgTimestampString") {
    chunks.push(sql`timezone('UTC', ${value})`);
    } else {
    chunks.push(sql`${value}`);
    }
    });

    return sql<SelectResultFields<T>>`coalesce(json_build_object(${sql.join(
  26. rphlmr revised this gist Jan 25, 2024. 1 changed file with 3 additions and 0 deletions.
    3 changes: 3 additions & 0 deletions utils.ts
    View file
    Original file line number Diff line number Diff line change
    @@ -116,3 +116,6 @@ function getSubQueryColumns<S extends ColumnsSelection, A extends string>(

    return selection;
    }

    export type InferSubQueryModel<T extends SQL.Aliased> = T["_"]["type"];

  27. rphlmr revised this gist Jan 23, 2024. 1 changed file with 2 additions and 2 deletions.
    4 changes: 2 additions & 2 deletions utils.ts
    View file
    Original file line number Diff line number Diff line change
    @@ -107,8 +107,8 @@ export function inJsonArray<T extends SQL.Aliased<unknown[]>>(


    // Like getTableColumns but for sub queries https://orm.drizzle.team/docs/goodies#get-typed-table-columns
    function getSubQueryColumns<S extends SubqueryWithSelection<any, string>>(
    subQuery: S,
    function getSubQueryColumns<S extends ColumnsSelection, A extends string>(
    subQuery: SubqueryWithSelection<S, A> | WithSubqueryWithSelection<S, A>,
    ) {
    const { selection } = (subQuery as any)[SubqueryConfig] as {
    selection: (typeof subQuery)["_"]["selectedFields"];
  28. rphlmr revised this gist Jan 23, 2024. 1 changed file with 12 additions and 0 deletions.
    12 changes: 12 additions & 0 deletions utils.ts
    View file
    Original file line number Diff line number Diff line change
    @@ -104,3 +104,15 @@ export function inJsonArray<T extends SQL.Aliased<unknown[]>>(
    WHERE ${inArray(sql`${element}->>${key}`, values)}
    )`;
    }


    // Like getTableColumns but for sub queries https://orm.drizzle.team/docs/goodies#get-typed-table-columns
    function getSubQueryColumns<S extends SubqueryWithSelection<any, string>>(
    subQuery: S,
    ) {
    const { selection } = (subQuery as any)[SubqueryConfig] as {
    selection: (typeof subQuery)["_"]["selectedFields"];
    };

    return selection;
    }
  29. rphlmr revised this gist Dec 28, 2023. 1 changed file with 2 additions and 1 deletion.
    3 changes: 2 additions & 1 deletion utils.ts
    View file
    Original file line number Diff line number Diff line change
    @@ -43,7 +43,8 @@ export function count<Column extends AnyColumn>(column: Column) {

    /**
    * Coalesce a value to a default value if the value is null
    * Ex: themes: coalesce(pubThemeListQuery.themes, sql`'[]'`),
    * Ex default array: themes: coalesce(pubThemeListQuery.themes, sql`'[]'`)
    * Ex default number: votesCount: coalesce(PubPollAnswersQuery.count, sql`0`)
    */
    export function coalesce<T>(value: SQL.Aliased<T>, defaultValue: SQL) {
    return sql<T>`coalesce(${value}, ${defaultValue})`;
  30. rphlmr revised this gist Dec 28, 2023. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion utils.ts
    View file
    Original file line number Diff line number Diff line change
    @@ -43,7 +43,7 @@ export function count<Column extends AnyColumn>(column: Column) {

    /**
    * Coalesce a value to a default value if the value is null
    *
    * Ex: themes: coalesce(pubThemeListQuery.themes, sql`'[]'`),
    */
    export function coalesce<T>(value: SQL.Aliased<T>, defaultValue: SQL) {
    return sql<T>`coalesce(${value}, ${defaultValue})`;