Gary, el Pingüino Artefacto Posts

Gary, el Pingüino Artefacto

Explore posts from servers

RRailway TtRPC TTCTheo's Typesafe Cult XXata DTDrizzle Team

•Created by Gary, el Pingüino Artefacto on 11/16/2024 in #help

Brand ids?

Hi, it's possible to mapped a primary key to a branded type from effect?

export type UserId = string & Brand.Brand<"UserId">
export const UserId = Brand.nominal<UserId>()

export const usersTable = pgTable("users", {
  // Possible?
  id: uuid().primaryKey().transform((value) => UserId(value)),
  createdAt: timestamp().notNull().defaultNow(),
  givenName: varchar({ length: 64 }).notNull(),
  familyName: varchar({ length: 64 }).notNull(),
})

export type UserId = string & Brand.Brand<"UserId">
export const UserId = Brand.nominal<UserId>()

export const usersTable = pgTable("users", {
  // Possible?
  id: uuid().primaryKey().transform((value) => UserId(value)),
  createdAt: timestamp().notNull().defaultNow(),
  givenName: varchar({ length: 64 }).notNull(),
  familyName: varchar({ length: 64 }).notNull(),
})

9 replies

DTDrizzle Team

•Created by Gary, el Pingüino Artefacto on 11/1/2024 in #help

Conditional batch on Neon Batch API?

Hi, it's possible to do something like:

const deleteImages = true // can be true or false

await db.batch([
  db.update(users).set({ ... }).where(...),
  deleteImages ? db.delete(userImages).where(...) : false
])

const deleteImages = true // can be true or false

await db.batch([
  db.update(users).set({ ... }).where(...),
  deleteImages ? db.delete(userImages).where(...) : false
])

3 replies

DTDrizzle Team

•Created by Gary, el Pingüino Artefacto on 10/14/2024 in #help

Applying drizzle migrations on Vercel with Hono

Hi, I'm trying to apply that migrations on the directory like this:

await migrate(drizzleDb, {
  migrationsFolder: "./src/databases/tenants/migrations",
})

await migrate(drizzleDb, {
  migrationsFolder: "./src/databases/tenants/migrations",
})

I'm using Nextjs, deployed on Vercel with a hono api. The path is /api/hono/tenants/create. But every time I get this error: Error: Can't find meta/_journal.json file at /var/task/.next/server/app/api/hono/[[...route]]/route.js:2467:42887 at qp (/var/task/.next/server/app/api/hono/[[...route]]/route.js:2467:43323) at /var/task/.next/server/app/api/hono/[[...route]]/route.js:2467:44022 at process.processTicksAndRejections (node:internal/process/task_queues:95:5) at async o (/var/task/.next/server/chunks/636.js:67:10412) at async Function.P [as begin] (/var/task/.next/server/chunks/636.js:67:9981) at async qf (/var/task/.next/server/app/api/hono/[[...route]]/route.js:2467:43460) I have tried absolute urls, relative, using path.join, etc. Thanks for the help 😄

2 replies

DTDrizzle Team

•Created by Gary, el Pingüino Artefacto on 8/7/2024 in #help

Get table definition?

How can I get a SQL to create a table based on a table. For example:

const files = pgTable(
  "files",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    createdAt: timestamp("created_at", { mode: "date" }).notNull().defaultNow(),
    url: text("url").notNull(),
    size: decimal("size"),
    contentType: text("content_type"),
    originalName: text("original_name"),
    path: text("path").notNull().default(""),
  },
  (table) => ({
    pathIdx: index().on(table.path),
  })
)


const definition = getTableDefinition(files);

const files = pgTable(
  "files",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    createdAt: timestamp("created_at", { mode: "date" }).notNull().defaultNow(),
    url: text("url").notNull(),
    size: decimal("size"),
    contentType: text("content_type"),
    originalName: text("original_name"),
    path: text("path").notNull().default(""),
  },
  (table) => ({
    pathIdx: index().on(table.path),
  })
)


const definition = getTableDefinition(files);

Definition should be something like this:

CREATE TABLE IF NOT EXISTS "files" (
    "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
    "created_at" timestamp DEFAULT now() NOT NULL,
    "url" text NOT NULL,
    "size" numeric,
    "content_type" text,
    "original_name" text,
    "path" text DEFAULT '' NOT NULL
);

CREATE TABLE IF NOT EXISTS "files" (
    "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
    "created_at" timestamp DEFAULT now() NOT NULL,
    "url" text NOT NULL,
    "size" numeric,
    "content_type" text,
    "original_name" text,
    "path" text DEFAULT '' NOT NULL
);

2 replies

DTDrizzle Team

•Created by Gary, el Pingüino Artefacto on 6/3/2024 in #help

Helpers for querying in the new PostGIS Geometry type

