Repository: knex/knex
Pull request #4576 (2021-07-16 22:34:08)

nguymin4/improve types
- Improve type support for analytic functions (See tests for more detail)
- Extract common interfaces for tsd

Important note: I intended to migrate analytic-function-types testing to tsd but I found a problem with the tsd library
For example
```typescript
  expectType<Pick<User, 'id'>>(
    await knex<User>('users').select('foo')
  );
```
I expect this test fails but the test somehow passes. I think this is a tsd issue. Thus we need to be more careful before migrating from `dtslint` to `tsd`. I will try to debug this if I have time but in my opinion `dtslint` is still doing a good job for us



Patch:
diff --git a/lib/dialects/postgres/schema/pg-columncompiler.js b/lib/dialects/postgres/schema/pg-columncompiler.js
index a059e9e4..459b9093 100644
--- a/lib/dialects/postgres/schema/pg-columncompiler.js
+++ b/lib/dialects/postgres/schema/pg-columncompiler.js
@@ -71,10 +71,10 @@ class ColumnCompiler_PG extends ColumnCompiler {
     } else {
       useTz = !withoutTz;
     }
+    useTz = typeof useTz === 'boolean' ? useTz : true;
+    precision = precision ? '(' + precision + ')' : '';
 
-    return `${useTz ? 'timestamptz' : 'timestamp'}${
-      precision ? '(' + precision + ')' : ''
-    }`;
+    return `${useTz ? 'timestamptz' : 'timestamp'}${precision}`;
   }
 
   timestamp(withoutTz = false, precision) {
@@ -84,10 +84,10 @@ class ColumnCompiler_PG extends ColumnCompiler {
     } else {
       useTz = !withoutTz;
     }
+    useTz = typeof useTz === 'boolean' ? useTz : true;
+    precision = precision ? '(' + precision + ')' : '';
 
-    return `${useTz ? 'timestamptz' : 'timestamp'}${
-      precision ? '(' + precision + ')' : ''
-    }`;
+    return `${useTz ? 'timestamptz' : 'timestamp'}${precision}`;
   }
 
   // Modifiers:
diff --git a/types/index.d.ts b/types/index.d.ts
index 35628002..88a8e097 100644
--- a/types/index.d.ts
+++ b/types/index.d.ts
@@ -1464,8 +1464,15 @@ export declare namespace Knex {
   }
 
   interface AnalyticFunction<TRecord = any, TResult = unknown[]> {
-    <TResult2 = AggregationQueryResult<TResult, Dict<number>>>(alias: string, raw: Raw | QueryCallback<TRecord, TResult>): QueryBuilder<TRecord, TResult2>;
-    <TResult2 = AggregationQueryResult<TResult, Dict<number>>>(alias: string, orderBy: string | string[], partitionBy?: string | string[]): QueryBuilder<
+    <
+      TAlias extends string,
+      TResult2 = AggregationQueryResult<TResult, {[x in TAlias]: number}>
+    >(alias: TAlias, raw: Raw | QueryCallback<TRecord, TResult>): QueryBuilder<TRecord, TResult2>;
+    <
+      TAlias extends string,
+      TKey extends keyof ResolveTableType<TRecord>,
+      TResult2 = AggregationQueryResult<TResult, {[x in TAlias]: number}>
+    >(alias: TAlias, orderBy: TKey | TKey[], partitionBy?: TKey | TKey[]): QueryBuilder<
       TRecord,
       TResult2
     >;


Test patch:
diff --git a/test-tsd/common.ts b/test-tsd/common.ts
index 8e819d77..625912fb 100644
--- a/test-tsd/common.ts
+++ b/test-tsd/common.ts
@@ -6,3 +6,31 @@ export const clientConfig: Knex.Config = {
     filename: './mydb.sqlite',
   },
 };
+
+export interface User {
+  id: number;
+  age: number;
+  name: string;
+  active: boolean;
+  departmentId: number;
+}
+
+export interface Department {
+  id: number;
+  departmentName: string;
+}
+
+export interface Article {
+  id: number;
+  subject: string;
+  body?: string;
+  authorId?: string;
+}
+
+export interface Ticket {
+  name: string;
+  from: string;
+  to: string;
+  at: Date;
+}
+
diff --git a/test-tsd/select.test-d.ts b/test-tsd/select.test-d.ts
index e59f0d25..71cd274b 100644
--- a/test-tsd/select.test-d.ts
+++ b/test-tsd/select.test-d.ts
@@ -1,17 +1,9 @@
 import Knex from '../types';
-import { clientConfig } from './common';
+import { clientConfig, User } from './common';
 import { expectType } from 'tsd';
 
 const knex = Knex(clientConfig);
 
