npm package discovery and stats viewer.

Discover Tips

  • General search

    [free text search, go nuts!]

  • Package details

    pkg:[package-name]

  • User packages

    @[username]

Sponsor

Optimize Toolset

I’ve always been into building performant and accessible sites, but lately I’ve been taking it extremely seriously. So much so that I’ve been building a tool to help me optimize and monitor the sites that I build to make sure that I’m making an attempt to offer the best experience to those who visit them. If you’re into performant, accessible and SEO friendly sites, you might like it too! You can check it out at Optimize Toolset.

About

Hi, 👋, I’m Ryan Hefner  and I built this site for me, and you! The goal of this site was to provide an easy way for me to check the stats on my npm packages, both for prioritizing issues and updates, and to give me a little kick in the pants to keep up on stuff.

As I was building it, I realized that I was actually using the tool to build the tool, and figured I might as well put this out there and hopefully others will find it to be a fast and useful way to search and browse npm packages as I have.

If you’re interested in other things I’m working on, follow me on Twitter or check out the open source projects I’ve been publishing on GitHub.

I am also working on a Twitter bot for this site to tweet the most popular, newest, random packages from npm. Please follow that account now and it will start sending out packages soon–ish.

Open Software & Tools

This site wouldn’t be possible without the immense generosity and tireless efforts from the people who make contributions to the world and share their work via open source initiatives. Thank you 🙏

© 2024 – Pkg Stats / Ryan Hefner

@vramework/schemats

v1.0.8

Published

Generate typescript interface definitions from postgres SQL database schema

Downloads

1,181

Readme

Schemats

Before anything, I would like to give a massive thank you to sweetiq and their contributors for giving me a huge head start.

The reason I have created a new repo instead of a fork is because I don't support mysql and have some breaking changes due to how this library is consumed by postgres-typed and vramework.

I have kept the name and based off their MIT license as means of attribution and thanks.

Why Schemats

Because being able to make a change to your database structure and have it:

  • validate through your node backend APIs
  • get verified against automatically generate JSON schemas
  • raise errors in your frontend application

Is just a great developer experience in my opinion.

This allows us to some pretty amazing things when it comes to refactoring and maintaining codebases, and also provide the meta-data to help with libraries like postgres-typed.

Quickstart

Installing

yarn add -d @vramework/schemats || npm install -d @vramework/schemats

Generating the type definition from schema

Assuming you have the following schema (this is a bit of a random one):

CREATE SCHEMA "pet_store";

CREATE TYPE "pet_store"."animal" AS enum (
  'cat',
  'dog'
);

CREATE TABLE "pet_store"."user" (
  "uuid" uuid PRIMARY KEY default gen_random_uuid(),
  "name" text NOT NULL
);

CREATE TABLE "pet_store"."pet" (
  "uuid" uuid PRIMARY KEY default gen_random_uuid(),
  "owner" uuid REFERENCES "pet_store"."user",
  "type" pet_store.animal NOT NULL,
  "name" text NOT NULL,
  "birthdate" date,
  "last_seen_location" point,
  "random_facts" jsonb,
  "pet_search_document" tsvector
);
COMMENT ON COLUMN pet_store.pet.random_facts is '@type {RandomPetFacts}';

You can now generate a bunch of different schema definitions.

My personal favourite is the following:

schemats postgres postgres://postgres@localhost/database -f ./db-custom-types.ts -s pet_store -c -e -o db-types.ts

While will result in the following typescript file:


/**
 * AUTO-GENERATED FILE @ Fri, 27 Aug 2021 08:26:50 GMT - DO NOT EDIT!
 *
 * This file was automatically generated by schemats v.0.0.8
 * $ schemats generate postgres://username:password@localhost:5432/schemats -C -s pet_store
 *
 */

import { RandomPetFacts } from './db-custom-types'

export enum Animal {
	'Cat' = 'cat',
	'Dog' = 'dog' 
}

export interface User { 
	uuid: string
	name: string 
}

export interface Pet { 
	uuid: string
	owner?: string | null
	type: Animal
	name: string
	birthdate?: Date | null
	lastSeenLocation?: { x: number, y: number } | null
	randomFacts?: RandomPetFacts | null
	moreRandomFacts?: unknown | null 
	petSearchDocument?: string | null
}

export interface Tables {
    user: User,
	pet: Pet
}

export type CustomTypes = RandomPetFacts

But you have quite a bit of flexbility:

Usage: schemats mysql [options] [connection]

Generate a typescript schema from mysql

Arguments:
  connection                   The connection string to use, if left empty will use env variables

Options:
  -s, --schema <schema>        the schema to use (default: "public")
  -t, --tables <tables...>     the tables within the schema
  -f, --typesFile <typesFile>  the file where jsonb types can be imported from
  -c, --camelCase              use camel case for enums, table names, and column names
  -C, --camelCaseTypes         use camel case only for TS names - not modifying the column names
  -e, --enums                  use enums instead of types
  -o, --output <output>        where to save the generated file relative to the current working directory
  --no-header                  don't generate a header
  -h, --help                   display help for command
Generate a typescript schema from mysql

Arguments:
  connection                   The connection string to use, if left empty will use env variables

Options:
  -s, --schema <schema>        the schema to use (default: "public")
  -t, --tables <tables...>     the tables within the schema
  -f, --typesFile <typesFile>  the file where jsonb types can be imported from
  -c, --camelCase              use camel case for enums, table names, and column names
  -C, --camelCaseTypes         use camel case only for TS names - not modifying the column names
  -e, --enums                  use enums instead of types
  -o, --output <output>        where to save the generated file relative to the current working directory
  --no-header                  don't generate a header
  -h, --help                   display help for command

Features

Camel Case -c --camelCase, -C --camelCaseTypes

This automatically turns all your tables and Enums / Types and column names to camelcase, which is the default experience for javascript and is more consistent to use

You can use Camel Case Types to just camel case the TS entities - leaving the strings representing the SQL columns alone.

Enums -e --enums

Using enums turns all postgres enums into Enums instead of normal types, which is just a preference aspect for developers since renaming enum values or order will change the Enum key and value.

Types File -f --typesFile <typesFile>

This is a VERY useful feature for jsonb fields. Normally a jsonb field type is unknown, however if you provide a types json file this will get the type out of the comment of a field and assign it to the value.

The structure of a custom type file could either be from another file:

export type { RandomPetFacts }  from './somewhere-else'

or it could just be defined straight in the file.

export type RandomPetFacts = Record<string, string>

Tables | Custom Types -t --tables <tables...>

These types are automatically generated to power typed-postgres

Using in typescript

You can import all your interfaces / enums from the file:

import * as DB from './db-types'

// And then you can start picking how you want your APIs to be used:
type updatePetLocation = Pick<DB.Pet, 'lastSeenAt'>

Tests

So where are the tests? The original schemats library has an amazing 100% coverage and this one has 0.

To be honest, I'm using this library in a few of my current projects and any error in it throws dozens in the entire codebase, so it sort of tests itself. That being said I will be looking to add some in again, but in terms of priorties not my highest.

However for manual testing and experimenting you can easily replicate this project by:

# Clone the repo
git clone [email protected]:vramework/schemats.git
# Enter repo
cd schemats
# Install dependencies
yarn install
# Run the example, which will run create the schemats library and generate the db-types library
yarn run example:postgres