pg#Pool TypeScript Examples

setupServer = (database: Pool, disableSearchApi = false): FastifyInstance => {
  // let repositories;
  const repositories = Repositories.configure(database);
  const services = Services.configure(
    repositories,
    NETWORK_ID,
    // eslint-disable-next-line no-magic-numbers
    1097911063,
    JSON.parse(fs.readFileSync(path.resolve(process.env.TOPOLOGY_FILE_PATH)).toString()),
    Number(process.env.DEFAULT_RELATIVE_TTL)
  );
  return buildServer(services, cardanoCliMock, cardanoNodeMock, process.env.LOGGER_LEVEL, {
    networkId: NETWORK_ID,
    pageSize: Number(process.env.PAGE_SIZE) || DEFAULT_PAGE_SIZE,
    mock: true,
    disableSearchApi
  });
}

// consumer

    protected async syncState(): Promise<void> {
        if (this.started && !this.paused) {
            if (!this.runner) {
                this.consumerPool = await this.createPool()
                this.runner = await run({
                    // graphile's types refer to a local node_modules version of Pool
                    pgPool: this.consumerPool as Pool as any,
                    schema: this.serverConfig.JOB_QUEUE_GRAPHILE_SCHEMA,
                    noPreparedStatements: !this.serverConfig.JOB_QUEUE_GRAPHILE_PREPARED_STATEMENTS,
                    concurrency: 1,
                    // Install signal handlers for graceful shutdown on SIGINT, SIGTERM, etc
                    noHandleSignals: false,
                    pollInterval: 100,
                    // you can set the taskList or taskDirectory but not both
                    taskList: {
                        pluginJob: (payload) => {
                            void this.onJob?.([payload as EnqueuedJob])
                        },
                    },
                })
            }
        } else {
            if (this.runner) {
                const oldRunner = this.runner
                this.runner = null
                await oldRunner?.stop()
                await this.consumerPool?.end()
            }
        }
    }

public async initialize() {
    // Always load the cache first if available, if not then default to config.js
    try {
      await jsonFileReader(cacheConfigPath)
      .then(async (data: any) => {
          if (data) {
              this.loadCtx(data);
              return;
          }
      });
    } catch (err) {
      console.log('Cache config file error, falling back to default config.js', err);
      if (cfg.configMode === 'file' || cfg.configMode === 'database') {
        this.loadCtx(contextsConfig);
      }
    }

    // Try to load from database if it's also set
    if (cfg.configMode === 'database') {
      this.dbCfgPool = new Pool(cfg.databaseModeConfig);
      this.dbConfigTimerStart(true);
    } else if (cfg.configMode === 'file') {
      // Do nothing since we always default to file
    }
    else {
      throw new Error('Invalid configMode');
    }
  }

// init will set the connection pool from the configuration file.
    // eslint-disable-next-line @typescript-eslint/require-await
    public async init() {
        this.pool = new Pool({
            connectionString: Config.core_db_connection_string,
        });
        // ensures timestamps returned from the db are in UTC strings to match what is in the db
        types.setTypeParser(1114, (stringValue) => {
            return new Date(Date.parse(stringValue + '+0000'));
        });
    }

async function pg(): Promise<Pool> {
  if (!pgPool) {
    let poolConfig: PoolConfig = {
      user: process.env.POSTGRES_USER || 'postgres',
      database: process.env.POSTGRES_DB || 'cloud_pricing',
      password: process.env.POSTGRES_PASSWORD || '',
      port: Number(process.env.POSTGRES_PORT) || 5432,
      host: process.env.POSTGRES_HOST || 'localhost',
      max: Number(process.env.POSTGRES_MAX_CLIENTS) || 10,
    };

    if (process.env.POSTGRES_URI) {
      poolConfig = {
        connectionString:
          process.env.POSTGRES_URI ||
          'postgresql://postgres:@localhost:5432/cloud_pricing',
      };
    }

    pgPool = new Pool(poolConfig);
  }
  return pgPool;
}

