Marcel Overdijk
Marcel Overdijk
Explore posts from servers
PPrisma
Created by Marcel Overdijk on 10/22/2024 in #help-and-questions
How to setup multiple Prisma clients in combination with `prismaSchemaFolder`
I'm trying to setup multiple Prisma clients (as I have 2 sperate databases) in combination with prismaSchemaFolder. As I'm having 2 Prisma clients I need to define the ouput folder manually like:
generator client {
output = "../../../node_modules/@prisma/db1/client/"
provider = "prisma-client-js"
previewFeatures = ["prismaSchemaFolder"]
}
generator client {
output = "../../../node_modules/@prisma/db1/client/"
provider = "prisma-client-js"
previewFeatures = ["prismaSchemaFolder"]
}
1) Is that output fulder the recommended destination in this use case? The generated files look good as far as I can see, however when importing this later like:
import { PrismaClient as db1PrismaClient } from '@prisma/db1/client';
import { PrismaClient as db1PrismaClient } from '@prisma/db1/client';
I get the following error: Cannot find module '@prisma/db1/client'
4 replies
PPrisma
Created by Marcel Overdijk on 9/12/2024 in #help-and-questions
Order by `rowid`?
Is it possible to order by (SQLite) rowid? I need to order by rowid as my table has no other natural sorting column.
const driverFamilyRelationships = await c.var.f1db.driverFamilyRelationship.findMany({
where: {
AND: [{ driverId: driverId }, pageableQueryOptions.where],
},
orderBy: [
// TODO: { _raw: Prisma.sql`rowid` },
...pageableQueryOptions.orderBy,
],
skip: pageableQueryOptions.skip,
take: pageableQueryOptions.take,
});
const driverFamilyRelationships = await c.var.f1db.driverFamilyRelationship.findMany({
where: {
AND: [{ driverId: driverId }, pageableQueryOptions.where],
},
orderBy: [
// TODO: { _raw: Prisma.sql`rowid` },
...pageableQueryOptions.orderBy,
],
skip: pageableQueryOptions.skip,
take: pageableQueryOptions.take,
});
3 replies
KKysely
Created by Marcel Overdijk on 9/9/2024 in #help
Dynamic query parts based on user input
Based on certain user input (REST API), I need to filter and/or sort by provided fields. This could mean that some joins need to be added as well. The fields to filter and sort are not always a 1-1 mapping to a database column, the names can deviate. I came of with a mapping like:
const countryJoin = { type: 'left', sql: 'country AS coun ON coun.id = country_id']
const countryContinentJoin = { type: 'left', sql: 'continent AS coun_cont ON coun_cont.id = cons.id']

const queryFields = [
'name': { col: 'name' },
// .. more constructor fields
'country.code': { col: 'coun.alpha2_code', join: [countryJoin] },
// .. more country fields
'country.continent.code': { col: 'cont.code', join: [countryJoin, countryContinentJoin] },
// .. more continent fields
]
const countryJoin = { type: 'left', sql: 'country AS coun ON coun.id = country_id']
const countryContinentJoin = { type: 'left', sql: 'continent AS coun_cont ON coun_cont.id = cons.id']

const queryFields = [
'name': { col: 'name' },
// .. more constructor fields
'country.code': { col: 'coun.alpha2_code', join: [countryJoin] },
// .. more country fields
'country.continent.code': { col: 'cont.code', join: [countryJoin, countryContinentJoin] },
// .. more continent fields
]
and this is an example case of how to use it:
let query = db
.selectFrom('constructor')
.selectAll('constructor');

if (_sort_on_country_code_) { // ignore for now how resolved.
const field = queryFields['country.code'];
if (field.join && field.join.length > 1) {
field.join.forEach((join) => {
query.leftJoin(join.sql)
});
}
const direction: 'asc' | 'desc' = 'asc'; // ignore for how resolved.
query.orderBy(sql`${field.col}`, direction);
}

const result = await query;
let query = db
.selectFrom('constructor')
.selectAll('constructor');

