We have an application which needs to be tested with a bunch of test data in the MySQL database. For generating random data, we used to run scripts with INSERT statements to generate the dummy data or in some cases wrote stored procedures. We were looking out for a tool which can auto-populate a database with loads of test data based on a schema. As part of the Hackathon project in Qxf2, I took up this task and started exploring. It is when I came across a tool called FillDB.
Why this posts
I spent a good amount of time in figuring out a way to quickly generate random test data into a database. Using FillDB, you can achieve this by just following some simple steps. I thought writing about this tool will be useful for testers who are looking out for such tools. It saves a lot of time and testers can allocate their time in other critical tasks.
To follow along, I assume that the reader of the post is familiar with basic database concepts like database modeling and relational databases.
A brief about FillDB tool
FillDB is a free tool that allows you to generate large volumes of test data in MySQL format and populate databases with random data. Based on the column names and datatype selected it generates random dates, names, addresses. Simply upload the database schema, select tables, define type and format of the data to be generated like foreign key, date, text etc., for each column and it will generate unique test data in different relational tables. For the Hackathon project, I used a schema with two tables employees and departments
Below are the few key features of FillDB:
- Generate Unique user data
- Texts, Paragraphs and sentences
- Date and Time values
- Locations information like countries, city,state
- Foreign key support
- Country and Langauge specific Names/Addresses/PhoneNumbers
- Generate image urls
How to use FillDB
I simply followed below steps to generate data and fill the MySQL database,
1. Submit Database Schema
2. Generate Random Data
3. Export Database with generated data
Submit Database Schema
You can use an already existing schema or create a new schema. There are two ways of submitting an already existing Schema.
If you do not have a schema, you can create a new one using FillDB – DB Generator and follow the instructions given in the page.
Generate Random Data
Next step is to generate the random data. For this, select a table from Current Table drop down for which you want to fill the data and choose what data to be generated for every column. You can follow below steps.
i) Select the type of data for each column. For Eg:- the birthdate field which is of type date, you can choose a format of the date from the Generate dropdown list depending on your requirement.
ii) Define the default parameter values for the column in the parameters columns.
iii) You can use function foreignKey() from the dropdown list and indicate table name and table column as parameters.
iv) Once you finish filling table details, you can define the no. of rows to be generated.
v) If you need country-specific Names/Addresses/PhoneNumbers, you can also choose the country and generate country-specific Names/Addresses/PhoneNumbers.
Once you click on the Generate Data button, you can now see the generated data.
Export Database with generated data
Now you have successfully created some random test data for testing your application, you can simply export this data into MySQL database.
When you Export Database, it automatically downloads a .sql file which you can use for filling the database. You can refer to this link to import a .sql file into the MySQL.
In this way, I was able to create loads of test data into the database by simply uploading a schema. I hope this helps you too in creating custom data for your project needs. Happy DB filling!