{"id":10623,"date":"2019-02-11T23:24:00","date_gmt":"2019-02-12T04:24:00","guid":{"rendered":"https:\/\/qxf2.com\/blog\/?p=10623"},"modified":"2019-02-11T23:24:00","modified_gmt":"2019-02-12T04:24:00","slug":"generate-mysql-testdata-using-filldb","status":"publish","type":"post","link":"https:\/\/qxf2.com\/blog\/generate-mysql-testdata-using-filldb\/","title":{"rendered":"Generate MySQL test data using FillDB"},"content":{"rendered":"<p>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 <a href=\"http:\/\/filldb.info\/\">FillDB<\/a>.<\/p>\n<hr\/>\n<h3>Why this posts<\/h3>\n<p>I spent a good amount of time in figuring out a way to quickly generate random test data into a database. Using <a href=\"http:\/\/filldb.info\/\">FillDB<\/a>, 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.<\/p>\n<p>To follow along, I assume that the reader of the post is familiar with basic database concepts like database modeling and relational databases.<\/p>\n<hr\/>\n<h3>A brief about FillDB tool<\/h3>\n<p><a href=\"http:\/\/filldb.info\/\">FillDB <\/a> 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 <em>employees<\/em> and <em>departments<\/em><\/p>\n<p>Below are the few key features of FillDB:<\/p>\n<ul>\n<li>Generate Unique user data<\/li>\n<li>Texts, Paragraphs and sentences<\/li>\n<li>Date and Time values<\/li>\n<li>Locations information like countries, city,state<\/li>\n<li>Foreign key support<\/li>\n<li>Country and Langauge specific Names\/Addresses\/PhoneNumbers<\/li>\n<li> Generate image urls<\/li>\n<\/ul>\n<hr\/>\n<h3>How to use FillDB<\/h3>\n<p>I simply followed below steps to generate data and fill the MySQL database,<\/p>\n<p>\t1. Submit Database Schema<br \/>\n\t2. Generate Random Data<br \/>\n\t3. Export Database with generated data<\/p>\n<hr\/>\n<h3>Submit Database Schema<\/h3>\n<p>You can use an already existing schema or create a new schema. There are two ways of submitting an already existing Schema.<\/p>\n<li>You can Upload the schema from using File Submission option or<\/li>\n<li>You can simply paste the schema in the text area as shown below<\/li>\n<figure id=\"attachment_10651\" aria-describedby=\"caption-attachment-10651\" style=\"width: 912px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/qxf2.com\/blog\/wp-content\/uploads\/2019\/01\/upload-schema-1.jpg\" data-rel=\"lightbox-image-0\" data-rl_title=\"\" data-rl_caption=\"\" title=\"\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/qxf2.com\/blog\/wp-content\/uploads\/2019\/01\/upload-schema-1.jpg\" alt=\"\" width=\"912\" height=\"567\" class=\"size-full wp-image-10651\" srcset=\"https:\/\/qxf2.com\/blog\/wp-content\/uploads\/2019\/01\/upload-schema-1.jpg 912w, https:\/\/qxf2.com\/blog\/wp-content\/uploads\/2019\/01\/upload-schema-1-300x187.jpg 300w, https:\/\/qxf2.com\/blog\/wp-content\/uploads\/2019\/01\/upload-schema-1-768x477.jpg 768w\" sizes=\"auto, (max-width: 912px) 100vw, 912px\" \/><\/a><figcaption id=\"caption-attachment-10651\" class=\"wp-caption-text\">Upload a db schema<\/figcaption><\/figure>\n<p>If you do not have a schema, you can create a new one using FillDB &#8211; <a href=\"http:\/\/filldb.info\/dbgenerator\">DB Generator<\/a> and follow the instructions given in the page.<\/p>\n<hr\/>\n<h3>Generate Random Data<\/h3>\n<p>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.<\/p>\n<p>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.<br \/>\nii) Define the default parameter values for the column in the parameters columns.<br \/>\niii) You can use function foreignKey() from the dropdown list and indicate table name and table column as parameters.<br \/>\niv) Once you finish filling table details, you can define the no. of rows to be generated.<br \/>\nv) If you need country-specific Names\/Addresses\/PhoneNumbers, you can also choose the country and generate country-specific Names\/Addresses\/PhoneNumbers.<\/p>\n<figure id=\"attachment_10658\" aria-describedby=\"caption-attachment-10658\" style=\"width: 789px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/qxf2.com\/blog\/wp-content\/uploads\/2019\/01\/fill_data-3.jpg\" data-rel=\"lightbox-image-1\" data-rl_title=\"\" data-rl_caption=\"\" title=\"\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/qxf2.com\/blog\/wp-content\/uploads\/2019\/01\/fill_data-3.jpg\" alt=\"\" width=\"789\" height=\"580\" class=\"size-full wp-image-10658\" srcset=\"https:\/\/qxf2.com\/blog\/wp-content\/uploads\/2019\/01\/fill_data-3.jpg 789w, https:\/\/qxf2.com\/blog\/wp-content\/uploads\/2019\/01\/fill_data-3-300x221.jpg 300w, https:\/\/qxf2.com\/blog\/wp-content\/uploads\/2019\/01\/fill_data-3-768x565.jpg 768w\" sizes=\"auto, (max-width: 789px) 100vw, 789px\" \/><\/a><figcaption id=\"caption-attachment-10658\" class=\"wp-caption-text\">Fill tables with data<\/figcaption><\/figure>\n<p>Once you click on the Generate Data button, you can now see the generated data.<\/p>\n<figure id=\"attachment_10654\" aria-describedby=\"caption-attachment-10654\" style=\"width: 896px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/qxf2.com\/blog\/wp-content\/uploads\/2019\/01\/table-data-1.jpg\" data-rel=\"lightbox-image-2\" data-rl_title=\"\" data-rl_caption=\"\" title=\"\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/qxf2.com\/blog\/wp-content\/uploads\/2019\/01\/table-data-1.jpg\" alt=\"\" width=\"896\" height=\"429\" class=\"size-full wp-image-10654\" srcset=\"https:\/\/qxf2.com\/blog\/wp-content\/uploads\/2019\/01\/table-data-1.jpg 896w, https:\/\/qxf2.com\/blog\/wp-content\/uploads\/2019\/01\/table-data-1-300x144.jpg 300w, https:\/\/qxf2.com\/blog\/wp-content\/uploads\/2019\/01\/table-data-1-768x368.jpg 768w\" sizes=\"auto, (max-width: 896px) 100vw, 896px\" \/><\/a><figcaption id=\"caption-attachment-10654\" class=\"wp-caption-text\">Generated table data<\/figcaption><\/figure>\n<hr\/>\n<h3>Export Database with generated data<\/h3>\n<p>Now you have successfully created some random test data for testing your application, you can simply export this data into MySQL database. <\/p>\n<figure id=\"attachment_10655\" aria-describedby=\"caption-attachment-10655\" style=\"width: 893px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/qxf2.com\/blog\/wp-content\/uploads\/2019\/01\/export-1.jpg\" data-rel=\"lightbox-image-3\" data-rl_title=\"\" data-rl_caption=\"\" title=\"\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/qxf2.com\/blog\/wp-content\/uploads\/2019\/01\/export-1.jpg\" alt=\"\" width=\"893\" height=\"308\" class=\"size-full wp-image-10655\" srcset=\"https:\/\/qxf2.com\/blog\/wp-content\/uploads\/2019\/01\/export-1.jpg 893w, https:\/\/qxf2.com\/blog\/wp-content\/uploads\/2019\/01\/export-1-300x103.jpg 300w, https:\/\/qxf2.com\/blog\/wp-content\/uploads\/2019\/01\/export-1-768x265.jpg 768w\" sizes=\"auto, (max-width: 893px) 100vw, 893px\" \/><\/a><figcaption id=\"caption-attachment-10655\" class=\"wp-caption-text\">Export database<\/figcaption><\/figure>\n<p>When you Export Database, it automatically downloads a .sql file which you can use for filling the database. You can refer to this <a href=\"https:\/\/dev.mysql.com\/doc\/workbench\/en\/wb-admin-export-import-management.html\">link<\/a> to import a .sql file into the MySQL.<\/p>\n<hr\/>\n<p>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!<\/p>\n<hr\/>\n<h3>References<\/h3>\n<p>1. <a href=\"http:\/\/filldb.info\/\">FillDB<\/a><br \/>\n2. <a href=\"https:\/\/dev.mysql.com\/doc\/workbench\/en\/wb-admin-export-import-management.html\">Import .sql file into MySQL database<\/a><\/p>\n<hr\/>\n","protected":false},"excerpt":{"rendered":"<p>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 [&hellip;]<\/p>\n","protected":false},"author":16,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[186,127,184,185],"tags":[],"class_list":["post-10623","post","type-post","status-publish","format-standard","hentry","category-database","category-mysql","category-schemas","category-testdata"],"_links":{"self":[{"href":"https:\/\/qxf2.com\/blog\/wp-json\/wp\/v2\/posts\/10623","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/qxf2.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/qxf2.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/qxf2.com\/blog\/wp-json\/wp\/v2\/users\/16"}],"replies":[{"embeddable":true,"href":"https:\/\/qxf2.com\/blog\/wp-json\/wp\/v2\/comments?post=10623"}],"version-history":[{"count":26,"href":"https:\/\/qxf2.com\/blog\/wp-json\/wp\/v2\/posts\/10623\/revisions"}],"predecessor-version":[{"id":23003,"href":"https:\/\/qxf2.com\/blog\/wp-json\/wp\/v2\/posts\/10623\/revisions\/23003"}],"wp:attachment":[{"href":"https:\/\/qxf2.com\/blog\/wp-json\/wp\/v2\/media?parent=10623"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/qxf2.com\/blog\/wp-json\/wp\/v2\/categories?post=10623"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/qxf2.com\/blog\/wp-json\/wp\/v2\/tags?post=10623"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}