Data Generator
The Data Generator fills database tables with random test data that looks real: names, email addresses, dates, numbers. Use it to try a new schema, to test an application, or to show a demo without real customer data. It fills child tables with keys that exist in the parent tables, so the foreign keys stay valid. The Data Generator needs DbSchema Pro.
Generate rows for a table
The Data Generator includes the tables of the diagram that is open. To generate data for other tables, open or create a diagram that holds them.
- Choose Database → Data Generator, or press Data in the toolbar and choose Data Generator.
- Select the tables you do not want to fill and press Exclude Tables.
- Double-click the Rows cell of a table and type how many rows to generate.
- Press Generate.
DbSchema inserts the rows in the database, and the Result column shows how many succeeded.
If a table already holds rows, DbSchema asks what to do with them:
- Leave Current Data adds the new rows to the existing ones.
- Drop Current Data deletes the rows of the tables in the list first. Never choose it on a database whose data you need.
To get the rows as a file instead of in the database, press Generate to File... and choose SQL or CSV.
Keep foreign keys valid
A foreign key is a column that points to a row in another table, such as orders.customer_id.
Its value must exist in the other table, or the database rejects the row.
- A foreign key column gets the generator
load_values_from_pk. It picks values from the primary key of the table it points to. - That table must have rows before the generator fills the child table. The list runs from top to bottom, so the parent tables have to come first.
- If they do not, press Reorder, or move a table with the up and down arrows. The hint at the bottom of the dialog says the same.
Choose how each column is generated
Each column has a generator: a pattern that says what values to make. DbSchema picks one from the column's name and data type, and you can change it.
- Select a table and press Edit Generators.
- Double-click a column, or select it and press Choose Generator.
- Pick a pattern, or type your own, and press Apply & Close.
- Null%: how many values stay empty. A red star marks a mandatory (not null) column, which cannot be empty.
- Seed: a number that makes the values repeat from one run to the next. Two columns with the same seed get the same values.
- Sample Output: a value that the pattern makes, so you can check it before you generate.
DbSchema keeps the generators in the model, so they come back when you open the model file again.
Pattern reference
Choose Generator opens the list of ready-made patterns, grouped by kind: Text, Number, Date, Personal, Location and more.
The main patterns:
| Pattern | What it makes |
|---|---|
int:from=0;to=100; | Whole numbers in a range. short:, long:, double: and float: work the same way. |
double:from=0.5;to=5.5;format=#.## | Decimal numbers with two digits after the point. |
sequence:from=1;step=1; | 1, 2, 3 and so on. |
identity: | Nothing: the database fills the column itself. |
skip | Nothing: the column is left out. |
boolean:percent_true=0.7; | true in 70% of the rows, false in the rest. |
date:from='01.01.2020';to='01.01.2024'; | Dates in a range. timestamp: works the same way, with a time. |
list:file=NamesFirst.txt | One line, picked at random, from a text file. |
load_values_from_pk | Values of the primary key that a foreign key points to. |
uuid: | A random UUID. |
ref:name=Integer | The pattern saved under that name in the list. |
groovy: followed by code | Whatever the Groovy code returns. |
Any other text is a reverse regular expression: DbSchema makes text that matches it.
For example, (Mr|Ms) [A-Z][a-z]{3,8} makes values such as Ms Kelora.
In a Groovy pattern, the values of the columns generated before it in the same row are variables.
generate('pattern') runs any other pattern:
groovy: income_cost + vat_cost
groovy: generate('(Anna|John|Cindy)') + ' ' + generate('(Smith|Popescu)')