Skip to content

Generating staging tables

Turn your source entities into staging DDL

This is the smallest of the code generation examples: a single template that takes an entity and writes the CREATE TABLE statement for a staging version of it.

It is the right place to start if you have not used CrossGenerate before - there is one template, one scope, and the output is short enough to read at a glance.

How it works

CrossGenerate runs a template once per model object of a given scope. This template's scope is Entity, so selecting All Entities runs it once per entity and produces one file each - you never write a loop over your entities, you write what one table should look like.

Inside the template, the entity is available as an object: its name becomes the table name, and its attributes become columns, each carrying the datatype, length, precision and scale you modelled. So the DDL is not a transformation of SQL you already had - it is derived from the model, which is why re-running it after a model change is safe and boring.

That is the whole idea behind generating a staging layer this way: the layer stops being something you maintain by hand and becomes a projection of the model. If you add an attribute, the column appears on the next run. If you change a datatype, the column changes with it.

Get the workspace

On the welcome page, under Tutorials & Examples, choose:

examples → code-generation → staging → nunjucks (or handlebars)

Both variants generate the same DDL. Pick the template language you prefer.

What's in the workspace

On top of the BrightGreen models, the workspace adds:

Path Purpose
BrightGreen/Generation/Templates/entity/StagingTable.njk The main template. Scope: Entity
BrightGreen/Generation/Templates/entity/attributes/SqlColumnDefinition.njk An include that renders one column definition

The Handlebars variant has a single StagingTable.handlebars template and no separate include.

Why two templates?

Splitting the column definition into its own file keeps the main template readable, and lets you reuse the column logic in other templates. Nunjucks resolves includes relative to the directory of the main template - the same trick the documentation example uses more heavily.

Generate the DDL

  1. Open CrossGenerate from the right-hand bar, or from View → CrossGenerate. You can also use the command palette (Ctrl+Shift+P) and run CrossGenerate: Open Test View.
  2. Select an Entity - any entity under Sources/JuiceERP/ works well, for example Customer. Choose All Entities to generate the whole layer at once.
  3. Choose engine Nunjucks (or Handlebars for that variant).
  4. Select template StagingTable.njk.
  5. Set the output filename pattern, for example Stg_{{entity.id}}.sql.
  6. Press Generate.

The result is a CREATE TABLE statement with one column per attribute, typed from the attribute's datatype, length, precision and scale.

What to try next

  • Change the naming convention. Edit the output filename pattern, or the table name inside the template, to match your own standards.
  • Add a column. Staging layers often carry load metadata. Adding a LOAD_TIMESTAMP column to the template gives every generated table one - which is exactly the kind of house standard type definitions are designed to capture on the model side.
  • Move up a layer. Generating data warehouse views builds directly on this example and adds mappings.