-interface User {
-  id: number;
-  age: number;
-  name: string;
-  active: boolean;
-  departmentId: number;
-}
-
 const main = async () => {
   expectType<any[]>(await knex('users').select('id').select('age'));
 
diff --git a/test-tsd/tables.test-d.ts b/test-tsd/tables.test-d.ts
index dcdefb51..7c3f8b4e 100644
--- a/test-tsd/tables.test-d.ts
+++ b/test-tsd/tables.test-d.ts
@@ -1,36 +1,9 @@
 import { knex, Knex } from '../types';
-import { clientConfig } from './common';
+import { clientConfig, User, Department, Article } from './common';
 import { expectType } from 'tsd';
 
 const knexInstance = knex(clientConfig);
 
-interface User {
-  id: number;
-  age: number;
-  name: string;
-  active: boolean;
-  departmentId: number;
-}
-
-interface Department {
-  id: number;
-  departmentName: string;
-}
-
-interface Article {
-  id: number;
-  subject: string;
-  body?: string;
-  authorId?: string;
-}
-
-interface Ticket {
-  name: string;
-  from: string;
-  to: string;
-  at: Date;
-}
-
 declare module '../types/tables' {
   interface Tables {
     users_inferred: User;
diff --git a/test-tsd/transaction.test-d.ts b/test-tsd/transaction.test-d.ts
index dec50d87..4ad999a7 100644
--- a/test-tsd/transaction.test-d.ts
+++ b/test-tsd/transaction.test-d.ts
@@ -1,16 +1,9 @@
 import knexDefault, { Knex } from '../types';
-import { clientConfig } from './common';
+import { clientConfig, Article } from './common';
 import { expectType } from 'tsd';
 
 const knexInstance = knexDefault(clientConfig);
 
-interface Article {
-  id: number;
-  subject: string;
-  body?: string;
-  authorId?: string;
-}
-
 const main = async () => {
   // # Select:
 
diff --git a/test-tsd/types.test-d.ts b/test-tsd/types.test-d.ts
index c7e0c96a..41dcaeb1 100644
--- a/test-tsd/types.test-d.ts
+++ b/test-tsd/types.test-d.ts
@@ -1,5 +1,5 @@
 import { expectAssignable, expectType } from 'tsd';
-import { clientConfig } from './common';
+import { clientConfig, User } from './common';
 
 import knexDefault, { Knex, knex } from '../types';
 import * as knexStar from '../types';
@@ -61,14 +61,6 @@ type DeferredKeySelection<
   _unionProps: TUnionProps;
 };
 
-interface User {
-  id: number;
-  age: number;
-  name: string;
-  active: boolean;
-  departmentId: number;
-}
-
 // # Insert onConflict
 expectType<
   QueryBuilder<
diff --git a/test/unit/schema-builder/postgres.js b/test/unit/schema-builder/postgres.js
index 8d2252cb..1e9040ab 100644
--- a/test/unit/schema-builder/postgres.js
+++ b/test/unit/schema-builder/postgres.js
@@ -1155,6 +1155,19 @@ describe('PostgreSQL SchemaBuilder', function () {
     );
   });
 
+  it('adding default datetime', () => {
+    tableSql = client
+      .schemaBuilder()
+      .table('users', (table) => {
+        table.datetime('foo');
+      })
+      .toSQL();
+    equal(1, tableSql.length);
+    expect(tableSql[0].sql).to.equal(
+      'alter table "users" add column "foo" timestamptz'
+    );
+  });
+
   it('adding timestamp with timezone', () => {
     tableSql = client
       .schemaBuilder()
@@ -1168,6 +1181,19 @@ describe('PostgreSQL SchemaBuilder', function () {
     );
   });
 
+  it('adding datetime with timezone', () => {
+    tableSql = client
+      .schemaBuilder()
+      .table('users', (table) => {
+        table.datetime('foo', false);
+      })
+      .toSQL();
+    equal(1, tableSql.length);
+    expect(tableSql[0].sql).to.equal(
+      'alter table "users" add column "foo" timestamptz'
+    );
+  });
+
   it('adding timestamp without timezone', () => {
     tableSql = client
       .schemaBuilder()
@@ -1181,6 +1207,19 @@ describe('PostgreSQL SchemaBuilder', function () {
     );
   });
 
+  it('adding datetime without timezone', () => {
+    tableSql = client
+      .schemaBuilder()
+      .table('users', (table) => {
+        table.datetime('foo', true);
+      })
+      .toSQL();
+    equal(1, tableSql.length);
+    expect(tableSql[0].sql).to.equal(
+      'alter table "users" add column "foo" timestamp'
+    );
+  });
+
   it('adding timestamp with precision', () => {
     tableSql = client
       .schemaBuilder()
@@ -1194,6 +1233,19 @@ describe('PostgreSQL SchemaBuilder', function () {
     );
   });
 
+  it('adding datetime with precision', () => {
+    tableSql = client
+      .schemaBuilder()
+      .table('users', (table) => {
+        table.datetime('foo', undefined, 3);
+      })
+      .toSQL();
+    equal(1, tableSql.length);
+    expect(tableSql[0].sql).to.equal(
+      'alter table "users" add column "foo" timestamptz(3)'
+    );
+  });
+
   it('adding timestamp with options object', () => {
     tableSql = client
       .schemaBuilder()
@@ -1207,6 +1259,32 @@ describe('PostgreSQL SchemaBuilder', function () {
     );
   });
 
+  it('adding timestamp with options object but no timestamp', () => {
+    tableSql = client
+      .schemaBuilder()
+      .table('users', (table) => {
+        table.timestamp('foo', { precision: 3 });
+      })
+      .toSQL();
+    equal(1, tableSql.length);
+    expect(tableSql[0].sql).to.equal(
+      'alter table "users" add column "foo" timestamptz(3)'
+    );
+  });
+
+  it('adding timestamp with empty options object', () => {
+    tableSql = client
+      .schemaBuilder()
+      .table('users', (table) => {
+        table.timestamp('foo', {});
+      })
+      .toSQL();
+    equal(1, tableSql.length);
+    expect(tableSql[0].sql).to.equal(
+      'alter table "users" add column "foo" timestamptz'
+    );
+  });
+
   it('adding datetime with options object', () => {
     tableSql = client
       .schemaBuilder()
@@ -1220,6 +1298,32 @@ describe('PostgreSQL SchemaBuilder', function () {
     );
   });
 
+  it('adding datetime with options object but no timestamp', () => {
+    tableSql = client
+      .schemaBuilder()
+      .table('users', (table) => {
+        table.datetime('foo', { precision: 3 });
+      })
+      .toSQL();
+    equal(1, tableSql.length);
+    expect(tableSql[0].sql).to.equal(
+      'alter table "users" add column "foo" timestamptz(3)'
+    );
+  });
+
+  it('adding datetime with empty options object', () => {
+    tableSql = client
+      .schemaBuilder()
+      .table('users', (table) => {
+        table.datetime('foo', {});
+      })
+      .toSQL();
+    equal(1, tableSql.length);
+    expect(tableSql[0].sql).to.equal(
+      'alter table "users" add column "foo" timestamptz'
+    );
+  });
+
   it('adding timestamps', () => {
     tableSql = client
       .schemaBuilder()
diff --git a/types/test.ts b/types/test.ts
index eada0847..b7869ceb 100644
--- a/types/test.ts
+++ b/types/test.ts
@@ -1282,24 +1282,34 @@ const main = async () => {
     .from('users_composite');
 
   // Analytic
-  // $ExpectType (Pick<User, "age"> & Dict<number>)[]
+  // $ExpectType (Pick<User, "age"> & { rowNum: number; })[]
   await knexInstance<User>('users').select('age').rowNumber('rowNum', 'age');
 
-  // $ExpectType (Pick<User, "age"> & Dict<number>)[]
+  await knexInstance<User>('users')
+    .select('age')
+    // $ExpectError
+    .rowNumber('rowNum', 'non_existing_field');
+
+  // $ExpectType (Pick<User, "age"> & { rowNum: number; })[]
   await knexInstance<User>('users').select('age').rowNumber('rowNum', ['age']);
 
-  // $ExpectType (Pick<User, "age"> & Dict<number>)[]
+  // $ExpectType (Pick<User, "age"> & { rowNum: number; })[]
   await knexInstance<User>('users').select('age').rowNumber('rowNum', (builder) => {
     builder.orderBy('age');
   });
 
-  // $ExpectType (Pick<User, "age"> & Dict<number>)[]
+  // $ExpectType (Pick<User, "age"> & { rowNum: number; })[]
   await knexInstance<User>('users').select('age').rowNumber('rowNum', 'age', 'departmentId');
 
-  // $ExpectType (Pick<User, "age"> & Dict<number>)[]
+  await knexInstance<User>('users')
+    .select('age')
+    // $ExpectError
+    .rowNumber('rowNum', 'age', 'non_existing_field');
+
+  // $ExpectType (Pick<User, "age"> & { rowNum: number; })[]
   await knexInstance<User>('users').select('age').rowNumber('rowNum', 'age', ['departmentId', 'active']);
 
-  // $ExpectType (Pick<User, "age"> & Dict<number>)[]
+  // $ExpectType (Pick<User, "age"> & { rowNum: number; })[]
   await knexInstance<User>('users').select('age').rowNumber('rowNum', (builder) => {
     builder.orderBy('age').partitionBy('departmentId');
   });