Typescript Definition

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.

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.

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.

@spion

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;

}

@3n-mb

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

@spion

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

}

@spion

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

@3n-mb

@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

@spion

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

@3n-mb

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

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

@Strate

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

@spion

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>;

}

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.

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 :)

@spion

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

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)

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

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?

@3n-mb

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

@jayarjo

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

@3n-mb

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

@jayarjo

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

@3n-mb

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

@jayarjo

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

@jayarjo

@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

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

@jayarjo

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

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

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

@3n-mb

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

Navigation Menu

Repository navigation

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

27 remaining items

Metadata

Assignees

Labels

Projects

Milestone

Relationships

Development

Typescript Definition #351

Description

Activity

zenlor commented on Mar 1, 2017

spion commented on Mar 1, 2017

3n-mb commented on Apr 27, 2017

Setting a goal

Hack

Analysis

Asking:

Hacked definitions, first round:

spion commented on Apr 27, 2017

3n-mb commented on Apr 27, 2017

Hacked code with jsType, second round:

3n-mb commented on Apr 27, 2017

spion commented on Apr 28, 2017

3n-mb commented on Apr 28, 2017

spion commented on Apr 28, 2017

Strate commented on Apr 28, 2017

3n-mb commented on Apr 29, 2017

3n-mb commented on Apr 29, 2017

Suggestion for types for column methods

selectDeep question

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

spion commented on Apr 30, 2017

3n-mb commented on Apr 30, 2017

Adding types to update

Suggestion

Sample use

3n-mb commented on Apr 30, 2017

27 remaining items

zenlor commented on Mar 26, 2018

jayarjo commented on Apr 24, 2020

3n-mb commented on Apr 24, 2020

jayarjo commented on Apr 25, 2020

3n-mb commented on Apr 25, 2020

jayarjo commented on Apr 26, 2020

3n-mb commented on Apr 26, 2020

jayarjo commented on May 6, 2020

3n-mb commented on May 6, 2020

zenlor commented on May 9, 2020

jayarjo commented on May 9, 2020

3n-mb commented on May 9, 2020

jayarjo commented on May 10, 2020

3n-mb commented on May 10, 2020

jayarjo commented on May 11, 2020

Metadata

Metadata

Assignees

Labels

Projects

Milestone

Relationships

Development

Issue actions