search

drizzle-team / drizzle-orm

Headless TypeScript ORM with a head. Runs on Node, Bun and Deno. Lives on the Edge and yes, it's a JavaScript ORM too 😅
https://orm.drizzle.team
Apache License 2.0
24.47k stars 642 forks source link

error: cannot drop sequence drizzle.__drizzle_migrations_id_seq because other objects depend on it #3320

Open JohnCido opened 4 months ago

JohnCido commented 4 months ago 
[✓] Pulling schema from database...

 Warning  You are about to execute current statements:

DROP SEQUENCE "drizzle"."__drizzle_migrations_id_seq";

error: cannot drop sequence drizzle.__drizzle_migrations_id_seq because other objects depend on it
    at .../node_modules/drizzle-kit/bin.cjs:79476:15
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async Object.query (.../node_modules/drizzle-kit/bin.cjs:121418:26)
    at async pgPush (.../node_modules/drizzle-kit/bin.cjs:124361:13)
    at async _Command.<anonymous> (.../node_modules/drizzle-kit/bin.cjs:131618:7) {
  length: 343,
  severity: 'ERROR',
  code: '2BP01',
  detail: 'default value for column id of table drizzle.__drizzle_migrations depends on sequence drizzle.__drizzle_migrations_id_seq',
  hint: 'Use DROP ... CASCADE to drop the dependent objects too.',
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'dependency.c',
  line: '1202',
  routine: 'reportDependentObjects'
}
JohnCido commented 4 months ago

Using latest drizzle-kit

abhishek-butola commented 4 months ago

Having same issue. It works on initial db:push. After making a change repush gives this error

works fine on 0.22.7

rainspelldev commented 4 months ago

Same here.

I'm using a Supabase database and whenever I run drizzle-kit push it tries to drop the following sequences and then fails with a similar error message:

DROP SEQUENCE "auth"."refresh_tokens_id_seq";
DROP SEQUENCE "pgsodium"."key_key_id_seq";
DROP SEQUENCE "graphql"."seq_schema_version";
DROP SEQUENCE "drizzle"."__drizzle_migrations_id_seq";
DROP SEQUENCE "realtime"."subscription_id_seq";
DROP SEQUENCE "realtime"."messages_id_seq";
error: cannot drop sequence auth.refresh_tokens_id_seq because other objects depend on it

This did not happen with version 0.22.7.

MrOxMasTer commented 3 months ago

I use postgresql. The same mistake.

First there was an error with ssl, which I fixed in the configuration:

dbCredentials: {
    ...
    ssl: false,
  },

Then this error occurred drizzle-kit v0.23.0 I used drizzle-up - it doesn't help

MrOxMasTer commented 3 months ago

I just deleted the id_seq and I managed to make a push 😂 Thank you for this pet Project. But after such moments, I realized that drizzle is not yet ready to be used in production practice

kimdanielarthur-cowlabs commented 3 months ago

Also get the same error as OP

AirZona commented 3 months ago

same issue, works fine with v0.22.8

and get the error: default value for column id of table drizzle.__drizzle_migrations depends on sequence drizzle.__drizzle_migrations_id_seq with version 0.23.0

Thanks!

martinamps commented 3 months ago

+1

jonmarada commented 3 months ago

Encountering the same issue with PostgreSQL on Neon had to go back to version v0.22.8, seems to work fine from there.

krunalmiracle commented 3 months ago

Same error "DROP SEQUENCE \"pgsodium\".\"key_key_id_seq\";" On postgresql log in supabase, gives the following error 

Event message
must be owner of sequence key_key_id_seq
Severity
ERROR

Timestamp
2024-07-22T13:29:11.332Z

Postgres Username
postgres

Session ID
669e5ea4.93da

