Phases for Data Migration

[tanishapatil1234]

Phases for Data Migration

Tanisha Patil
This guide aims to outline the phases for data migration from JSON endpoint to RDS using the SQLTools extension.

Current RDS schema is :

Phase 1 : Obtain new JSON data from API endpoint

{
"kasm_server_needed": false,
"name": "testUser",
"role": "User",
"sections": [
{
"abreviation": "CSP"
},
{
"abreviation": "CSA"
}
],
"uid": "testUser"
}

Phase 2: Connect to RDS using SQLTools

Installations

1. Navigate to extensions in VSCODE.
2. Install the following :
   
   

Using SQL Tools

1. Navigate to the SQL tools icon on the side bar
   

2. On the top banner, click 'Add New Connection' button
   

3. When prompted, select 'MySQL'
   

4. When prompted, fill out connection settings :
   

5. Test and then Save working Connection
   

   Now, under connections you should see the new connection just made:
   

6. Now, a .sql file named after the connection should appear in editor:
   

Phase 3: Empty data from RDS

1. Paste the SQL queries in this order (to avoid violating foreign key constraints) and click 'Run on active connection' to empty data from RDS :

DELETE FROM user_sections WHERE user_id IN (SELECT id FROM users);

DELETE FROM users;

Phase 4: Prompt ChatGPT for SQL query from new JSON data to insert endpoint data into RDS schema

1. Insert the users into the users table.

INSERT INTO users (_name, _uid, _role, kasm_server_needed, _password, _pfp, status)
VALUES
('Luna Iwazaki', 'lunaiwa', 'User', false, '', '', 1),
('Aliya Tang', 'aliyatang', 'User', false, '', '', 1),
('Tay Kim', 'TayKimmy', 'User', false, '', '', 1),
('Ethan Tran', 'realethantran', 'User', false, '', '', 1),
('Raymond Sheng', 'raymondYsheng', 'User', false, '', '', 1),
('Eunseo Lim', 'eunseolim123', 'User', false, '', '', 1),
('Jordan Pham', 'jntpham', 'User', false, '', '', 1),
('Aashray Reddy', 'KinetekEnergy', 'User', false, '', '', 1),
('Avanthika Daita', 'avanthikadaita', 'User', false, '', '', 1),
('Ryan Liu', 'RyanZLiu', 'User', false, '', '', 1),
('Dante Atanassov', 'DanteA-tech', 'User', false, '', '', 1),
('Aditya D', 'ad1tyad3sa1', 'User', false, '', '', 1),
('Saaras', 'Saaras859', 'User', false, '', '', 1),
('William Cheng', 'will-w-cheng', 'User', false, '', '', 1),
('Rayane Souissi', 'rayanesouuuu1234', 'User', false, '', '', 1),
('Timo Al-Faraje', 'TimoA35', 'User', false, '', '', 1),
('Jake Shim', 'jkaeshim', 'User', false, '', '', 1),
('Daniel Lee', 'daniel-s-lee', 'User', false, '', '', 1),
('Shubhay Choubey', 'Vynz123', 'User', false, '', '', 1),
('Cayden Shi', 'Deeskili', 'User', false, '', '', 1),
('Grayson Guyot', 'Delta760', 'User', false, '', '', 1),
('Lakshanya S', 'lakshusan', 'User', false, '', '', 1),
('Anthony Yoo', 'yooanthony', 'User', false, '', '', 1),
('Sri Vaidya S', 'srivaidyas', 'User', false, '', '', 1),
('Arushi Pandey', 'Arushi-maker', 'User', false, '', '', 1);

2. Insert the relationships into the user_sections table.
   Note, CSA has id 1 and CSP has id 2

  -- CSA has id 1 and CSP has id 2
  INSERT INTO user_sections (user_id, section_id)
  SELECT u.id, s.id
 FROM users u
 JOIN sections s ON s._abbreviation = 'CSA'
 WHERE u._uid IN ('lunaiwa', 'aliyatang', 'TayKimmy', 'realethantran', 'raymondYsheng');

 INSERT INTO user_sections (user_id, section_id)
 SELECT u.id, s.id
FROM users u
JOIN sections s ON s._abbreviation = 'CSP'
WHERE u._uid IN ('lunaiwa', 'aliyatang', 'TayKimmy', 'realethantran', 'eunseolim123', 'jntpham', 'KinetekEnergy', 
 'avanthikadaita', 'RyanZLiu', 'DanteA-tech', 'ad1tyad3sa1', 'Saaras859', 'will-w-cheng', 'rayanesouuuu1234', 'TimoA35', 
 'jkaeshim', 'daniel-s-lee', 'Vynz123', 'Deeskili', 'Delta760', 'lakshusan', 'yooanthony', 'srivaidyas', 'Arushi-maker'); </code>

Done! All data from endpoint has now been migrated into the RDS system.

[jm1021]

This article belongs on portfolio_2025, but it could have use in flask_2025 discussion on migrating and changing schema.