start = async (databaseInstance: Pool) => {
  let server;
  try {
    const environment: Environment = parseEnvironment();
    const repository = Repostories.configure(databaseInstance);
    // FIXME: validate the following paraemeters when implementing (2)
    // https://github.com/input-output-hk/cardano-rosetta/issues/101
    const cardanoNode = CardanoNode.configure(environment.CARDANO_NODE_PATH);
    const cardanoCli = CardanoCli.configure(environment.CARDANO_CLI_PATH, networkMagic);
    const services = Services.configure(
      repository,
      networkId,
      networkMagic,
      environment.TOPOLOGY_FILE,
      environment.DEFAULT_RELATIVE_TTL
    );
    server = buildServer(services, cardanoCli, cardanoNode, environment.LOGGER_LEVEL, {
      networkId,
      pageSize: environment.PAGE_SIZE,
      disableSearchApi: environment.DISABLE_SEARCH_API
    });

    server.addHook('onClose', (_, done) => databaseInstance.end(done));
    // eslint-disable-next-line no-magic-numbers
    await server.listen(environment.PORT, environment.BIND_ADDRESS);
    server.blipp();
  } catch (error) {
    server?.log.error(error);
    await databaseInstance?.end();
    // eslint-disable-next-line unicorn/no-process-exit
    process.exit(1);
  }
}

constructor(config: Config) {
    this.#config = config
    this.#koa = new Koa()
    this.#router = new Router()
    this.#db = new Kysely<Database>({
      dialect: new PostgresDialect({
        pool: async () => new Pool(this.#config.database),
      }),
    })

    this.#koa.use(compress())
    this.#koa.use(bodyParser())
    this.#koa.use(json())

    this.#koa.use(this.errorHandler)
    this.#koa.use(this.decorateContext)

    userController(this.#router)

    this.#koa.use(this.#router.routes())
    this.#koa.use(this.#router.allowedMethods())
  }

async start({
    postgresUrl,
    postgresReplicaUrl,
    appName = "api",
  }: PostgresParams) {
    this.postgresUrl = postgresUrl;
    if (!postgresUrl) {
      throw new Error("no postgres url provided");
    }
    try {
      await ensureDatabase(postgresUrl);
    } catch (e) {
      console.error(`error in ensureDatabase: ${e.message}`);
      throw e;
    }
    this.pool = new Pool({
      connectionTimeoutMillis: CONNECT_TIMEOUT,
      connectionString: postgresUrl,
      application_name: `${appName}-${hostname()}`,
    });

    if (postgresReplicaUrl) {
      console.log("replica url found, using read replica");
      this.replicaPool = new Pool({
        connectionTimeoutMillis: CONNECT_TIMEOUT,
        connectionString: postgresReplicaUrl,
        application_name: `${appName}-read-${hostname()}`,
      });
    } else {
      console.log("no replica url found, not using read replica");
    }

    await this.query("SELECT NOW()");
    await this.replicaQuery("SELECT NOW()");
    await this.makeTables();
  }

constructor(host: string, port: number, user: string, password: string, database: string) {
        this.args = {
            host, port, user, password, database,
            application_name: 'eosio-contract-api'
        };
        this.pool = new Pool(this.args);

        this.pool.on('error', (err) => {
            logger.warn('PG pool error', err);
        });

        this.name = host + '::' + port + '::' + database;
    }

export async function getApp(
  options: LighthouseAuditServiceOptions = {},
  providedConn?: DbConnectionType,
): Promise<Application> {
  logger.info('building express app...');
  const conn = providedConn || new Pool(options.postgresConfig);

  await awaitDbConnection(conn);
  await runDbMigrations(conn);

  const app = express();
  app.set('connection', conn);
  configureMiddleware(app, options);

  const router = expressPromiseRouter();
  configureRoutes(router, conn);
  app.use(router);

  configureErrorMiddleware(app);

  return app;
}

module.exports = function main() {
    let args = process.argv.slice(2)

    if (args.indexOf('--help') >= 0) {
        help()
        process.exit(1)
    }

    if (args.length != 1) {
        help()
        process.exit(1)
    }

    let model = loadModel(args[0])
    let db = new Pool(createPoolConfig())
    let port = process.env.GRAPHQL_SERVER_PORT || 3000

    serve({model, db, port}).then(
        () => {
            console.log('OpenReader is listening on port ' + port)
        },
        err => {
            console.error(err)
            process.exit(1)
        }
    )
}

