use deno pgClient to get the correct result

stackql · yunchengyang515 · Dec 9, 2023 · Nov 11, 2023 · Nov 11, 2023 · Nov 11, 2023
commit 83a3c582f60c40ca200448cda1bdda6537c35ef9
diff --git a/src/services/server.test.ts b/src/services/server.test.ts
@@ -5,7 +5,9 @@ import { startStackQLServer } from "../../testing/utils.ts";
 Deno.test("Successful Connection", async () => {
   const { closeProcess } = await startStackQLServer();
   const server = new Server();
-  const pg = await server.connect("http://127.0.0.1:5444");
+  const pg = await server.connect(
+    "postgres://postgres:password@localhost:5444/postgres",
+  );
   assert(pg);
   await server.close();
   await closeProcess();

diff --git a/src/services/server.ts b/src/services/server.ts
@@ -1,29 +1,32 @@
-import {
-  pgconnect,
-  PgConnection,
-} from "https://raw.githubusercontent.com/kagis/pgwire/main/mod.js";
+import { Client } from "https://deno.land/x/postgres/mod.ts";
 
 export class Server {
-  private pg: PgConnection | null = null;
-  constructor() {}
+  private client: Client | null = null;
+
+  constructor(connectionString?: string) {
+    if (connectionString) {
+      this.client = new Client(connectionString);
+    }
+  }
 
   public async connect(connectionString?: string) {
     const maxRetries = 3;
     let currentAttempt = 0;
 
     while (currentAttempt < maxRetries) {
       try {
-        // Attempt to connect
         const connection = Deno.env.get("POSTGRES") || connectionString;
         if (!connection) {
           throw new Error(
             "Connection string not found \n Please set the POSTGRES environment variable or pass the connection string as an argument",
           );
         }
+
         console.log("connecting", connection);
-        this.pg = await pgconnect(connection);
+        this.client = new Client(connection);
+        await this.client.connect();
         console.log("connected");
-        return this.pg;
+        return this.client;
       } catch (error) {
         currentAttempt++;
         console.log(`Attempt ${currentAttempt} failed: ${error.message}`);
@@ -41,8 +44,10 @@ export class Server {
   }
 
   public async close() {
-    if (this.pg) {
-      await this.pg.end();
+    if (this.client) {
+      await this.client.end();
     }
   }
+
+  // Additional methods for query execution can be added here
 }
diff --git a/src/stackql.test.ts b/src/stackql.test.ts
@@ -1,6 +1,11 @@
 import { assertStringIncludes } from "https://deno.land/std@0.206.0/assert/mod.ts";
 import { StackQL } from "./stackql.ts";
 import { removeStackQLDownload, startStackQLServer } from "../testing/utils.ts";
+import { assert } from "https://deno.land/std@0.160.0/_util/assert.ts";
+import {
+  assertEquals,
+  assertExists,
+} from "https://deno.land/std@0.160.0/testing/asserts.ts";
 
 Deno.test("StackQL runQuery - Successful Execution", async () => {
   // Arrange
@@ -20,26 +25,33 @@ Deno.test("StackQL runQuery - Successful Execution", async () => {
   assertStringIncludes(result, "okta");
 });
 
-Deno.test.only("StackQL runServerQuery - Successful Execution", async () => {
+Deno.test("StackQL runServerQuery - Successful Execution", async () => {
   const { closeProcess } = await startStackQLServer();
+  const stackQL = new StackQL();
 
   try {
     // Arrange
-    const stackQL = new StackQL();
     await stackQL.initialize({
       serverMode: true,
-      connectionString: "http://127.0.0.1:5444",
+      connectionString: "postgres://postgres:password@localhost:5444/postgres",
     });
     const pullQuery = "REGISTRY PULL github;";
-    const testQuery = "SHOW PROVIDERS;"; // Replace with a valid query for your context
+    const testQuery = "SHOW SERVICES IN github LIKE '%repos%';"; // Replace with a valid query for your context
 
     // Act
-    const pullResults = await stackQL.runServerQuery(pullQuery);
+    await stackQL.runServerQuery(pullQuery);
     const results = await stackQL.runServerQuery(testQuery);
+    assertExists(results);
+    assertEquals(results.length, 1);
+    const result = results[0] as {
+      name: string;
+    };
+    assertEquals(result.name, "repos");
 
     // Assert
   } finally {
     // Cleanup
     await closeProcess();
+    await stackQL.closeConnection();
   }
 });
diff --git a/src/stackql.ts b/src/stackql.ts
@@ -1,8 +1,12 @@
 import { assertExists } from "https://deno.land/std@0.206.0/assert/assert_exists.ts";
 import { Downloader } from "./services/downloader.ts";
 import { fileExists } from "./utils.ts";
-import { PgConnection } from "https://raw.githubusercontent.com/kagis/pgwire/main/mod.js";
 import { Server } from "./services/server.ts";
+import { Client } from "https://deno.land/x/postgres@v0.17.0/client.ts";
+import {
+  QueryArrayResult,
+  QueryObjectResult,
+} from "https://deno.land/x/postgres@v0.17.0/query/query.ts";
 
 export interface StackQLConfig {
   binaryPath?: string;
@@ -14,7 +18,8 @@ export class StackQL {
   private binaryPath?: string;
   private downloader: Downloader = new Downloader();
   private serverMode = false;
-  private connection?: PgConnection;
+  private connection?: Client;
+  private format: "object" = "object";
   constructor() {
   }
   public async initialize(config: StackQLConfig) {
@@ -35,6 +40,12 @@ export class StackQL {
     this.connection = await server.connect(connectionString);
   }
 
+  public async closeConnection() {
+    if (this.connection) {
+      await this.connection.end();
+    }
+  }
+
   public async runQuery(query: string) {
     assertExists(this.binaryPath);
     const process = new Deno.Command(this.binaryPath, {
@@ -56,15 +67,20 @@ export class StackQL {
     }
   }
 
-  public async runServerQuery(query: string) {
+  private async queryObjectFormat(query: string) {
     assertExists(this.connection);
+    const pgResult = await this.connection.queryObject(query);
+    return pgResult.rows;
+  }
+
+  public async runServerQuery(query: string) {
     try {
-      const pgResult = await this.connection.query(query);
-      console.log("pgResult", pgResult);
-      if (pgResult.status == "OK") {
-        return pgResult.rows;
+      if (this.format === "object") {
+        const result = await this.queryObjectFormat(query);
+        return result;
       }
     } catch (error) {
+      console.error(error);
       throw new Error(`StackQL server query failed: ${error.message}`);
     }
   }