DT
Drizzle Team•4mo ago
Reuben Abraham

push not detecting existing constraint and then failing with error

Hi! I just migrated to the latest versions of drizzle-orm (0.44.2) and drizzle-kit (0.31.1). I have a postgresql db. Most things with the migration seem to just work, I have a codebase-first approach and an existing drizzle folder with a history of snapshots. I swapped my commands to use drizzle-kit now, primarily drizzle-kit generate and drizzle-kit migrate. However, since my team has grown, I wanted to experiment with using drizzle-kit push to rapid prototype during development without actually generating files. To test, I first ran generate - nothing to change. Then I ran my old migrate.ts file - nothing to change. Then I tried running push - this yielded the following error: """ [✓] Pulling schema from database... · You're about to add unique_name_per_user unique constraint to the table, which contains 29 items. If this statement fails, you will receive an error from the database. Do you want to truncate topics table? """ That constraint is defined in my schema, is included in the snapshots, and already exists in my local DB (I inspected to confirm). Why would push be trying to add the constraint?
3 Replies
TOSL
TOSL•4mo ago
drizzle-kit push compares the introspected DB with your schema files, not your migration history. The diff engine believes the constraint is missing or different in your database compared to the schema. The simplest case is that Drizzle wants to generate the name for the constraint that doesn't match the existing name. In that case, you can give the constraint the correct name already in the live DB.
Reuben Abraham
Reuben AbrahamOP•4mo ago
I ran the following SQL query to introspect my DB: 
SELECT 
    tc.constraint_name,
    tc.constraint_type,
    string_agg(kcu.column_name, ', ' ORDER BY kcu.ordinal_position) as columns
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu 
    ON tc.constraint_name = kcu.constraint_name
WHERE tc.table_name = 'topics'
GROUP BY tc.constraint_name, tc.constraint_type
ORDER BY tc.constraint_type, tc.constraint_name;
SELECT 
    tc.constraint_name,
    tc.constraint_type,
    string_agg(kcu.column_name, ', ' ORDER BY kcu.ordinal_position) as columns
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu 
    ON tc.constraint_name = kcu.constraint_name
WHERE tc.table_name = 'topics'
GROUP BY tc.constraint_name, tc.constraint_type
ORDER BY tc.constraint_type, tc.constraint_name;
The result was: """ topics_user_id_users_id_fk | FOREIGN KEY | user_id topics_pkey | PRIMARY KEY | id unique_name_per_user | UNIQUE | user_id, name """ As you can see there is a unique_name_per_user constraint already in it - same name. Are you sure it's a problem with my db and migration history? Also, in the schema, it's defined on the pgTable('topics') as follows: 
uniqueNamePerUser: unique('unique_name_per_user').on(t.userId, t.name),
uniqueNamePerUser: unique('unique_name_per_user').on(t.userId, t.name),
@moderator wondering if I could get some help here
Mario564
Mario564•4mo ago
In dev, you can make a custom reset script and then run the push command The reset can be done by dropping and recreating the public and drizzle schemas

Did you find this page helpful?