if (_sort_on_country_code_) { // ignore for now how resolved.
const field = queryFields['country.code'];
if (field.join && field.join.length > 1) {
field.join.forEach((join) => {
query.leftJoin(join.sql)
});
}
const direction: 'asc' | 'desc' = 'asc'; // ignore for how resolved.
query.orderBy(sql`${field.col}`, direction);
}

const result = await query;
above is just for illustration, in fact the call will be something like:
let query = db
.selectFrom('constructor')
.selectAll('constructor');

this.enhanceQuery(query); // enhanceQuery has access to API args + queryFields config.

const result = await query;
let query = db
.selectFrom('constructor')
.selectAll('constructor');

this.enhanceQuery(query); // enhanceQuery has access to API args + queryFields config.

const result = await query;
and this works! But the thing is it depends havily on the sql tag, so none is typesafe unfortuantely. Kylesy itself does a tremendous job about type safety. Is there a way to make that queryFields with the joins type safe? e.g. with helper methods?
5 replies
DTDrizzle Team
Created by Marcel Overdijk on 9/9/2024 in #help
creating a join for re-use
Drizzle support creating re-usable where and order by expressions like: gt(customerTable.salary, 5000); desc(customerTable.salary) for this no query builder (qb ) is needed. is something similar available (without the need of a qb) to create joins (and apply them later)? and then later do something like:
const join = // a set of joins to add
const where = // a set of conditions to add
const orderBy = // a set of order by's to add
const pagination = // pagination params

const result = await db
.select()
.from(customerTable)
.join(...joins)
.where(...where)
.orderBy(...orderBy)
.limit(pagination.limit)
.offset(pagination.offset)
const join = // a set of joins to add
const where = // a set of conditions to add
const orderBy = // a set of order by's to add
const pagination = // pagination params

const result = await db
.select()
.from(customerTable)
.join(...joins)
.where(...where)
.orderBy(...orderBy)
.limit(pagination.limit)
.offset(pagination.offset)
for where and orderBy this is already possible, but for joins I could't find anything. my use case is that I'm doing some dynamic stuff. for now I created my own wrapper like:

export interface Join {
type: 'inner' | 'left';
table: Table;
on: SQL | SQL[];
}

export interface Join {
type: 'inner' | 'left';
table: Table;
on: SQL | SQL[];
}
and I'm doing something like:

if (joins) {
joins.forEach((join) => {
if (!appliedJoins.includes(join)) {
const conditions = Array.isArray(join.on) ? join.on : [join.on];
if (join.type === 'left') {
qb.leftJoin(join.table, and(...conditions));
}
if (join.type === 'inner') {
qb.innerJoin(join.table, and(...conditions));
}
appliedJoins.push(join);
}
});
}