init: (config: PoolConfig) => {
  query: (sql: string) => Promise<PostgresMetaResult<any>>
  end: () => Promise<void>
} = (config) => {
  // NOTE: Race condition could happen here: one async task may be doing
  // `pool.end()` which invalidates the pool and subsequently all existing
  // handles to `query`. Normally you might only deal with one DB so you don't
  // need to call `pool.end()`, but since the server needs this, we make a
  // compromise: if we run `query` after `pool.end()` is called (i.e. pool is
  // `null`), we temporarily create a pool and close it right after.
  let pool: Pool | null = new Pool(config)
  return {
    async query(sql) {
      try {
        if (!pool) {
          const pool = new Pool(config)
          const { rows } = await pool.query(sql)
          await pool.end()
          return { data: rows, error: null }
        }

        const { rows } = await pool.query(sql)
        return { data: rows, error: null }
      } catch (e: any) {
        return { data: null, error: { message: e.message } }
      }
    },

    async end() {
      const _pool = pool
      pool = null
      // Gracefully wait for active connections to be idle, then close all
      // connections in the pool.
      if (_pool) await _pool.end()
    },
  }
}

cosmosPool = new Pool({ database: ENV.COSMOS_DB })

pool = new Pool(process.platform == 'android' ? optionsAndoid : options)

consumerPool: Pool | null

constructor(@Inject('db') private db: Pool) {}

constructor(options: RunnerOptions = {}) {
    const {
      schedules,
      pgPool,
      connectionString,
      checkInterval,
      leadTime,
      maxAge,
      workerSchema,
      ...workerOptions
    } = options;
    this.options = options;
    this.checkInterval = checkInterval ?? 60 * 1000;
    this.leadTime = leadTime ?? 0;
    this.maxAge = maxAge ?? 1000 * 60 * 60 * 24 * 3;

    this.logger = options.logger ?? new Logger(consoleLogFactory);

    assert(
      !pgPool || !connectionString,
      "Both `pgPool` and `connectionString` are set, at most one of these options should be provided"
    );

    if (pgPool) {
      this.pgPool = pgPool;
    } else if (connectionString) {
      this.pgPool = new Pool({ connectionString: connectionString });
    } else if (process.env.DATABASE_URL) {
      this.pgPool = new Pool({ connectionString: process.env.DATABASE_URL });
    } else {
      throw new Error(
        "You must either specify `pgPool` or `connectionString`, or you must make the `DATABASE_URL` environmental variable available."
      );
    }

    this.pgPool.on("error", err => {
      /*
       * This handler is required so that client connection errors don't bring
       * the server down (via `unhandledError`).
       *
       * `pg` will automatically terminate the client and remove it from the
       * pool, so we don't actually need to take any action here, just ensure
       * that the event listener is registered.
       */
      this.logger.error(`PostgreSQL client generated error: ${err.message}`, {
        error: err,
      });
    });

    this.schedules = schedules;
    this.scheduleNames = schedules?.map(s =>
      typeof s === "string" ? s : s.name
    );

    // add in our embed schedule tasks
    let taskList = options.taskList ?? {};
    for (const schedule of this.schedules ?? []) {
      if (typeof schedule === "object" && schedule.task != null) {
        if (options.taskDirectory != null) {
          throw new Error(
            "You cannot specify `taskDirectory` and `task` in `schedules`."
          );
        }

        taskList = { ...taskList, [schedule.name]: schedule.task };
      }
    }

    this.workerOptions = {
      ...workerOptions,
      taskList,
      pgPool: this.pgPool,
      schema: workerSchema,
    };
  }

dbPool = new Pool({
  max: 25,
  connectionTimeoutMillis: 5_000,
  idleTimeoutMillis: 30_000,
  query_timeout: 20_000,
  connectionString: process.env.DATABASE_URL || defaultDatabaseUrl
})

export async function query(sql: SQLStatement, client: Pool | PoolClient = pool) {
  if(client === pool) await initializationLock;
  return client.query(sql);
}

private pool!: Pool;

pgPool: Pool

