Orchestrating Work Across Different Databases

When your data is spread across more than one kind of database, you almost always end up with a different set of tools and scripts for each of them. Each vendor has its own way of creating tables, loading files and copying data, and the scripts built around those tools have to be kept in step by hand. If you only ever work with one database you can live with that, and the vendor's own tools are usually fine. But once the same structure and the same data have to exist on more than one database, or a deployment has to move from one database to another, keeping those scripts in sync becomes a job in itself. Foundry is our command-line tool for orchestrating that work across databases. It builds database structures from definition files, imports and exports data, generates synthetic data through Alchemy, and runs those steps together as a single deployment. The commands behave the same way on any supported database and the files they read don't change between them, so moving a deployment to a different database means pointing it at a different connection.

How do you describe a database once and build it anywhere?

Foundry builds database structures from declarative definition files. You write down the tables, columns, keys, indexes and views you want to exist, rather than the statements that create them, and the column types are neutral ones like varchar, long and geography. When Foundry builds the definition on a particular database, it creates each object in that database's own form, so one file gives you the same structure on any supported database. Here's a definition for a small fleet, with depots that have a location and vehicles assigned to a depot.

<?xml version="1.0" encoding="utf-8"?>
<schema name="fleet" xmlns="http://www.yndigoblue.com/velocity">
    <tables>
        <table name="depot">
            <columns>
                <column type="integer" notnull="true">depot_id</column>
                <column type="varchar" size="100" notnull="true">name</column>
                <column type="geography" notnull="true">location</column>
            </columns>
            <constraints>
                <constraint type="primarykey">
                    <primarykey name="pk_depot">
                        <keycolumn>depot_id</keycolumn>
                    </primarykey>
                </constraint>
            </constraints>
            <spatial name="spi_depot_location" xmin="-180" ymin="-90" xmax="180" ymax="90"
                     smallGridSize="0.01" mediumGridSize="0.1" largeGridSize="1.0">
                <column>location</column>
            </spatial>
        </table>
        <table name="vehicle">
            <columns>
                <column type="integer" notnull="true">vehicle_id</column>
                <column type="integer" notnull="true">depot_id</column>
                <column type="varchar" size="17" notnull="true">vin</column>
                <column type="date">in_service</column>
            </columns>
            <constraints>
                <constraint type="primarykey">
                    <primarykey name="pk_vehicle">
                        <keycolumn>vehicle_id</keycolumn>
                    </primarykey>
                </constraint>
                <constraint type="foreignkey">
                    <foreignkey name="fk_vehicle_depot">
                        <sourcecolumns>
                            <sourcecolumn>depot_id</sourcecolumn>
                        </sourcecolumns>
                        <lookupschema>fleet</lookupschema>
                        <lookuptable>depot</lookuptable>
                        <lookupcolumns>
                            <lookupcolumn>depot_id</lookupcolumn>
                        </lookupcolumns>
                    </foreignkey>
                </constraint>
            </constraints>
            <indexes>
                <index name="idx_vehicle_vin">
                    <indexcolumn order="ascending">vin</indexcolumn>
                </index>
            </indexes>
        </table>
    </tables>
</schema>

To build it, you point Foundry at a database with a datasource file, which is a small JSON file holding the database type, the server and the credentials. Here's one for PostgreSQL.

{
  "type": "PostgreSQL",
  "hostname": "pg-reporting",
  "port": 5432,
  "database": "fleet",
  "username": "foundry",
  "password": "your-password-here"
}

A Teradata datasource looks the same apart from its type and port, and foundry build schema builds the fleet structure on either one from the same definition.

foundry build schema --datasource postgres.json --file schemas/fleet.xml
foundry build schema --datasource teradata.json --file schemas/fleet.xml

If you'd rather keep credentials in environment variables, for example in a pipeline, you can pass a connection string and database type with --conn and --db instead. The --drop option removes the existing objects before building, which suits environments you rebuild from scratch. Some constructs aren't available on all databases, such as certain check constraints, and --ignore lets the build skip the one object the target can't create and carry on with the rest.

You don't have to write definitions by hand. The foundry load schema command reads the schema of a live database and writes it out as a definition file, which gives an existing database a declared form you can keep in version control and build somewhere else. Definitions can be XML, JSON or YAML, and foundry definition schema convert turns one format into another. The foundry definition schema validate command checks whether a live database still matches its definition and reports the result as an exit code, so a pipeline can stop when a database has drifted from what was declared. The definition format and the schema commands are covered in the Foundry schema documentation.

How does data move in and out?

The foundry import table command loads a CSV file into a table that already exists. CSV files from different sources rarely agree on the details, so the import options let you deal with the differences, such as dates that aren't in ISO format, and large files can be streamed instead of read into memory first. If a file needs more settings than fit comfortably on a command line, you can keep them in an import configuration file and pass it with --import-config.

foundry import table --datasource postgres.json \
  --schema fleet --table depot --file data/depots.csv

