Typescript Definition - Githubissues

InDIOS commented 7 years ago

I really want to use this library, but i can't know how work with it. I trying to do a d.ts file to improve it use but i need some help to know what do with each method and what it receive as parameters. if somebody can help me, i'll really apreciate it.

zenlor commented 7 years ago

If you want to use it without type checking you can simply:

const sql = require("sql") as any;

At least the compiler will not complain about it.

spion commented 7 years ago

With the newest mapped types in TypeScript, its actually possible to model node-sql

There is a work-in-progress implementation here: https://github.com/doxout/anydb-sql/blob/4e4c0ff4a7f2efb7f820baaafea1f624f1ae0399/d.ts/anydb-sql.d.ts which can be used as a starting point.

3n-mb commented 7 years ago

Personally, I see greatest benefit when types guide usage of column fields on table object. Ones columns are something that may change often, and we do want computer's help in refactoring via type-checking.

Setting a goal

With this in mind, the following question comes surfaces: How table definition can produce table with the type that has custom columns on it?

Let's take table:

const t = sql.define({
    name: 'membership',
    schema: 'club',
    columns: {
      group_id: { dataType: 'int', primaryKey: true},
      user_id:  { dataType: 'character varying', primaryKey: true},
    }
})

What definition will allow for group_id and user_id to be attached to table type as Column type?

Hack

Adapting @spion mentioned d.ts, full text will follow below, if group_id and user_id are treated as Column types, we may use typescipt's &-ing of types like so

function define<T>(tabDef: TableDefinition<T>): Table & typeof tabDef.columns;

with related definition

interface TableDefinition<T> {
...
    columns:T
}

Note that columns become type loose, which should be explicitly tightened at definition of the table. Let's see how definition should look now:

const t = sql.define({
    ...
    columns: {
      group_id: { dataType: 'int', ... } as Column<number>,
      user_id:  { dataType: 'character varying', ... } as Column<string>,
    }
})

Column extends interface for ColumnDefinition, to allow this cast.

It works nicely, in that t.group_id has a correct type, in accordance to what node-sql does!

Analysis

Stepping back, yes, this hack works for the purpose of strictly following columns, attached to table object. Yet, requirement of explicit cast seems ugly, initially. There are two types of complexity, intrinsic and superficial. An explicit cast to Column serves a secondary purpose of taking a js type for column content. Map of db and js types is many to one, at best, making it an intrinsic complexity. Then we may ride with extra type information, passing T in Column, so that further things like equals() or gt(), defined for different T related to this, may restrict their inputs, partially limiting amount of feet-shooting.

Asking:

do you like this?
can this be improved?
please, adjust/add function, methods/fields in adapted from anydb-sql, as I am lost in all of these types, like @InDIOS
how far methods on columns, carrier of T, can improve strictness of equals() or gt()

Hacked definitions, first round:


declare module 'sql' {

    interface Dictionary<T> {
        [key:string]:T;
    }

    interface OrderByValueNode {}
    interface ColumnDefinition {
        primaryKey?:boolean;
        dataType?:string;
        references?: {table:string; column: string}
        notNull?:boolean
        unique?:boolean
    }

    interface TableDefinition<T> {
        name:string
        columns:T
        has?:Dictionary<{from:string; many?:boolean}>
    }

    interface QueryLike {
        query:string;
        values: any[]
        text:string
    }

    interface SubQuery<T> {
        select(node:Column<T>):SubQuery<T>
        where(...nodes:any[]):SubQuery<T>
        from(table:TableNode):SubQuery<T>
        group(...nodes:any[]):SubQuery<T>
        order(criteria:OrderByValueNode):SubQuery<T>
        exists():BinaryNode
        notExists(subQuery:SubQuery<any>):BinaryNode
    }

    interface Executable {
        toQuery():QueryLike;
    }

    interface Queryable<T> {
        where(...nodes:any[]):Query<T>
        delete():ModifyingQuery
        select<U>(...nodes:any[]):Query<U>
        selectDeep<U>(...nodesOrTables:any[]):Query<U>
    }

    interface Query<T> extends Executable, Queryable<T> {
        from(table:TableNode):Query<T>
        update(o:Dictionary<any>):ModifyingQuery
        update(o:{}):ModifyingQuery
        group(...nodes:any[]):Query<T>
        order(...criteria:OrderByValueNode[]):Query<T>
        limit(l:number):Query<T>
        offset(o:number):Query<T>
    }

    interface ModifyingQuery extends Executable {
        returning<U>(...nodes:any[]):Query<U>
        where(...nodes:any[]):ModifyingQuery
    }

    interface TableNode {
        join(table:TableNode):JoinTableNode
        leftJoin(table:TableNode):JoinTableNode
    }

    interface JoinTableNode extends TableNode {
        on(filter:BinaryNode):TableNode
        on(filter:string):TableNode
    }

    interface CreateQuery extends Executable {
        ifNotExists():Executable
    }
    interface DropQuery extends Executable {
        ifExists():Executable
    }
    interface Table extends TableNode, Queryable<any> {
        create():CreateQuery
        drop():DropQuery
        as(name:string):Table
        update(o:any):ModifyingQuery
        insert<T>(row:T):ModifyingQuery
        insert<T>(rows:T[]):ModifyingQuery
        select<T>():Query<T>
        select<U>(...nodes:any[]):Query<U>
        from<U>(table:TableNode):Query<U>
        star():Column<any>
        subQuery<U>():SubQuery<U>
        eventEmitter:{emit:(type:string, ...args:any[])=>void
                            on:(eventName:string, handler:Function)=>void}
        columns:Column<any>[]
        sql: SQL;
        alter():AlterQuery<any>
    }
    interface AlterQuery<T> extends Executable {
        addColumn(column:Column<any>): AlterQuery<T>;
        addColumn(name: string, options:string): AlterQuery<T>;
        dropColumn(column: Column<any>|string): AlterQuery<T>;
        renameColumn(column: Column<any>, newColumn: Column<any>):AlterQuery<T>;
        renameColumn(column: Column<any>, newName: string):AlterQuery<T>;
        renameColumn(name: string, newName: string):AlterQuery<T>;
        rename(newName: string): AlterQuery<T>
    }