if (joins) {
joins.forEach((join) => {
if (!appliedJoins.includes(join)) {
const conditions = Array.isArray(join.on) ? join.on : [join.on];
if (join.type === 'left') {
qb.leftJoin(join.table, and(...conditions));
}
if (join.type === 'inner') {
qb.innerJoin(join.table, and(...conditions));
}
appliedJoins.push(join);
}
});
}
Note what I'm doing is quite dyamic, so having a re-usable withCountryJoin(qb) won't help me unfortunately.
1 replies
KKysely
Created by Marcel Overdijk on 9/5/2024 in #help
Error: don't await SelectQueryBuilder instances directly.
To execute the query you need to call execute or executeTakeFirst. I'm trying to create a function that enhances the query (SelectQueryBuilder) with pagination, sorting and filtering options that are derived from a HTTP request. This function looks like:
async applyQueryParams<DB, TB extends keyof DB, O>(qb: SelectQueryBuilder<DB, TB, O>): Promise<SelectQueryBuilder<DB, TB, O>> {
const data = await this.getValidatedData<PaginationSchema & SortSchema & FilterSchema>();
const page = .. // derive page
const pageSize = .. // derive page size
const sort = .. // derive sort
const filter = .. // derive filter

console.log("Query builder before pagination:", qb);

qb = qb.limit(pageSize);
qb = qb.offset((page - 1) * pageSize);

console.log("Query builder after pagination:", qb);

return qb;
}
async applyQueryParams<DB, TB extends keyof DB, O>(qb: SelectQueryBuilder<DB, TB, O>): Promise<SelectQueryBuilder<DB, TB, O>> {
const data = await this.getValidatedData<PaginationSchema & SortSchema & FilterSchema>();
const page = .. // derive page
const pageSize = .. // derive page size
const sort = .. // derive sort
const filter = .. // derive filter

console.log("Query builder before pagination:", qb);

qb = qb.limit(pageSize);
qb = qb.offset((page - 1) * pageSize);

console.log("Query builder after pagination:", qb);

return qb;
}
this function needs to async as the getValidatedData() is also async in a handler I call applyQueryParams like:
async handle(c: C) {

let qb = c.var.mydb
.selectFrom('customer')
.selectAll('customer');

console.log("Before applying query params:", qb);

qb = await this.applyQueryParams(qb);

console.log("After applying query params:", qb);

const customers = await qb.execute();
async handle(c: C) {

let qb = c.var.mydb
.selectFrom('customer')
.selectAll('customer');

console.log("Before applying query params:", qb);

qb = await this.applyQueryParams(qb);

console.log("After applying query params:", qb);

const customers = await qb.execute();
but this fails with:
Before applying query params: SelectQueryBuilderImpl {}
Query builder before pagination: SelectQueryBuilderImpl {}
Query builder after pagination: SelectQueryBuilderImpl {}
✘ [ERROR] Error: don't await SelectQueryBuilder instances directly. To execute the query you need to call `execute` or `executeTakeFirst`.
Before applying query params: SelectQueryBuilderImpl {}
Query builder before pagination: SelectQueryBuilderImpl {}
Query builder after pagination: SelectQueryBuilderImpl {}
✘ [ERROR] Error: don't await SelectQueryBuilder instances directly. To execute the query you need to call `execute` or `executeTakeFirst`.
and I don't understand what is wrong 😦 .... this seems not to be correct: qb = await this.applyQueryParams(qb);, but I need to await it. anybody an idea what I'm doing wrong?
9 replies
DTDrizzle Team
Created by Marcel Overdijk on 9/1/2024 in #help
Using `$dynamic()` to enhance where clause
I have a function like: export const applyFilter = (qb: SQLiteSelect, paging: Paging, filter: string | undefined) => { .. } which adds limit and offset and also where using qb.where(..). This works great, but now I have the use case the qb received already contains a where clause. But the qb.where(..) overwrites it unfortunately. Is there a way to merge them instead, if the qb already has a where?
22 replies
CDCloudflare Developers
Created by Marcel Overdijk on 8/27/2024 in #workers-help
Global ContextHolder
I'm using Hono on cloudflare workers and my handle functions look like:
async handle(ctx: Context<Env>) { .. }
async handle(ctx: Context<Env>) { .. }
and I'm then passing that ctx that to other method calls in multiple layers. which feels quite cumbersome. is it possible to create some global ContextHolder where I can set and get the ctx from? Like a request-scoped holder... I wonder how others do this?
3 replies
CDCloudflare Developers
Created by Marcel Overdijk on 8/27/2024 in #workers-help
Simple logging in development
In development I want to be able to do some logging, like:
logger.debug('Fetching customer with id=%s', id);
logger.trace('Querying database for customer with id=%s', id);
logger.debug('Fetching customer with id=%s', id);
logger.trace('Querying database for customer with id=%s', id);
by default it's fine to have just debug, but sometimes I would also like to trace logs for more details. Besides the log message itself, I would like to log also automatically some context info, like the Hono request-id. I tried Pino js logger, but it gives some problems on Cloudflare workers, so I wonder if there are other (simple) solutions, or that I can better write my own?
3 replies
DTDrizzle Team
Created by Marcel Overdijk on 8/26/2024 in #help
[sqlite] How to order by rowid?
Having a query like:
db.query.countryTable.findAll({
where: eq(countryTable.continentId, continentId),
orderBy: // how to order by rowid
})
db.query.countryTable.findAll({
where: eq(countryTable.continentId, continentId),
orderBy: // how to order by rowid
})
I would like to order by the sqlite rowid. Maybe in this example it would make more sense to order by the country name, but this just for an example. In my case I have no natural column to order by, but want to sort on rowid.
4 replies
DTDrizzle Team
Created by Marcel Overdijk on 7/31/2024 in #help
Dynamically adding joins based on relation?
I have this setup:
export const circuitTable = sqliteTable('circuit', {
id: text('id').notNull().primaryKey(),
name: text('name').notNull(),
placeName: text('place_name').notNull(),
countryId: text('country_id')
.notNull()
.references(() => countryTable.id),
});

export const circuitRelations = relations(circuitTable, ({ one }) => ({
country: one(countryTable, {
fields: [circuitTable.countryId],
references: [countryTable.id],
}),
}));
export const circuitTable = sqliteTable('circuit', {
id: text('id').notNull().primaryKey(),
name: text('name').notNull(),
placeName: text('place_name').notNull(),
countryId: text('country_id')
.notNull()
.references(() => countryTable.id),
});

export const circuitRelations = relations(circuitTable, ({ one }) => ({
country: one(countryTable, {
fields: [circuitTable.countryId],
references: [countryTable.id],
}),
}));
I can easily refer to columns within the circuit table like circuitTable.name or circuitTable.placeName. Can I also refer to relations to dynamically add a join? E.g.:
const baseQuery = ctx.mydb
.select({
...getTableColumns(circuitTable),
})
.from(circuitTable)
.$dynamic();
const baseQuery = ctx.mydb
.select({
...getTableColumns(circuitTable),
})
.from(circuitTable)
.$dynamic();
and then based on user input I want to add some joins dynamically. E.g. the user input could be sort on placeName or country.name, and in the latter case I want to add the country join. As I have this in many places I would like to do something smart like:
const sortFields = {
'id': circuitTable.id,
'name': circuitTable.name,
'placeName': circuitTable.placeName,
'countryId': circuitTable.countryId,
'countryName': {
column: countryTable.name,
join: circuitRelations.country, // not possible
}
};
const sortFields = {
'id': circuitTable.id,
'name': circuitTable.name,
'placeName': circuitTable.placeName,
'countryId': circuitTable.countryId,
'countryName': {
column: countryTable.name,
join: circuitRelations.country, // not possible
}
};
based on such configs I wanted to apply the sort based on user input sort field. Would something like that be possible?
7 replies
DTDrizzle Team
Created by Marcel Overdijk on 7/8/2024 in #help
Typesafe where? argument possible?
I have a service class containing:
async findAll(limit: number, offset: number): Promise<Circuit[]> {
logger.debug('CircuitService.findAll() called with limit=%s, offset=%s', limit, offset);

// Retrieve the circuits from the database.
const circuits = await this.f1db.query.circuits.findMany({ limit, offset });

return circuits;
}
async findAll(limit: number, offset: number): Promise<Circuit[]> {
logger.debug('CircuitService.findAll() called with limit=%s, offset=%s', limit, offset);

// Retrieve the circuits from the database.
const circuits = await this.f1db.query.circuits.findMany({ limit, offset });

return circuits;
}
now I want to add a where? argument to this findAll function. Is there a type I can use from Drizzle to make this where? arg it typesafe?
8 replies
CDCloudflare Developers
Created by Marcel Overdijk on 5/4/2024 in #workers-help
Logging
I'm wondering what others are using for application level logging? Just console.log ? I woud like to enable some custom logging statements based on the environment, like log.debug, log.warn Has anyone tries libraries like Winston or Pino on workers? Is there a recommendation?
10 replies
HHono
Created by Marcel Overdijk on 4/15/2024 in #help
Validate response objects
Using the zod validator middleware it is possible to validate params , query, request body like:
const route = app.post(
'/posts',
zValidator(
'form',
z.object({
body: z.string(),
})
),
(c) => {
// ...
return {
..
}
}
)
const route = app.post(
'/posts',
zValidator(
'form',
z.object({
body: z.string(),
})
),
(c) => {
// ...
return {
..
}
}
)
But is it also possible to validate the response object returned by the handler, to eg make sure response does contain required properties, but also does not include any unwanted properties?
4 replies
HHono
Created by Marcel Overdijk on 4/9/2024 in #help
How to generate openapi.yaml spec with Zod OpenAPI Hono?
Is it possible to generate the openapi.yaml spec with Zod OpenAPI Hono from the commandline using a npm command? I need this in my development workflow to use the openapi.yaml to generate some other client code from the spec.
4 replies
CDCloudflare Developers
Created by Marcel Overdijk on 4/8/2024 in #workers-help
D1 false type-safety using TypeScript
As explained here: https://developers.cloudflare.com/d1/build-with-d1/d1-client-api/#typescript-support
D1 client API is fully-typed via the @cloudflare/workers-types package, and also supports generic types as part of its TypeScript API. A generic type allows you to provide an optional type parameter so that a function understands the type of the data it is handling.

When using the query statement methods stmt.all(), stmt.raw() and stmt.first(), you can provide a type representing each database row. D1’s API will return the result object with the correct type.

For example, providing an OrderRow type as a type parameter to stmt.all() will return a typed Array<OrderRow> object instead of the default Record<string, unknown> type:
D1 client API is fully-typed via the @cloudflare/workers-types package, and also supports generic types as part of its TypeScript API. A generic type allows you to provide an optional type parameter so that a function understands the type of the data it is handling.

When using the query statement methods stmt.all(), stmt.raw() and stmt.first(), you can provide a type representing each database row. D1’s API will return the result object with the correct type.

For example, providing an OrderRow type as a type parameter to stmt.all() will return a typed Array<OrderRow> object instead of the default Record<string, unknown> type:
but trying this out it's giving false type-safety. Imagine this type definition:
type PetRow = {
id: number;
type: "DOG" | "CAT";
name: string;
dateOfBirth: string;
dateOfDeath: string;
chipNumber: string;
}
type PetRow = {
id: number;
type: "DOG" | "CAT";
name: string;
dateOfBirth: string;
dateOfDeath: string;
chipNumber: string;
}
(note the camel case property names, and there is no breed field) and the following query:
const result = await context.env.DB.prepare(
`SELECT id, type, name, date_of_birth, date_of_death, chip_number, breed
FROM pet
ORDER BY name ASC
LIMIT 100`
).all<PetRow>();

const pets = result.results;
const result = await context.env.DB.prepare(
`SELECT id, type, name, date_of_birth, date_of_death, chip_number, breed
FROM pet
ORDER BY name ASC
LIMIT 100`
).all<PetRow>();

const pets = result.results;
(note the underscore case as it is stored like that in the database) Now the result is:
[
{
"id": 1,
"type": "DOG",
"name": "Simba",
"date_of_birth": null,
"date_of_death": null,
"chip_number": '1234567890',
"breed": 'Australian Shepard',
},
..
]
[
{
"id": 1,
"type": "DOG",
"name": "Simba",
"date_of_birth": null,
"date_of_death": null,
"chip_number": '1234567890',
"breed": 'Australian Shepard',
},
..
]
All properties (date_of_birth, date_of_death, chip_number, breed) in the query that do not match the type definition are still included in the result. It gives some false type-safety.
2 replies
CDCloudflare Developers
Created by Marcel Overdijk on 4/8/2024 in #pages-help
[TS] Passing object/data from CF Functions middleware to function handlers
I would like to setup Prisma in a functions _middleware.ts like:
import { PrismaClient } from '@prisma/client';
import { PrismaD1 } from '@prisma/adapter-d1';

export const onRequest: PagesFunction<Env> = async (context) => {
const adapter = new PrismaD1(context.env.DB);
const prisma = new PrismaClient({ adapter });

// now store prisma so it's available in function hadlers!
context.env.prisma = prisma;
// or
context.data.prisma = prisma;

return await context.next();
}
import { PrismaClient } from '@prisma/client';
import { PrismaD1 } from '@prisma/adapter-d1';

export const onRequest: PagesFunction<Env> = async (context) => {
const adapter = new PrismaD1(context.env.DB);
const prisma = new PrismaClient({ adapter });

// now store prisma so it's available in function hadlers!
context.env.prisma = prisma;
// or
context.data.prisma = prisma;

return await context.next();
}
I have a wrangler types functions/worker-configuration.d.ts generated file that looks like:
// Generated by Wrangler on Fri Apr 05 2024 23:40:46 GMT+0200 (Central European Summer Time)
// by running `wrangler types functions/worker-configuration.d.ts`

interface Env {
DB: D1Database;
}
// Generated by Wrangler on Fri Apr 05 2024 23:40:46 GMT+0200 (Central European Summer Time)
// by running `wrangler types functions/worker-configuration.d.ts`

interface Env {
DB: D1Database;
}
In my function handler I would like to use prisma client (type-safe) like:
export const onRequestGet: PagesFunction<Env> = async (context) => {
// TODO: get typed prisma client
const pets = await prisma.pet.findMany();
return Response.json(pets);
};
export const onRequestGet: PagesFunction<Env> = async (context) => {
// TODO: get typed prisma client
const pets = await prisma.pet.findMany();
return Response.json(pets);
};
How should I pass the prisma client best? Via context.env or context.data ? And how to type it?
1 replies
CDCloudflare Developers
Created by Marcel Overdijk on 4/7/2024 in #pages-help
Failed: an internal error occurred. If this continues, contact support: https://cfl.re/3WgEyrH
I've see multiple Failed: an internal error occurred errors here, but non of them helped me track down why my app is giving me the same. Unfortunately there is no detailed error log, it just gives me:
22:53:42.785 Success: Finished cloning repository files
22:53:44.548 wrangler.toml config file detected
22:53:45.465 Failed: an internal error occurred. If this continues, contact support: https://cfl.re/3WgEyrH
22:53:42.785 Success: Finished cloning repository files
22:53:44.548 wrangler.toml config file detected
22:53:45.465 Failed: an internal error occurred. If this continues, contact support: https://cfl.re/3WgEyrH
It's a very small app, the dist folder just being 7 files and 508 KB. It has some functions using Prisma using the D1 binding. Locally the app runs fine with wrangler dev... Deployment ID: 03c8c103127e788e820573a1587a5cab0808522a
7 replies
CDCloudflare Developers
Created by Marcel Overdijk on 4/5/2024 in #pages-help
running `ng serve` together with `wrangler pages dev`
I'm running a POC with an Angular frontend (non-SSR) and an API inside the functions folders. The setup is running nicely on CF when deployed. But I'm now seeking for a good development workflow... With wrangler pages dev I can test my api on port 8787 and it picks up changes to my API in development without restarting. I would like to integrate the Angular reload capabilities in my development environment as well via, ng serve (that runs on port 4200). Ideally I would like to run them on the same port.. Maybe proxying client request towards to 8787 to 4200 in wrangler dev? I wonder how others are doing this. Btw this is similar for running a React setup using Functions.
1 replies
PPrisma
Created by Marcel Overdijk on 4/2/2024 in #help-and-questions
Cursor based pagination without numeric id available in table
When using cursor based pagination (https://www.prisma.io/docs/orm/prisma-client/queries/pagination#cursor-based-pagination) what if a table has no sequential number? Imagine a Person table with an id which is a guid, or maybe just in the format 'firstname-lastname' (like 'vivian-defoe'). And then I would like to do cursor based pagination in combination with an user supplied order (like via a REST api users can provide the sorting option like first_name, last_name, gender, date_of_birth, place_name_of_origin. Is that possible, or should I use offset pagination in that case?
3 replies
CDCloudflare Developers
Created by Marcel Overdijk on 4/2/2024 in #pages-help
Downloading data before building pages (SSG/SSR)
I have the use case that I'm experimenting building a Cloudflare Pages project using Astro. It will using a dynamic GraphQL endpoint which queries data from a D1 database. However it will also generate static pages (SSG). For these static pages I have a local SQLite file that will be queries to dynamically create the (static) pages. Locally on my development machine this works perfectly. However, I would like to switch to a mode that I automatically re-deploy my site via a push to Github. Now that local SQLite is ~60MB (zipped ~6MB) and I don't want to upload that Github... Is it possible to either download that file during the build process, or upload it somewhere before the build kicks off? Note that the SQLite file (zip) is publicly available so that's not an issue. I'm looking at the possibilities I have; and what possible limitations are.
14 replies