pg#Client TypeScript Examples

/**
 * Run the SQL file containing DDL or DML on the database.
 *
 * @param file - A SQL file that we would like to execute against our database.
 * @param databaseConfig - A database config object that holds connection information.
 */
async function executeSqlFile(
  file: string,
  databaseConfig: typeof config.database,
): Promise<void> {
  const sql = await fs.promises.readFile(file, 'utf8')
  const client = new Client(databaseConfig.connection)

  try {
    // Connect to the database
    await client.connect()

    // Execute SQL query
    logger.debug(`Executing query:\n${sql}`)
    await client.query(sql)

    // Close the database connection
    await client.end()
  } catch (err) {
    logger.fatal(
      '\nerror running query',
      file,
      err.message,
      '\n\nCheck that Postgres is running and that there is no port conflict\n',
    )

    // If we can't execute our SQL, our app is in an indeterminate state, so kill it.
    process.exit(1)
  }
}

server = http.createServer((req, res) => {
  const client = new Client({
    host: process.env.POSTGRES_HOST || 'postgres',
    user: 'root',
    password: 'root',
    database: 'test'
  });
  client.connect();
  client.query(`SELECT * FROM "user" where name = 'u1'`).then(
    (resDB: any) => {
      res.end(JSON.stringify(resDB.rows));
      client.end();
    },
    (err: any) => {
      client.end();
    },
  );
})

export function processOptions(options: RunnerOptions = {}) {
  return {
    ...defaults,
    ...options,

    escapedWorkerSchema: Client.prototype.escapeIdentifier(
      options.workerSchema ?? defaults.workerSchema
    ),
    escapedSchedulerSchema: Client.prototype.escapeIdentifier(
      options.schedulerSchema ?? defaults.schedulerSchema
    ),
  };
}

export async function updateObject(
  postgres: Client,
  table: string,
  object: any,
  condition: any
): Promise<QueryResult<any>> {
  const columns = Object.keys(object);
  const values = Object.values(object);
  // TODO support compound conditions, not just one
  let query = `UPDATE ${table} SET ${columns
    .map((c, i) => `"${c}" = $${i + 1}`)
    .join(',')}
    WHERE "${Object.keys(condition)[0]}" = $${Object.keys(object).length + 1}
    RETURNING *`;
  //console.log(query);
  const result = await postgres.query(query, [
    ...values,
    condition[Object.keys(condition)[0]],
  ]);
  return result;
}

export async function insertObject(
  postgres: Client,
  table: string,
  object: any
): Promise<QueryResult<any>> {
  const columns = Object.keys(object);
  const values = Object.values(object);
  let query = `INSERT INTO ${table} (${columns.map((c) => `"${c}"`).join(',')})
    VALUES (${values.map((_, i) => '$' + (i + 1)).join(',')})
    RETURNING *`;
  // console.log(query);
  const result = await postgres.query(query, values);
  return result;
}

export async function upsertObject(
  postgres: Client,
  table: string,
  object: any,
  conflict: any
): Promise<QueryResult<any>> {
  const columns = Object.keys(object);
  const values = Object.values(object);
  let query = `INSERT INTO ${table} (${columns.map((c) => `"${c}"`).join(',')})
    VALUES (${values.map((_, i) => '$' + (i + 1)).join(',')})
    ON CONFLICT ("${Object.keys(conflict).join(',')}")
    DO UPDATE SET ${Object.keys(object)
      .map((c) => `"${c}" = EXCLUDED."${c}"`)
      .join(',')}
    RETURNING *`;
  // console.log(query);
  const result = await postgres.query(query, values);
  return result;
}

(async () => {
  const dummyDataFile = path.resolve(__dirname, './03-dummy-data.sql.sample')
  const data = await fs.readFile(dummyDataFile, 'utf-8')

  const dbConfig = {
    connectionString: process.env.DATABASE_URL,
  }
  const client = new Client(dbConfig)
  await client.connect()
  await client.query(data)
  await client.end()
})()

async function connectAndMigrate(databaseUrl: string | undefined, migrationsDirectory: string) {
  const dbConfig = {
    connectionString: databaseUrl,
    connectionTimeoutMillis: 10_000,
  }
  const client = new Client(dbConfig)
  try {
    await client.connect()
    await migrate({ client }, migrationsDirectory)
  } finally {
    await client.end()
  }
}

