Schemats Tutorial and Ultimate Guide For Beginners

Introduction: What is Schemats and Why Should You Care?

Schemats is a tool that helps you automatically generate TypeScript interface definitions from SQL database schema. It completely supports popular relational database management systems (RDBMS) like MySQL and PostgreSQL.

This Schemats Tutorial will help you get started and understand the basics of this amazing tool in no time.

Let’s say you have a database schema of the “Users” table something like this:

Name	Type
id	SERIAL
email	VARCHAR
password	VARCHAR
last_seen	TIMESTAMP

Now, Schemats can automatically generate the following TypesScript Interface for you.

interface Users {
    id: number;
    email: string;
    password: string;
    last_seen: Date;
}

---

How to Install Schemats?

The Schemats library is available on npm (Node Package Manager). So, you can easily install it by executing the below-mentioned command in your terminal.

npm install -g schemats

---

Generate TS Interface Definition from Schema

Let’s say you have a database named “company” with a table “users”. Now, to generate a TypeScript interface definition from your schema, simply run the below command.

For PostgreSQL, use this command:

schemats generate -c postgres://postgres@localhost/company -t users -o company.ts

For MySQL, use this command:

schemats generate -c mysql://mysql@localhost/company -t users -o company.ts

These commands will generate the typescript interfaces for the “company” database with table “users” inside a new file called “company.ts”.

---

Using schemats.json config file

Schemats is well equipped to read configuration options from a JSON config file. By default, this config file is known as “schemats.json”. So, if you don’t want to provide the command-line parameters as we did in our previous example, you can instead define the configurations inside the config file.

For example, add the following configuration options inside schemats.json. Note:- Don’t forget to place the schemats.json file in the current working directory.

{
    "conn": "postgres://postgres@localhost/company",
    "table": ["users"],
    "output": "company.ts"
}

Now, executing schemats generate here is the same as executing schemats generate -c postgres://postgres@localhost/company -t users -o company.ts

Here I’m listing all the available configuration options you could use inside the config file.

Option Name	Type
conn	string
table	string[] or string
schema	string
output	string
camelCase	boolean
noHeader	boolean

---

Write Code with Typed Schema

By the way, we can also import our output file (such as “company.ts“) directly inside the code. It allows us to write code with autocompletion and static type checks because the type definition for our database schema was already generated.

---

Real-world Usecase of Schemats

The below flow/chain is unavoidable if the UI needs to show information that the database schema currently doesn’t have.

• Change front-end components to show information to the user.
• That causes type errors in the API types that front and back share.
• Fixing those causes errors in the backend.
• (Rarely) Fixing those causes errors in the database schema.

A perfect solution is to use Schemats to extract TypeScript type definitions from the database schema on every migrate. There’s an unbroken static typing chain from the database columns, through the backend, through the API, to the Preact components rendering DOM nodes.

---

Schemats Alternatives

Zapatos

Zapatos is a great alternative for Schemats. It is a non-ORM database library that offers Zero-abstraction Postgres for TypeScript.

Just like Schemats, Zapatos is also a command-line tool that helps you communicate with the Postgres database and write the TypeScript schema.

Basically, the main objective of Zapatos is to make Postgres and TypeScript work together nicely. It also has built-in support for type safety.

Features of Zapatos:-

• Create a TypeScript schema for every database table.
• It enables you to write arbitrary SQL using tagged templates.
• Automatically typed CRUD queries.
• JOINs as nested JSON.
• Manage and retry transactions using transaction helper functions.

typeorm-model-generator

Another alternative for Schemats is typeorm-model-generator. It takes existing databases as input and generates models for TypeORM.

The supported databases by typeorm-model-generator are mentioned below.

• Microsoft SQL Server
• PostgreSQL
• MySQL
• MariaDB
• Oracle Database
• SQLite

---

Download Schemats

Schemats is an open-source library that is available under the MIT license. You can view/download its source code from Github or install it through npm.