Table Schemas in TypeScript, Checked on Every Write

Intro

0:00 The schema of a table : TypeScript, written from the real table, kept in Git, and checked by the server on every write, whoever writes. In this video, the rules of the support tickets of Calm Home.

Rules, the usual way

0:14 Data rules usually live in an entity class, a DTO and a validator for each table, copied into every endpoint and script, and drifting away from the real table. With API Maker, one schema per table, written from the table, its rules run on every API and in your code, and the server compares it with the table. In this video : a generated schema, types, arrays, vectors and JSON, validations and conversions, keys the server refuses, a save refused and a save cleaned, and the schema against the table.

A generated schema

0:49 Instance API, MongoDB, catalog, products, and its schema. It was written from the documents themselves : a type for each field, required when every document has it, arrays of texts for the images and the tags, nested objects, specs and its dimensions, and an array of objects, the variants. category_id even points to the categories : the name was enough. Now PostgreSQL : the instance postgres, crm, support_tickets. Written from the table : the primary key, numbered by the database, the foreign key to customers, the lengths of the columns, and NOT NULL as required.

Keys the server refuses

1:33 A rule that sounds right, isUnique, the kind an AI assistant writes. Update : refused, by its name, with the list of the valid ones. A schema never keeps a rule that does nothing.

Validations and conversions

1:46 The real rules. subject : five characters or more, after a trim. status : one of three values, upper-cased first, OPEN when it is missing. priority the same way, NORMAL by default, and created_at stamped with the date when it is not given.

The schema against the table

2:04 Update. Compare with the database : on the left your schema, on the right the one written from the table as it is today. A column added or changed by somebody shows up here. Back to the editor, Update and Close. The list of tables says it too, before you open anything : when a table and its schema differ, its row carries a mark, and the tooltip lists what differs.

2:31 A click settles it, field by field : the schema follows the table, or the table follows the schema. Video fifty four shows both ways.

A save refused, a save cleaned

2:40 API Testing, PostgreSQL, crm, support_tickets, Save single-multiple. A bad ticket : four hundred, and one error per field : the subject is too short once trimmed, the status and the priority are off their lists. Nothing reached the table. A messy one : spaces around the subject, a priority in lower case, no status, no date. Two hundred and one : the subject trimmed, HIGH, OPEN, and created_at, stamped by the schema.

Good to know

3:15 The same rules run for your custom APIs, hooks and schedulers that write with g.sys.db. Five types : string, number, boolean, date and objectId, with nested objects and arrays on MongoDB. validatorFun and conversionFun take your own function for what the keys do not cover. And the schema is a file in Git : every change has its history.

Vectors, JSON and bytes

3:41 A SQL column can hold more than one value too. A vector, on all five SQL engines, is a list of numbers : its schema says an array of numbers. A JSON column takes objects and lists. Bytes travel as a Buffer object, and shapes as GeoJSON. Generated APIs, schema APIs and g.sys.db in all eight languages save, read and update them the same way.

What a schema is written from

4:09 A new schema opens with a second tab. Data Analysis : what the schema is written from. For each field, in how many documents it is, of which types, and whether its values repeat.

The history of a schema

4:23 A saved schema has its Schema history : the version live in the database, and each commit of its file in Git. A commit, and what it changed : here, the rules of the support tickets. The first one, without the rules, loaded into the editor, once you say yes : nothing is saved until Update. Revert would write a version into the database at once : video forty one shows it.

Built on standards

4:48 One place for the rules of a table, next to the table. Unknown keys and wrong types are refused, so a rule is never silently ignored, whoever typed it. And one error format, field by field, on every engine.

Recap

5:02 To sum up. A schema written from the table. Types, nested objects, arrays, vectors and JSON. Validations and conversions, checked on every write. Invented keys refused by name. The schema compared with the table. Next : data safety : encryption, hashing, ids and concurrency. API Maker. Build faster, spend smarter, deliver better.