Exporting works in the other direction. The foundry export data command writes a single table to a CSV file, and foundry export schema writes a whole schema to a folder, with a definition file for its structure and a CSV file for each table's data. Between them, export and import move data from one database product to another without needing any of the vendors' own tools. You can export a schema from SQL Server, build it on PostgreSQL from the exported definition and then import the tables from the exported files, and the data passes through ordinary files you can look at along the way.

foundry export schema --datasource sqlserver.json \
  --target fleet --out exports/ --format Xml

How does Foundry work with Alchemy?

Alchemy is our synthetic data API, and Foundry can call it directly with foundry generate dataset and foundry generate table. Both read a request from a payload file and save the result to disk. Foundry reads your API key from the ALCHEMY_API_KEY environment variable, or you can pass it with --api-key. Adding --estimate checks the payload and gives you the cost without generating anything, and estimates aren't billed. If you want to know more about what goes into a payload, How to Generate Synthetic Data covers Alchemy itself.

foundry generate dataset --endpoint https://alchemy.yndigoblue.com \
  --payload fleet-dataset.json --estimate

foundry generate dataset --endpoint https://alchemy.yndigoblue.com \
  --payload fleet-dataset.json --out fleet.csvx

The output of generate dataset is a .csvx package, which holds a CSV file for each generated table along with a manifest and a schema file. That schema file is in the same definition format Foundry builds from, including the primary keys and the foreign keys between the tables, so foundry import schema can load the whole package in one step.

foundry import schema --datasource postgres.json --file fleet.csvx --drop

The command builds the schema the package describes, so none of the tables need to exist beforehand, and then imports the tables in an order that satisfies the foreign keys. The manifest records the CSV settings the files were written with, so a package generated with a custom delimiter imports without any extra options. The --drop option replaces a schema that an earlier import created.

What about maintenance and inspection?

If you rebuild or reload environments, you'll need to clear them out first. The foundry empty schema command removes all of the objects in a schema but leaves the schema itself in place, and foundry empty table deletes the rows of a single table. The foundry drop commands remove individual objects, from a whole schema down to a single spatial index, and each one has to name its target explicitly so nothing gets removed by accident. They behave the same way on any supported database, which matters most in the parts of a deployment that run unattended. Their options are described under Drop and Empty.

Foundry can also read data back so you can check it. The foundry view command shows the rows of a table or view in the terminal, and it can page through long results. For spatial data, foundry spatial export writes a table's spatial column and its attributes to a GeoJSON file, and foundry spatial import loads GeoJSON into a table. The foundry spatial view command plots the points of a spatial column right in the terminal, which is a quick way to check that a load put the coordinates where you expected.

How is a whole deployment orchestrated?

Run scripts are how you turn individual commands into a deployment. Each one is a JSON file, usually named project.json, that lists steps in order, and each step holds one command with the same options you'd use on the command line. The foundry run command executes the steps in turn. Connection settings at the top of the script are inherited by the steps, and any step can override them with its own datasource, which is how one script works across two databases. Here's a script that rebuilds the fleet environment on PostgreSQL, loads the depots from a CSV file, checks the cost of an Alchemy dataset, and then generates and loads it.

{
  "$schema": "schemas/run-script.schema.json",
  "name": "Fleet environment",
  "datasource": "postgres.json",
  "steps": [
    {
      "name": "Build the fleet schema",
      "build-schema": { "file": "schemas/fleet.xml", "drop": true }
    },
    {
      "name": "Load the depots",
      "import-table": { "schema": "fleet", "table": "depot", "file": "data/depots.csv" }
    },
    {
      "name": "Check the payload and the balance",
      "generate-dataset": {
        "endpoint": "https://alchemy.yndigoblue.com",
        "payload": "fleet-dataset.json",
        "estimate": true
      }
    },
    {
      "name": "Generate the activity data",
      "generate-dataset": {
        "endpoint": "https://alchemy.yndigoblue.com",
        "payload": "fleet-dataset.json",
        "out": "fleet-activity.csvx"
      }
    },
    {
      "name": "Load the activity data",
      "import-schema": { "file": "fleet-activity.csvx", "drop": true }
    }
  ]
}

Running the same environment on Teradata means changing the datasource at the top of the script. The steps inherit the new connection, and the definition file, the CSV file and the Alchemy payload stay exactly as they are.

Run scripts can call other run scripts as steps, so a common sequence like clearing and reloading reference tables can be written once and shared between deployments. By default a run stops at the first step that fails. The continueOnError setting changes that for the whole script or for a single step, and a step that's allowed to fail is reported as tolerated in the run's summary instead of failing the run. The foundry run command exits with 0 when the run succeeds and 1 when it doesn't, so a CI/CD pipeline can run a deployment and act on the result without parsing any output. New projects start from foundry project create, which creates the folder, a starter project.json and, if you want one, a datasource file for the database type you choose. The run script format is described in Run and Project, and the rest of the commands are in the Foundry documentation.