Last active
July 22, 2024 12:56
-
-
Save rphlmr/0d1722a794ed5a16da0fdf6652902b15 to your computer and use it in GitHub Desktop.
Drizzle snippets
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// Credits to Louistiti from Drizzle Discord: https://discord.com/channels/1043890932593987624/1130802621750448160/1143083373535973406 | |
import { sql } from "drizzle-orm"; | |
const clearDb = async (): Promise<void> => { | |
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); | |
} | |
// previous version | |
// for (let table of tables) { | |
// const query = sql.raw(`TRUNCATE TABLE ${table.table_name} CASCADE;`); | |
// await db.execute(query); | |
// } | |
}; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
import { sql } from "drizzle-orm"; | |
import { type Logger } from "drizzle-orm/logger"; | |
import { drizzle, type PostgresJsDatabase } from "drizzle-orm/postgres-js"; | |
import postgres from "postgres"; | |
class QueryLogger implements Logger { | |
logQuery(query: string, params: unknown[]): void { | |
console.debug("___QUERY___"); | |
console.debug(query); | |
console.debug(params); | |
console.debug("___END_QUERY___"); | |
} | |
} | |
const client = postgres(process.env.DATABASE_URL!); | |
export const db = drizzle(client, { logger: new QueryLogger() }); |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
import { | |
sql, | |
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"; | |
export function jsonBuildObject<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}',`)); | |
// 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 | |
>( | |
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`[]`}')`; | |
} | |
// with filter non-null + distinct | |
export function jsonAgg<Column extends AnyColumn>(column: Column) { | |
return coalesce<GetColumnData<Column, "raw">[]>( | |
sql`json_agg(distinct ${sql`${column}`}) filter (where ${column} is not null)`, | |
sql`'[]'` | |
); | |
} | |
// generalist | |
export function jsonAgg<Column extends AnyColumn>(column: Column) { | |
return coalesce<GetColumnData<Column, "raw">[]>( | |
sql`json_agg(${sql`${column}`})`, | |
sql`'[]'` | |
); | |
} | |
// 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)`; | |
} | |
// 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)}`; | |
} | |
// exemple: | |
await db | |
.select() | |
.from(existingDiscussionQuery) | |
.where( | |
arrayContained( | |
// from Drizzle | |
existingDiscussionQuery.participants, | |
toArray( | |
[ | |
"c3b1399f-2c6b-40d7-9d37-cfaf9a7c6164", | |
"77c75084-7123-481b-a326-49c9ebceb431", | |
], | |
"uuid[]" | |
) | |
) | |
); | |
// 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); |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
import { | |
sql, | |
type AnyColumn, | |
type SQL, | |
type InferSelectModel, | |
} from "drizzle-orm"; | |
import { | |
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 takeFirst<T>(items: T[]) { | |
return items.at(0); | |
} | |
export function takeFirstOrThrow<T>(items: T[]) { | |
const first = takeFirst(items); | |
if (!first) { | |
throw new Error("First item not found"); | |
} | |
return first; | |
} | |
export function distinct<Column extends AnyColumn>(column: Column) { | |
return sql<Column["_"]["data"]>`distinct(${column})`; | |
} | |
export function distinctOn<Column extends AnyColumn>(column: Column) { | |
return sql<Column["_"]["data"]>`distinct on (${column}) ${column}`; | |
} | |
export function max<Column extends AnyColumn>(column: Column) { | |
return sql<Column["_"]["data"]>`max(${column})`; | |
} | |
export function count<Column extends AnyColumn>(column: Column) { | |
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> | 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 || ""}`; | |
} | |
// | |
// 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">> | |
) { | |
return sql`${column} @> ${value}`; | |
} | |
// Select a JSONB field | |
// example: | |
// const results = await db | |
// .select({ | |
// myField: pickJsonbField< | |
// MyDataType, // the one you use for jsonb("data").$type<MyDataType>().notNull(), | |
// "fieldKey" // one of MyDataType | |
// >(table.data, "fieldKey"), | |
// }) | |
export function pickJsonbField< | |
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 | |
})`; | |
} | |
// .where(inJsonArray(subQueryWithJsonAggregate.anArray, "keyName", [valueFromParams])) | |
export function inJsonArray<T extends SQL.Aliased<unknown[]>>( | |
jsonArray: T, | |
key: keyof T["_"]["type"][number], | |
values: string[] | |
) { | |
const element = sql.raw(`${String(key)}_array_element`); | |
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> | |
) { | |
const { selection } = (subQuery as any)[SubqueryConfig] as { | |
selection: (typeof subQuery)["_"]["selectedFields"]; | |
}; | |
return selection; | |
} | |
export type InferSubQueryModel<T extends SQL.Aliased> = T["_"]["type"]; |
Thank you for sharing this, I was too lazy to do this myself :)
I just have an update that adds distinct and non-null options to jsonBuildObject
:
export function jsonBuildObject<T extends SelectedFields>(
shape: T,
options: { distinct?: boolean },
) {
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<T>
>`${options.distinct ? sql.raw('distinct ') : sql.raw('')}coalesce(jsonb_build_object(${sql.join(chunks)}), '{}')`;
}
export function jsonAggBuildObject<T extends SelectedFields, Column extends AnyColumn>(
shape: T,
options?: {
orderBy?: { colName: Column; direction: 'ASC' | 'DESC' };
distinct?: boolean;
notNullColumn?: keyof T;
},
) {
return sql<
SelectResultFields<T>[]
>`coalesce(jsonb_agg(${jsonBuildObject(shape, { distinct: options?.distinct })}${
options?.orderBy
? sql`order by ${options.orderBy.colName} ${sql.raw(options.orderBy.direction)}`
: undefined
})${options?.notNullColumn ? sql` filter (where ${shape[options.notNullColumn]} is not null)` : sql.raw('')}, '${sql`[]`}')`;
}
I just switched to jsonb
functions since that's what we are using in our setup.
export function jsonBuildObject<T extends SelectedFields>(shape: T): SQL<SelectResultFields<T>> {
const chunks: SQL[] = Object.entries(shape).flatMap(([key, value], index) => {
const keyValueChunk = [
sql.raw(`'${key}',`),
is(value, PgTimestampString) ? sql`timezone('UTC', ${value})` : sql`${value}`
];
return index > 0 ? [sql.raw(','), ...keyValueChunk] : keyValueChunk;
});
return sql`coalesce(json_build_object(${sql.join(chunks)}), '{}')`;
}
export function jsonAggBuildObject<T extends SelectedFields, Column extends AnyColumn>(
shape: T,
options?: { orderBy?: { colName: Column; direction: 'ASC' | 'DESC' } }
): SQL<SelectResultFields<T>[]> {
const orderByClause = options?.orderBy
? sql`order by ${options.orderBy.colName} ${sql.raw(options.orderBy.direction)}`
: sql``;
return sql`coalesce(jsonb_agg(${jsonBuildObject(shape)} ${orderByClause}), '[]'::jsonb)`;
}
export function jsonAggBuildObjectOrEmptyArray<T extends SelectedFields, Table>(
table: Table,
shape: T
): SQL<SelectResultFields<T>[]> {
return sql`
CASE
WHEN COUNT(${table}) = 0 THEN '[]'::jsonb
ELSE jsonb_agg(${jsonBuildObject(shape)})
END
`;
}
I'm using these, enjoy!
What are the imports for these functions/values: is()
and PgTimestampString
?
What are the imports for these functions/values:
is()
andPgTimestampString
?
@luisfontes oups
import { PgTimestampString } from "drizzle-orm/pg-core";
import { is } from "drizzle-orm";
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
@rphlmr thanks for the response! I have made this helper for now, which does the trick, though I am not sure how portable it is
used like this: