Denorm

Denorm builds Postgres databases that materialize derived columns directly into tables. Denorm leverages the foreign key as the channel for denormalization: values can flow from parent to child, and aggregate up from child to parent.
Denorm also materializes derived columns within a table, which can depend upon other derived columns.

Denorm also expands the foreign key functionality to include:

• ranged (floor/ceiling) matches, not just for time series
• automatic creation of parent rows
• automatic creation of ranged sets of child rows

At run-time, database writes cost more. In exchange you get non-subvertible business logic, guaranteed termination, no derived-value races, and simpler reads and writes.

Read more at: The Denorm Tradeoff.

Status: 1.0. The syntax and feature set are stable and denorm is in daily use. Developed and tested on Linux, against Postgres 18.4. Details below.

The denorm DSL

A Denorm schema is a Postgres schema specified in the denorm language. Here is a simple example, docs/examples/00-example.dnm.

table us_states
  column state_code char(2) primary key
  column state_name varchar(20)
  column children_count integer count child_table

table child_table
  column child_id serial primary key

  fk us_states references us_states pushes child_table
  column state_code is us_states.state_code

  column state_name sync us_states.state_name

Once the database is built, reads are against plain tables, because the derived values are already in the rows:

-- state_name came from us_states when the row was written
select child_id, state_code, state_name from child_table;

-- children_count was maintained by the insert and delete of every child row
select state_code, state_name, children_count from us_states;

The complete language reference is at language-reference.md

Try It Out

Check a Schema and Generate DDL

Install:

npm install -g denorm

Fetch the schema shown above:

curl -o example.dnm \
  https://gitlab.com/kendowns/denorm/-/raw/main/docs/examples/00-example.dnm

If you have the repository cloned, it is docs/examples/00-example.dnm — copy that instead, and the rest of this section reads the same.

Check it. This needs no database and no credentials, and a clean schema prints nothing:

denorm check example.dnm

Now generate the build script. --from-scratch treats the live schema as empty and never connects to Postgres, so the database named by -d does not have to exist — the name is used for the roles denorm generates:

denorm -d example -s example.dnm --from-scratch -o example.sql

The build script goes to stdout, so -o is how you keep it in a file. Without -o it pipes:

denorm -d example -s example.dnm --from-scratch | psql -d example

Open example.sql. Its header records the denorm version, the schema it came from, and when it was generated. Below that is the entire DDL that produces the two tables, the roles, the triggers and trigger functions.

Denorm by default writes no other files. If desired, inspection artifacts can be generated by providing a --dump-dir <dir>. See the CLI Reference.

Create a Live Database

Set up the build user and .env as described in Production Postgres Setup, then drop --from-scratch:

denorm -d example -s example.dnm

Denorm creates the database if it does not exist and applies the migration in a single transaction. It also emits the same build script it applied, and that goes to stdout — expect the DDL to scroll past. Add -o example.sql to keep it in a file instead.

The build creates unprivileged example_denorm_app_user, a NOLOGIN role which can read and write to all tables, but cannot subvert derived values. For regular application access to the database, use LOGIN roles that are members of the example_denorm_app_user role.

CREATE ROLE example_app WITH LOGIN PASSWORD 'change-me';
GRANT example_denorm_app_user TO example_app;

Write some rows:

insert into us_states (state_code, state_name) values ('NY', 'New York');
insert into child_table (state_code) values ('NY');
insert into child_table (state_code) values ('NY');

and read them back:

-- children_count is 2, maintained by the two inserts above
select state_code, children_count from us_states;

-- state_name is 'New York' on both rows, pushed down as each was written
select child_id, state_code, state_name from child_table;

How Migrations Behave

Denorm builds are idempotent, cumulative, and additive only.

A build is safe to repeat. The schema file is the entire description of where the database should end up, so it adds in new tables and columns as needed.

