Plain .sql Files

A project can keep its schema, or part of it, in plain .sql files of CREATE statements. chant build reads every .sql file in the source directory and turns each object into the declaration chant import would write for it, so a file and the tagged templates can sit side by side and reference each other.

Put the file in the source directory

-- src/users.sql
CREATE TABLE app.users (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text NOT NULL
);
COMMENT ON TABLE app.users IS 'People who can sign in';
CREATE INDEX users_email_idx ON app.users (lower(email));

src/app.ts

import { schema, view } from "@intentius/chant-lexicon-sql/postgres";

export const app = schema`CREATE SCHEMA app`;
export const activeUsers = view`CREATE VIEW app.active_users AS SELECT id, email FROM app.users`;

The build finds the file the way it finds .ts source: dist, git-ignored paths, child projects and the project’s exclude globs are left out. The dialect is the templates’ (here Postgres, from the schema tag). A project with no templates reads its files as sql.dialect, which defaults to ClickHouse, so a Postgres project of .sql files alone sets sql: { dialect: "postgres" } in chant.config.ts.

Each object gets an export name, as an import would name it: users and usersEmailIdx above, <name>Schema for a schema, <name>Db for a ClickHouse database. The names show up in the build output, in dependsOn, and in plan and lint messages. A file’s object whose export name a template already uses is a build error; rename the template’s export.

What a file can hold

Postgres reads each CREATE statement chant declares (schema, table, index, view, materialized view, sequence, enum type, domain, extension, function, procedure, trigger, policy, role), each GRANT and REVOKE, and each ALTER DEFAULT PRIVILEGES as one object. Three kinds of statement are folded into the object they finish:

• COMMENT ON joins the object it comments on, wherever in the file it is.
• ALTER TABLE ... ENABLE | DISABLE | FORCE ROW LEVEL SECURITY joins its table.
• ALTER TABLE <t> ADD [CONSTRAINT <c>] FOREIGN KEY | UNIQUE | PRIMARY KEY | CHECK | EXCLUDE ... becomes a table constraint in <t>’s CREATE TABLE. pg_dump and most ORMs print foreign keys this way.

BEGIN and COMMIT are skipped.

ClickHouse reads CREATE DATABASE, CREATE TABLE, CREATE VIEW, CREATE MATERIALIZED VIEW, CREATE DICTIONARY and CREATE FUNCTION, and the access statements CREATE USER, CREATE ROLE, CREATE ROW POLICY and GRANT (Access Control). Each access statement is read by its tag, so what the tag refuses is refused in its words: a password (IDENTIFIED BY), or a REVOKE.

Anything else fails the build: an INSERT, a DROP, a SET, a COMMENT ON for an object the file does not create, or a statement that does not parse. The error names the file and every statement it could not read, so the whole file is reported at once:

src/users.sql: a statement chant cannot read as declarations:
  - not a statement that declares an object: DROP TABLE app.old_users

SQL that is not schema

The build reads every .sql file under the directory it builds. chant build src builds src/. chant lifecycle diff, and the Plan phase of an ApplyOp, build sourceDir from chant.config.ts, or the project root when it is unset. A project that keeps other SQL beside its schema sets sourceDir, so those builds read the schema and nothing else:

chant.config.ts

export default {
  lexicons: ["sql"],
  sourceDir: "src",
  sql: { dialect: "postgres", profiles: { /* ... */ } },
};

Wherever the build walks, these files are left out:

• A file under a directory named migrations, at any depth: versioned migrations (migrations/<id>/migration.sql) and an ORM’s generated ones (prisma/migrations/...). They hold ALTER statements, and CREATEs of objects declared elsewhere.
• A file whose first lines carry -- chant-discovery-skip, for seed data or queries.
• A file an exclude glob in chant.config.ts matches.

Any other .sql file in the walk is read as schema. A DDL dump that another tool turns into declarations (an ORM’s schema, read into src/ by a generator) is a duplicate of those declarations, and the build fails naming both. Keep it outside sourceDir, or mark it.

References and order

A name in a file’s statement is a reference to another object, in a file or in a template, when:

• it is qualified, app.users, and an object has that schema and name;
• it is bare, where SQL names an object (after FROM, JOIN or REFERENCES, an index’s, trigger’s or policy’s table, a trigger’s function, a column’s or a domain’s type), and an object declared without a schema has that name.

The reference becomes an interpolation, as in the import’s output, so it carries into dependsOn and the build orders the objects by it. A template cannot import a file’s object, so it names it in its text; a qualified or bare name of a file’s object in a template’s statement counts as a reference the same way. Above, users comes after app, and activeUsers after users.

Two files’ objects that reference each other in a cycle are a build error, as two templates would be. An object declared in a file and again in a template (the same type and qualified name) is an error naming both.

Turn a file into templates

chant import <file>.sql --lexicon sql writes the file’s objects as one schema.ts of tagged templates, the same declarations the build reads from the file. The dialect is read off the statements: an ENGINE = clause, a CREATE DATABASE, a CREATE DICTIONARY, a CREATE ROW POLICY or a lambda CREATE FUNCTION f AS (x) -> ... is ClickHouse, and a statement only Postgres has (CREATE SCHEMA, CREATE INDEX, COMMENT ON, ALTER TABLE, GRANT) or a CREATE TABLE with no engine is Postgres. --parser-option dialect=postgres names it. A statement the import cannot read stops it, named, as on a build.

From code

The same reading is exported from the package root (and from @intentius/chant-lexicon-sql/sql-files), for a tool that turns DDL it gets elsewhere into declarations:

import { readSqlFile, sqlFileDeclarations, sqlFileEntities, SqlFileError } from "@intentius/chant-lexicon-sql";

// The objects, each with its export name, type, qualified name and statements.
const objects = readSqlFile("postgres", ddl, { origin: "prisma migrate diff" });

// The TypeScript chant import would write, with comment lines at the top.
const { content } = sqlFileDeclarations("postgres", ddl, { origin: "db/legacy.sql", schema: "app", header: "Generated from db/legacy.sql" });

// The declarations themselves, references to `known` entities interpolated.
const entities = sqlFileEntities("postgres", [{ origin: "db/legacy.sql", ddl }], { known });

schema qualifies the names the DDL leaves unqualified, as an ORM does when the schema comes from its connection: an object’s own name, an index’s, trigger’s or policy’s table, a foreign key’s table, a comment’s target and a column type naming an enum or domain the file creates. On ClickHouse it is the database for an object’s unqualified name. Each function throws a SqlFileError, whose problems list the statements it could not read.