handler: Handler = async (event) => {
  const connection = await connectToDatabase();

  // Create P&E database and user.
  try {
    await connection.query(
      `CREATE USER ${process.env.PE_DB_USERNAME} WITH PASSWORD '${process.env.PE_DB_PASSWORD}';`
    );
  } catch (e) {
    console.log(
      "Create user failed. This usually means that the user already exists, so you're OK if that was the case. Here's the exact error:",
      e
    );
  }
  try {
    await connection.query(
      `GRANT ${process.env.PE_DB_USERNAME} to ${process.env.DB_USERNAME};`
    );
  } catch (e) {
    console.log('Grant role failed. Error:', e);
  }
  try {
    await connection.query(
      `CREATE DATABASE ${process.env.PE_DB_NAME} owner ${process.env.PE_DB_USERNAME};`
    );
  } catch (e) {
    console.log(
      "Create database failed. This usually means that the database already exists, so you're OK if that was the case. Here's the exact error:",
      e
    );
  }

  // Connect to the PE database.
  const client = new Client({
    user: process.env.PE_DB_USERNAME,
    host: process.env.DB_HOST,
    database: process.env.PE_DB_NAME,
    password: process.env.PE_DB_PASSWORD
  });
  client.connect();

  // Drop all tables in the PE database.
  await client.query(`drop owned by ${process.env.PE_DB_USERNAME}`);

  // Generate initial PE tables.
  const sql = String(PE_DATA_SCHEMA);
  await client.query(sql);

  console.log('Done.');
  client.end();
}

function prismaTestContext() {
  const prismaBinary = join(__dirname, '..', 'node_modules', '.bin', 'prisma')
  let schema = ''
  let databaseUrl = ''
  let prismaClient: null | PrismaClient = null

  return {
    async before() {
      // Generate a unique schema identifier for this test context
      schema = `test_${nanoid()}`
      // Generate the pg connection string for the test schema
      databaseUrl = `postgres://postgres:postgres@localhost:5432/testing?schema=${schema}`

      // Set the required environment variable to contain the connection string
      // to our database test schema
      process.env.DATABASE_URL = databaseUrl

      // Run the migrations to ensure our schema has the required structure
      execSync(`${prismaBinary} migrate up --create-db --experimental`, {
        env: {
          ...process.env,
          DATABASE_URL: databaseUrl,
        },
      })

      // Construct a new Prisma Client connected to the generated Postgres schema
      prismaClient = new PrismaClient()

      return prismaClient
    },
    async after() {
      // Drop the schema after the tests have completed
      const client = new Client({
        connectionString: databaseUrl,
      })
      await client.connect()
      await client.query(`DROP SCHEMA IF EXISTS "${schema}" CASCADE`)
      await client.end()

      // Release the Prisma Client connection
      await prismaClient?.$disconnect()
    },
  }
}

postgres = new Client({
  connectionString: config.DATABASE_URL,
  ssl: { rejectUnauthorized: false },
})

postgres: Client | undefined = undefined

postgres: Client | undefined = undefined

postgres2 = new Client({
  connectionString: config.DATABASE_URL,
  ssl: { rejectUnauthorized: false },
})

export class TestClient extends Client {

    private id: number = 1;

    constructor() {
        super({
            ...connectionConfig.postgres,
            database: `${connectionConfig.postgres.database}-test`,
        });

        // eslint-disable-next-line no-console
        this.connect().catch(console.error);
    }

    getId(): number {
        return ++this.id;
    }

    async init(): Promise<void> {}

    async createContractCode(values: Record<string, any> = {}): Promise<Record<string, any>> {
        return await this.insert('contract_codes', {
            account: 'account',
            block_num: this.getId(),
            block_time: this.getId(),
            ...values,
        });
    }

    protected async insert(table: string, data: Record<string, any>): Promise<Record<string, any>> {
        data = data || {};

        const columns = Object.keys(data);

        const columnsSQL = (columns.length ? '('+columns.join(',')+')' : '');
        const valuesSQL = (columns.length ? `VALUES (${columns.map((c, i) => `$${i + 1}`).join(',')})` : 'DEFAULT VALUES');
        const values = columns.map(c => data[c]);

        const sql = `INSERT INTO ${table} ${columnsSQL} ${valuesSQL} RETURNING *`;

        const {rows} = await this.query(sql, values);

        return rows[0];
    }

}

