PGT Posts - Answer Overflow

PGT

•Created by PGT on 7/19/2024 in #help

migration fail when table exists

I have a table messages that is created in an earlier migration file:

DO $$ BEGIN
 CREATE TYPE "public"."result" AS ENUM('LEFT', 'RIGHT');
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;
--> statement-breakpoint
CREATE TABLE IF NOT EXISTS "messages" (
    "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
    "message" text NOT NULL,
    "created_at" timestamp with time zone DEFAULT now() NOT NULL,
    "updated_at" timestamp with time zone DEFAULT now() NOT NULL
);
...

DO $$ BEGIN
 CREATE TYPE "public"."result" AS ENUM('LEFT', 'RIGHT');
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;
--> statement-breakpoint
CREATE TABLE IF NOT EXISTS "messages" (
    "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
    "message" text NOT NULL,
    "created_at" timestamp with time zone DEFAULT now() NOT NULL,
    "updated_at" timestamp with time zone DEFAULT now() NOT NULL
);
...

In a subsequent migration, I add a column to this table:

ALTER TABLE "messages" ADD COLUMN "email_id" uuid;--> statement-breakpoint
DO $$ BEGIN
 ALTER TABLE "messages" ADD CONSTRAINT "messages_email_id_emails_id_fk" FOREIGN KEY ("email_id") REFERENCES "public"."emails"("id") ON DELETE no action ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

ALTER TABLE "messages" ADD COLUMN "email_id" uuid;--> statement-breakpoint
DO $$ BEGIN
 ALTER TABLE "messages" ADD CONSTRAINT "messages_email_id_emails_id_fk" FOREIGN KEY ("email_id") REFERENCES "public"."emails"("id") ON DELETE no action ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

Both of these .sql files were generated with drizzle-kit generate --config=drizzle.config.ts On a completely fresh DB, running drizzle-kit migrate --config=drizzle.config.ts gets an error:

[⣯] applying migrations...error: relation "messages" does not exist
    at /Users/pthieu/www/cedar-service/node_modules/drizzle-kit/bin.cjs:77696:15
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at <anonymous> (/Users/pthieu/www/cedar-service/node_modules/src/pg-core/dialect.ts:89:7)
    at NodePgSession.transaction (/Users/pthieu/www/cedar-service/node_modules/src/node-postgres/session.ts:155:19)
    at PgDialect.migrate (/Users/pthieu/www/cedar-service/node_modules/src/pg-core/dialect.ts:82:3)
    at migrate (/Users/pthieu/www/cedar-service/node_modules/src/node-postgres/migrator.ts:10:2) {
  length: 106,
  severity: 'ERROR',
  code: '42P01',
...
  routine: 'RangeVarGetRelidExtended'

[⣯] applying migrations...error: relation "messages" does not exist
    at /Users/pthieu/www/cedar-service/node_modules/drizzle-kit/bin.cjs:77696:15
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at <anonymous> (/Users/pthieu/www/cedar-service/node_modules/src/pg-core/dialect.ts:89:7)
    at NodePgSession.transaction (/Users/pthieu/www/cedar-service/node_modules/src/node-postgres/session.ts:155:19)
    at PgDialect.migrate (/Users/pthieu/www/cedar-service/node_modules/src/pg-core/dialect.ts:82:3)
    at migrate (/Users/pthieu/www/cedar-service/node_modules/src/node-postgres/migrator.ts:10:2) {
  length: 106,
  severity: 'ERROR',
  code: '42P01',
...
  routine: 'RangeVarGetRelidExtended'

1 replies

DTDrizzle Team

•Created by PGT on 12/6/2023 in #help

drizzle-kit not detecting tsconfig and getting path alias '~/'

When I try to default to a TS enum, drizzle-kit push fails when i run drizzle-kit push:pg --config=drizzle.config.ts If I just remove the .default(PDF_PARSE_STATUS.PENDING) in my schema (but keep the import import { PDF_PARSE_STATUS } from '~/types';), it will succeed Here's my code: schema.ts

import {
  pgTable,
  text,
  timestamp,
  uuid,
  varchar,
} from 'drizzle-orm/pg-core';

import { PDF_PARSE_STATUS } from '~/types';

export const UserResumeTable = pgTable('user_resumes', {
  id: uuid('id').primaryKey().defaultRandom(),
  filename: text('filename').notNull().unique(),
  content: text('content'),
  status: varchar('status')
    .$type<PDF_PARSE_STATUS>()
    .default(PDF_PARSE_STATUS.PENDING),
  notes: text('notes'),
  createdAt: timestamp('created_at', { withTimezone: true }).defaultNow(),
});
export const UserResume = UserResumeTable;

export type UserResume = typeof UserResumeTable.$inferSelect;
export type NewUserResume = typeof UserResumeTable.$inferInsert;

import {
  pgTable,
  text,
  timestamp,
  uuid,
  varchar,
} from 'drizzle-orm/pg-core';

import { PDF_PARSE_STATUS } from '~/types';

export const UserResumeTable = pgTable('user_resumes', {
  id: uuid('id').primaryKey().defaultRandom(),
  filename: text('filename').notNull().unique(),
  content: text('content'),
  status: varchar('status')
    .$type<PDF_PARSE_STATUS>()
    .default(PDF_PARSE_STATUS.PENDING),
  notes: text('notes'),
  createdAt: timestamp('created_at', { withTimezone: true }).defaultNow(),
});
export const UserResume = UserResumeTable;

export type UserResume = typeof UserResumeTable.$inferSelect;
export type NewUserResume = typeof UserResumeTable.$inferInsert;

types.ts

export enum PDF_PARSE_STATUS {
  SUCCESS = 'SUCCESS',
  FAILED = 'FAILED',
  PENDING = 'PENDING',
}

export enum PDF_PARSE_STATUS {
  SUCCESS = 'SUCCESS',
  FAILED = 'FAILED',
  PENDING = 'PENDING',
}

drizzle.config.ts

import 'dotenv/config';
import type { Config } from 'drizzle-kit';

export default {
  schema: './src/db/schema/index.ts',
  out: './src/db/migrations',
  driver: 'pg',
  dbCredentials: {
    connectionString: process.env.DATABASE_URL!,
  },
} satisfies Config;

import 'dotenv/config';
import type { Config } from 'drizzle-kit';

export default {
  schema: './src/db/schema/index.ts',
  out: './src/db/migrations',
  driver: 'pg',
  dbCredentials: {
    connectionString: process.env.DATABASE_URL!,
  },
} satisfies Config;

10 replies

DTDrizzle Team

•Created by PGT on 11/17/2023 in #help

varchar but cast to typescript enum?

I have the following table:

export const Table = pgTable('table', {
  ...
  status: varchar('status', { length: 32 }).notNull(),
  ...

export const Table = pgTable('table', {
  ...
  status: varchar('status', { length: 32 }).notNull(),
  ...

The type of this field will be string. I don't want to use a db enum, but I want to automatically define the type of this field as type STATUS, which is a TS enum. How would I do this?

4 replies

DTDrizzle Team

•Created by PGT on 9/29/2023 in #help

update where multiple conditions

Trying to do something like a: UPDATE table SET values WHERE condition1 AND condition2 other ORMs will all you to pass in an object for conditions and they'll handle it under the hood, does Drizzle have something like this or do I need to do something like:

const query = db
    .update(Table)
    .set({ ...data, updatedAt: new Date() })
    .where(eq(condition1)
    .where(eq(condition2));

const query = db
    .update(Table)
    .set({ ...data, updatedAt: new Date() })
    .where(eq(condition1)
    .where(eq(condition2));

27 replies

DTDrizzle Team

•Created by PGT on 9/24/2023 in #help

update a nested jsonb field

I have a table with a jsonb column that is something like this:

{
  foo: {
     bar: 'test'
     baz: 'hi'
  },
  foo2: {
    bar: 'test2'
    baz: 'hi2'
  }
}

{
  foo: {
     bar: 'test'
     baz: 'hi'
  },
  foo2: {
    bar: 'test2'
    baz: 'hi2'
  }
}

I want to update columnName.foo.bar and not mutate anything else, how would I do this? In SQL, would be something like:

UPDATE table
SET
    columnName = jsonb_set(columnName, '{foo,bar}', to_jsonb('changing test to something else'), false)
WHERE id = 'someId';

UPDATE table
SET
    columnName = jsonb_set(columnName, '{foo,bar}', to_jsonb('changing test to something else'), false)
WHERE id = 'someId';

Looked around on GitHub issues, Discord, and the docs and can't find a way to do this the Drizzle way.

5 replies

DTDrizzle Team

•Created by PGT on 9/13/2023 in #help

any way to automatically cast numeric types on retrieval?

I have a numeric field in a video table:

export const VideoTable = pgTable('videos', {
  id: uuid('id').primaryKey().defaultRandom(),
  height: integer('height'),
  width: integer('width'),
  duration: numeric('duration', { precision: 10, scale: 5 }),
});

export const VideoTable = pgTable('videos', {
  id: uuid('id').primaryKey().defaultRandom(),
  height: integer('height'),
  width: integer('width'),
  duration: numeric('duration', { precision: 10, scale: 5 }),
});

but duration is a string so I end up casting it around my app is there a better type to use or a way to cast on retreival? (i don't really need that much precision)

5 replies

DTDrizzle Team

•Created by PGT on 9/2/2023 in #help

What's the correct type to use for an update function?

I'm trying to do a simple function where I update a record in a table:

import { NewVideo, VideoTable } from '~/db/schema/video';

const Table = VideoTable;

export function update(id: string, data: NewVideo) {
  const query = db.update(Table).set(data).where(eq(Table.id, id));
  return query;
}

import { NewVideo, VideoTable } from '~/db/schema/video';

const Table = VideoTable;

export function update(id: string, data: NewVideo) {
  const query = db.update(Table).set(data).where(eq(Table.id, id));
  return query;
}

When doing this:

  await Video.update(newVideo.id, {
    transcription_id: transcribeJobData.transcribeJobName,
  });

  await Video.update(newVideo.id, {
    transcription_id: transcribeJobData.transcribeJobName,
  });

I get the error:

Property 'url' is missing in type '{ transcription_id: string | undefined; }' but required in type '{ url: string; id?: string | undefined; transcription_id?: string | null | undefined; transcription?: { start_time: number; end_time: number; type: string; content: string; }[] | null | undefined; createdAt?: Date | ... 1 more ... | undefined; updatedAt?: Date | ... 1 more ... | undefined; }'

Property 'url' is missing in type '{ transcription_id: string | undefined; }' but required in type '{ url: string; id?: string | undefined; transcription_id?: string | null | undefined; transcription?: { start_time: number; end_time: number; type: string; content: string; }[] | null | undefined; createdAt?: Date | ... 1 more ... | undefined; updatedAt?: Date | ... 1 more ... | undefined; }'

Here's my schema

import {
  jsonb,
  pgTable,
  text,
  timestamp,
  uuid,
  varchar,
} from 'drizzle-orm/pg-core';

export const tableName = 'videos';

export const VideoTable = pgTable(tableName, {
  id: uuid('id').primaryKey().defaultRandom(),
  url: text('url').notNull(),
  transcription_id: varchar('transcription_id'),
  transcription: jsonb('transcription').$type<
    Array<{
      start_time: number;
      end_time: number;
      type: string;
      content: string;
    }>
  >(),
  createdAt: timestamp('created_at', { withTimezone: true }).defaultNow(),
  updatedAt: timestamp('updated_at', { withTimezone: true }).defaultNow(),
});

const Table = VideoTable;
export type Video = typeof Table.$inferSelect;
export type NewVideo = typeof Table.$inferInsert;

import {
  jsonb,
  pgTable,
  text,
  timestamp,
  uuid,
  varchar,
} from 'drizzle-orm/pg-core';

export const tableName = 'videos';

export const VideoTable = pgTable(tableName, {
  id: uuid('id').primaryKey().defaultRandom(),
  url: text('url').notNull(),
  transcription_id: varchar('transcription_id'),
  transcription: jsonb('transcription').$type<
    Array<{
      start_time: number;
      end_time: number;
      type: string;
      content: string;
    }>
  >(),
  createdAt: timestamp('created_at', { withTimezone: true }).defaultNow(),
  updatedAt: timestamp('updated_at', { withTimezone: true }).defaultNow(),
});

const Table = VideoTable;
export type Video = typeof Table.$inferSelect;
export type NewVideo = typeof Table.$inferInsert;

Because I'm using NewVideo, it TS expects a url param, but because the record has already been created, this should already exist. Should I use something like Partial<NewVideo>?

3 replies

DTDrizzle Team

•Created by PGT on 7/13/2023 in #help

NeonDbError: db error: ERROR: permission denied for schema drizzle

Created a new database on neon.tech today and trying to run migrations

// src/db/migrate.ts
import { neon } from '@neondatabase/serverless';
import { drizzle } from 'drizzle-orm/neon-http';
import { migrate } from 'drizzle-orm/postgres-js/migrator';
import { resolve } from 'node:path';

export async function migrateLatest() {
  console.log('Running migrations...');
  const dbUrl: string = process.env.DATABASE_URL!;
  const client = neon(dbUrl);
  // const client = postgres(dbUrl, { max: 1 });

  const db = drizzle(client);
  // XXX(Phong): if you change `process.cwd()`, you need to change Dockerfile
  const dbDir = resolve(
    process.env.NODE_ENV === 'production' ? process.cwd() : __dirname,
    'migrations',
  );
  await migrate(db, {
    migrationsFolder: dbDir,
  });
  console.log('Migrations completed successfully');
  // client.end();
}

// src/db/migrate.ts
import { neon } from '@neondatabase/serverless';
import { drizzle } from 'drizzle-orm/neon-http';
import { migrate } from 'drizzle-orm/postgres-js/migrator';
import { resolve } from 'node:path';

export async function migrateLatest() {
  console.log('Running migrations...');
  const dbUrl: string = process.env.DATABASE_URL!;
  const client = neon(dbUrl);
  // const client = postgres(dbUrl, { max: 1 });

  const db = drizzle(client);
  // XXX(Phong): if you change `process.cwd()`, you need to change Dockerfile
  const dbDir = resolve(
    process.env.NODE_ENV === 'production' ? process.cwd() : __dirname,
    'migrations',
  );
  await migrate(db, {
    migrationsFolder: dbDir,
  });
  console.log('Migrations completed successfully');
  // client.end();
}

Running migrations with:

tsx src/db/migrate.ts --config=drizzle.config.ts

tsx src/db/migrate.ts --config=drizzle.config.ts

Error:

> [email protected] migrate /Users/pthieu/www/p-stack-fs
> tsx src/db/migrate.ts --config=drizzle.config.ts

Running migrations...
(Use `node --trace-warnings ...` to show where the warning was created)
/Users/pthieu/www/p-stack-fs/node_modules/.pnpm/@[email protected]/node_modules/@neondatabase/serverless/index.js:1518
if(C===400){let{message:Q,code:j}=await S.json(),V=new Ve(Q);throw V.code=j,V}else{
                                                   ^


NeonDbError: db error: ERROR: permission denied for schema drizzle

Caused by:
    ERROR: permission denied for schema drizzle
...
Node.js v18.9.0

> [email protected] migrate /Users/pthieu/www/p-stack-fs
> tsx src/db/migrate.ts --config=drizzle.config.ts

Running migrations...
(Use `node --trace-warnings ...` to show where the warning was created)
/Users/pthieu/www/p-stack-fs/node_modules/.pnpm/@[email protected]/node_modules/@neondatabase/serverless/index.js:1518
if(C===400){let{message:Q,code:j}=await S.json(),V=new Ve(Q);throw V.code=j,V}else{
                                                   ^


NeonDbError: db error: ERROR: permission denied for schema drizzle

Caused by:
    ERROR: permission denied for schema drizzle
...
Node.js v18.9.0

3 replies

DTDrizzle Team

•Created by PGT on 5/31/2023 in #help

Running `drizzle-kit introspect:pg` returns "client password must be a string"

I'm attempting to move from objection.js+knex.js over to Drizzle and I'm running drizzle-kit introspect:pg against my database but I'm getting the error:

Error: SASL: SCRAM-SERVER-FIRST-MESSAGE: client password must be a string

Error: SASL: SCRAM-SERVER-FIRST-MESSAGE: client password must be a string

Here's my drizzle.config.ts:

import type { Config } from 'drizzle-kit';

export default {
  schema: './src/db/schema/*',
  out: './src/db/migrations',
  connectionString: 'DATABASE_URL',
} satisfies Config;

import type { Config } from 'drizzle-kit';

export default {
  schema: './src/db/schema/*',
  out: './src/db/migrations',
  connectionString: 'DATABASE_URL',
} satisfies Config;

If i change connectionString: 'DATABASE_URL', to connectionString: process.env.DATABASE_URL, I get the error:

Invalid input

Invalid input

I've also tried to specify all the information in my connection string in the CLI params like drizzle-kit instropect:pg --host=... --user=... --password... but get the same error. Any help would be appreciated. Thanks in advance.

6 replies

Gaming

Programming