    interface SQL {
        functions: {
            LOWER(c:Column<string>):Column<string>
        }
    }

    interface BinaryNode {
        and(node:BinaryNode):BinaryNode
        or(node:BinaryNode):BinaryNode
    }

    interface Column<T> extends ColumnDefinition {
        in(arr:T[]):BinaryNode
        in(subQuery:SubQuery<T>):BinaryNode
        notIn(arr:T[]):BinaryNode
        equals(node:any):BinaryNode
        notEquals(node:any):BinaryNode
        gte(node:any):BinaryNode
        lte(node:any):BinaryNode
        gt(node:any):BinaryNode
        lt(node:any):BinaryNode
        like(str:string):BinaryNode
        multiply:{
            (node:Column<T>):Column<T>
            (n:number):Column<number>
        }
        isNull():BinaryNode
        isNotNull():BinaryNode
        sum():Column<number>
        count():Column<number>
        count(name:string):Column<number>
        distinct():Column<T>
        as(name:string):Column<T>
        ascending:OrderByValueNode
        descending:OrderByValueNode
        asc:OrderByValueNode
        desc:OrderByValueNode
        name: string;
        table: Table;
    }

    function define<T>(tabDef: TableDefinition<T>): Table & typeof tabDef.columns;

}

spion commented 7 years ago

@3n-mb if you provide a defaultValue for the column definition, everything works, e.g. try

const t = sql.define({
    name: 'membership',
    //schema: 'club',
    columns: {
      group_id: { dataType: 'int', primaryKey: true, defaultValue: 0},
      user_id:  { dataType: 'character varying', primaryKey: true, defaultValue: ''},
    }
})

If you can't do that, you can provide a type parameter manually:

const t2 = sql.define<'membership', {group_id: number, user_id: string}>({
    name: 'membership',
    //schema: 'club',
    columns: {
      group_id: { dataType: 'int', primaryKey: true},
      user_id:  { dataType: 'character varying', primaryKey: true},
    }
})

Another alternative is to add sql.intColumn, sql.stringColumn etc functions that automatically assign the type to the column. That would look like this

const t3 = sql.define({
    name: 'membership',
    //schema: 'club',
    columns: {
      group_id: sql.intColumn({ primaryKey: true})
      user_id:  sql.varcharColumn({ primaryKey: true}),
    }
})

and the column types will be correct.

3n-mb commented 7 years ago

@spion yes! this works. I still cannot completely grasp types' reshuffling you have in the code (line 28 and 221, taken together ?).

Let's build on it! Explicit approach makes one write things twice, and name has to go into <>'s, while there also need to be an overarching schema, as well. What about having a mandatory type filed, not connected with defaultValue, as, I assume, defaultValue has impact on table in db, and we just want to fix js types.

interface ColumnDefinition<Name extends string, Type> extends Named<Name> {
    jsType: Type;
    ...
    defaultValue?: Type
}

And example becomes

