Populate MySQL Database with sample Data

How many times you find yourself creating records from scratch locally so you can get to a point in your program where you can actually debug what is going on?

If the whole team shares some sample data in a populateDB.sql script it could be run at any time by any developer.

The main problem we face is with foreign keys that we need to use but we don't know their ids.

Below is an unobtrusive MySQL script that creates a new employee referencing an existing office and a newly created department. Hopefully this is enough to get any data populated thanks to @variables and the last_insert_id() function.

You can pick any existing id:

SELECT @office_d:=id FROM office LIMIT 1;

You can insert a record and keep the id:

INSERT INTO `department` (name) VALUES ('Engineering');
SET @department_id = last_insert_id();

Then use the variables to insert the new record:

INSERT INTO `employee` (email, first_name, department_id, office_id) VALUES ('test@sample.com','Nestor', @department_id, @office_id);