Hi, I was playing around with the new types and I wondered if there are some utility helpers for querying the point. For example:

const stores = pgTable("stores", {
  id: uuid("id").primaryKey().defaultRandom(),
  name: text("name").notNull(),  
  location: geometry("location", { type: 'point', srid: 4326 }),
});

const nearStores = await db
  .select({ id: stores.id, name: stores.name })
  .from(stores)
  .where(ST_DWithin(stores.location, [some_lon, some_lat], 1000))

const stores = pgTable("stores", {
  id: uuid("id").primaryKey().defaultRandom(),
  name: text("name").notNull(),  
  location: geometry("location", { type: 'point', srid: 4326 }),
});

const nearStores = await db
  .select({ id: stores.id, name: stores.name })
  .from(stores)
  .where(ST_DWithin(stores.location, [some_lon, some_lat], 1000))

The SQL should be something like this:

SELECT id, name
FROM stores
WHERE ST_DWithin(location, ST_MakePoint(some_lon, some_lat)::geography, 1000);

SELECT id, name
FROM stores
WHERE ST_DWithin(location, ST_MakePoint(some_lon, some_lat)::geography, 1000);

Thanks 🙂

3 replies

DTDrizzle Team

•Created by Gary, el Pingüino Artefacto on 4/30/2024 in #help

Filtering a jsonb with the shape Array<{id:string,name:string}>

Hi. I have a query that returns all the posts with it's categories like this,

type Posts = {
  id: string
  title: string
  categories: { id: string; name: string }[]
}[]

type Posts = {
  id: string
  title: string
  categories: { id: string; name: string }[]
}[]

I'm trying to filter the posts based on it's categories. This is what I have Helpers:

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)}), '{}')`
}

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`[]`}')`
}

function coalesce<T>(value: SQL.Aliased<T> | SQL<T>, defaultValue: SQL) {
  return sql<T>`coalesce(${value}, ${defaultValue})`
}

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)}), '{}')`
}

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`[]`}')`
}

function coalesce<T>(value: SQL.Aliased<T> | SQL<T>, defaultValue: SQL) {
  return sql<T>`coalesce(${value}, ${defaultValue})`
}

Current query:

const sb = db.$with("sb").as(
  db
    .select({
      ...getTableColumns(posts),
      categories: jsonAggBuildObject({
        id: categories.id,
        name: categories.name,
      }).as("categories"),
    })
    .from(posts)
    .leftJoin(postTags, eq(postTags.postId, posts.id))
    .leftJoin(tags, eq(tags.id, postTags.tagId))
    .groupBy(posts.id)
)

const postsWithCategories = await db
  .with(postsQuery)
  .select()
  .from(postsQuery)
  .where(
    sql`${postsQuery.tags} @> '[{"id": "098b9acb-694d-4c00-b87f-64f9811f8810"},{"id":"46bba9b2-9b50-4e70-9284-6cddb2fe32d4"}]'`
  )

const sb = db.$with("sb").as(
  db
    .select({
      ...getTableColumns(posts),
      categories: jsonAggBuildObject({
        id: categories.id,
        name: categories.name,
      }).as("categories"),
    })
    .from(posts)
    .leftJoin(postTags, eq(postTags.postId, posts.id))
    .leftJoin(tags, eq(tags.id, postTags.tagId))
    .groupBy(posts.id)
)

const postsWithCategories = await db
  .with(postsQuery)
  .select()
  .from(postsQuery)
  .where(
    sql`${postsQuery.tags} @> '[{"id": "098b9acb-694d-4c00-b87f-64f9811f8810"},{"id":"46bba9b2-9b50-4e70-9284-6cddb2fe32d4"}]'`
  )

4 replies

DTDrizzle Team

•Created by Gary, el Pingüino Artefacto on 3/3/2024 in #help

sql operator with array of strings

This works

const example = await db
  .select({
    col: sql`ARRAY['1', '2', '3']`,
  })
  .from(users)
  .limit(1)

const example = await db
  .select({
    col: sql`ARRAY['1', '2', '3']`,
  })
  .from(users)
  .limit(1)

This also works

const example = await db
  .select({
    col: sql`ARRAY['1', '2', '3'] @> ARRAY['1', '2']`,
  })
  .from(users)
  .limit(1)

const example = await db
  .select({
    col: sql`ARRAY['1', '2', '3'] @> ARRAY['1', '2']`,
  })
  .from(users)
  .limit(1)

This crashes

  const example = await db
    .select({
      col: sql`ARRAY['1', '2', '3'] @> ${["1", "2"]}`,
    })
    .from(users)
    .limit(1)

  const example = await db
    .select({
      col: sql`ARRAY['1', '2', '3'] @> ${["1", "2"]}`,
    })
    .from(users)
    .limit(1)

Is there any workaround to pass an array in the sql operator?

4 replies

Gaming

Programming