Create once, maintain over time, apply everywhere. DataTongs keeps environments aligned without hand-written scripts.
Now that you have your source and structure under source control and you've got your state based deployments in place it's time to manage your seed data in the same way. DataTongs will help you extract all or parts of your data and manage deployment via generated MERGE scripts using the data in a Json format. It connects to a SQL Server database and extracts data for the tables specified in the config file and will generate MERGE statements that insert missing rows and optionally update and delete based on the extracted data as well. You can define a filter clause in case you allow mixing user and system data in a single table and both the extraction and DELETE will honor that filter.
Simply point the application at a database with the correct seed data to capture, fill out the list of tables and the key columns for each, set the merge options you wish to generate and we'll handle generating the scripts for you. You can generate new scripts any time you need to update the seed data or manually update the script(s) if you find that simpler. Your application seed data will become part of your source control and state based updates.
appSettings.json
| Setting | Value |
|---|---|
Source:Server | The server to connect and extract from. |
Source:User | The user to authenticate with. Leave blank to use Windows authentication. |
Source:Password | The password to authenticate with. Leave blank to use Windows authentication. |
Source:Database | The database to extract from. |
ScriptPath | The path to write the generated scripts. |
ContentPath | The path to write the generated json files (*.tabledata). |
Tables | Key value pairs of schema.table and the list of columns to match. Normally the PK or another unique index. Leaving the key columns blank will cause DataTongs to attempt to assign them using the PK or simplest unique index defined on the table. |
TableFilters | Key value pairs of schema.table and the optional filter clause for selecting and/or deleting data. |
ShouldCast:OutputContentFiles | Indicates whether separate json files should be generated for each table. |
ShouldCast:OutputScripts | Indicates whether merge scripts should be generated for each table. |
ShouldCast:TokenizeScripts | Indicates whether the generated scripts should use a token instead of embedding the content data as json. |
ShouldCast:MergeUpdate | Indicates whether the generated MERGE statement should update modified values. Defaults to true. |
ShouldCast:MergeDelete | Indicates whether the generated MERGE statement should delete rows not provided. Defaults to true. |
Execute DataTongs from the command line and tongs will begin the casting process.
> DataTongsOnce you determine your deployment scheme you can add them to your repository and configure either a script folder or the data deployment section of each table to deploy your seed data.
See the DataTongs Walkthrough to help you get started with the tool.
Depending on your configuration the export will be .tabledata files containing the json content of each configured table and/or scripts to deploy those contents. The script can either embed the json content directly or provide a tokenized script that will allow you to manage the json separately from the scripts to deploy it. If you select tokenized scripts you will need to configure each output file as a file token within you template with the expected token name.
If you export content files, you can optionally configure the .tabledata content to be deployed automatically for each table without needing to manage the scripts or script tokens yourself. You simply set a handful of properties on the table to indicate what sort of merge and where to find the content file and Quench can do the rest.
If configured the tool will also export a series of scripts containing either the table contents in json form or a token, and a MERGE statement generated to insert missing rows. Optionally an update and/or delete section will be included in the MERGE depending on your configuration. We have a special quench slot for the templates intended to deploy these scripts. It supports looping to assist with dependency management though it is certainly faster if you modify script names to order the deployment to account for those. You can define one or more folders with the TableData quench slot and the scripts within will be executed every release and will perform the looping as needed before reporting any scripts that can't be deployed successfully.
Each method has tradeoffs in simplicity vs control so you can choose the method that works best for you and even vary your deployment choice per table to handle special cases.
You can also use this tool to script out test data as well and then use the scripts outside of SchemaQuench to deploy test data as needed via your test pipeline. This can simplify the setup of integration test data and allow you to easily reset to a consistent starting state between test runs.