setTimeout(async () => {
  const pool = new Pool({
    connectionString:
      'postgresql://commonwealth:edgeware@localhost/commonwealth',
  });
  pool.on('error', (err, client) => {
    console.error('Unexpected error on idle client', err);
    assert.fail();
  });

  const client = new Client({
    connectionString:
      'postgresql://commonwealth:edgeware@localhost/commonwealth',
  });
  await client.connect();

  const chains: {
    id: string;
    url: string;
    substrate_spec: string;
    base: string;
  }[] = await getChains(pool);
  if (chains.length === 0)
    console.log(
      '\nWARNING: Supported-chains/chains-to-test should have HAS_CHAIN_EVENTS_LISTENER set to true in the ' +
        'database'
    );
  await clearDB(pool);
  await clearQueues(["ChainEventsHandlersQueue", "SubstrateIdentityEventsQueue"]);
  await prepareDB(client);

  describe('Tests for single chain per node', () => {
    pool.on('error', (err, _client) => {
      console.error('Unexpected error on idle client', err);
      assert.fail();
    });

    chains.forEach((chain, chainIndex) => {
      if (!supportedChains.includes(chain.id)) return;
      describe(`Tests for a ${chain.id} chain-events node`, () => {
        it(`Should start a node with a ${chain.id} listener`, (done) => {
          const child = spawn(
            'ts-node',
            [`${__dirname}../../../server/scripts/dbNode.ts`],
            {
              env: {
                ...process.env,
                TESTING: 'true',
                WORKER_NUMBER: String(chainIndex),
                NUM_WORKERS: String(chains.length)
              },
            }
          );

          childExit([child]);

          child.on('error', (error) => {
            console.log(error);
            assert.fail(String(error));
          });

          console.log(`\n${chain.id}:`);
          child.stdout.on('data', (data) => {
            data = String(data);
            console.log(`${data}`);
            if (data.includes('Listener Validation')) {
              const arr = data.split('Listener Validation:');
              const listeners = JSON.parse(arr[arr.length - 1]);
              verifyListener([chain.id], listeners);
              done();
            }
          });

          child.stderr.on('data', (data) => {
            console.error(`child stderr:\n${data}`);
            if (
              !dbNodeIgnoreErrors.map((o) => data.includes(o)).includes(true)
            ) {
              assert.fail(String(data));
            }
          });
        }).timeout(30000);

        // only substrate chains use the identity cache
        if (
          chain.base === 'substrate' &&
          chain.id !== 'stafi' &&
          chain.id !== 'clover'
        ) {
          // check publish functionality
          it('Should clear the identity cache', async () => {
            // check identity cache is empty
            const query =
              'SELECT * FROM "IdentityCaches" WHERE "chain"=\'polkadot\';';
            assert((await pool.query(query)).rows.length === 0);
          });

          // delay briefly due to message latency
          delay(10000);

          it('Should publish a message to the identity queue', async () => {
            // check the proper number of messages have been queued in the identity queue
            const res = await fetch('http://127.0.0.1:15672/api/queues/', {
              method: 'GET',
              headers: {
                Authorization: 'Basic Z3Vlc3Q6Z3Vlc3Q=',
              },
            });
            const data = await res.json();
            const identityQueue = data.filter(
              (obj) => obj.name === 'identityQueue'
            )[0];
            assert.equal(identityQueue.messages, 1);
          });

          // check handle functionality
          it.skip('Should handle identity events and clear the identity cache', async () => {
            // check identity cache is empty
            const query = format(
              'SELECT * FROM "IdentityCaches" WHERE "chain"=%L;',
              chain
            );
            assert((await pool.query(query)).rows.length === 0);

            // check that no identity events are queued in the identity queue
            const res = await fetch('http://127.0.0.1:15672/api/queues/', {
              method: 'GET',
              headers: {
                Authorization: 'Basic Z3Vlc3Q6Z3Vlc3Q=',
              },
            });
            const data = await res.json();
            const identityQueue = data.filter(
              (obj) => obj.name === 'identityQueue'
            );
            assert.equal(identityQueue.messages, 0);

            assert.isTrue(await verifyIdentityChanges(pool, chain.id));
          });
        }
      });

      describe('Tests for the chain-events consumer', () => {
        it('Should start the chain-events consumer', (done) => {
          const consumer = spawn(
            'ts-node',
            [`${__dirname}../../../server/scripts/chainEventsConsumer`],
            {
              env: {
                ...process.env,
                USE_NEW_IDENTITY_CACHE: 'true',
              },
            }
          );

          childExit([consumer]);

          consumer.on('error', (error) => {
            console.log(error);
            assert.fail();
          });

          console.log(`\n${chain.id}:`);
          consumer.stdout.on('data', (data) => {
            data = String(data);
            console.log(`${data}`);
            if (data.includes('Consumer started')) {
              done();
            }
          });

          consumer.stderr.on('data', (data) => {
            console.error(`child stderr:\n${data}`);
            assert.fail();
          });
        }).timeout(30000);

        // only substrate chains use the identity cache
        if (
          chain.base === 'substrate' &&
          chain.id !== 'stafi' &&
          chain.id !== 'clover'
        ) {
          delay(10000);

          it('Should consume identity events', async () => {
            assert.isTrue(await verifyIdentityChanges(pool, chain.id));

            // makre sure identity queue is empty
            const res = await fetch('http://127.0.0.1:15672/api/queues/', {
              method: 'GET',
              headers: {
                Authorization: 'Basic Z3Vlc3Q6Z3Vlc3Q=',
              },
            });
            const data = await res.json();
            const SubstrateIdentityEventsQueue = data.filter(
              (obj) => obj.name === 'SubstrateIdentityEventsQueue'
            )[0];
            assert.equal(SubstrateIdentityEventsQueue.messages, 0);
          });
        }
      });
    });
  });
  run();
}, 5000);