emilioastarita/typed-rows

Extract metadata from mysql tables to produce typescript interfaces for typed rows results.

typed-rows

typed-rows is a little command-line tool to generate type information for mysql tables in form of Typescript Interfaces.

Motivation

Typescript language provides great safety checks and tooling for javascript. If you query the database the rows that you get using the default node-mysql query function are a collection of the any object type.

    connection.query('SELECT * FROM Users;', (err, rows) => {
        rows[0].id; // No type information
    });

You can manually provide the type information for the type of result that you are expecting using an interface:

    interface IUsers {
        id : number;
        name : string;
    }
    connection.query('SELECT * FROM Users;', (err, rows : IUsers[]) => {
        rows[0].id; // Now you have type information!
    });

That works but if you have a big database with a lot of tables and columns could be very tedious to write (and maintain) all these interfaces. So you can use typed-rows to automatically generate a TyepedRows.ts file with all the interfaces generated automatically from your database.

    $ typed-rows --database=phpmyadmin --user=root --outfile=TypedRows.ts
    Password:
    $ cat TypedRows.ts
    export interface IPmaTracking { 
            db_name : string;
            table_name : string;
            version : number;
            date_created : Date;
            date_updated : Date;
            schema_snapshot : string;
            schema_sql? : string;
            data_sql? : string;
            tracking? : any;
            tracking_active : number;
    }
    
    export interface IPmaUserconfig { 
            username : string;
            timevalue : any;
            config_data : string;
    }
    
    export interface IPmaUsergroups { 
            usergroup : string;
            tab : string;
            allowed : 'Y' | 'N';
    }
    
    export interface IPmaUsers { 
            username : string;
            usergroup : string;
    }
    //...

Now you can import this file and use the interfaces to have typed rows.

    import {IPmaUsergroups} from "./TypedRows";
    connection.query('SELECT * FROM pma__usergroups;', (err, rows : IPmaUsergroups[]) => {
        rows[0].allowed = 'Y';
    });

Helpers

If you are using typed-rows could be nice to have a generic function like:

  export interface FunDb<T> {
    (err: mysql.IError, rows: T[]): void;
  }

  function typedRows<T>(q, replaces?, callback? : FunDb<T>) {
     return conn.query(q, replaces, (err, result : T[]) => {
       if (err) {
         console.log('Error Query', q,  err);
       }
       if (callback) {
         callback(err, result);
       }
     });
  }

Then you can use typedRows function with a TypedRows interface:

    import {IPmaUsergroups} from "./TypedRows";
    typedRows<IPmaUsergroups>('SELECT * FROM pma__usergroups;', callback);    

Features

• Supported mysql fields: text, varchar, int, integer, smalint, tinyint, mediumint, decimal, numeric, double, bigint, float, decimal, enum, datetime.
• If a mysql data type is not recognized the column would be any.
• The generated interfaces are compatible with the automatic typecasts do it by node-mysql. Eg. a mysql datetime type would be mapped to a Date class in the typed-rows generated interface.

Installation & usage

npm installation:

  $ npm install -g typed-rows

The installation should give you a global typed-rows command, which you can use directly to extract type information from a mysql database and write the interfaces to the standard output.

  Usage: typed-rows [options]

  Options:

    -h, --help                 output usage information
    -V, --version              output the version number
    -u, --user [user]          Mysql user - Default root
    -p, --password [password]  mysql password
    -d, --database <database>  database name
    -h, --host [host]          database host - Default 127.0.0.1
    -p, --port [port]          database port - Default 3306
    -o, --outfile <outfile>    Out file - Default ./TypedRows.ts

Tests

To run tests you must create an empty database and configure a test/config.json based on test/config_template.json.

{
  "dbTest": {
    "user": "root",
    "password": "",
    "database": "typed_rows_test",
    "host": "127.0.0.1"
  }
}

Then you simply run:

npm test

TODO

• More databases support (postgresql, sqlite).
• More datatypes support.
• More testing.
• Test windows & mac support.