Metadata
{
  "file": null,
  "host": "db-host hidden text",
  "metadata": [],
  "parsed": [
    {
      "application_name": "postgres.js",
      "backend_type": "client backend",
      "command_tag": "DROP SEQUENCE",
      "connection_from": "Hidden-ipv6",
      "context": null,
      "database_name": "postgres",
      "detail": null,
      "error_severity": "ERROR",
      "hint": null,
      "internal_query": null,
      "internal_query_pos": null,
      "leader_pid": null,
      "location": null,
      "process_id": 37850,
      "query": "DROP SEQUENCE \"pgsodium\".\"key_key_id_seq\";",
      "query_id": query id hidden,
      "query_pos": null,
      "session_id": "hidden session id",
      "session_line_num": 4,
      "session_start_time": "2024-07-22 13:29:08 UTC",
      "sql_state_code": "42501",
      "timestamp": "2024-07-22 13:29:11.332 UTC",
      "transaction_id": 0,
      "user_name": "postgres",
      "virtual_transaction_id": "8/4726"
    }
  ],
  "parsed_from": null,
  "project": null,
  "source_type": null

Though already modified the drizzle config to ignore the supabase internal schemas.

    dialect: 'postgresql',
    schema: './src/lib/server/schemas/schemas.ts',
    out: './drizzle',
    dbCredentials: {
        url: connectionString
    },
    // Added this line to exclude pgsodium and other Supabase-managed schemas and only use "public" schema
    schemaFilter: ["public"],
    // Explicitly exclude Supabase's internal schemas
    tablesFilter: ['!pgsodium.*', '!auth.*', '!storage.*', '!graphql.*', '!graphql_public.*', '!realtime.*', '!supabase_functions.*'],
});
T04435 commented 3 months ago

Not a solution for PROD envs, but...

I had this error while setting up the project and I'm on yolo push mode.

DO NOT USE IN PROD

"drizzle-kit": "^0.23.0",

Still looking for a proper solution...

AndriiSherman commented 3 months ago

Hey, sorry everyone, I just noticed this issue. I'm working on fixing it now. It seems like drizzle-kit is not filtering some sequences that should not be used in the push process. I'll try to make this fix as soon as possible.

By the way, on July 30th, drizzle-kit will be open-sourced, so I hope you can find solutions for such problems faster

Naeem-gg commented 3 months ago

Hey, sorry everyone, I just noticed this issue. I'm working on fixing it now. It seems like drizzle-kit is not filtering some sequences that should not be used in the push process. I'll try to make this fix as soon as possible.

By the way, on July 30th, drizzle-kit will be open-sourced, so I hope you can find solutions for such problems faster

Thank you @AndriiSherman

AndriiSherman commented 3 months ago

Please try the drizzle-kit@seq_fix version. If that fixes your problem, I'll include it in the latest release asap

I would also suggest using the verbose and strict flags to ensure the push is doing what's expected

skycoop commented 3 months ago

That test version seems to resolve the issue for me. Ran push before and after installing the version and could confirm that the error went away. Looking forward to it being released 😁

krunalmiracle commented 3 months ago

