ts2sql

Generate

Generates files with SQL create queries out of provided typescript files.

Usage: ts2sql mysql --sql-out ../db/sql ./src/User.ts

Arguments

| Argument | Description | | ------------------------------- | ------------------------------------------------------------------ | | first argument, type | Databse type:mysql, postgres, hana, sqlite | | second argument, input, file, i | Typescript input file | | sql-out | SQL files output directory. Default ./sql | | ts-out | Typescript files output directory. Deaults to input file directory | | database, db | Schema or database name in SQL files | | version | Adds a comment with version to the top of the files | | skip-views | Skips generating views | | test | Test parameter skips execution |

Annotations

Table |Name|Description| |----|-----------| |name|Table name| |database|Database name| |charset|Default character set. Default utf8| |collate|Default collate. Default utf8_bin|

Column |Name|Description| |----|-----------| |name|Column name| |type|Column typeDefault: number -> INT(32); string -> VARCHAR(256); boolean -> INT(1); Date -> DATETIME| |primary|Generates PRIMARY KEY| |autoincrement|Generates AUTO_INCREMENT| |unique|Genarates UNIQUE| |key|Generates KEY| |null|Generates NULL. Optional [?] members also generate NULL. Default NOT NULL| |default|Generates DEFAULT 'value'| |serialize|Generates type BLOB and serializes to JSON| |virtual|Member will be ignored and is not reflected in the database table| |hide|Member is hidden in typescript and in select statement but is reflected in the database table| |value|Value for member in insert statement. Default ?|

Example

/**
 * @database test
 */
export interface User {
  /**
   * @primary
   * @autoincrement
   */
  id: number;
  /**
   * @unique
   */
  email: string;
  /**
   * @hide
   */
  password: string;
  /**
   * @null
   * @type DATE
   */
  birthday: Date;
  /**
   * @virtual
   */
  weekday: string;
  /**
   * @sertialize
   */
  roles: Role[];
  /**
   * @default NOW()
   * @value default
   */
  created: Date;
  active?: boolean;
}

creates

create_table_user.gen.sql

CREATE TABLE IF NOT EXISTS test.user (
    id INT(32) NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT 'ts id: number',
    email VARCHAR(256) NOT NULL UNIQUE COMMENT 'ts email: string',
    password VARCHAR(256) NOT NULL COMMENT 'ts password: string',
    birthday DATE NULL COMMENT 'ts birthday: Date',
    roles BLOB NOT NULL COMMENT 'ts roles: Role[]',
    created DATETIME NOT NULL DEFAULT NOW() COMMENT 'ts created: Date',
    active INT(1) NULL COMMENT 'ts active: boolean'
) DEFAULT CHARSET utf8 DEFAULT COLLATE utf8_bin;

UserDatabase.gen.ts;

import { User } from './User.ts';

export interface UserResult {
  id: number;
  email: string;
  birthday: string;
  roles: string;
  created: string;
  active: number;
}

export function transformUserResult(result: UserResult): User {
  return {
    id: result.id,
    email: result.email,
    birthday: new Date(result.birthday),
    weekday: null,
    roles: JSON.parse(result.roles),
    created: new Date(result.created),
    active: !!result.active,
  };
}

export type UserResultSet = UserResult[];

export type UserInsert = [number, string, string, Date, string, Date, boolean];

export const selectUserSql: string = 'SELECT id, email, birthday, roles, created, active FROM test.user';

export const insertUserSql: string =
  'INSERT INTO test.user (id, email, password, birthday, roles, created, active) VALUES (default, ?, ?, ?, ?, default, ?)';

Notice

• Member with type of a class are ignored and transformed to null
• Member with type of an enum are transformed as ENUM
• Member with type of an alias are transformed in their original type
• Member with multiple type are transformed as their first type
• Member with fixed types are transformed as ENUM
• Member with array types of interfaces are transformed into SQL views
• Member with other array types are ignored