Dan Posts - Answer Overflow

Dan

Explore posts from servers

•Created by Dan on 7/26/2023 in #help

Postgres + WITH sql

I'm trying to do the following sql:

WITH v (id) AS (
    VALUES('448e2cc0-aa49-4339-babc-db8aec706ea6'),
        ('b7709b0a-5c38-436a-bad9-a7330890d7db'),
        ('41ca49eb-3550-48ca-8810-f0c03d2bf1c3'),
        ('87d2793d-95dc-40e4-9b9b-288c6a897422'),
        ('d6a5c825-c3b7-49c1-afdb-36edbc881927')
)
SELECT v.id
FROM v
LEFT JOIN "items" ON "items"."id" = v.id::uuid
WHERE "items"."id" IS NULL

WITH v (id) AS (
    VALUES('448e2cc0-aa49-4339-babc-db8aec706ea6'),
        ('b7709b0a-5c38-436a-bad9-a7330890d7db'),
        ('41ca49eb-3550-48ca-8810-f0c03d2bf1c3'),
        ('87d2793d-95dc-40e4-9b9b-288c6a897422'),
        ('d6a5c825-c3b7-49c1-afdb-36edbc881927')
)
SELECT v.id
FROM v
LEFT JOIN "items" ON "items"."id" = v.id::uuid
WHERE "items"."id" IS NULL

It returns a list of missing ids in the database. I have it done like this:

const values = Array.from(wmids).map((id) => `('${id}')`).join(',');
const query = sql.raw(`WITH v (id) AS (VALUES ${values}) SELECT v.id FROM v `);
query.append(sql`LEFT JOIN ${Items} ON ${Items.id} = v.id::uuid `);
query.append(sql`WHERE ${isNull(Items.id)}`);

const res = await ctx.db.execute(query);

const values = Array.from(wmids).map((id) => `('${id}')`).join(',');
const query = sql.raw(`WITH v (id) AS (VALUES ${values}) SELECT v.id FROM v `);
query.append(sql`LEFT JOIN ${Items} ON ${Items.id} = v.id::uuid `);
query.append(sql`WHERE ${isNull(Items.id)}`);

const res = await ctx.db.execute(query);

Is there a way to do it more object friendly by using db.$with and db.with(name).... ? The presence of VALUES seems there might not be any helpers for this.

1 replies

DTDrizzle Team

•Created by Dan on 6/17/2023 in #help

How to do migrations that require data changes?

Hi, how can I migrate a live database that requires to do a more complicated data shuffling, e.g. I have this schema:

const Pages = pgTable('pages', {
    id: uuid('id').defaultRandom().primaryKey(),
    title: text('title').notNull(),
    status: text('status', { enum: ['draft', 'published', 'deleted'] })
      .notNull()
      .default('draft'),
    // ...other columns
});

const Pages = pgTable('pages', {
    id: uuid('id').defaultRandom().primaryKey(),
    title: text('title').notNull(),
    status: text('status', { enum: ['draft', 'published', 'deleted'] })
      .notNull()
      .default('draft'),
    // ...other columns
});

If I do this change:

// remove
status: text('status', { enum: ['draft', 'published', 'deleted'] })
  .notNull()
  .default('draft'),
// add
status: integer('status').notNull().default(1),

// remove
status: text('status', { enum: ['draft', 'published', 'deleted'] })
  .notNull()
  .default('draft'),
// add
status: integer('status').notNull().default(1),

It will need more than drizzle-kit is providing, e.g. update query to map existing data to new data. Is drizzle-kit able to handle this case by providing additional code/sql and if not does it mean that I need to do it manually and then reset drizzle snapshots to be able to continue with drizzle-kit from there?

5 replies

Gaming

Programming