PostgresError: relation "public.artist" does not exist

Hi, I'm new to drizzle. I am getting this error while trying to apply migrations,

  npm run db:migrate

> app@0.0.1 db:migrate
> tsx src/lib/database/migrate.ts

{
  severity_local: 'NOTICE',
  severity: 'NOTICE',
  code: '42P06',
  message: 'schema "drizzle" already exists, skipping',
  file: 'schemacmds.c',
  line: '131',
  routine: 'CreateSchemaCommand'
}
{
  severity_local: 'NOTICE',
  severity: 'NOTICE',
  code: '42P07',
  message: 'relation "__drizzle_migrations" already exists, skipping',
  file: 'parse_utilcmd.c',
  line: '217',
  routine: 'transformCreateStmt'
}
node:internal/process/promises:288
            triggerUncaughtException(err, true /* fromPromise */);
            ^

PostgresError: relation "public.artist" does not exist
    at ErrorResponse (file:///home/apoorv/repos/app/node_modules/postgres/src/connection.js:788:26)
    at handle (file:///home/apoorv/repos/app/node_modules/postgres/src/connection.js:474:6)
    at Socket.data (file:///home/apoorv/repos/app/node_modules/postgres/src/connection.js:315:9)
    at Socket.emit (node:events:517:28)
    at addChunk (node:internal/streams/readable:368:12)
    at readableAddChunk (node:internal/streams/readable:341:9)
    at Readable.push (node:internal/streams/readable:278:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23) {
  severity_local: 'ERROR',
  severity: 'ERROR',
  code: '42P01',
  where: 'SQL statement "ALTER TABLE "album" ADD CONSTRAINT "album_artist_id_artist_id_fk" FOREIGN KEY ("artist_id") REFERENCES "public"."artist"("id") ON DELETE cascade ON UPDATE no action"\n' +
    'PL/pgSQL function inline_code_block line 2 at SQL statement',
  file: 'namespace.c',
  line: '427',
  routine: 'RangeVarGetRelidExtended'
}

Node.js v18.19.0

  npm run db:migrate

> app@0.0.1 db:migrate
> tsx src/lib/database/migrate.ts

{
  severity_local: 'NOTICE',
  severity: 'NOTICE',
  code: '42P06',
  message: 'schema "drizzle" already exists, skipping',
  file: 'schemacmds.c',
  line: '131',
  routine: 'CreateSchemaCommand'
}
{
  severity_local: 'NOTICE',
  severity: 'NOTICE',
  code: '42P07',
  message: 'relation "__drizzle_migrations" already exists, skipping',
  file: 'parse_utilcmd.c',
  line: '217',
  routine: 'transformCreateStmt'
}
node:internal/process/promises:288
            triggerUncaughtException(err, true /* fromPromise */);
            ^

PostgresError: relation "public.artist" does not exist
    at ErrorResponse (file:///home/apoorv/repos/app/node_modules/postgres/src/connection.js:788:26)
    at handle (file:///home/apoorv/repos/app/node_modules/postgres/src/connection.js:474:6)
    at Socket.data (file:///home/apoorv/repos/app/node_modules/postgres/src/connection.js:315:9)
    at Socket.emit (node:events:517:28)
    at addChunk (node:internal/streams/readable:368:12)
    at readableAddChunk (node:internal/streams/readable:341:9)
    at Readable.push (node:internal/streams/readable:278:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23) {
  severity_local: 'ERROR',
  severity: 'ERROR',
  code: '42P01',
  where: 'SQL statement "ALTER TABLE "album" ADD CONSTRAINT "album_artist_id_artist_id_fk" FOREIGN KEY ("artist_id") REFERENCES "public"."artist"("id") ON DELETE cascade ON UPDATE no action"\n' +
    'PL/pgSQL function inline_code_block line 2 at SQL statement',
  file: 'namespace.c',
  line: '427',
  routine: 'RangeVarGetRelidExtended'
}

Node.js v18.19.0

69 Replies

CyberCipher•3w ago

        "drizzle-orm": "^0.33.0",
        "drizzle-kit": "^0.24.0",

        "drizzle-orm": "^0.33.0",
        "drizzle-kit": "^0.24.0",

CyberCipher•3w ago

I found this, https://github.com/t3-oss/create-t3-turbo/issues/511 which said to remove s from table names, which I already tried but no luck.

GitHub

bug: drizzle adapter with postgres ends with `error: relation "t3tu...

Provide environment information System: OS: Windows 10 10.0.25300 CPU: (16) x64 AMD Ryzen 7 6800HS with Radeon Graphics Memory: 12.63 GB / 31.26 GB Binaries: Node: 20.5.1 - ~\AppData\Local\Volta\to...

CyberCipher•3w ago

I have also tried creating,

const schema = pgSchema("core");

const schema = pgSchema("core");

then replacing all pgTable to schema.table, and I get this error,

  npm run db:migrate

> app@0.0.1 db:migrate
> tsx src/lib/database/migrate.ts

{
  severity_local: 'NOTICE',
  severity: 'NOTICE',
  code: '42P06',
  message: 'schema "drizzle" already exists, skipping',
  file: 'schemacmds.c',
  line: '131',
  routine: 'CreateSchemaCommand'
}
{
  severity_local: 'NOTICE',
  severity: 'NOTICE',
  code: '42P07',
  message: 'relation "__drizzle_migrations" already exists, skipping',
  file: 'parse_utilcmd.c',
  line: '217',
  routine: 'transformCreateStmt'
}
node:internal/process/promises:288
            triggerUncaughtException(err, true /* fromPromise */);
            ^

PostgresError: schema "core" does not exist
    at ErrorResponse (file:///home/apoorv/repos/app/node_modules/postgres/src/connection.js:788:26)
    at handle (file:///home/apoorv/repos/app/node_modules/postgres/src/connection.js:474:6)
    at Socket.data (file:///home/apoorv/repos/app/node_modules/postgres/src/connection.js:315:9)
    at Socket.emit (node:events:517:28)
    at addChunk (node:internal/streams/readable:368:12)
    at readableAddChunk (node:internal/streams/readable:341:9)
    at Readable.push (node:internal/streams/readable:278:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23) {
  severity_local: 'ERROR',
  severity: 'ERROR',
  code: '3F000',
  where: `SQL statement "CREATE TYPE "core"."user_role" AS ENUM('ADMIN', 'USER')"\n` +
    'PL/pgSQL function inline_code_block line 2 at SQL statement',
  file: 'namespace.c',
  line: '3051',
  routine: 'get_namespace_oid'
}

  npm run db:migrate

> app@0.0.1 db:migrate
> tsx src/lib/database/migrate.ts

{
  severity_local: 'NOTICE',
  severity: 'NOTICE',
  code: '42P06',
  message: 'schema "drizzle" already exists, skipping',
  file: 'schemacmds.c',
  line: '131',
  routine: 'CreateSchemaCommand'
}
{
  severity_local: 'NOTICE',
  severity: 'NOTICE',
  code: '42P07',
  message: 'relation "__drizzle_migrations" already exists, skipping',
  file: 'parse_utilcmd.c',
  line: '217',
  routine: 'transformCreateStmt'
}
node:internal/process/promises:288
            triggerUncaughtException(err, true /* fromPromise */);
            ^

PostgresError: schema "core" does not exist
    at ErrorResponse (file:///home/apoorv/repos/app/node_modules/postgres/src/connection.js:788:26)
    at handle (file:///home/apoorv/repos/app/node_modules/postgres/src/connection.js:474:6)
    at Socket.data (file:///home/apoorv/repos/app/node_modules/postgres/src/connection.js:315:9)
    at Socket.emit (node:events:517:28)
    at addChunk (node:internal/streams/readable:368:12)
    at readableAddChunk (node:internal/streams/readable:341:9)
    at Readable.push (node:internal/streams/readable:278:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23) {
  severity_local: 'ERROR',
  severity: 'ERROR',
  code: '3F000',
  where: `SQL statement "CREATE TYPE "core"."user_role" AS ENUM('ADMIN', 'USER')"\n` +
    'PL/pgSQL function inline_code_block line 2 at SQL statement',
  file: 'namespace.c',
  line: '3051',
  routine: 'get_namespace_oid'
}

Can anyone help with this?

Angelelz•3w ago

You are trying to apply a migration to a database that is not in the state the migration script expects You might have dropped tables or otherwise changed the structure manually?

CyberCipher•3w ago

I have tried deleting the migration directory and DB entirely and start from scratch multiple times still same error.

Angelelz•3w ago

If you deleted the migration directory, you should generate a new migration. You also have to make sure you drop the __drizzle_migrations table from the database as well Just so you know, you should not delete those files manually. There is a drop command when you want to start over

CyberCipher•3w ago

I use use docker for postgresql and I ran docker system prune -fa to delete the container, volume and all, and also delete the migrations directory. So its almost like nothing ever happened.. after this I have to generate the migration again and then run migrate. Yes, I am aware I am only doing this because ATM its not working.

Darren•3w ago

youre creating a schema... but are you also exporting it? if you dont export it like table defintions, it won't get created, thus you see the error youre getting

CyberCipher•3w ago

What do you mean by exporting? these are the 2 commands I run,

        "db:generate": "drizzle-kit generate",
        "db:migrate": "tsx src/lib/database/migrate.ts",

        "db:generate": "drizzle-kit generate",
        "db:migrate": "tsx src/lib/database/migrate.ts",

here is my migrate.ts file,

import "dotenv/config";
import { drizzle } from 'drizzle-orm/postgres-js';
import { migrate } from 'drizzle-orm/postgres-js/migrator';
import postgres from 'postgres';

// for migrations
const migrationClient = postgres(process.env.DATABASE_URL as string, { max: 1 });

async function main() {
    await migrate(drizzle(migrationClient), {
        migrationsFolder: "./src/lib/database/migrations",
    })

    await migrationClient.end()
}

main()

import "dotenv/config";
import { drizzle } from 'drizzle-orm/postgres-js';
import { migrate } from 'drizzle-orm/postgres-js/migrator';
import postgres from 'postgres';

// for migrations
const migrationClient = postgres(process.env.DATABASE_URL as string, { max: 1 });

async function main() {
    await migrate(drizzle(migrationClient), {
        migrationsFolder: "./src/lib/database/migrations",
    })

    await migrationClient.end()
}

main()

Darren•3w ago

you wrote this line

const schema = pgSchema("core");

const schema = pgSchema("core");

then tried to add a table to it... you would need

export const schema = pgSchema("core");

export const schema = pgSchema("core");

CyberCipher•3w ago

ATM I am not using pgSchema I am using pgTable() But I can try using pgSchema again with export.

Darren•3w ago

in the last error log you posted that was the issue

CyberCipher•3w ago

Yes, I said I tried that, and that also causes error. OK,

export const schema =  pgSchema("core");

export const schema =  pgSchema("core");

works.. Why does directly using pgTable not work?

Darren•3w ago

it does... but you were using a new schema in the second example you posted.. which needed to be created

CyberCipher•3w ago

after every change I made I deleted the migrations directory and docker container hosting postgres DB. and ran generate and migrate after that..

Gaming

Programming

PostgresError: relation "public.artist" does not exist