Dan
Dan
Explore posts from servers
DTDrizzle Team
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