Typescript Definition

[InDIOS]

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]

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]

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]

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]

@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]

@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]

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

[spion]

@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]

@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]

@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]

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

[3n-mb]

@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]

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]

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]

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]

@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.