populateTransactions = async (
  databaseInstance: Pool,
  transactionsMap: TransactionsMap
): Promise<PopulatedTransaction[]> => {
  const transactionsHashes = Object.keys(transactionsMap).map(hexStringToBuffer);
  const operationsQueries = [
    Queries.findTransactionsInputs,
    Queries.findTransactionsOutputs,
    Queries.findTransactionWithdrawals,
    Queries.findTransactionRegistrations,
    Queries.findTransactionDeregistrations,
    Queries.findTransactionDelegations,
    Queries.findTransactionMetadata,
    Queries.findTransactionPoolRegistrationsData,
    Queries.findTransactionPoolOwners,
    Queries.findTransactionPoolRelays,
    Queries.findPoolRetirements
  ];
  const [
    inputs,
    outputs,
    withdrawals,
    registrations,
    deregistrations,
    delegations,
    votes,
    poolsData,
    poolsOwners,
    poolsRelays,
    poolRetirements
  ] = await Promise.all(
    operationsQueries.map(operationQuery => databaseInstance.query(operationQuery, [transactionsHashes]))
  );
  transactionsMap = populateTransactionField(transactionsMap, inputs.rows, parseInputsRow);
  transactionsMap = populateTransactionField(transactionsMap, outputs.rows, parseOutputsRow);
  transactionsMap = populateTransactionField(transactionsMap, withdrawals.rows, parseWithdrawalsRow);
  transactionsMap = populateTransactionField(transactionsMap, registrations.rows, parseRegistrationsRow);
  transactionsMap = populateTransactionField(transactionsMap, deregistrations.rows, parseDeregistrationsRow);
  transactionsMap = populateTransactionField(transactionsMap, delegations.rows, parseDelegationsRow);
  transactionsMap = populateTransactionField(transactionsMap, poolRetirements.rows, parsePoolRetirementRow);
  transactionsMap = populateTransactionField(transactionsMap, votes.rows, parseVoteRow);

  const mappedPoolRegistrations = mapToTransactionPoolRegistrations(poolsData.rows, poolsOwners.rows, poolsRelays.rows);
  transactionsMap = populateTransactionField(transactionsMap, mappedPoolRegistrations, parsePoolRegistrationsRows);

  // eslint-disable-next-line @typescript-eslint/ban-ts-comment
  // @ts-ignore it will never be undefined
  return Object.values(transactionsMap);
}

async listen(): Promise<void> {
    this.runner = await run({
      noHandleSignals: false,
      pgPool: new Pool(this.getPostgresPoolConfig()),
      taskList: this.getTaskHandlers(),
    });
  }

pgPool = new Pool({
  connectionString: `postgres://${User}:${Password}@${Host}:${Port}/${Database}`
})

describe("DB", () => {
  let db: DB;
  let table: Table<TestObject>;
  beforeEach(async () => {
    db = new DB();
    await db.start({ postgresUrl: `postgresql://postgres@localhost/test` });
    table = new Table<TestObject>({ db, schema: testSchema });
    await table.ensureTable();
  });

  afterEach(async () => {
    await db.close();
    const pool = new Pool({
      connectionString: `postgresql://postgres@localhost/postgres`,
      connectionTimeoutMillis: 5000,
    });
    await pool.query("DROP DATABASE test");
    await pool.end();
  });

  it("should do CRUD operations", async () => {
    const doc = <TestObject>{
      id: uuid(),
      example: "test text",
    };
    await table.create(doc);
    let ret = await table.get(doc.id);
    expect(ret.example).toEqual(doc.example);
    expect(ret.importance).toEqual(doc.importance);
    await table.replace({
      id: doc.id,
      example: "changed text",
      importance: 5,
    });
    ret = await table.get(doc.id);
    expect(ret.example).toEqual("changed text");
    await table.delete(doc.id);
    ret = await table.get(doc.id);
    expect(ret).toEqual(null);
  });

  it("should find() stuff", async () => {});
});

readonly database: Pool;

