As developers, we often need to seed our database tables for testing purposes, a practice commonly referred to as mocking data. One of the oldest methods (older than the universe itself) for seeding our database involves inserting one row at a time. Imagine the countless hours required if you had to create thousands of rows this way!
Fortunately, Mockaroo comes to our rescue! Available for free at mockaroo.com, Mockaroo allows you to generate up to 1,000 rows of realistic test data in CSV, JSON, SQL, and Excel formats. This site also offers a wide range of data types, from simple random strings to countries and more.
In this post, I will guide you through seeding a table in a Postgres database. I’ll cover the necessity, how to create mock data seeds, and how to insert the data into the database.
In this example, i need to seed my tables called customers with at least 1000 rows. The table consists of several columns:
The constraint of all the column is each row must not have a null value
Let’s head to mockaroo websites. This is the UI at the time of writing.
From there we can see that it already gives us some of the field name, type and options settings. Based on what I stated on 1. The Need, here is my setup to fullll my need.
If some of you are wondering what each line of option does, below are short explanations for each of them
will create a column named id with datatype UUID and no rows is null
will create a column named username with mockaroo’s custom datatype named Username and no rows is null
will create a column named email with mockaroo’s custom datatype named Email Address and no rows is null
will create a column named country_of_origin with mockaroo’s custom datatype named Country and no rows is null
will create a column named date_of_birth with mockaroo’s custom datatype named Datetime and no rows is null. On this line have an extra configuration stated in the image.
After all finished, don’t forget to set the mockaroo’s output format to SQL, and check the part ‘Include Create Table’. Also, customize your desired table name, I use ‘customers’ there.
You can now click the ‘Generate Data’ button colored green on the bottom of mockaroo’s website. It should gives you an SQL file ready to be downloaded to your local folder. My generated file can be downloaded here.
To quickly show what the output looks like, here is my file opened in VS Code
Finally, now we will insert the generated data to our postgres database. On my setup, the database server is hosted on my Ubuntu VPS and containerized using Docker. To create a new database, use this query.
postgres=# create database mockaroo_seed;
To seed the actual database, let’s utilize the psql command
psql -U postgres -h your.database.public.ip -p 5432 -d mockaroo_seed < ~/Downloads/customers.sql
After that, you may be prompted to input your password. When succeed, postgres will start executing our seeder file.
On success, we can now check the database by simply fetching 1 row and counting all rows.
Let’s run the check to see whether the seeder process succeed.
mockaroo_seed=# select * from customers limit 1;
id | username | email | country_of_origin | birth_date
--------------------------------------+-----------+---------------------------+-------------------+------------
d3dfcfa7-93f0-4895-9ada-6f35dbf6dff9 | kgamblin0 | jchaytor0@istockphoto.com | United Kingdom | 2020-01-05
(1 row)
mockaroo_seed=# select count(*) from customers ;
count
-------
1000
(1 row)
Voila! We now officially have a thousand row on our customers table. We can do a bunch of thing with this table. But that will be our next post!
I hope that this post is telling you new knowledge and don’t forget to comment what should be our topic! See you.
2 Comments
temp mailApril 6, 2024
Wonderful blog! I’d love to know your hosting provider—your site loads incredibly fast. If you have an affiliate link, please share it. I’d like my own site to run this smoothly!
temp mailApril 6, 2024
Hi! I noticed you visited my site, so I’m returning the favor. I’m looking for ideas to improve my own site, and I appreciate the inspiration. Hope it’s okay if I take some cues from here.