dataType in define for create DDL

[jmc420]

I'm trying to generate SQL create DDL from a SQL define using this code:

const post = this.sql.define<{ id: number; title: string; body: string }>({
           name: 'post',
           columns: ['id', 'title', 'body']
       });

       try {
           let query = post.create().toQuery();

           console.log("Create Query " + query.text)

           return query;
       }
       catch (e) {
           console.log("ERROR " + e.message + " STACK " + e.stack)
       }

This generates an error: ERROR dataType missing for column id (CREATE TABLE and ADD COLUMN statements require a dataType).

I can get the define to work by changing the columns in the define to:

columns: [{name: 'id', dataType: "number"}, {name:'title', dataType: "string"}, {name:'body', dataType: "string"}]

This will generate the create DDL: "CREATE TABLE post (id number, title string, body string)"

What are the valid values for dataType and is there a way that they can be converted to the correct type for the database (e.g. string might be varchar(255)?

[hstanford]

The dataType values are injected directly into the query string (https://github.com/charsleysa/node-sql-ts/blob/master/lib/dialect/dialect.ts#L789), so the valid values are whatever your underlying database accepts. For example, with Postgres I might use dataType: 'TEXT'.

If this extension were to be supported I'd be worth getting @charsleysa 's input up front, as this has the potential to be a breaking change. Perhaps supporting a dataTypes export that exposes DataType objects so that the ColumnNode dataType?: string | DataType, so it's backwards compatible and supports overrides for unusual types. API could be:

import { dataTypes, Sql } from 'sql-ts';

const sql = new Sql('postgres');

const post = sql.define<{ id: number; title: string; body: string }>({
  name: 'post',
  columns: [
    {name: 'id', dataType: dataTypes.number},
    {name: 'title', dataType: dataTypes.string},
    {name: 'body', dataType: dataTypes.string}
  ]
});

An extra advantage might be you could infer the typescript interface from the columns defined!

It might be worth matching knex's data types API here as they've already solved this problem and I wouldn't want to create an alternative standard without a really good reason: https://github.com/knex/knex/blob/0918bf9bdea3e0a4bb5cb0e9013e09fc75a1dd92/lib/schema/columncompiler.js#L115

Happy to do this myself if the design is signed off