Denorm never drops data on its own. Tables, columns, and views that are present in the database but absent from the schema become inert at runtime and are collected into a drop script for a person to read and run, and that is the only path by which they leave the database. What "inert" means concretely is that a build drops the triggers on them, which is the one thing denorm does take away without being asked: a table that has left the schema keeps every row it had, and loses its ability to write to a table that is still maintained. Pass --dump-dir out/ to write it as out/example.drops.sql. A build that finds such objects without a dump directory to write to reports the count on stderr, so they are never passed over in silence.

The CLI Reference covers the drop script and the other inspection artifacts.

Backing Up and Restoring

A denorm database is backed up with pg_dump, as any Postgres database is:

pg_dump -h localhost -U denorm_superuser -d example -Fc -f example.dump

Restoring takes one step more. The two NOLOGIN roles a build creates are cluster-global, so they are not in the dump, while the GRANT and ALTER ... OWNER TO statements that name them are. restore-roles.sql ships with denorm, recreates the roles from the database name, and runs before the archive:

createdb -h localhost -U denorm_superuser example
psql -h localhost -U denorm_superuser -d example \
  -f "$(npm root -g)/denorm/scripts/restore-roles.sql"
pg_restore -h localhost -U denorm_superuser -d example --exit-on-error example.dump

If you have the repository cloned, the script is scripts/restore-roles.sql and the rest of these commands read the same.

Backup and Restore covers restoring under a different database name, what a restore leaves behind when the roles are missing, and which parts of the cluster this procedure does not carry.

Editor Support

Experimental language support for .dnm files ships as a language server, denorm-lsp, with instructions for neovim, vim and for VSCodium / VS Code in the repository. It is the least tested part of denorm. Setup is in editors/.

Using an LLM to author Schemas

I have experienced good results using models to write schemas, where a prompt describing a schema change becomes a strong schema in a single turn, when providing only the Language Reference as context. I have been using Anthropic models, during the period Sept 2025 to this writing, August 2026.

In concrete terms, the agent can run denorm check schema.dnm with no database connection and get a report of errors with line numbers and positions, which can be checked against the language reference.

Beyond that concrete fact I can only offer conjecture as to why it works. First, the Denorm syntax is a small set of declarations over ordinary SQL types and constraints, which are well represented in the training data. Second, as every derived value travels through the foreign key, there is only one mechanism to generalize from.

Third, and I certainly hope this is true, the Denorm syntax follows a few rules for explicitness and consistency which I believe work well with the pattern-matching nature of an LLM.

Other Details

Full Language Reference

Production Postgres Setup

Application DB Access

Backup and Restore

CLI Reference

Editor Support

Potential Features

Hacking Denorm

Status

Release 1.0 is out. The condition set for it was that every claim and every instruction in this README and in docs/ be run against generated code, and that is what happened before it shipped.

The 1.0 feature set is stable in terms of syntax. The language for 1.0 expresses everything denorm does for 1.0.

Denorm is developed and tested on Linux, against Postgres 18.4. Other platforms and older Postgres majors are untested rather than unsupported — Production Postgres Setup says what that distinction is worth.

Denorm is actively maintained and in daily use. Within the stable foundation of 1.x I expect to keep adding tests, bug fixes, and optimizations.

Denorm uses Semver as follows:

• Major release: breaking change to syntax or to behavior of syntax
• Minor release: new large features
• Point release: bug fixes and small features

License

Denorm is Copyright (C) 2026 by Kenneth Downs, and is licensed under the Apache License, Version 2.0.

The schemas you write and the SQL denorm generates from them are your own work. Denorm claims no rights over its output, and nothing in this license reaches the databases you build with it.

History and Provenance

Denorm began in 2002 as Andromeda, which I wrote in PHP, and used daily from 2002-2009. After 2009 Donald Organ maintained Andromeda until about 2012, when the project wound down.

While that was the end of the PHP code, it was not the end of the approach. Denorm is a complete rewrite in TypeScript of an approach that ran in production for a decade — undertaken now because a parser, resolver, and code generator suddenly became a tractable amount of work for one developer.

I use denorm daily, and every database I have designed since 2002 has been built this way.