Generate MySQL test data using FillDB

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.

  • You can Upload the schema from using File Submission option or
  • You can simply paste the schema in the text area as shown below
  • Upload a db 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.

    Fill tables with data

    Once you click on the Generate Data button, you can now see the generated data.

    Generated table 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.

    Export 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!


    1. FillDB
    2. Import .sql file into MySQL database

    10 thoughts on “Generate MySQL test data using FillDB

    1. It has limit of creating data for 100K records only. Is there any other tool which can create data for 50lacs record.

        1. Hi,

          In the examples, we have shown where some column takes integers. If those examples didn’t help you Can you tell me what kind of dummy data you expect or want to use it?

    2. how would I generate data for two elements clock_in, and clock_out using DATETIME and offset clock_out to only be 0 years, 0 months, 0 days, 8 hours, ? minutes, ? seconds.

      this way the clock_in would be a random DATETIME(‘2020-04-26 05:02:34’) and clock_out under the same row would be DATETIME(‘2020-04-26 13:57:43′) then repeated with this example throughout all generated ’employee_clocks’ table.

      1. Hi,
        Looks like FillDB does not support a JSON field, I was not able to generate synthetic data for a JSON field too.
        I have contacted FillDB to confirm if this is indeed a limitation.

    Leave a Reply

    Your email address will not be published. Required fields are marked *