Repository: knex/knex
Pull request #4327 (2021-03-02 01:44:26)

Adding support for `pg-native`
👋 Hello! 

Thanks for all the work on this project, it has been a great help to our team.

Based on feedback from @tgriesser and #4062, I am submitting a small PR to allow for `pg-native` to be used. 

# Problem
Due to problems with the non-native driver holding open connections longer than necessary, and those in [this thread](https://github.com/brianc/node-postgres/issues/1993), my team and I need to adopt the `pg-native` library. In production the difference is very noticeable, where our pool gets drained much quicker than it should and starts causing issues at scale (connections timing out, locking issues, session issues in AWS RDS). 

# Description
Based on feedback, I've created a new `pgnative` dialect that descends from the `postgresql` dialect, but loads in the native bindings and will use the updated callback signature for `client.connect` that is compatible with both the `node-postgres` and `pg-native` bindings.

Installing `pg-native` module in a project, then selecting this dialect will allow this strategy to work. It has the same configuration object as the normal `postgresql` dialect.

# Example Configuration
```javascript

const pg = require('knex')({
  client: 'pgnative',
  connection: process.env.PG_CONNECTION_STRING,
  searchPath: ['knex', 'public'],
});
```

# Old Description Before Feedback - Discard, kept for posterity.
This PR allows for the `pg-native` library to be utilized in place of the standard `pg` bindings if either the `NODE_PG_FORCE_NATIVE` env var is set, or if the `useNative` config is set to `true` when passed into the strategy.

## Example Configuration

```javascript
const pg = require('knex')({
  client: 'pg',
  connection: process.env.PG_CONNECTION_STRING,
  searchPath: ['knex', 'public'],
  useNative: true
});
```

# Note
This is my first PR to this code base so I am assuming not everything is ready to go. I've added an integration test configuration in a way that may not be ideal - I'm happy to tweak it to fit the situation better.

machuga: Noting Node 10 integration build failed due to pull request rate limit being hit. Hopefully it should pass after rate limits let off
elhigu: I would rather see this implemented as a separate npm module (in contributing.md there are instructions how to make dialects that are not in knex core), which contains also better tests checking that it works.

Otherwise this will start burdening knex more with bug reports, when people start using it and thinks that everything will work the same  between pg / pg-native variants. That repository then will have some other people maintaining it and will be able to fix pg-native related issues of their side.
machuga: Thanks for the quick response! 

If we profile the standard `node-postgres` bindings, we find that we have extended `ClientRead` sections in our monitoring. It's similar to here: https://github.com/brianc/node-postgres/issues/1774. Our nodes are already in the same data center, so the one comment in the thread doesn't apply. @brianc acknowledges it is a bug that he hasn't been able to track down yet, and many report success with moving to `pg-native` bindings.

When compared to our older service using a different stack to the same database (we are migrating away from this stack to the new stack with `knex`), the queries do not exhibit the same behavior.

When high quantities of writes happen concurrently that is when we see this issue crop up most notably.
elhigu: I do acknowledge the original issue and see why you like to use the pg-native. Though a clear testcase which demonstrates the problem (I know that brianc was able to somewhat demonstrate it by himself according to the linked issue) would probably be really useful. Even some docker based stress test example. 

Though all the above is mostly irrelevant since the main point of my last comment was that:

Is there any reason why wouldn't you do this in a separate npm module and provide maintenance for it? It can be even published under knex organization in github if you wish.

This will start burdening knex maintainers in case if it is added to core and I don't believe that need for this is going to remain forever, which would also make it more suitable to reside in a separate npm package with own maintainers.
kibertoad: To be fair, we are not getting additional burden due to supporting `mysql` and `mysql2`, I can't remember a single case when we had to fix something specific to just one of them, and didn't have an issue that affected both in shared code.

If `pg-native` can be supported in a similar way as a completely drop-in replacement for `pg` that would pass all same tests, I don't see a reason not to include it.
machuga: Thanks for the guidance!

Yep, I can totally understand and appreciate the maintenance burden. A module would likely be sufficient - we've been running from a fork. I'll can look into it today.

I think some of the changes may want to be considered regardless if I have read the `node-postgres` docs correctly. The callback that I modified in https://github.com/knex/knex/pull/4327/files#diff-1fc6054df84862e1c3a2f9469a7a36e03d8b4afc522df61dc1ba48153b613598R82 looks to currently be defined in `node-postgres` as:

```typescript
client.connect(callback: (err: Error) => void) => void
```

and this is also the same in `pg-native`.

Since the default `postgresql` strategy in `knex` uses `node-postgres`, the current use in trunk of:

```javascript
client.connect(function (err, connection)
``` 
looks to be on a deprecated callback signature. Removing `connection` and favoring the client created with 

```javascript
const clientConnection = new client.driver.Client(client.connectionSettings);
```
seems like it would match their newest guidelines. I could 100% be reading some of this wrong, but that's what it seemed like to me on my initial digging.

From there I figured it was a small leap to load in the native driver since the major blocker (connection syntax) was removed.

elhigu: >To be fair, we are not getting additional burden due to supporting mysql and mysql2, I can't remember a single case when we had to fix something specific to just one of them, and didn't have an issue that affected both in shared code.

I actually have resolved for example pooling issues, where mysql2 was a bit unstable not recognizing broken connections and probably with pg most of the open issues will be about streaming support and I don't think that mysql2 version should be supported be us either :)

When things are fixed in pg diealect, they still would be automatically fixed to external `pg-native` module, because it would be inherited from knex's `pg` dialect where all the fixes are.

machuga: @elhigu Would it be acceptable for me to reduce my PR down to just adjusting the callback signature + remove the `usePgNative` flag? This would make the function signature compatible between the `pg-native` / `pg` versions while also aligning it to the documentation. This has the benefit of not explicitly calling out support for `pg-native` in `knex` which should prevent maintenance burden from growing. The PR would simply not prohibit the use of `pg-native` via the environment variable being included.
elhigu: To me either way is the same. Both will add more features to knex core, when I would like to push knex to have less features in core package and more pushed to additional modules. 

If the community wants it in anyways, I suppose having the configure flag for it or implementing it as a completely separate dialect like mysql2 is better.
machuga: @elhigu Got it. So what are next steps? Should this PR be closed? Should we wait for additional community input?
kibertoad: @machuga I would say that if you can rework the PR in a way that follows the `mysql`/`mysql2` style and is covered by CI, this PR can carry on. It's not even a new dialect per se, just exposing more of the driver we already use. And we've been relying on community contributions for the majority of fixes lately anyway.
machuga: Thanks @kibertoad! I'll get started down that path soon.
machuga: Alright the code has been updated to use a separate driver. I'm still looking around a bit to see if I missed any other places this code should be referenced, but I think it is fairly complete.
kibertoad: @machuga https://github.com/knex/knex/blob/master/.github/workflows/coverage.yml also needs to be updated.
Also we have a problem here, as `https://github.com/knex/knex/blob/master/.github/workflows/integration-tests.yml` does not execute pgnative currently; considering that we match db name to docker entry there, might be a bit of a problem; probably easiest solution would be to simply duplicate `pg` docker-compose entry as `pgnative` in `https://github.com/knex/knex/blob/master/scripts/docker-compose.yml`
kibertoad: Also documentation at https://github.com/knex/documentation needs to be updated
kibertoad: Also there is a bunch of `if (knex.client.driverName === 'pg') {` still left in the code. Could you replace them with `isPostgreSQL` check?
machuga: Thanks for the guidance! You've already seen it, but for others: I have separated this out to #4356 to keep the extra helpers clean up separate from the `pg-native` work.
machuga: > @machuga https://github.com/knex/knex/blob/master/.github/workflows/coverage.yml also needs to be updated.
> Also we have a problem here, as https://github.com/knex/knex/blob/master/.github/workflows/integration-tests.yml does not execute pgnative currently; considering that we match db name to docker entry there, might be a bit of a problem; probably easiest solution would be to simply duplicate `pg` docker-compose entry as `pgnative` in `https://github.com/knex/knex/blob/master/scripts/docker-compose.yml`

@kibertoad Got it! I'll get these updated. I noticed that `mysql2` can be passed in via `DB`, but the `integration-tests.yml` file doesn't specify loading an independent DB. Is that because mysql2 runs primarily under the `integration2` suite? I was considering loading it up how mysql/mysql2 would load for consistency sake.
However, your way of creating a separate docker-compose entry may make the most sense if the tests would use the same container instance so we could avoid conflicting rows/flaky tests should they be run in parallel. If they use independent instances or will never run in parallel then we can ignore that situation. 
machuga: Should be able to come back to this this weekend. Thanks for your patience!
machuga: Going back to some of the earlier comments, the streaming may not work, but I am looking into it.
kibertoad: @machuga Can I help you with finishing this?
machuga: Hi @kibertoad! Sure. I think I'll have a few questions regarding which feature I should disallow and how to reject them according to conventions. Thanks for reminding me to get back on it.
machuga: @kibertoad I had a design question pop up. The `pg` library returns error messages in the format `error: TestError - syntax error at or near "TestError"`, while `pg-native` returns them as `Error: TestError - syntax error at or near "TestError"`. In the tests I see it pop up, but I don't believe it'll interfere with user-based usage. Do you have any opinions?

Example: https://github.com/knex/knex/pull/4327/files#diff-ef380392f7c99984959538380cd3a01631d49da7eb768b02fd71695bfb2a3febR1123-R1129

The `name` here differs between the previously mentioned `error` or `Error`
kibertoad: @machuga I don't think we should be tinkering with driver output here, generally we prefer to stay out of driver's way. Case-insensitivity sounds like a better approach.
machuga: @kibertoad I've opted for a faked-out stream (it will stream the results after they're fetched in order to keep the same interface as other drivers. 

I have four timeout failures happening right now with the pg-native driver that I need to debug. I'm not sure the timeout support is available in pg-native yet so I need to verify.
kibertoad: Awesome, thank you for the update!
machuga: Query cancellation in pgnative must be handled via the `cancel` function on the `pgnative`/`pg` wrapper; `processID` odes not exist on queries returned from `pgnative`. This behavior needs to step into the `pgnative` implementation directly, skipping the wrapper, because the wrapper will not expose errors.

On clean runs this normally works, but there a few issues:

1. Somewhere, in the `additional` tests file there is a resource (or multiple) not being cleaned up.
2. The `forShare()` test in `selects` tests file hangs due to the timeout implementation. Sometimes it completes, sometimes it doesn't

Trying to work around it.
kibertoad: Our notoriously flaky test is failing, any chance you could take a look at it?
machuga: > Our notoriously flaky test is failing, any chance you could take a look at it?

Able to reproduce on my machine if I run it in isolation.

```javascript
 Integration Tests
    postgresql | pgnative
NOTICE:  table "migration_test_1" does not exist, skipping
NOTICE:  table "migration_test_2" does not exist, skipping
NOTICE:  table "migration_test_2_1" does not exist, skipping
      knex.migrate.latest in parallel
Error: create table "knex_migrations" ("id" serial primary key, "name" varchar(255), "batch" integer, "migration_time" timestamptz) - duplicate key value violates unique constraint "pg_type_typname_nsp_index"
    at module.exports.Client._emitResult (./src/knex/node_modules/pg-native/index.js:173:26)
    at module.exports.Client._read (./src/knex/node_modules/pg-native/index.js:215:31)
    at PQ.emit (node:events:365:28) {
  severity: 'ERROR',
  code: '23505',
  messageDetail: 'Key (typname, typnamespace)=(knex_migrations_id_seq, 2200) already exists.',
  schema: 'pg_catalog',
  table: 'pg_type',
  constraint: 'pg_type_typname_nsp_index',
  file: 'nbtinsert.c',
  line: '649',
  routine: '_bt_check_unique'
}
[
  1,
  [ '20131019235242_migration_1.js', '20131019235306_migration_2.js' ]
]

```
This is what I get from the promise results in this area. Digging in a bit further.
machuga: Okay the error that came up was not the same, looked like it was something triggered by running in isolation only. Tests passing now 👍 

Patch:
diff --git a/.github/workflows/coverage.yml b/.github/workflows/coverage.yml
index d2792397..e2e3481d 100644
--- a/.github/workflows/coverage.yml
+++ b/.github/workflows/coverage.yml
@@ -39,7 +39,7 @@ jobs:
         run: npm run test:coverage
         env:
           CI: true
-          DB: "postgres mysql mysql2 mssql sqlite3"
+          DB: "postgres pgnative mysql mysql2 mssql sqlite3"
           KNEX_TEST_TIMEOUT: 60000
 
       - name: Stop Databases
diff --git a/lib/constants.js b/lib/constants.js
index 44cca024..0b9e06f7 100644
--- a/lib/constants.js
+++ b/lib/constants.js
@@ -12,6 +12,7 @@ const SUPPORTED_CLIENTS = Object.freeze(
     'mysql2',
     'oracledb',
     'postgres',
+    'pgnative',
     'redshift',
     'sqlite3',
   ].concat(Object.keys(CLIENT_ALIASES))
@@ -23,6 +24,7 @@ const DRIVER_NAMES = Object.freeze({
   MySQL2: 'mysql2',
   Oracle: 'oracledb',
   PostgreSQL: 'pg',
+  PgNative: 'pgnative',
   Redshift: 'pg-redshift',
   SQLite: 'sqlite3',
 });
diff --git a/lib/dialects/pgnative/index.js b/lib/dialects/pgnative/index.js
new file mode 100644
index 00000000..4c0fb41b
--- /dev/null
+++ b/lib/dialects/pgnative/index.js
@@ -0,0 +1,59 @@
+// PostgreSQL Native Driver (pg-native)
+// -------
+const Client_PG = require('../postgres');
+
+class Client_PgNative extends Client_PG {
+  constructor(...args) {
+    super(...args);
+    this.driverName = 'pgnative';
+    this.canCancelQuery = true;
+  }
+
+  _driver() {
+    return require('pg').native;
+  }
+
+  _stream(connection, obj, stream, options) {
+    if (!obj.sql) throw new Error('The query is empty');
+
+    const client = this;
+    return new Promise((resolver, rejecter) => {
+      stream.on('error', rejecter);
+      stream.on('end', resolver);
+
+      return client
+        ._query(connection, obj)
+        .then((obj) => obj.response)
+        .then(({ rows }) => rows.forEach((row) => stream.write(row)))
+        .catch(function (err) {
+          stream.emit('error', err);
+        })
+        .then(function () {
+          stream.end();
+        });
+    });
+  }
+
+  async cancelQuery(connectionToKill) {
+    try {
+      return await this._wrappedCancelQueryCall(null, connectionToKill);
+    } catch (err) {
+      this.logger.warn(`Connection Error: ${err}`);
+    }
+  }
+
+  _wrappedCancelQueryCall(emptyConnection, connectionToKill) {
+    return new Promise(function (resolve, reject) {
+      connectionToKill.native.cancel(function (err) {
+        if (err) {
+          reject(err);
+          return;
+        }
+
+        resolve(true);
+      });
+    });
+  }
+}
+
+module.exports = Client_PgNative;
diff --git a/lib/dialects/postgres/index.js b/lib/dialects/postgres/index.js
index 5d5a9066..047299be 100644
--- a/lib/dialects/postgres/index.js
+++ b/lib/dialects/postgres/index.js
@@ -62,34 +62,40 @@ class Client_PG extends Client {
     return `"${value.replace(/"/g, '""')}"${arrayAccessor}`;
   }
 
+  _acquireOnlyConnection() {
+    const connection = new this.driver.Client(this.connectionSettings);
+
+    return connection.connect().then(() => connection);
+  }
+
   // Get a raw connection, called by the `pool` whenever a new
   // connection needs to be added to the pool.
   acquireRawConnection() {
     const client = this;
-    return new Promise(function (resolver, rejecter) {
-      const connection = new client.driver.Client(client.connectionSettings);
-      connection.connect(function (err, connection) {
-        if (err) {
-          return rejecter(err);
-        }
+
+    return this._acquireOnlyConnection()
+      .then(function (connection) {
         connection.on('error', (err) => {
           connection.__knex__disposed = err;
         });
+
         connection.on('end', (err) => {
           connection.__knex__disposed = err || 'Connection ended unexpectedly';
         });
+
         if (!client.version) {
           return client.checkVersion(connection).then(function (version) {
             client.version = version;
-            resolver(connection);
+            return connection;
           });
         }
-        resolver(connection);
+
+        return connection;
+      })
+      .then(function setSearchPath(connection) {
+        client.setSchemaSearchPath(connection);
+        return connection;
       });
-    }).then(function setSearchPath(connection) {
-      client.setSchemaSearchPath(connection);
-      return connection;
-    });
   }
 
   // Used to explicitly close a connection, called internally by the pool
diff --git a/package.json b/package.json
index a652d585..1258dd8f 100644
--- a/package.json
+++ b/package.json
@@ -29,6 +29,7 @@
     "test:oracledb": "cross-env DB=oracledb npm run test:db",
     "test:sqlite": "cross-env DB=sqlite3 npm run test:db",
     "test:postgres": "cross-env DB=postgres npm run test:db",
+    "test:pgnative": "cross-env DB=pgnative npm run test:db",
     "test:tape": "node test/tape/index.js | tap-spec",
     "test:cli": "cross-env KNEX_PATH=../knex.js KNEX=bin/cli.js jake -f test/jake/Jakefile",
     "db:start": "docker-compose -f scripts/docker-compose.yml up --build -d mysql oracledbxe postgres mssql && docker-compose -f scripts/docker-compose.yml up waitmssql waitmysql waitpostgres waitoracledbxe",
@@ -36,6 +37,8 @@
     "db:stop": "docker-compose -f scripts/docker-compose.yml down",
     "db:start:postgres": "docker-compose -f scripts/docker-compose.yml up --build -d postgres && docker-compose -f scripts/docker-compose.yml up waitpostgres",
     "db:stop:postgres": "docker-compose -f scripts/docker-compose.yml down",
+    "db:start:pgnative": "docker-compose -f scripts/docker-compose.yml up --build -d pgnative && docker-compose -f scripts/docker-compose.yml up waitpgnative",
+    "db:stop:pgnative": "docker-compose -f scripts/docker-compose.yml down",
     "db:start:mysql": "docker-compose -f scripts/docker-compose.yml up --build -d mysql && docker-compose -f scripts/docker-compose.yml up waitmysql",
     "db:stop:mysql": "docker-compose -f scripts/docker-compose.yml down",
     "db:start:oracle": "docker-compose -f scripts/docker-compose.yml up --build -d oracledbxe && docker-compose -f scripts/docker-compose.yml up waitoracledbxe",
@@ -72,6 +75,9 @@
     "pg": {
       "optional": true
     },
+    "pg-native": {
+      "optional": true
+    },
     "sqlite3": {
       "optional": true
     }
@@ -105,6 +111,7 @@
     "nyc": "^15.1.0",
     "oracledb": "^5.2.0",
     "pg": "^8.7.1",
+    "pg-native": "^3.0.0",
     "pg-query-stream": "^4.2.1",
     "prettier": "2.3.2",
     "rimraf": "^3.0.2",
@@ -177,6 +184,7 @@
     "mysql": false,
     "mysql2": false,
     "pg": false,
+    "pg-native": false,
     "pg-query-stream": false,
     "oracle": false,
     "sqlite3": false,
diff --git a/scripts/docker-compose.yml b/scripts/docker-compose.yml
index 8be9cc73..c79a7933 100644
--- a/scripts/docker-compose.yml
+++ b/scripts/docker-compose.yml
@@ -84,6 +84,27 @@ services:
       - -c
       - 'until /usr/local/bin/psql postgres://testuser:knextest@postgres/knex_test -c "SELECT 1"; do sleep 5; done'
 
+  pgnative:
+    image: postgres:alpine
+    # see https://www.postgresql.org/docs/current/non-durability.html
+    command: '-c full_page_writes=off -c fsync=off -c synchronous_commit=off'
+    ports:
+      - '25433:5432'
+    environment:
+      - POSTGRES_USER=testuser
+      - POSTGRES_PASSWORD=knextest
+      - POSTGRES_DB=knex_test
+  waitpgnative:
+    image: postgres:alpine
+    links:
+      - pgnative
+    depends_on:
+      - pgnative
+    entrypoint:
+      - bash
+      - -c
+      - 'until /usr/local/bin/psql postgres://testuser:knextest@pgnative/knex_test -c "SELECT 1"; do sleep 5; done'
+
   oracledbxe:
     image: quillbuilduser/oracle-18-xe
     container_name: oracledbxe_container


Test patch:
diff --git a/.github/workflows/integration-tests.yml b/.github/workflows/integration-tests.yml
index 452a181c..40492ea4 100644
--- a/.github/workflows/integration-tests.yml
+++ b/.github/workflows/integration-tests.yml
@@ -19,7 +19,7 @@ jobs:
       fail-fast: false
       matrix:
         node-version: [16.x, 14.x, 12.x, 10.x]
-        database-type: [postgres, mysql, mssql, sqlite3]
+        database-type: [postgres, pgnative, mysql, mssql, sqlite3]
 
     steps:
       - name: Checkout Repository
diff --git a/scripts/stress-test/docker-compose.yml b/scripts/stress-test/docker-compose.yml
index 274e72d3..f384b164 100644
--- a/scripts/stress-test/docker-compose.yml
+++ b/scripts/stress-test/docker-compose.yml
@@ -7,11 +7,13 @@ services:
       - "8474:8474"
       - "23306:23306"
       - "25432:25432"
+      - "25433:25433"
       - "21521:21521"
       - "21433:21433"
     links:
       - "mysql"
       - "postgresql"
+      - "pgnative"
       - "oracledbxe"
       - "mssql"
 
@@ -31,6 +33,14 @@ services:
       - POSTGRES_PASSWORD=postgresrootpassword
       - POSTGRES_USER=postgres
 
+  pgnative:
+    image: mdillon/postgis
+    ports:
+      - "35433:5432"
+    environment:
+      - POSTGRES_PASSWORD=postgresrootpassword
+      - POSTGRES_USER=postgres
+
   oracledbxe:
     image: wnameless/oracle-xe-11g
     ports:
diff --git a/scripts/stress-test/knex-stress-test.js b/scripts/stress-test/knex-stress-test.js
index 2b8ff4cd..07586d08 100644
--- a/scripts/stress-test/knex-stress-test.js
+++ b/scripts/stress-test/knex-stress-test.js
@@ -13,6 +13,13 @@ const pg = Knex({
   pool: { max: 50 },
 });
 
+const pgnative = Knex({
+  client: 'pgnative',
+  connection:
+    'postgres://postgres:postgresrootpassword@localhost:25433/postgres',
+  pool: { max: 50 },
+});
+
 const mysql2 = Knex({
   client: 'mysql2',
   connection:
@@ -91,10 +98,10 @@ setInterval(() => {
   lastCounters = _.cloneDeep(counters);
 }, 2000);
 
-async function killConnectionsPg() {
-  return pg.raw(`SELECT pg_terminate_backend(pg_stat_activity.pid) 
+async function killConnectionsPg(client) {
+  return client.raw(`SELECT pg_terminate_backend(pg_stat_activity.pid)
     FROM pg_stat_activity
-    WHERE pg_stat_activity.datname = 'postgres' 
+    WHERE pg_stat_activity.datname = 'postgres'
       AND pid <> pg_backend_pid()`);
 }
 
@@ -154,6 +161,7 @@ async function main() {
   // create TCP proxies for simulating bad connections etc.
   async function recreateProxies() {
     await recreateProxy('postgresql', 25432, 5432);
+    await recreateProxy('postgresql', 25433, 5433);
     await recreateProxy('mysql', 23306, 3306);
     await recreateProxy('oracledbxe', 21521, 1521);
     await recreateProxy('mssql', 21433, 1433);
@@ -164,6 +172,9 @@ async function main() {
   loopQueries('PSQL:', pg.raw('select 1'));
   loopQueries('PSQL TO:', pg.raw('select 1').timeout(20));
 
+  loopQueries('PGNATIVE:', pgnative.raw('select 1'));
+  loopQueries('PGNATIVE TO:', pgnative.raw('select 1').timeout(20));
+
   loopQueries('MYSQL:', mysql.raw('select 1'));
   loopQueries('MYSQL TO:', mysql.raw('select 1').timeout(20));
 
@@ -181,7 +192,8 @@ async function main() {
     await delay(20); // kill everything every quite often from server side
     try {
       await Promise.all([
-        killConnectionsPg(),
+        killConnectionsPg(pg),
+        killConnectionsPg(pgnative),
         killConnectionsMyslq(mysql),
         //        killConnectionsMyslq(mysql2),
         killConnectionsMssql(),
diff --git a/test/integration/query/additional.js b/test/integration/query/additional.js
index a4540700..1e117c4a 100644
--- a/test/integration/query/additional.js
+++ b/test/integration/query/additional.js
@@ -14,6 +14,7 @@ const {
   isMysql,
   isMssql,
   isPgBased,
+  isPgNative,
 } = require('../../util/db-helpers');
 const { DRIVER_NAMES: drivers } = require('../../util/constants');
 
@@ -254,6 +255,7 @@ module.exports = function (knex) {
         .testSql(function (tester) {
           tester('mysql', 'truncate `test_table_two`');
           tester('pg', 'truncate "test_table_two" restart identity');
+          tester('pgnative', 'truncate "test_table_two" restart identity');
           tester('pg-redshift', 'truncate "test_table_two"');
           tester('sqlite3', 'delete from `test_table_two`');
           tester('oracledb', 'truncate table "test_table_two"');
@@ -295,6 +297,8 @@ module.exports = function (knex) {
         [drivers.MySQL2]: 'SHOW TABLES',
         [drivers.PostgreSQL]:
           "SELECT table_name FROM information_schema.tables WHERE table_schema='public'",
+        [drivers.PgNative]:
+          "SELECT table_name FROM information_schema.tables WHERE table_schema='public'",
         [drivers.Redshift]:
           "SELECT table_name FROM information_schema.tables WHERE table_schema='public'",
         [drivers.SQLite]: "SELECT name FROM sqlite_master WHERE type='table';",
@@ -363,6 +367,25 @@ module.exports = function (knex) {
               },
             }
           );
+          tester(
+            'pgnative',
+            'select * from information_schema.columns where table_name = ? and table_catalog = current_database() and table_schema = current_schema()',
+            null,
+            {
+              enum_value: {
+                defaultValue: null,
+                maxLength: null,
+                nullable: true,
+                type: 'text',
+              },
+              uuid: {
+                defaultValue: null,
+                maxLength: null,
+                nullable: false,
+                type: 'uuid',
+              },
+            }
+          );
           tester(
             'pg-redshift',
             'select * from information_schema.columns where table_name = ? and table_catalog = ? and table_schema = current_schema()',
@@ -463,6 +486,17 @@ module.exports = function (knex) {
               type: 'uuid',
             }
           );
+          tester(
+            'pgnative',
+            'select * from information_schema.columns where table_name = ? and table_catalog = current_database() and table_schema = current_schema()',
+            null,
+            {
+              defaultValue: null,
+              maxLength: null,
+              nullable: false,
+              type: 'uuid',
+            }
+          );
           tester(
             'pg-redshift',
             'select * from information_schema.columns where table_name = ? and table_catalog = ? and table_schema = current_schema()',
@@ -613,6 +647,9 @@ module.exports = function (knex) {
               tester('pg', [
                 'alter table "accounts" rename "about" to "about_col"',
               ]);
+              tester('pgnative', [
+                'alter table "accounts" rename "about" to "about_col"',
+              ]);
               tester('pg-redshift', [
                 'alter table "accounts" rename "about" to "about_col"',
               ]);
@@ -672,6 +709,9 @@ module.exports = function (knex) {
             .testSql(function (tester) {
               tester('mysql', ['alter table `accounts` drop `first_name`']);
               tester('pg', ['alter table "accounts" drop column "first_name"']);
+              tester('pgnative', [
+                'alter table "accounts" drop column "first_name"',
+              ]);
               tester('pg-redshift', [
                 'alter table "accounts" drop column "first_name"',
               ]);
@@ -724,6 +764,9 @@ module.exports = function (knex) {
         [drivers.PostgreSQL]: function () {
           return knex.raw('SELECT pg_sleep(1)');
         },
+        [drivers.PgNative]: function () {
+          return knex.raw('SELECT pg_sleep(1)');
+        },
         [drivers.MySQL]: function () {
           return knex.raw('SELECT SLEEP(1)');
         },
@@ -776,6 +819,9 @@ module.exports = function (knex) {
         [drivers.PostgreSQL]: function () {
           return knex.raw('SELECT pg_sleep(10)');
         },
+        [drivers.PgNative]: function () {
+          return knex.raw('SELECT pg_sleep(10)');
+        },
         [drivers.MySQL]: function () {
           return knex.raw('SELECT SLEEP(10)');
         },
@@ -813,6 +859,9 @@ module.exports = function (knex) {
         [drivers.PostgreSQL]: function () {
           return knex.raw('SELECT * from pg_stat_activity');
         },
+        [drivers.PgNative]: function () {
+          return knex.raw('SELECT * from pg_stat_activity');
+        },
         [drivers.MySQL]: function () {
           return knex.raw('SHOW PROCESSLIST');
         },
@@ -882,19 +931,22 @@ module.exports = function (knex) {
       // until the first query finishes. Setting a sleep time longer than the
       // mocha timeout exposes this behavior.
       const testQueries = {
-        pg: function () {
+        [drivers.PostgreSQL]: function () {
+          return knex.raw('SELECT pg_sleep(10)');
+        },
+        [drivers.PgNative]: function () {
           return knex.raw('SELECT pg_sleep(10)');
         },
-        mysql: function () {
+        [drivers.MySQL]: function () {
           return knex.raw('SELECT SLEEP(10)');
         },
-        mysql2: function () {
+        [drivers.MySQL2]: function () {
           return knex.raw('SELECT SLEEP(10)');
         },
-        mssql: function () {
+        [drivers.MsSQL]: function () {
           return knex.raw("WAITFOR DELAY '00:00:10'");
         },
-        oracledb: function () {
+        [drivers.Oracle]: function () {
           return knex.raw('begin dbms_lock.sleep(10); end;');
         },
       };
@@ -921,13 +973,16 @@ module.exports = function (knex) {
       }
 
       const getProcessesQueries = {
-        pg: function () {
+        [drivers.PostgreSQL]: function () {
+          return knex.raw('SELECT * from pg_stat_activity');
+        },
+        [drivers.PgNative]: function () {
           return knex.raw('SELECT * from pg_stat_activity');
         },
-        mysql: function () {
+        [drivers.MySQL]: function () {
           return knex.raw('SHOW PROCESSLIST');
         },
-        mysql2: function () {
+        [drivers.MySQL2]: function () {
           return knex.raw('SHOW PROCESSLIST');
         },
       };
@@ -998,6 +1053,9 @@ module.exports = function (knex) {
         [drivers.PostgreSQL]: function () {
           return knexDb.raw('SELECT pg_sleep(10)');
         },
+        [drivers.PgNative]: function () {
+          return knexDb.raw('SELECT pg_sleep(10)');
+        },
         [drivers.MySQL]: function () {
           return knexDb.raw('SELECT SLEEP(10)');
         },
@@ -1021,15 +1079,19 @@ module.exports = function (knex) {
 
       // We must use the original knex instance without the exhausted pool to list running queries
       const getProcessesForDriver = {
-        pg: async () => {
+        [drivers.PostgreSQL]: async () => {
           const results = await knex.raw('SELECT * from pg_stat_activity');
           return _.map(_.filter(results.rows, { state: 'active' }), 'query');
         },
-        mysql: async () => {
+        [drivers.PgNative]: async () => {
+          const results = await knex.raw('SELECT * from pg_stat_activity');
+          return _.map(_.filter(results.rows, { state: 'active' }), 'query');
+        },
+        [drivers.MySQL]: async () => {
           const results = await knex.raw('SHOW PROCESSLIST');
           return _.map(results[0], 'Info');
         },
-        mysql2: async () => {
+        [drivers.MySQL2]: async () => {
           const results = await knex.raw('SHOW PROCESSLIST');
           return _.map(results[0], 'Info');
         },
@@ -1046,7 +1108,7 @@ module.exports = function (knex) {
       try {
         const promise = query.timeout(50, { cancel: true }).then(_.identity);
 
-        await delay(10);
+        await delay(15);
         const processesBeforeTimeout = await getProcesses();
         expect(processesBeforeTimeout).to.include(query.toString());
 
@@ -1065,7 +1127,7 @@ module.exports = function (knex) {
 
     it('.timeout(ms, {cancel: true}) should release connections after failing if connection cancellation throws an error', async function () {
       // Only mysql/postgres query cancelling supported for now
-      if (!isPostgreSQL(knex)) {
+      if (!isPostgreSQL(knex) || isPgNative(knex)) {
         return this.skip();
       }
 
@@ -1079,6 +1141,8 @@ module.exports = function (knex) {
       const rawTestQueries = {
         [drivers.PostgreSQL]: (sleepSeconds) =>
           `SELECT pg_sleep(${sleepSeconds})`,
+        [drivers.PgNative]: (sleepSeconds) =>
+          `SELECT pg_sleep(${sleepSeconds})`,
       };
 
       const driverName = knex.client.driverName;
@@ -1097,11 +1161,15 @@ module.exports = function (knex) {
       const originalWrappedCancelQueryCall =
         knexPrototype._wrappedCancelQueryCall;
 
-      knexPrototype._wrappedCancelQueryCall = (conn) => {
-        return knexPrototype.query(conn, {
-          method: 'raw',
-          sql: 'TestError',
-        });
+      knexPrototype._wrappedCancelQueryCall = (conn, connectionToKill) => {
+        if (isPgNative(knex)) {
+          throw new Error('END THIS');
+        } else {
+          return knexPrototype.query(conn, {
+            method: 'raw',
+            sql: 'TestError',
+          });
+        }
       };
 
       const queryTimeout = 10;
diff --git a/test/integration/query/selects.js b/test/integration/query/selects.js
index 3bcd0665..b18c77ab 100644
--- a/test/integration/query/selects.js
+++ b/test/integration/query/selects.js
@@ -9,6 +9,7 @@ const { TEST_TIMESTAMP } = require('../../util/constants');
 const {
   isMysql,
   isPostgreSQL,
+  isPgNative,
   isMssql,
   isSQLite,
   isOracle,
@@ -38,6 +39,12 @@ module.exports = function (knex) {
             [],
             ['1', '2', '3', '4', '5', '7']
           );
+          tester(
+            'pgnative',
+            'select "id" from "accounts" order by "id" asc',
+            [],
+            ['1', '2', '3', '4', '5', '7']
+          );
           tester(
             'pg-redshift',
             'select "id" from "accounts" order by "id" asc',
@@ -83,6 +90,12 @@ module.exports = function (knex) {
             [],
             ['1', '2', '3', '4', '5', '7']
           );
+          tester(
+            'pgnative',
+            'select "accounts"."id" from "accounts" order by "accounts"."id" asc',
+            [],
+            ['1', '2', '3', '4', '5', '7']
+          );
           tester(
             'pg-redshift',
             'select "accounts"."id" from "accounts" order by "accounts"."id" asc',
@@ -129,6 +142,12 @@ module.exports = function (knex) {
             [2],
             ['3', '4', '5', '7']
           );
+          tester(
+            'pgnative',
+            'select "id" from "accounts" order by "id" asc offset ?',
+            [2],
+            ['3', '4', '5', '7']
+          );
           tester(
             'pg-redshift',
             'select "id" from "accounts" order by "id" asc offset ?',
@@ -227,6 +246,19 @@ module.exports = function (knex) {
               { id: '7' },
             ]
           );
+          tester(
+            'pgnative',
+            'select /*+ invalid() */ "id" from "accounts" order by "id" asc',
+            [],
+            [
+              { id: '1' },
+              { id: '2' },
+              { id: '3' },
+              { id: '4' },
+              { id: '5' },
+              { id: '7' },
+            ]
+          );
           tester(
             'pg-redshift',
             'select /*+ invalid() */ "id" from "accounts" order by "id" asc',
@@ -286,6 +318,12 @@ module.exports = function (knex) {
             [1],
             { id: '1', first_name: 'Test' }
           );
+          tester(
+            'pgnative',
+            'select "id", "first_name" from "accounts" order by "id" asc limit ?',
+            [1],
+            { id: '1', first_name: 'Test' }
+          );
           tester(
             'pg-redshift',
             'select "id", "first_name" from "accounts" order by "id" asc limit ?',
@@ -314,6 +352,9 @@ module.exports = function (knex) {
     });
 
     it('allows you to stream', function () {
+      if (isPgNative(knex)) {
+        return this.skip();
+      }
       let count = 0;
       return knex('accounts')
         .stream(function (rowStream) {
@@ -327,6 +368,10 @@ module.exports = function (knex) {
     });
 
     it('returns a stream if not passed a function', function (done) {
+      if (isPgNative(knex)) {
+        return this.skip();
+      }
+
       let count = 0;
       const stream = knex('accounts').stream();
       stream.on('data', function () {
@@ -498,6 +543,17 @@ module.exports = function (knex) {
                 },
               ]
             );
+            tester(
+              'pgnative',
+              'select "first_name", "last_name" from "accounts" where "id" = ?',
+              [1],
+              [
+                {
+                  first_name: 'Test',
+                  last_name: 'User',
+                },
+              ]
+            );
             tester(
               'pg-redshift',
               'select "first_name", "last_name" from "accounts" where "id" = ?',
@@ -572,6 +628,17 @@ module.exports = function (knex) {
                 },
               ]
             );
+            tester(
+              'pgnative',
+              'select "first_name", "last_name" from "accounts" where "id" = ?',
+              [1],
+              [
+                {
+                  first_name: 'Test',
+                  last_name: 'User',
+                },
+              ]
+            );
             tester(
               'pg-redshift',
               'select "first_name", "last_name" from "accounts" where "id" = ?',
@@ -634,6 +701,11 @@ module.exports = function (knex) {
               'select "email", "logins" from "accounts" where "id" > ?',
               [1]
             );
+            tester(
+              'pgnative',
+              'select "email", "logins" from "accounts" where "id" > ?',
+              [1]
+            );
             tester(
               'pg-redshift',
               'select "email", "logins" from "accounts" where "id" > ?',
@@ -700,6 +772,25 @@ module.exports = function (knex) {
                 },
               ]
             );
+            tester(
+              'pgnative',
+              'select * from "accounts" where "id" = ?',
+              [1],
+              [
+                {
+                  id: '1',
+                  first_name: 'Test',
+                  last_name: 'User',
+                  email: 'test@example.com',
+                  logins: 1,
+                  balance: 0,
+                  about: 'Lorem ipsum Dolore labore incididunt enim.',
+                  created_at: TEST_TIMESTAMP,
+                  updated_at: TEST_TIMESTAMP,
+                  phone: null,
+                },
+              ]
+            );
             tester(
               'pg-redshift',
               'select * from "accounts" where "id" = ?',
@@ -796,6 +887,12 @@ module.exports = function (knex) {
               [],
               []
             );
+            tester(
+              'pgnative',
+              'select "first_name", "email" from "accounts" where "id" is null',
+              [],
+              []
+            );
             tester(
               'pg-redshift',
               'select "first_name", "email" from "accounts" where "id" is null',
@@ -830,6 +927,12 @@ module.exports = function (knex) {
           .testSql(function (tester) {
             tester('mysql', 'select * from `accounts` where `id` = ?', [0], []);
             tester('pg', 'select * from "accounts" where "id" = ?', [0], []);
+            tester(
+              'pgnative',
+              'select * from "accounts" where "id" = ?',
+              [0],
+              []
+            );
             tester(
               'pg-redshift',
               'select * from "accounts" where "id" = ?',
@@ -944,6 +1047,37 @@ module.exports = function (knex) {
             },
           ]
         );
+        tester(
+          'pgnative',
+          'select distinct on ("id") "email", "logins" from "accounts" order by "id" asc',
+          [],
+          [
+            {
+              email: 'test@example.com',
+              logins: 1,
+            },
+            {
+              email: 'test2@example.com',
+              logins: 1,
+            },
+            {
+              email: 'test3@example.com',
+              logins: 2,
+            },
+            {
+              email: 'test4@example.com',
+              logins: 2,
+            },
+            {
+              email: 'test5@example.com',
+              logins: 2,
+            },
+            {
+              email: 'test6@example.com',
+              logins: 2,
+            },
+          ]
+        );
       });
     });
 
@@ -1034,6 +1168,25 @@ module.exports = function (knex) {
                 },
               ]
             );
+            tester(
+              'pgnative',
+              'select * from "composite_key_test" where ("column_a", "column_b") in ((?, ?), (?, ?)) order by "status" desc',
+              [1, 1, 1, 2],
+              [
+                {
+                  column_a: 1,
+                  column_b: 1,
+                  details: 'One, One, One',
+                  status: 1,
+                },
+                {
+                  column_a: 1,
+                  column_b: 2,
+                  details: 'One, Two, Zero',
+                  status: 0,
+                },
+              ]
+            );
             tester(
               'pg-redshift',
               'select * from "composite_key_test" where ("column_a", "column_b") in ((?, ?), (?, ?)) order by "status" desc',
@@ -1134,6 +1287,19 @@ module.exports = function (knex) {
                 },
               ]
             );
+            tester(
+              'pgnative',
+              'select * from "composite_key_test" where "status" = ? and ("column_a", "column_b") in ((?, ?), (?, ?))',
+              [1, 1, 1, 1, 2],
+              [
+                {
+                  column_a: 1,
+                  column_b: 1,
+                  details: 'One, One, One',
+                  status: 1,
+                },
+              ]
+            );
             tester(
               'pg-redshift',
               'select * from "composite_key_test" where "status" = ? and ("column_a", "column_b") in ((?, ?), (?, ?))',
@@ -1432,7 +1598,7 @@ module.exports = function (knex) {
               expect('Second query should have timed out').to.be.false;
             })
             .catch((err) => {
-              // mssql fails because it tires to rollback at the same time when update query is running
+              // mssql fails because it tries to rollback at the same time when update query is running
               // hopefully for share really works though...
               if (isMssql(knex)) {
                 expect(err.message).to.be.contain(
diff --git a/test/integration2/util/knex-instance-provider.js b/test/integration2/util/knex-instance-provider.js
index 49578fb2..6e85bc84 100644
--- a/test/integration2/util/knex-instance-provider.js
+++ b/test/integration2/util/knex-instance-provider.js
@@ -5,6 +5,7 @@ const testConfig =
 
 const Db = {
   PostgresSQL: 'postgres',
+  PgNative: 'pgnative',
   MySQL: 'mysql',
   MySQL2: 'mysql2',
   MSSQL: 'mssql',
@@ -12,7 +13,14 @@ const Db = {
   Oracle: 'oracledb',
 };
 
-const defaultDbs = [Db.PostgresSQL, Db.MySQL, Db.MySQL2, Db.SQLite, Db.MSSQL];
+const defaultDbs = [
+  Db.PostgresSQL,
+  Db.PgNative,
+  Db.MySQL,
+  Db.MySQL2,
+  Db.SQLite,
+  Db.MSSQL,
+];
 
 function getAllDbs() {
   return process.env.DB ? process.env.DB.split(' ') : defaultDbs;
@@ -97,6 +105,21 @@ const testConfigs = {
     seeds,
   },
 
+  pgnative: {
+    client: Db.PgNative,
+    connection: testConfig.pgnative || {
+      adapter: 'postgresql',
+      port: 25433,
+      host: 'localhost',
+      database: 'knex_test',
+      user: 'testuser',
+      password: 'knextest',
+    },
+    pool,
+    migrations,
+    seeds,
+  },
+
   sqlite3: {
     client: 'sqlite3',
     connection: testConfig.sqlite3 || ':memory:',
diff --git a/test/knexfile.js b/test/knexfile.js
index f085e09c..75163cbf 100644
--- a/test/knexfile.js
+++ b/test/knexfile.js
@@ -9,7 +9,7 @@ const _ = require('lodash');
 
 // excluding redshift, oracle, and mssql dialects from default integrations test
 const testIntegrationDialects = (
-  process.env.DB || 'sqlite3 postgres mysql mysql2 mssql oracledb'
+  process.env.DB || 'sqlite3 postgres pgnative mysql mysql2 mssql oracledb'
 ).match(/\w+/g);
 
 console.log(`ENV DB: ${process.env.DB}`);
@@ -108,6 +108,21 @@ const testConfigs = {
     seeds,
   },
 
+  pgnative: {
+    client: 'pgnative',
+    connection: testConfig.pgnative || {
+      adapter: 'postgresql',
+      port: 25433,
+      host: 'localhost',
+      database: 'knex_test',
+      user: 'testuser',
+      password: 'knextest',
+    },
+    pool,
+    migrations,
+    seeds,
+  },
+
   redshift: {
     client: 'redshift',
     connection: testConfig.redshift || {
diff --git a/test/unit/dialects/postgres.js b/test/unit/dialects/postgres.js
index cb860fa7..220b699b 100644
--- a/test/unit/dialects/postgres.js
+++ b/test/unit/dialects/postgres.js
@@ -2,7 +2,6 @@ const knex = require('../../../knex');
 const expect = require('chai').expect;
 const sinon = require('sinon');
 const pgDialect = require('../../../lib/dialects/postgres/index.js');
-const pg = require('pg');
 const _ = require('lodash');
 const { isFunction } = require('../../../lib/util/is');
 
@@ -25,10 +24,9 @@ describe('Postgres Unit Tests', function () {
       .callsFake(function () {
         return Promise.resolve('9.6');
       });
-
-    sinon.stub(pg.Client.prototype, 'connect').callsFake(function (cb) {
-      cb(null, fakeConnection);
-    });
+    sinon
+      .stub(pgDialect.prototype, '_acquireOnlyConnection')
+      .returns(Promise.resolve(fakeConnection));
   });
   afterEach(() => {
     querySpy.resetHistory();
@@ -37,18 +35,17 @@ describe('Postgres Unit Tests', function () {
     sinon.restore();
   });
 
-  it('does not resolve client version if specified explicitly', (done) => {
+  it('does not resolve client version if specified explicitly', () => {
     const knexInstance = knex({
-      client: 'postgresql',
+      client: 'postgres',
       version: '10.5',
       connection: {
         pool: {},
       },
     });
-    knexInstance.raw('select 1 as 1').then((result) => {
+    return knexInstance.raw('select 1 as 1').then((result) => {
       expect(checkVersionStub.notCalled).to.equal(true);
       knexInstance.destroy();
-      done();
     });
   });
 
@@ -75,17 +72,16 @@ describe('Postgres Unit Tests', function () {
     );
   });
 
-  it('resolve client version if not specified explicitly', (done) => {
+  it('resolve client version if not specified explicitly', () => {
     const knexInstance = knex({
       client: 'postgresql',
       connection: {
         pool: {},
       },
     });
-    knexInstance.raw('select 1 as 1').then((result) => {
+    return knexInstance.raw('select 1 as 1').then((result) => {
       expect(checkVersionStub.calledOnce).to.equal(true);
       knexInstance.destroy();
-      done();
     });
   });
 
@@ -130,12 +126,13 @@ describe('Postgres Unit Tests', function () {
         );
       });
   });
-  it('Uses documented query config as param when providing bindings', (done) => {
+
+  it('Uses documented query config as param when providing bindings', () => {
     const knexInstance = knex({
       client: 'postgresql',
       connection: {},
     });
-    knexInstance.raw('select 1 as ?', ['foo']).then((result) => {
+    return knexInstance.raw('select 1 as ?', ['foo']).then((result) => {
       sinon.assert.calledOnce(querySpy);
       sinon.assert.calledWithExactly(
         querySpy,
@@ -146,7 +143,6 @@ describe('Postgres Unit Tests', function () {
         sinon.match.func
       );
       knexInstance.destroy();
-      done();
     });
   });
 });
diff --git a/test/util/db-helpers.js b/test/util/db-helpers.js
index 6d5a54b1..b57cc6d3 100644
--- a/test/util/db-helpers.js
+++ b/test/util/db-helpers.js
@@ -6,11 +6,19 @@ function getDriverName(knex) {
 }
 
 function isPostgreSQL(knex) {
-  return getDriverName(knex) === drivers.PostgreSQL;
+  return isOneOfDbs(knex, [drivers.PostgreSQL, drivers.PgNative]);
+}
+
+function isPgNative(knex) {
+  return getDriverName(knex) === drivers.PgNative;
 }
 
 function isPgBased(knex) {
-  return isOneOfDbs(knex, [drivers.PostgreSQL, drivers.Redshift]);
+  return isOneOfDbs(knex, [
+    drivers.PostgreSQL,
+    drivers.PgNative,
+    drivers.Redshift,
+  ]);
 }
 
 function isMssql(knex) {
@@ -36,7 +44,7 @@ function isSQLite(knex) {
 /**
  *
  * @param knex
- * @param {('pg'|'pg-redshift'|'oracledb'|'mysql'|'mysql2'|'mssql'|'sqlite3')[]} supportedDbs - supported DB values in DRIVER_NAMES from lib/constants.
+ * @param {('pg'|'pgnative'|'pg-redshift'|'oracledb'|'mysql'|'mysql2'|'mssql'|'sqlite3')[]} supportedDbs - supported DB values in DRIVER_NAMES from lib/constants.
  * @returns {*}
  */
 function isOneOfDbs(knex, supportedDbs) {
@@ -49,6 +57,7 @@ module.exports = {
   isMssql,
   isOracle,
   isPostgreSQL,
+  isPgNative,
   isPgBased,
   isRedshift,
   isSQLite,