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.

[3n-mb]

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]

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

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]

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

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]

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]

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

[3n-mb]

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

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

[zenlor]

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]

@aliem yes, please. Do a PR.

[zenlor]

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]

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

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

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

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]

I have created a PR that adds the onConflict clause for Postgresql to the type definition.
#378

[Justus-Maier]

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

[Justus-Maier]

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]

@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 😈 ), 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]

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

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

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

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

[spion]

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

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]

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]

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

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]

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]

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]

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

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

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

@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 😄 ). 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]

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

[3n-mb]

@jayarjo give more context. How is table consumed/used? What doesn't work precisely?
If you have any sensitive elements in code, just strip/replace them, and show context in which you want to use this. Also, what is you typescript version?

[zenlor]

@jayarjo
You need to provide the needed type, a Object.keys(x) is not enough, you will, at least need to cast it to a ColumnDefinition

[jayarjo]

It's too much hassle, really. I simply wrote my own query generator. Five lines - that's it. You guys overcomplicate.

[3n-mb]

@jayarjo I look at types not as a hassle, but as a fun exercise of using very flexible Typescript type system.
Now, I will try to narrow your statement about "too much hassle", as to in your particular case.
In my org you can't commit a db code that is not also vetted in some form by compiler on top of regular reviews -- data security, correctness, etc. Hence, I am interested in cases that require deviation from 100% type strictness.
Can I ask you again, @jayarjo to provide more code. We have spent time to consider your questions. Can you extend the same kindness back to us? Thank you in advance.

[jayarjo]

@3n-mb I cannot provide my code, sorry. But what I basically did is that I wrote a generic data seeder using pg-structure and faker that generates arbitrary amount of fake seed data, taking into account structure of the given database, types of the columns, format, length, etc. It is actually interesting if there's anything you can recommend for such use case?

[3n-mb]

@3n-mb I cannot provide my code, sorry. But what I basically did is that I wrote a generic data seeder using pg-structure and faker that generates arbitrary amount of fake seed data, taking into account structure of the given database, types of the columns, format, length, etc. It is actually interesting if there's anything you can recommend for such use case?

@jayarjo

• You ask for something without providing details. Useful answers can't appear if details are not given. Software craft- men and women take deep personal responsibility and pride in providing useful answers to peer developers.
• No one asked for your production code. I asked for code snippets that convey your requirements in more precise language -- computer code. I even reminded you to strip/replace parts that relate to your stuff but are not material to an articulation of problem you've had. My ask still stands.
• Do you realize that you've managed to spell "FU" with "sorry"? I am not an emotionless AI, and I stand by my humanity.

[jayarjo]

@3n-mb no man, you over complicate. And apparently not only in tech. I described my case in quite a detail. Not sure what to add. I doubt that implementation will help to add any context.