const t2 = sql.define({
    name: 'membership',
    //schema: 'club',
    columns: {
      group_id: { dataType: 'int', jsType: 1, ...},
      user_id:  { dataType: 'character varying', jsType: '', ...},
      params:  { dataType: 'jsonb', jsType: {} as object, ...},
 }

Third example with sql.intColumn() and likes, does it require explicit patching sql? I hesitate, patching modules of others on the fly, unless docs explicitly tell what is allowed in bold.

Hacked code with jsType, second round:

declare module "sql" {

    interface OrderByValueNode {}

    interface Named<Name extends string> {
        name?: Name;
    }
    interface ColumnDefinition<Name extends string, Type> extends Named<Name> {
        jsType: Type;
        primaryKey?:boolean;
        dataType?:string;
        references?: {table:string; column: string}
        notNull?:boolean
        unique?:boolean
        defaultValue?: Type
    }

    interface TableDefinition<Name extends string, Row> {
        name: Name
        columns:{[CName in keyof Row]: ColumnDefinition<CName, Row[CName]>}
        has?:{[key: string]:{from:string; many?:boolean}}
    }

    interface QueryLike {
        query:string;
        values: any[]
        text:string
    }
    interface DatabaseConnection {
        queryAsync<T>(query:string, ...params:any[]):Promise<{rowCount:number;rows:T[]}>
        queryAsync<T>(query:QueryLike):Promise<{rowCount:number;rows:T[]}>
    }

    interface Transaction extends DatabaseConnection {
        rollback():void
        commitAsync():Promise<void>
    }

    interface Executable {
        toQuery():QueryLike;
    }

    interface Queryable<T> {
        where(...nodes:any[]):Query<T>
        delete():ModifyingQuery
        select<N1 extends string, T1>(n1: Column<N1, T1>):Query<T1>
        select<N1 extends string, T1, N2 extends string, T2>(
                n1: Column<N1, T1>,
                n2: Column<N2, T2>):Query<{[N in N1]: T1} & {[N in N2]: T2}>
        select<N1 extends string, T1, N2 extends string, T2, N3 extends string, T3>(
                n1: Column<N1, T1>,
                n2: Column<N2, T2>,
                n3: Column<N3, T3>):Query<{[N in N1]: T1} & {[N in N2]: T2} & {[N in N3]: T3}>
        select<U>(...nodesOrTables:any[]):Query<U>

        selectDeep<N1 extends string, T1>(n1: Table<N1, T1>):Query<T1>
        selectDeep<N1 extends string, T1, N2 extends string, T2>(
                n1: Table<N1, T1>,
                n2: Table<N2, T2>):Query<{[N in N1]: T1} & {[N in N2]: T2}>
        selectDeep<N1 extends string, T1, N2 extends string, T2, N3 extends string, T3>(
                n1: Table<N1, T1>,
                n2: Table<N2, T2>,
                n3: Table<N3, T3>):Query<{[N in N1]: T1} & {[N in N2]: T2} & {[N in N3]: T3}>
        //selectDeep<U>(...nodesOrTables:any[]):Query<U>
    }

    interface Query<T> extends Executable, Queryable<T> {
        from(table:TableNode):Query<T>
        from(statement:string):Query<T>
        update(o:{[key: string]:any}):ModifyingQuery
        update(o:{}):ModifyingQuery
        group(...nodes:any[]):Query<T>
        order(...criteria:OrderByValueNode[]):Query<T>
        limit(l:number):Query<T>
        offset(o:number):Query<T>
    }

    interface SubQuery<T> {
        select<Name>(node:Column<Name, T>):SubQuery<T>
        select(...nodes: any[]):SubQuery<T>
        where(...nodes:any[]):SubQuery<T>
        from(table:TableNode):SubQuery<T>
        from(statement:string):SubQuery<T>
        group(...nodes:any[]):SubQuery<T>
        order(criteria:OrderByValueNode):SubQuery<T>
        exists():BinaryNode
        notExists(): BinaryNode;
        notExists(subQuery:SubQuery<any>):BinaryNode
    }

    interface ModifyingQuery extends Executable {
        returning<U>(...nodes:any[]):Query<U>
        where(...nodes:any[]):ModifyingQuery
    }

    interface TableNode {
        join(table:TableNode):JoinTableNode
        leftJoin(table:TableNode):JoinTableNode
    }

    interface JoinTableNode extends TableNode {
        on(filter:BinaryNode):TableNode
        on(filter:string):TableNode
    }

    interface CreateQuery extends Executable {
        ifNotExists():Executable
    }
    interface DropQuery extends Executable {
        ifExists():Executable
    }

    type Columns<T> = {
        [Name in keyof T]: Column<Name, T[Name]>
    }
    type Table<Name extends string, T> = TableNode & Queryable<T> & Named<Name> & Columns<T> & {

        create():CreateQuery
        drop():DropQuery
        as<OtherName extends string>(name:OtherName):Table<OtherName, T>
        update(o:any):ModifyingQuery
        insert(row:T):ModifyingQuery
        insert(rows:T[]):ModifyingQuery
        select():Query<T>
        select<U>(...nodes:any[]):Query<U>
        from<U>(table:TableNode):Query<U>
        from<U>(statement:string):Query<U>
        star():Column<void, void>
        subQuery<U>():SubQuery<U>
        eventEmitter:{emit:(type:string, ...args:any[])=>void
                            on:(eventName:string, handler:Function)=>void}
        columns:Column<void, void>[]
        sql: SQL;
        alter():AlterQuery<T>;
        indexes(): IndexQuery;
    }

    type Selectable<Name extends string, T> = Table<Name, T> | Column<Name, T>

    interface AlterQuery<T> extends Executable {
        addColumn(column:Column<any, any>): AlterQuery<T>;
        addColumn(name: string, options:string): AlterQuery<T>;
        dropColumn(column: Column<any, any>|string): AlterQuery<T>;
        renameColumn(column: Column<any, any>, newColumn: Column<any, any>):AlterQuery<T>;
        renameColumn(column: Column<any, any>, newName: string):AlterQuery<T>;
        renameColumn(name: string, newName: string):AlterQuery<T>;
        rename(newName: string): AlterQuery<T>
    }
    interface IndexQuery {
        create(): IndexCreationQuery;
        create(indexName: string): IndexCreationQuery;
        drop(indexName: string): Executable;
        drop(...columns: Column<any, any>[]): Executable
    }
    interface IndexCreationQuery extends Executable {
        unique(): IndexCreationQuery;
        using(name: string): IndexCreationQuery;
        on(...columns: (Column<any, any>|OrderByValueNode)[]): IndexCreationQuery;
        withParser(parserName: string): IndexCreationQuery;
        fulltext(): IndexCreationQuery;
        spatial(): IndexCreationQuery;
    }

    interface SQL {
        functions: {
                LOWER<Name>(c:Column<Name, string>):Column<Name, string>
        }
    }

    interface BinaryNode {
        and(node:BinaryNode):BinaryNode
        or(node:BinaryNode):BinaryNode
    }

    interface Column<Name, T> {
        name: Name
        in(arr:T[]):BinaryNode
        in(subQuery:SubQuery<T>):BinaryNode
        notIn(arr:T[]):BinaryNode
        equals(node:any):BinaryNode
        notEquals(node:any):BinaryNode
        gte(node:any):BinaryNode
        lte(node:any):BinaryNode
        gt(node:any):BinaryNode
        lt(node:any):BinaryNode
        like(str:string):BinaryNode
        multiply:{
                (node:Column<any, T>):Column<any, T>
                (n:number):Column<any, number> //todo check column names
        }
        isNull():BinaryNode
        isNotNull():BinaryNode
        //todo check column names
        sum():Column<any, number>
        count():Column<any, number>
        count(name:string):Column<any, number>
        distinct():Column<Name, T>
        as<OtherName>(name:OtherName):Column<OtherName, T>
        ascending:OrderByValueNode
        descending:OrderByValueNode
        asc:OrderByValueNode
        desc:OrderByValueNode
    }

    function define<Name extends string, T>(map:TableDefinition<Name, T>): Table<Name, T>;

}

3n-mb commented 7 years ago

@spion is has in interface TableDefinition anydb-sql's construct, or, is it node-sql?

spion commented 7 years ago

@3n-mb most of the "magic" is on line 21. For each field name in the type of the table row, create a column type based on the type of that field in the table row.

Now the reason why it wont work without a default value is because TypeScript has to somehow infer what kind of type is the field - once it does that, it can "pull it in" from TableDefinition<Name, Row> up to the table Row type and then use it to generate the Table<Name, Row> columns. If a default value is not provided, the type of Row[CName] remains a mystery to typescript and the entire row must be provided explicitly as a type parameter between <>.

I don't know if its possible to put a type as a field value in the column definition. It might be, with more mapped types hackery, but I don't think it is... Lets try:

  type Constructor<T> = {
    new(t:any):T
  }

We added a constructor type, now we can add another optional field in the definition that node-sql will never look at:

    export interface ColumnDefinition<Name extends string, Type> extends Named<Name> {
        primaryKey?:boolean;
        dataType?:string;
        references?: {table:string; column: string}
        notNull?:boolean
        unique?:boolean
        defaultValue?: Type
        jsType?: Constructor<Type>
    }

Now we can use jsType instead:

const t4 = sql.define({
    name: 'membership',
    //schema: 'club',
    columns: {
      group_id: {dataType: 'int', primaryKey: true, jsType: Number},
      user_id:  { dataType: 'character varying', primaryKey: true, jsType: String},
    }
})

But you still need hackery for JSON objects with specific shapes. Lets try adding that too... From within the module:

    export function JSONObject<T>(o: {[K in keyof T]: Constructor<T[K]>}): Constructor<T> {
        return o as any;
    }

Now we can trick typescript into realising whats the type of this JSON object:

const t5 = sql.define({
    name: 'membership',
    //schema: 'club',
    columns: {
      group_id: {dataType: 'int', primaryKey: true, jsType: Number},
      user_id: { dataType: 'character varying', primaryKey: true, jsType: String },
      extras: {dataType: 'json', jsType: anydbSQL.JSONObject({field1: String, field2: Number}) }
    }
})

Its not important what the function returns, its only there to placate TypeScript into accepting the type of the column.

Yes, sql.intColumn and other types of columns would have to be added to node-sql. We could perhaps send a PR for it and if @brianc is okay with it he could merge it. They're just small things that would make it easier and more ergonomic to model the library with typescript... I mean the above hacks work too, they are just annoying and error prone (what if we use dataType int with jsType string? Chaos!)

has is from anydb-sql, it was sort of a failed experiment to model relationships. Feel free to ignore it 😀

One of these days, I'm hoping I'll have the chance to clean all this up and contribute proper typings... but right now I can't

3n-mb commented 7 years ago

@spion thank you for this piece, it really is useful in the second form, i.e. I am now using it this way, love it so far, in comparison to other ... non-options, in comparison. I posted code in its entirely, so that other folks may copy and use it directly, buying us time.

For the future, I think that in column interface, things like equals should be using T that is already with the column. You have all of ground work done. At least it seems so.

spion commented 7 years ago

@3n-mb sorry I started editing my original comment and came up with another hack that almost gets the definitions where you wanted them to be (the only caveat is you need to put the JSONObject function somewhere - probably not in the sql module but something like sql-tools maybe - and import it to create new object types)

edit: actually individual types can be in a separate module called sql-types or something like that. They would generate column definitions with the appropriate dataType for your database... and include all the type goodies that let TS infer what the column contains.

I'm really excited to polish this up a little more... as soon as I find the chance!

Strate commented 7 years ago

It would be really good if we can somehow "export" information about selected columns, to achieve typecheck for query result.

3n-mb commented 7 years ago

@Strate let try a little hack to pick type for variable, into which result will be placed. First, let's take example table:

interface MembershipRecord {
  group_id: number;
  user_id: string;
  params?: object;
}
const t2 = sql.define<'membership', MembershipRecord>({
    name: 'membership',
    schema: 'club',
    columns: {
       group_id: { dataType: 'int', ...},
       user_id:  { dataType: 'character varying', ...},
       params:  { dataType: 'jsonb', ...},
}

then, let's take a particular query we want to make:

const q = t2
   .select(t2.user_id, t2.params)
   .where(t2.group_id.equals(-1));

before we jump toQuery(), we need to arrest type of this Query<T>. @spion did magic here, and query's generic type will be in this example something along the lines T = { user_id: string } & { params: object|undefined }. So, to extract it, we add a phantom field to interface Query<T>:

interface Query<T> {
   ...
   resultType: T;
}

expanding our example:

const q = t2
   .select(t2.user_id, t2.params)
   .where(t2.group_id.equals(-1));
let resultRow: typeof q.resultType;

Try to use it, share your opinion.

Note that I have introduced record's type in the very front, and seeded with its type into sql.define<...>(). Initially, I thought this way is too typing-consuming, but now, after use, I realize that this is the the more lazy way of writing, cause (a) sooner or later you will want to have record type, (b) typing inside define is Ctrl+Space driven activity, (c) any renaming will take all correct places anyway, and (d) reading explicit js type for whole record is easier.

3n-mb commented 7 years ago

Suggestion for types for column methods

As for types for equals() and others, I changed their incoming types in interface Column:

    interface Column<Name, T> {
       ...
        equals(node: T|Column<any, T>):BinaryNode
        notEquals(node: T|Column<any, T>):BinaryNode
        gte(node: T|Column<any, T>):BinaryNode
        lte(node: T|Column<any, T>):BinaryNode
        gt(node:T|Column<any, T>):BinaryNode
        lt(node: T|Column<any, T>):BinaryNode
       ...
    }

It works for me, so far. @spion do you think there may be other unforeseen things here? Or can this be done better?

selectDeep question

@spion is Queryable.selectDeep(...) from node-sql, or from anydb-sql ? I am slowly groking this. I can see have name-as-type in a column helps, for example to get query result (previous comment). Table's name is used in selectDeep. Is it used elsewhere? Or do you have another plan for it?

Definitions, round three, adding some more node-sql's own things

declare module "sql" {

    interface OrderByValueNode {}

    interface Named<Name extends string> {
        name?: Name;
    }
    interface ColumnDefinition<Name extends string, Type> extends Named<Name> {
        jsType?: Type;
        dataType: string;
        primaryKey?: boolean;
        references?: {
            table:string;
            column: string;
            onDelete?: 'restrict' | 'cascade' | 'no action' | 'set null' | 'set default';
            onUpdate?: 'restrict' | 'cascade' | 'no action' | 'set null' | 'set default';
        };
        notNull?: boolean;
        unique?: boolean;
        defaultValue?: Type;
    }

    interface TableDefinition<Name extends string, Row> {
        name: Name;
        schema: string;
        columns: {[CName in keyof Row]: ColumnDefinition<CName, Row[CName]>};
        isTemporary?: boolean;
        foreignKeys?: {
            table: string,
            columns: string[],
            refColumns: string[]
        }
    }

    interface QueryLike {
        values: any[]
        text:string
    }

    interface Executable {
        toQuery():QueryLike;
    }

    interface Queryable<T> {
        where(...nodes:any[]):Query<T>
        delete():ModifyingQuery
        select<N1 extends string, T1>(n1: Column<N1, T1>):Query<T1>
        select<N1 extends string, T1, N2 extends string, T2>(
                n1: Column<N1, T1>,
                n2: Column<N2, T2>):Query<{[N in N1]: T1} & {[N in N2]: T2}>
        select<N1 extends string, T1, N2 extends string, T2, N3 extends string, T3>(
                n1: Column<N1, T1>,
                n2: Column<N2, T2>,
                n3: Column<N3, T3>):Query<{[N in N1]: T1} & {[N in N2]: T2} & {[N in N3]: T3}>
        select<U>(...nodesOrTables:any[]):Query<U>

        selectDeep<N1 extends string, T1>(n1: Table<N1, T1>):Query<T1>
        selectDeep<N1 extends string, T1, N2 extends string, T2>(
                n1: Table<N1, T1>,
                n2: Table<N2, T2>):Query<{[N in N1]: T1} & {[N in N2]: T2}>
        selectDeep<N1 extends string, T1, N2 extends string, T2, N3 extends string, T3>(
                n1: Table<N1, T1>,
                n2: Table<N2, T2>,
                n3: Table<N3, T3>):Query<{[N in N1]: T1} & {[N in N2]: T2} & {[N in N3]: T3}>
        selectDeep<U>(...nodesOrTables:any[]):Query<U>
    }

    interface Query<T> extends Executable, Queryable<T> {
        resultType: T;

        from(table:TableNode):Query<T>
        from(statement:string):Query<T>
        update(o:{[key: string]:any}):ModifyingQuery
        update(o:{}):ModifyingQuery
        group(...nodes:any[]):Query<T>
        order(...criteria:OrderByValueNode[]):Query<T>
        limit(l:number):Query<T>
        offset(o:number):Query<T>
    }

    interface SubQuery<T> {
        select<Name>(node:Column<Name, T>):SubQuery<T>
        select(...nodes: any[]):SubQuery<T>
        where(...nodes:any[]):SubQuery<T>
        from(table:TableNode):SubQuery<T>
        from(statement:string):SubQuery<T>
        group(...nodes:any[]):SubQuery<T>
        order(criteria:OrderByValueNode):SubQuery<T>
        exists():BinaryNode
        notExists(): BinaryNode;
        notExists(subQuery:SubQuery<any>):BinaryNode
    }

    interface ModifyingQuery extends Executable {
        returning<U>(...nodes:any[]):Query<U>
        where(...nodes:any[]):ModifyingQuery
    }

    interface TableNode {
        join(table:TableNode):JoinTableNode
        leftJoin(table:TableNode):JoinTableNode
    }

    interface JoinTableNode extends TableNode {
        on(filter:BinaryNode):TableNode
        on(filter:string):TableNode
    }

    interface CreateQuery extends Executable {
        ifNotExists():Executable
    }
    interface DropQuery extends Executable {
        ifExists():Executable
    }

    type Columns<T> = {
        [Name in keyof T]: Column<Name, T[Name]>
    }
    type Table<Name extends string, T> = TableNode & Queryable<T> & Named<Name> & Columns<T> & {
        getName(): string;
        getSchema(): string;

        create():CreateQuery
        drop():DropQuery
        as<OtherName extends string>(name:OtherName):Table<OtherName, T>
        update(o:any):ModifyingQuery
        insert(row:T):ModifyingQuery
        insert(rows:T[]):ModifyingQuery
        select():Query<T>
        select<U>(...nodes:any[]):Query<U>
        from<U>(table:TableNode):Query<U>
        from<U>(statement:string):Query<U>
        star():Column<void, void>
        subQuery<U>():SubQuery<U>
        columns:Column<void, void>[]
        sql: SQL;
        alter():AlterQuery<T>;
        indexes(): IndexQuery;
    }

    type Selectable<Name extends string, T> = Table<Name, T> | Column<Name, T>

    interface AlterQuery<T> extends Executable {
        addColumn(column:Column<any, any>): AlterQuery<T>;
        addColumn(name: string, options:string): AlterQuery<T>;
        dropColumn(column: Column<any, any>|string): AlterQuery<T>;
        renameColumn(column: Column<any, any>, newColumn: Column<any, any>):AlterQuery<T>;
        renameColumn(column: Column<any, any>, newName: string):AlterQuery<T>;
        renameColumn(name: string, newName: string):AlterQuery<T>;
        rename(newName: string): AlterQuery<T>
    }
    interface IndexQuery {
        create(): IndexCreationQuery;
        create(indexName: string): IndexCreationQuery;
        drop(indexName: string): Executable;
        drop(...columns: Column<any, any>[]): Executable
    }
    interface IndexCreationQuery extends Executable {
        unique(): IndexCreationQuery;
        using(name: string): IndexCreationQuery;
        on(...columns: (Column<any, any>|OrderByValueNode)[]): IndexCreationQuery;
        withParser(parserName: string): IndexCreationQuery;
        fulltext(): IndexCreationQuery;
        spatial(): IndexCreationQuery;
    }

    interface SQL {
        functions: {
                LOWER<Name>(c:Column<Name, string>):Column<Name, string>
        }
    }

    interface BinaryNode {
        and(node:BinaryNode):BinaryNode
        or(node:BinaryNode):BinaryNode
    }

    interface Column<Name, T> {
        name: Name
        in(arr:T[]):BinaryNode
        in(subQuery:SubQuery<T>):BinaryNode
        notIn(arr:T[]):BinaryNode
        equals(node: T|Column<any, T>):BinaryNode
        notEquals(node: T|Column<any, T>):BinaryNode
        gte(node: T|Column<any, T>):BinaryNode
        lte(node: T|Column<any, T>):BinaryNode
        gt(node:T|Column<any, T>):BinaryNode
        lt(node: T|Column<any, T>):BinaryNode
        like(str:string):BinaryNode
        multiply:{
                (node:Column<any, T>):Column<any, T>
                (n:number):Column<any, number> //todo check column names
        }
        isNull():BinaryNode
        isNotNull():BinaryNode
        //todo check column names
        sum():Column<any, number>
        count():Column<any, number>
        count(name:string):Column<any, number>
        distinct():Column<Name, T>
        as<OtherName>(name:OtherName):Column<OtherName, T>
        ascending:OrderByValueNode
        descending:OrderByValueNode
        asc:OrderByValueNode
        desc:OrderByValueNode
    }

    function define<Name extends string, T>(map:TableDefinition<Name, T>): Table<Name, T>;

}

spion commented 7 years ago

Yup, equals looks good.

regarding select(column1, column2) it should simply work and generate correct new row types without any extra work. It just needs to be removed from the definition of Table as its a duplicate (just select<U>(...nodes:any[]):Query<U>, the other overload that has no arguments is fine). It should be correctly provided already by Queryable<T>.

The same corrections need to be done for SubQuery<T>.

Another thing, Queryable<T> only has overloads up to 3 columns, might be a good idea to add support for bigger select statements (9 columns ought to be enough for everyone?)

selectDeep is added by anydb-sql and it lets you select multiple tables from a join query. It can be safely left out.

3n-mb commented 7 years ago

Adding types to update

So far, in type Table we had update(o: any): ModifyingQuery, i.e. no constraining for incoming types.

Suggestion

Let's make the following type:

type PartialRecord<T> = {
    [Name in keyof T]?: T[Name];
}

(note question mark for making it partial) and use it for update argument in type Table:

type Table<Name extends string, T> = TableNode & Queryable<T> & Named<Name> & Columns<T> & {
    ...
    update(o: PartialRecord<T>): ModifyingQuery;
    ...
}

Sample use

With this modification, given sample table:

interface MembershipRecord {
  group_id: number;
  user_id: string;
  params?: object;
}
const t2 = sql.define<'membership', MembershipRecord>({
    name: 'membership',
    schema: 'club',
    columns: {
       group_id: { dataType: 'int', ...},
       user_id:  { dataType: 'character varying', ...},
       params:  { dataType: 'jsonb', ...},
}

typing checks what one puts in update object both for keys and value types:

t2.update({ group_id: 9 }) ... // ok
t2.update({ groupId: 9 }) ... // not ok
t2.update({ group_id: '9' }) ... // not ok

Writing an update also becomes Ctrl+Space-driven activity :)

3n-mb commented 7 years ago

@spion have you added Name parameter to type Table to have deepSelect? Without selectDeep, I tried to remove Name parameter. It simplifies things a bit, but clear naming of type in editor is gone, and editor's help becomes less informative. So, even though, Table's name type isn't used like Column's name, we better keep it for decoration of code-reading activity.

3n-mb commented 7 years ago

We have Table.star() returning a peculiar type Column<void, void>, in that it cannot be created by code. Therefore, we may use it to add another overload for select:

interface Queryable<T> {
    ...
    select(star: Column<void, void>): Query<T>;
    ...
}

for those not uncommon times when we select star, like t2.select(t2.star()). ....

Works for me. Can it create any confusion later, or is it ok?

spion commented 7 years ago

+1 on the update update 😀

Yes I think you can get away w/o Name for the table.

Column<void, void> looks safe enough.... not 100% sure about it, but I think its okay. I might go with a fake branded type AllColumns, to ensure that it cannot be passed in other places where Column is expected...

type Brand<U> = { ' kind': U };

type AllColumns = Brand<'AllColumns'>

// the new definition of star()
type Table<T> = ... {
  ...
  star():AllColumns
  ...
}

3n-mb commented 7 years ago

Adding column names for aggregate standard functions

It touches Column:

interface Column<Name, T> {
    ...
    sum(): Column<'sum', number>;
    count(): Column<'count', number>;
    ...
}

Judging from experience with count and examples from postgres docs, column output for aggregate function is called with that function's name. Can this be a general SQL naming for all functions, even custom ones?

3n-mb commented 7 years ago

@spion I do not see any examples in node-sql for Column's

count(name:string): Column<any, number>;

with argument name. Is it anydb's?

spion commented 7 years ago

It looks like count's default column name is "tablename_count" http://node-sql-examples.github.io/#dialect=pg&query=count

Which is not possible to model with TS at the moment 😢

3n-mb commented 7 years ago

Have an update in restricting names of columns, named in foreignKeys:

    interface TableDefinition<Name extends string, Row> {
        name: Name;
        schema: string;
        columns: {[CName in keyof Row]: ColumnDefinition<CName, Row[CName]>};
        isTemporary?: boolean;
        foreignKeys?: {
            table: string,
            columns: (keyof Row)[],
            refColumns: string[],
            onDelete?: 'restrict' | 'cascade' | 'no action' | 'set null' | 'set default';
            onUpdate?: 'restrict' | 'cascade' | 'no action' | 'set null' | 'set default';
        }
    }

When indicating a table, we use that table's object. When indicating refColumns, we use that table's object. But columns were free-floating. So, (keyof Rows) narrows space for error.

spion commented 7 years ago

@3n-mb want to submit a PR to DefinitelyTyped or here or create a new repo with the definitions?

3n-mb commented 7 years ago

@spion ya. I haven't found any burning things. Except that other module functions can be added, as well comments. I'll do it with the current form, as a PR, as I personally like when types stay with the lib, rather than 3-rd party repo.

3n-mb commented 7 years ago

@spion It is PR #357 . Without your types in anydb-sql, it would not be possible.

zenlor commented 7 years ago

Thanks for your work! It think there is a missing function:

type SQLDialects =
    | "mysql"
    | "postgres"
    | "mssql"
    | "sqlite"
    ;
function setDialect(dialect: SQLDialects): void;

And also:

interface TableDefinition<Name extends string, Row> {
    ...
    dialect: SQLDialects,
    ...
}

Shall I proceed with a pull request for this?

3n-mb commented 7 years ago

@aliem yes, please. Do a PR.

zenlor commented 7 years ago

I have a couple more things to add:

and or support, https://github.com/aliem/node-sql/commit/829a8312ee20b119bc47d1a6db205afc61a0355d it's very naive.
insert (https://github.com/aliem/node-sql/commit/cbd544015d85855487787c5829c4444902fe10c1) requires a full blown T while, probably, a Partial<T> might be better. If you declare a serial columns you are required to provide it at insertion. insert statements would loose checks for notNull fields (for which I'm not sure if it is even possible to declare).

3n-mb commented 7 years ago

@aliem Partial<T> in insert would be a bit too weak as type safety at least for my sloppy coding :smile:

It turns out that empty object {} will be accepted as Partial<T>, with result seen only at runtime.
With Partial<T> typescript will not force me to remember to add all required fields, with result seen only at runtime.

insert should be a bit stricter than an update, and it seems that parameters should be set optional in type, in accordance of optionality in a table (use of sql nulls and defaults).

To me this trade off feels like a part of whole deal of typescript helping to deal with sql tables. I'd rather add an additional (smaller) type somewhere else for my code to use, than to reduce strictness, which corrects me as I code.

3n-mb commented 7 years ago

@aliem about enforcing not-null. 1) Have a not-null field as not optional in your T. 2) In tsconfig.json have "strictNullChecks": true. It does miracles. With this I found so many places with unnecessary ambiguity, that, upon code cleanup, code became more stable. It is better to be explicit about nulls and undefineds, especially with ability to always say, for example number|undefined, where it is needed.

zenlor commented 7 years ago

I agree Partial<T> is not the best. I've been testing with various configurations and I guess I'm going to stick with https://github.com/aliem/node-sql/blob/c0981fe197f44b9c2de7dc7d915d515382fc893b/lib/types.d.ts#L135-L137 until I find a better solution.

Using a ModifyingColumns that allows for T[Name] | SubQuery<U> | null. Allowing me to compile something like:

    const t = db.Actions;
    const f = db.Functions;

    return t
        .insert({
            id: null,
            created_at: null,
            updated_at: null,
            payload: opts.payload,
            workflow_id: opts.workflow_id,
            function_id: f
                .subQuery()
                .select(f.id)
                .where({ uuid: opts.function_uuid }),
        })
        .returning(t.id);

kernel-io commented 6 years ago

I have created a PR that adds the onConflict clause for Postgresql to the type definition. https://github.com/brianc/node-sql/pull/378

Justus-Maier commented 6 years ago

Please also merge #376 I'm still searching for documentation on the still non-optional but unnecessary schema attribute!

Justus-Maier commented 6 years ago

Was able to find https://github.com/brianc/node-sql/blob/master/test/dialects/schema-tests.js as documentation for the schema attribute. It's definitely not non-optional. Here is a workaround disabling type checking for this particular problem:

import { define } from 'sql';
export const user = define(<any>{ // <any> disables ts typecheck
  name: 'user',
  columns: ['id', 'name'],
});

3n-mb commented 6 years ago

@Justus-Maier In Typescript there will always be a trick up your sleeve. Instead of making cast to any, which will come to haunt you (trust me :smiling_imp: ), add schema: undefined as any,:

import { define } from 'sql';
export const user = define({
  name: 'user',
  schema: undefined as any,  // ugliness points to shortcoming in def, while undefined gives you needed js
  columns: ['id', 'name'],
});

This way you don't throw types (baby), while clearly identifying a shortcoming in type definition (bathing water).

Justus-Maier commented 6 years ago

@3n-mb I'd prefer your workaround if I wouldn't be running into the next type error:

TS2345: Argument of type '{ name: "user"; columns: string[]; schema: any; }' is not assignable to parameter of type 'TableDefinition<"user", { find: any; findIndex: any; fill: any; copyWithin: any; [Symbol.it...'.
      Types of property 'columns' are incompatible.
        Type 'string[]' is not assignable to type '{ find: ColumnDefinition<"find", {}>; findIndex: ColumnDefinition<"findIndex", {}>; fill: ColumnD...'.
          Types of property 'find' are incompatible.
            Type '{ <S extends string>(predicate: (this: void, value: string, index: number, obj: string[]) => valu...'
is not assignable to type 'ColumnDefinition<"find", {}>'.
              Property 'dataType' is missing in type '{ <S extends string>(predicate: (this: void, value: string, index:
 number, obj: string[]) => valu...'.

Is there something more I need to know to use typescript with node-sql?

This continues, user.id & user.name from the example are not recognised, and the new workaround looks like this:

...
export const user = <any>define(<any>{ // <any> disables ts typecheck
...

Maybe I should prefer :/

const sql = require("sql") as any;

3n-mb commented 6 years ago

@Justus-Maier error says that you don't give type to an sql column. Can db create column without being told its type? No. And here you found it before talking to sql db.

If schema can be optional (cause it will default to public), and it is a matter of hygiene to use it, then column type is a must.

By the way, I remember there was something security-related around public schema, with an advise to not use it. Structure you db with schemas. One day it will help you.

Justus-Maier commented 6 years ago

@3n-mb so this means I have to declare columns differently? Can you link an example please?

How is schema relevant if not using an OracleDB?

3n-mb commented 6 years ago

@Justus-Maier roll back, and start with postgres documentation. Then you'll see meaning in all of those typed fields: schema, column type.

No, there is no shorter way. :cry:

spion commented 6 years ago

@Justus-Maier its not the schema. The type definition version doesn't support the array of strings syntax for fields. Instead for each field you need to use the object notation specifying what dataType / jsType it has.

Justus-Maier commented 6 years ago

Sorry for spamming this thread, but it seems documentation is missing for this. Again: Please link an example! Or create tests for the type definitions that show how it is to be used.

Also the schema is not relevant for my usecase. The database I am accessing exists in mssql and postgres variants and therefor has no postgres schemas due to incompatbility. Bridging those variants is the reason why I'm trying to use this SQL query string generator.

from the Postgres documentation

5.7.7. Portability
In the SQL standard, the notion of objects in the same schema being owned by different users does not exist.  [...]

Also, there is no concept of a public schema in the SQL standard. For maximum conformance to the standard, you should not use (perhaps even remove) the public schema.

Justus-Maier commented 6 years ago

Improving on my own example..


import { define, ColumnDefinition } from 'sql';
export const user = define({
  name: 'user',
  schema: undefined as any,  // ugliness points to shortcoming in def, while undefined gives you needed js
  columns: {
    id: <ColumnDefinition<'id', String>>{
      name: 'id'
    }, 
    name: <ColumnDefinition<'name', String>>{
      name: 'name'
    }
  }
});

Looks pretty verbose but it works. Please add something like this to the readme.

spion commented 6 years ago

@Justus-Maier Use id: { name: 'id', jsType: String } for a less verbose version.

Indeed, this is not documented anywhere. Sorry about that. I'm hoping that in the following months I'll manage to find some time to contribute to the type support code and make it friendly for users (docs, examples, tests etc)

zenlor commented 6 years ago

I have been using a few helpers to generate definitions, take a look here: https://github.com/zenlor/node-sql-types it is probably what you are looking for, but it hides implementation details under another layer increasing overall complexity.

It's not complete and it's very opinionated given my use case.

The best integration I could try is still anydb-sql but it's in need of an update on both node-sql and the definitions.

I have been thinkering lately with a logicless model implementation but typescript's type inference, sometimes, is far too strict and difficult to implement without macros (it would be so much easier with reader macros)

jayarjo commented 4 years ago

Still not solved? Oh my. What if want to generate an insert query for some generic table. How do I safely type that, when I do not even know what the types of those tables are?

3n-mb commented 4 years ago

Still not solved? Oh my. What if want to generate an insert query for some generic table. How do I safely type that, when I do not even know what the types of those tables are?

@jayarjo what precisely do you mean?

I use existing typing, and I use it constantly to ensure strict setups of db's. Typing really helps. Start with definition of table, and everything else is checked automagically.
Are you ask about task not being closed. Do you have an impression that there is no typing?

jayarjo commented 4 years ago

@3n-mb I cannot finish definition of the table.

I do not know beforehand what the keys of the object will be. I just want to generate the insert query. Suggestions?

3n-mb commented 4 years ago

@jayarjo can you flesh it this out a bit more in code snippets of how you want to see things. This sounds to me like an exercise in flexibility of TypeScript typing mechanism. Do you just put any, or figure it with typeof and other tricks.

jayarjo commented 4 years ago

@3n-mb I need to be able to generate an sql for insert (or any other type) query out of totally generic tableName and array of column names. Something like:

const table = define({
    name: tableName,
    columns: columns
}

where columns array is dynamic and values can be arbitrary.

My impression is that it is not possible, at the moment.

3n-mb commented 4 years ago

@jayarjo Have you tried the following partial release of type strictness, assuming also that tableName and columns are not known at time of ts' compilation:

const table = define<'any-name', any>{
  name: tableName as 'any-name',
  columns: columns
}

Note that type constraint on table name value is simply enforcing strictness (it saved me once already :smile: ). But putting 'any-name' in type does nothing, cause TypeScript's types exist only when you write code, and not when code (js) runs.

jayarjo commented 4 years ago

@3n-mb not sure what that does, but it didn't work.

brianc / node-sql

Typescript Definition #351

Setting a goal

Hack

Analysis

Asking:

Hacked definitions, first round:

Hacked code with jsType, second round:

Suggestion for types for column methods

selectDeep question

Definitions, round three, adding some more node-sql's own things

Adding types to update

Suggestion

Sample use

Adding column names for aggregate standard functions