@def
    async config(): Promise<ApolloServerExpressConfig> {
        let plugins: PluginDefinition[] = []
        let typeDefs = [buildServerSchema(this.model(), this.dialect())]
        let resolvers = buildResolvers(this.model(), this.dialect())

        let requestCheck = this.customCheck()
        if (requestCheck) {
            plugins.push(createCheckPlugin(requestCheck, this.model()))
        }

        let context: () => ResolverContext

        let customResolvers = await this.customResolvers()
        if (customResolvers) {
            typeDefs.push(customResolvers.typeDefs)
            resolvers = mergeResolvers([resolvers, customResolvers.resolvers])
            context = () => this.createTypeormResolverContext()
        } else {
            context = () => this.createPoolResolverContext()
        }

        plugins.push({
            serverWillStart: async () => {
                if (customResolvers) {
                    this.db = await this.typeormConnection()
                } else {
                    this.db = this.pgPool()
                }
                return {
                    serverWillStop: async () => {
                        if (this.db == null) return
                        if (this.db instanceof Pool) {
                            await this.db.end()
                        } else {
                            await this.db.close()
                        }
                    }
                }
            },
            async requestDidStart() {
                return {
                    willSendResponse(req: any) {
                        return req.context.openReaderTransaction.close()
                    }
                }
            }
        })

        plugins.push(
            ApolloServerPluginDrainHttpServer({httpServer: this.httpServer()})
        )

        return {
            typeDefs,
            resolvers,
            context,
            plugins
        }
    }

pool = new Pool({
    connectionString: process.env.DATABASE_URL,
    // ssl: true,
    ssl: {
        rejectUnauthorized: false,
    },
})

export async function resetTestDatabase(
    code?: string,
    extraServerConfig: Partial<PluginsServerConfig> = {},
    extraRows: ExtraDatabaseRows = {},
    { withExtendedTestData = true }: { withExtendedTestData?: boolean } = {}
): Promise<void> {
    const config = { ...defaultConfig, ...extraServerConfig }
    const db = new Pool({ connectionString: config.DATABASE_URL! })
    try {
        await db.query('TRUNCATE TABLE ee_hook CASCADE')
    } catch {}

    await db.query(`
        TRUNCATE TABLE
            posthog_personalapikey,
            posthog_featureflag,
            posthog_annotation,
            posthog_dashboarditem,
            posthog_dashboard,
            posthog_cohortpeople,
            posthog_cohort,
            posthog_actionstep,
            posthog_action_events,
            posthog_action,
            posthog_element,
            posthog_elementgroup,
            posthog_sessionrecordingevent,
            posthog_persondistinctid,
            posthog_person,
            posthog_event,
            posthog_pluginstorage,
            posthog_pluginattachment,
            posthog_pluginlogentry,
            posthog_pluginconfig,
            posthog_plugin,
            posthog_eventdefinition,
            posthog_propertydefinition,
            posthog_grouptypemapping,
            posthog_team,
            posthog_organizationmembership,
            posthog_organization,
            posthog_user
        CASCADE
    `)
    const mocks = makePluginObjects(code)
    const teamIds = mocks.pluginConfigRows.map((c) => c.team_id)
    const teamIdToCreate = teamIds[0]
    await createUserTeamAndOrganization(db, teamIdToCreate)
    if (withExtendedTestData) {
        await insertRow(db, 'posthog_action', {
            id: teamIdToCreate + 67,
            team_id: teamIdToCreate,
            name: 'Test Action',
            created_at: new Date().toISOString(),
            created_by_id: commonUserId,
            deleted: false,
            post_to_slack: true,
            slack_message_format: '',
            is_calculating: false,
            updated_at: new Date().toISOString(),
            last_calculated_at: new Date().toISOString(),
        } as RawAction)
        await insertRow(db, 'posthog_actionstep', {
            id: teamIdToCreate + 911,
            action_id: teamIdToCreate + 67,
            tag_name: null,
            text: null,
            href: null,
            selector: null,
            url: null,
            url_matching: null,
            name: null,
            event: null,
            properties: [{ type: 'event', operator: PropertyOperator.Exact, key: 'foo', value: ['bar'] }],
        })
        for (const plugin of mocks.pluginRows.concat(extraRows.plugins ?? [])) {
            await insertRow(db, 'posthog_plugin', plugin)
        }
        for (const pluginConfig of mocks.pluginConfigRows.concat(extraRows.pluginConfigs ?? [])) {
            await insertRow(db, 'posthog_pluginconfig', pluginConfig)
        }
        for (const pluginAttachment of mocks.pluginAttachmentRows.concat(extraRows.pluginAttachments ?? [])) {
            await insertRow(db, 'posthog_pluginattachment', pluginAttachment)
        }
    }
    await db.end()
}