Hi. I know this topic is not fully related to Directus but there are some old tables using int ids in my project that needs to be accessible by the Directus REST API. The issue is since these are int ids, these are not safe to expose to public pages and also from a SaaS point of view. I thought of adding a separate public id_uuid key that would store uuid but the issue is the form submission logic would still need the int id for making join operations. As there is no layer on top of directus where I can add some custom logic to use uuid instead, I have to migrate these int ids to uuid while preserving relations (m2m, m2o, o2m). Im looking for some tips on how to approach this. TIA.
1 Like
Hey @Sadman_Yasar_Sayem, thanks for following up with how you got it working locally, please keep us updated with how you get on!
I’ve been able to migrate the database using the following scripts (created with Gemini 3 Flash):
-
UUID V7 generator:
CREATE EXTENSION IF NOT EXISTS pgcrypto; CREATE OR REPLACE FUNCTION uuid_generate_v7() RETURNS uuid AS $$ DECLARE v_time timestamp with time zone := clock_timestamp(); v_gts bigint; v_v7 text; BEGIN v_gts := (EXTRACT(EPOCH FROM v_time) * 1000)::bigint; v_v7 := lpad(to_hex(v_gts), 12, ‘0’) || ‘7’ || lpad(to_hex((random() \* 4095)::int), 3, '0') || ‘8’ || lpad(to_hex((random() \* 1152921504606846975)::bigint), 15, '0'); RETURN v_v7::uuid; END; $$ LANGUAGE plpgsql VOLATILE; -
Ran the script that does the following for each table:
- Creates a mapping table `old_id → new_uuid`.
2. Drops foreign key constraints pointing to the table.
3. Converts ID and FK columns to `uuid`.
4. Populates the new UUIDs using the mapping table.
5. Re-establishes Primary Key constraints.
6. Sets the default value of the ID column to `uuid_generate_v7()`.
DO $$ DECLARE rec RECORD; fk_rec RECORD; target_table TEXT; mapping_table TEXT; BEGIN FOR rec IN SELECT table_name FROM information_schema.columns WHERE table_schema = ‘public’ AND column_name = ‘id’ AND data_type = ‘integer’ AND table_name NOT LIKE ‘directus_%’ AND table_name NOT IN (‘spatial_ref_sys’, ‘layer’, ‘topology’) LOOP target_table := rec.table_name; mapping_table := '\_migration_map\_' || target_table; RAISE NOTICE 'Migrating table: %', target_table; – Create temporary mapping table EXECUTE format(‘CREATE TABLE %I (old_id integer PRIMARY KEY, new_id uuid DEFAULT uuid_generate_v7())’, mapping_table); EXECUTE format(‘INSERT INTO %I (old_id) SELECT id FROM %I’, mapping_table, target_table); – Find all FKs pointing to this table FOR fk_rec IN SELECT tc.table_name AS from_table, kcu.column_name AS from_column, tc.constraint_name FROM information_schema.table_constraints AS tc JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name AND tc.table_schema = kcu.table_schema JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name AND ccu.table_schema = tc.table_schema WHERE tc.constraint_type = ‘FOREIGN KEY’ AND ccu.table_name = target_table AND ccu.column_name = ‘id’ LOOP RAISE NOTICE ' Updating FK in table % column %', fk_rec.from_table, fk_rec.from_column; – Drop the FK constraint EXECUTE format(‘ALTER TABLE %I DROP CONSTRAINT IF EXISTS %I’, fk_rec.from_table, fk_rec.constraint_name); – Add temp storage for old FK value EXECUTE format(‘ALTER TABLE %I ADD COLUMN _old_fk_storage_tmp integer’, fk_rec.from_table); EXECUTE format(‘UPDATE %I SET _old_fk_storage_tmp = %I’, fk_rec.from_table, fk_rec.from_column); – Change column type EXECUTE format(‘ALTER TABLE %I ALTER COLUMN %I DROP DEFAULT’, fk_rec.from_table, fk_rec.from_column); EXECUTE format(‘ALTER TABLE %I ALTER COLUMN %I DROP NOT NULL’, fk_rec.from_table, fk_rec.from_column); EXECUTE format(‘ALTER TABLE %I ALTER COLUMN %I TYPE uuid USING NULL’, fk_rec.from_table, fk_rec.from_column); – Update values from map EXECUTE format( ‘UPDATE %I t SET %I = m.new_id FROM %I m WHERE t._old_fk_storage_tmp = m.old_id’, fk_rec.from_table, fk_rec.from_column, mapping_table ); – Cleanup temp storage EXECUTE format(‘ALTER TABLE %I DROP COLUMN _old_fk_storage_tmp’, fk_rec.from_table); END LOOP; – DROP existing PK constraint DECLARE pk_name TEXT; BEGIN SELECT conname INTO pk_name FROM pg_constraint WHERE conrelid = (quote_ident(target_table))::regclass AND contype = ‘p’; IF pk_name IS NOT NULL THEN EXECUTE format(‘ALTER TABLE %I DROP CONSTRAINT IF EXISTS %I CASCADE’, target_table, pk_name); END IF; EXCEPTION WHEN OTHERS THEN RAISE NOTICE 'Could not drop PK for %', target_table; END; – Add temp storage for old ID EXECUTE format(‘ALTER TABLE %I ADD COLUMN _old_id_storage_tmp integer’, target_table); EXECUTE format(‘UPDATE %I SET _old_id_storage_tmp = id’, target_table); – Change ID column type EXECUTE format(‘ALTER TABLE %I ALTER COLUMN id DROP DEFAULT’, target_table); EXECUTE format(‘ALTER TABLE %I ALTER COLUMN id DROP NOT NULL’, target_table); EXECUTE format(‘ALTER TABLE %I ALTER COLUMN id TYPE uuid USING NULL’, target_table); – Update values from map EXECUTE format(‘UPDATE %I t SET id = m.new_id FROM %I m WHERE t._old_id_storage_tmp = m.old_id’, target_table, mapping_table); – Restore Primary Key EXECUTE format(‘ALTER TABLE %I ADD PRIMARY KEY (id)’, target_table); – Cleanup temp storage EXECUTE format(‘ALTER TABLE %I DROP COLUMN _old_id_storage_tmp’, target_table); – Set new default to uuidv7 EXECUTE format(‘ALTER TABLE %I ALTER COLUMN id SET DEFAULT uuid_generate_v7()’, target_table); – Cleanup map EXECUTE format(‘DROP TABLE %I’, mapping_table); END LOOP; END $$; -
Set “On Create” to “Do Nothing” for each id field to prevent uuidv4 generation
Run the following SQL to update the Directus fields metadata:
UPDATE directus_fields SET special = NULL WHERE field = ‘id’ AND collection NOT LIKE ‘directus_%’;
So far its working in my local setup but yet to test further.