full-stack-todo / migrations /convert-user-id-to-text.sql
umarwaqar's picture
pushing code
bbeedce
Raw History Blame Contribute Delete
1.5 kB
-- Migration: Convert tasks.user_id from INTEGER to TEXT for Better Auth compatibility
-- Run this AFTER Better Auth tables are created and BEFORE creating new tasks
-- Step 1: Drop existing foreign key constraint if it exists
ALTER TABLE tasks DROP CONSTRAINT IF EXISTS tasks_user_id_fkey;
-- Step 2: Add a temporary column for the new TEXT user_id
ALTER TABLE tasks ADD COLUMN user_id_text TEXT;
-- Step 3: If you have existing tasks with INTEGER user_ids, you need to handle them
-- Option A: Delete all existing tasks (simplest for development)
-- TRUNCATE TABLE tasks;
-- Option B: Convert existing INTEGER user_ids to TEXT (if you want to keep data)
-- UPDATE tasks SET user_id_text = user_id::TEXT WHERE user_id IS NOT NULL;
-- Step 4: Drop the old INTEGER user_id column
ALTER TABLE tasks DROP COLUMN user_id;
-- Step 5: Rename the new column to user_id
ALTER TABLE tasks RENAME COLUMN user_id_text TO user_id;
-- Step 6: Make user_id NOT NULL (after ensuring all rows have values)
ALTER TABLE tasks ALTER COLUMN user_id SET NOT NULL;
-- Step 7: Add index on user_id for performance
CREATE INDEX IF NOT EXISTS idx_tasks_user_id ON tasks(user_id);
-- Step 8: Add foreign key constraint to Better Auth user table
ALTER TABLE tasks
ADD CONSTRAINT tasks_user_id_fkey
FOREIGN KEY (user_id) REFERENCES "user"(id) ON DELETE CASCADE;
-- Verify the migration
SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_name = 'tasks' AND column_name = 'user_id';