I have updated the drizzle-kit version to [0.23.0-eb2ca29](https://www.npmjs.com/package/drizzle-kit/v/0.23.0-eb2ca29) with package.json modified for drizzle-kit version "drizzle-kit": "^0.23.0-eb2ca29", But the output before pushing the migration changes still contain tables even with filter. Ran the following command bunx drizzle-kit generate --config=drizzle.config.ts; and afterwards drizzle-kit push --config=drizzle.config.ts but the output migration schema for supabase postgresql db is the same. 

...
CREATE INDEX IF NOT EXISTS "oauthUserIdx" ON "oauth_accounts" USING btree ("user_id");
DROP SEQUENCE "pgsodium"."key_key_id_seq";
DROP SEQUENCE "auth"."refresh_tokens_id_seq";
DROP SEQUENCE "realtime"."subscription_id_seq";
DROP SEQUENCE "realtime"."messages_id_seq";
DROP SEQUENCE "graphql"."seq_schema_version";
DROP SEQUENCE "cron"."jobid_seq";
DROP SEQUENCE "cron"."runid_seq";

❯ No, abort
  Yes, I want to execute all statements

Because the drizzle.config is still the same 

import { defineConfig } from 'drizzle-kit';
import * as dotenv from 'dotenv';
dotenv.config();
const connectionString = process.env.DATABASE_URL;
if (!connectionString) {
    throw Error('Postgres DATABASE_URL is not defined');
}
export default defineConfig({
    dialect: 'postgresql', // "mysql" | "sqlite"
    schema: './src/lib/server/schemas/schemas.ts',
    out: './drizzle',
    breakpoints: true,
    migrations: {
        table: 'custom_migrations_table',
        schema: 'public'
    },
    dbCredentials: {
        url: connectionString,
        ssl: 'prefer'
    },
    introspect: {
        casing: 'camel'
    },
    // Add this line to exclude pgsodium and other Supabase-managed schemas
    schemaFilter: ['public'],
    // Explicitly exclude Supabase's internal schemas
    // Explicitly exclude Supabase's internal schemas and sequences
    tablesFilter: [
        'pgsodium',
        'auth',
        'storage',
        'graphql',
        'graphql_public',
        'realtime',
        'supabase_functions',
        'cron'
    ],
    // Preserve the specified enums
    verbose: true,
    strict: true
});

Am I doing something wrong, or the filter is still not being applied for specific case of Supabase Postgres only?

AndriiSherman commented 3 months ago

I have updated the drizzle-kit version to [0.23.0-eb2ca29](https://www.npmjs.com/package/drizzle-kit/v/0.23.0-eb2ca29) with package.json modified for drizzle-kit version "drizzle-kit": "^0.23.0-eb2ca29", But the output before pushing the migration changes still contain tables even with filter. Ran the following command bunx drizzle-kit generate --config=drizzle.config.ts; and afterwards drizzle-kit push --config=drizzle.config.ts but the output migration schema for supabase postgresql db is the same.

...
CREATE INDEX IF NOT EXISTS "oauthUserIdx" ON "oauth_accounts" USING btree ("user_id");
DROP SEQUENCE "pgsodium"."key_key_id_seq";
DROP SEQUENCE "auth"."refresh_tokens_id_seq";
DROP SEQUENCE "realtime"."subscription_id_seq";
DROP SEQUENCE "realtime"."messages_id_seq";
DROP SEQUENCE "graphql"."seq_schema_version";
DROP SEQUENCE "cron"."jobid_seq";
DROP SEQUENCE "cron"."runid_seq";

❯ No, abort
  Yes, I want to execute all statements

Because the drizzle.config is still the same

import { defineConfig } from 'drizzle-kit';
import * as dotenv from 'dotenv';
dotenv.config();
const connectionString = process.env.DATABASE_URL;
if (!connectionString) {
  throw Error('Postgres DATABASE_URL is not defined');
}
export default defineConfig({
  dialect: 'postgresql', // "mysql" | "sqlite"
  schema: './src/lib/server/schemas/schemas.ts',
  out: './drizzle',
  breakpoints: true,
  migrations: {
      table: 'custom_migrations_table',
      schema: 'public'
  },
  dbCredentials: {
      url: connectionString,
      ssl: 'prefer'
  },
  introspect: {
      casing: 'camel'
  },
  // Add this line to exclude pgsodium and other Supabase-managed schemas
  schemaFilter: ['public'],
  // Explicitly exclude Supabase's internal schemas
  // Explicitly exclude Supabase's internal schemas and sequences
  tablesFilter: [
      'pgsodium',
      'auth',
      'storage',
      'graphql',
      'graphql_public',
      'realtime',
      'supabase_functions',
      'cron'
  ],
  // Preserve the specified enums
  verbose: true,
  strict: true
});

Am I doing something wrong, or the filter is still not being applied for specific case of Supabase Postgres only?

And when you run the drizzle-kit command, is the outputted CLI version for drizzle-kit also 0.23.0-eb2ca29?

krunalmiracle commented 3 months ago

Sorry for my oversight. There was version mismatch being installed by bun and the specified version in package.json. You were right, after fixing the version mismatch, it seems to have fixed all of the issues. Thank you for swift response! With this fixed version, no more issues in drizzle-push or generate to supabase. ❤️

AndriiSherman commented 3 months ago

@krunalmiracle awesome! thanks for checking!

AndriiSherman commented 3 months ago

released, should be fixed in drizzle-kit@0.23.1

Naeem-gg commented 3 months ago

released, should be fixed in drizzle-kit@0.23.1

Yayyyyyy!!!

NickBolles commented 3 months ago

Thank you @AndriiSherman ! I was getting PostgresError: must be owner of sequence subscription_id_seq and updated to the new version and it appears to have fixed it. 🎉

rhkdgns95 commented 3 months ago

I currently installed drizzle-kit@0.23.1, but an error is still occurring.

$ bun run drizzle-kit push --config=drizzle.config.ts

error: cannot drop sequence "USER_ID_seq" because other objects depend on it
    at /Users/khkim/Desktop/project/reading-app/node_modules/drizzle-kit/bin.cjs:75970:15
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async Object.query (/Users/khkim/Desktop/project/reading-app/node_modules/drizzle-kit/bin.cjs:117909:26)
    at async pgPush (/Users/khkim/Desktop/project/reading-app/node_modules/drizzle-kit/bin.cjs:120854:13)
    at async Object.handler (/Users/khkim/Desktop/project/reading-app/node_modules/drizzle-kit/bin.cjs:129148:9)
    at async rawCli (/Users/khkim/Desktop/project/reading-app/node_modules/drizzle-kit/bin.cjs:127543:5)
    at async run (/Users/khkim/Desktop/project/reading-app/node_modules/drizzle-kit/bin.cjs:127551:5) {
  length: 277,
  severity: 'ERROR',
  code: '2BP01',
  detail: 'default value for column ID of table "USER" depends on sequence "USER_ID_seq"',
  hint: 'Use DROP ... CASCADE to drop the dependent objects too.',
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'dependency.c',
  line: '1204',
  routine: 'reportDependentObjects'
}

However, when I downgraded to drizzle-kit: v0.22.8, it ran normally.

jeremya-pixulo commented 3 months ago

I currently installed drizzle-kit@0.23.1, but an error is still occurring.

I installed drizzle-kit@0.23.1, but I was still encountering an error. The issue was that the table prefix in pgTableCreator() didn't match the tablesFilter in drizzle.config.ts. After correcting this mismatch, everything worked without any errors.

qhkm commented 3 months ago

Still happening in 0.23.2. Reverting to version 0.22.8 works for me

krunalmiracle commented 3 months ago

Still happening in 0.23.2. Reverting to version 0.22.8 works for me

look at my previous comment. it works fine for me and no issue the new version. Explicitly add this configuration to drizzle.config.ts/js

        // Add this line to exclude pgsodium and other Supabase-managed schemas
    schemaFilter: ['public'],
    // Explicitly exclude Supabase's internal schemas and sequences
    tablesFilter: [
        'pgsodium',
        'auth',
        'storage',
        'graphql',
        'graphql_public',
        'realtime',
        'supabase_functions',
        'cron'
    ],
kyrregjerstad commented 2 months ago

Still happening in 0.23.2. Reverting to version 0.22.8 works for me

look at my previous comment. it works fine for me and no issue the new version. Explicitly add this configuration to drizzle.config.ts/js

        // Add this line to exclude pgsodium and other Supabase-managed schemas
  schemaFilter: ['public'],
  // Explicitly exclude Supabase's internal schemas and sequences
  tablesFilter: [
      'pgsodium',
      'auth',
      'storage',
      'graphql',
      'graphql_public',
      'realtime',
      'supabase_functions',
      'cron'
  ],

This does not work for me, reverting back to 0.22.8 solves it

raoufBen02 commented 2 months ago

same issue here i've clearly set tablesfilters to affect only rables that start with "rdshop*" but it's trying to drop tables from my other projects!!

No config path provided, using default 'drizzle.config.ts'
Reading config file '/home/rxf/Desktop/rd_shop/drizzle.config.ts'
Using 'pg' driver for database querying
[✓] Pulling schema from database...
error: cannot drop sequence "sands_n_safaris_contactUsMessage_id_seq" because other objects depend on it
export default defineConfig({
    schema: "./src/server/db/schema.ts",
    dialect: "postgresql",
    dbCredentials: {
        url: env.DATABASE_URL
    },
    tablesFilter: ["rd_shop_*"],
}) satisfies Config;

the config file is in "/home/rxf/Desktop/rd_shop/drizzle.config.ts" everything sould work just fine

i tried excluding other tables in my db in tablesfilters like this but it didn't help tablesFilter: ["rd_shop_*","!sands_n_safaris_*","!sns_*","!test_*"],

i had drizzle-kit version ^0.24.0 installed reverting to 0.23.1 didn't solve it reverting back to 0.22.8 solved it

chris-allen commented 2 months ago

@AndriiSherman Can this ticket be re-opened or should another ticket be created? It seems the tablesFilter option is being ignored when using ^0.24.2. Downgrading to 0.22.8 fixes the issue.

MGSimard commented 2 months ago

This is still an issue. I run a multi-project schema with "vertexdb" and "vertexblog" affixes. Now that I've started work on VertexBlog and initiated my "vertexblog" project schema it's actively trying to drop my primaryKey() sequence on my first project which uses affix "vertexdb".

PostgresError: cannot drop sequence "vertexdb_gameRssEntries_rss_id_seq" because other objects depend on it
    at ErrorResponse (file:///C:/X/vertexblog/node_modules/.pnpm/postgres@3.4.4/node_modules/postgres/src/connection.js:788:26)      
    at handle (file:///C:/X/vertexblog/node_modules/.pnpm/postgres@3.4.4/node_modules/postgres/src/connection.js:474:6)
    at TLSSocket.data (file:///C:/X/vertexblog/node_modules/.pnpm/postgres@3.4.4/node_modules/postgres/src/connection.js:315:9)      
    at TLSSocket.emit (node:events:520:28)
    at addChunk (node:internal/streams/readable:559:12)
    at readableAddChunkPushByteMode (node:internal/streams/readable:510:3)
    at Readable.push (node:internal/streams/readable:390:5)
    at TLSWrap.onStreamRead (node:internal/stream_base_commons:191:23)
    at TLSWrap.callbackTrampoline (node:internal/async_hooks:130:17) {
  severity_local: 'ERROR',
  severity: 'ERROR',
  code: '2BP01',
  detail: 'default value for column rss_id of table "vertexdb_gameRssEntries" depends on sequence "vertexdb_gameRssEntries_rss_id_seq"',
  hint: 'Use DROP ... CASCADE to drop the dependent objects too.',
  file: 'dependency.c',
  line: '1204',
  routine: 'reportDependentObjects'
}

The fact that this an issue at all is embarrassing, but the fact that it has remained an issue for months is simply hysterical and unacceptable - complete loss of trust in Drizzle when it comes to crucial database management.

chris-allen commented 1 month ago

Seems to be a duplicate of https://github.com/drizzle-team/drizzle-orm/issues/2701

emiliolopez95 commented 1 week ago

Still having this issue in drizzle-kit@0.25.0

SillyCoon commented 1 week ago

In my case I used the first capital letter in the table name (e.g. Exercise). After changing it to exercise, push doesn't try to drop the sequence anymore.