pg#QueryResult TypeScript Examples

postgresGet = async (
    db: DB,
    pluginConfigId: PluginConfig['id'],
    key: string
): Promise<QueryResult<any>> => {
    return await db.postgresQuery(
        'SELECT * FROM posthog_pluginstorage WHERE "plugin_config_id"=$1 AND "key"=$2 LIMIT 1',
        [pluginConfigId, key],
        'storageGet'
    )
}

async getByPlaybackId(
    playbackId: string,
    opts?: QueryOptions
  ): Promise<WithID<Asset>> {
    const res: QueryResult<DBLegacyObject> = await this.db.queryWithOpts(
      sql`SELECT id, data FROM asset WHERE data->>'playbackId' = ${playbackId}`.setName(
        `${this.name}_by_playbackid`
      ),
      opts
    );
    if (res.rowCount < 1) {
      return null;
    }
    return assetStatusCompat(res.rows[0].data as WithID<Asset>);
  }

// ClickHouse

    public clickhouseQuery(
        query: string,
        options?: ClickHouse.QueryOptions
    ): Promise<ClickHouse.QueryResult<Record<string, any>>> {
        return instrumentQuery(this.statsd, 'query.clickhouse', undefined, async () => {
            if (!this.clickhouse) {
                throw new Error('ClickHouse connection has not been provided to this DB instance!')
            }
            const timeout = timeoutGuard('ClickHouse slow query warning after 30 sec', { query })
            try {
                return await this.clickhouse.querying(query, options)
            } finally {
                clearTimeout(timeout)
            }
        })
    }

queryWithOpts<T, I extends any[] = any[]>(
    query: QueryConfig<I>,
    opts: QueryOptions = { useReplica: true }
  ): Promise<QueryResult<T>> {
    const { useReplica = true } = opts;
    if (useReplica && this.replicaPool) {
      return this.replicaPool.query(query);
    }
    return this.pool.query(query);
  }

async runQueryNoMetrics<T, I extends any[] = any[]>(
    pool: Pool,
    query: string | QueryConfig<I>,
    values?: I
  ): Promise<QueryResult<T>> {
    let queryLog: string;
    if (typeof query === "string") {
      queryLog = JSON.stringify({ query: query.trim(), values });
    } else {
      queryLog = JSON.stringify(query);
    }
    let result: QueryResult;
    logger.info(`runQuery phase=start query=${queryLog}`);
    const start = Date.now();
    try {
      result = await pool.query(query, values);
    } catch (e) {
      logger.error(
        `runQuery phase=error elapsed=${Date.now() - start}ms error=${
          e.message
        } query=${queryLog}`
      );
      throw e;
    }
    logger.info(
      `runQuery phase=success elapsed=${Date.now() - start}ms rows=${
        result?.rowCount
      } query=${queryLog}`
    );
    return result;
  }

// Internal logging function — use query() or replicaQuery() externally
  async runQuery<T, I extends any[] = any[]>(
    pool: Pool,
    query: string | QueryConfig<I>,
    values?: I
  ): Promise<QueryResult<T>> {
    let labels: QueryHistogramLabels = {
      query: typeof query === "string" ? query.trim() : query.text,
      result: "success",
    };
    const queryTimer = metricHistogram.startTimer();
    try {
      return await this.runQueryNoMetrics(pool, query, values);
    } catch (e) {
      labels.result = "error";
      throw e;
    } finally {
      queryTimer(labels);
    }
  }

async cachedUsageHistory(
    fromTime: number,
    toTime: number,
    opts?: QueryOptions
  ): Promise<UsageData[]> {
    let usage = [];
    const q1 = sql`SELECT
      id,
      (data->>'date')::real as date,
      (data->>'sourceSegmentsDuration')::real as sourceSegmentsDuration,
      (data->>'transcodedSegmentsDuration')::real as transcodedSegmentsDuration,
      (data->>'sourceSegments')::real as sourceSegments,
      (data->>'transcodedSegments')::real as transcodedSegments,
      (data->>'streamCount')::real as streamCount

    FROM usage WHERE data->>'date' >= ${fromTime}
      AND data->>'date' < ${toTime}
      ORDER BY date
    `;

    let res: QueryResult<DBUsageData>;
    res = await this.db.queryWithOpts(q1, opts);

    if (res.rowCount > 0) {
      for (const row of res.rows) {
        usage.push({
          id: row.id,
          date: row.date,
          sourceSegments: row.sourcesegments,
          transcodedSegments: row.transcodedsegments,
          sourceSegmentsDuration: row.sourcesegmentsduration,
          transcodedSegmentsDuration: row.transcodedsegmentsduration,
          streamCount: row.streamcount,
        });
      }
    }
    return usage;
  }

async getByStreamKey(
    streamKey: string,
    opts?: QueryOptions
  ): Promise<DBStream> {
    const res: QueryResult<DBLegacyObject> = await this.db.queryWithOpts(
      sql`SELECT data FROM stream  WHERE data->>'streamKey'=${streamKey}`.setName(
        `${this.name}_by_streamKey`
      ),
      opts
    );
    return res.rowCount < 1 ? null : (res.rows[0].data as DBStream);
  }

async getByPlaybackId(
    playbackId: string,
    opts?: QueryOptions
  ): Promise<DBStream> {
    const res: QueryResult<DBLegacyObject> = await this.db.queryWithOpts(
      sql`SELECT data FROM stream  WHERE data->>'playbackId'=${playbackId}`.setName(
        `${this.name}_by_playbackid`
      ),
      opts
    );
    return res.rowCount < 1 ? null : (res.rows[0].data as DBStream);
  }

async getLastSession(id: string, opts?: QueryOptions): Promise<DBStream> {
    const res: QueryResult<DBLegacyObject> = await this.db.queryWithOpts(
      sql`SELECT data FROM stream  WHERE data->>'parentId'=${id} ORDER BY data->'createdAt' DESC LIMIT 1`.setName(
        `${this.name}_by_parentid_last_session`
      ),
      opts
    );
    return res.rowCount < 1 ? null : (res.rows[0].data as DBStream);
  }

// get a single document by id
  async get(id: string, opts: GetOptions = { useReplica: true }): Promise<T> {
    if (!id) {
      throw new Error("missing id");
    }
    let res: QueryResult<DBLegacyObject>;
    if (!opts.useReplica) {
      res = await this.db.query(
        sql`SELECT data FROM `
          .append(this.name)
          .append(sql` WHERE id=${id}`.setName(`${this.name}_by_id`))
      );
    } else {
      res = await this.db.replicaQuery(
        sql`SELECT data FROM `
          .append(this.name)
          .append(sql` WHERE id=${id}`.setName(`${this.name}_by_id`))
      );
    }

    if (res.rowCount < 1) {
      return null;
    }
    return res.rows[0].data as T;
  }

async getMany(
    ids: Array<string>,
    opts: GetOptions = { useReplica: true }
  ): Promise<Array<T>> {
    if (!ids || !ids.length) {
      throw new Error("missing ids");
    }
    let res: QueryResult<DBLegacyObject> = await this.db.queryWithOpts(
      {
        name: `${this.name}_by_ids_${ids.length}`,
        text: `SELECT data FROM ${this.name}  WHERE id IN (${ids
          .map((_, i) => "$" + (i + 1))
          .join(",")})`,
        values: ids,
      },
      opts
    );

    if (res.rowCount < 1) {
      return null;
    }
    return res.rows.map((o) => o.data as T);
  }

function getAssetCountByCollection(params: RequestValues, ctx: AtomicAssetsContext): Promise<QueryResult<{
    collection_name: string;
    assets: string;
}>> {
    const collectionQuery = new QueryBuilder(
        'SELECT collection_name, COUNT(*) as assets ' +
        'FROM atomicassets_assets asset'
    );

    collectionQuery.equal('contract', ctx.coreArgs.atomicassets_account);
    collectionQuery.equal('owner', ctx.pathParams.account);

    buildGreylistFilter(params, collectionQuery, {collectionName: 'asset.collection_name'});
    buildHideOffersFilter(params, collectionQuery, 'asset');

    collectionQuery.group(['contract', 'collection_name']);
    collectionQuery.append('ORDER BY assets DESC');

    return ctx.db.query(collectionQuery.buildString(), collectionQuery.buildValues());
}

function getAssetCountByTemplate(params: RequestValues, ctx: AtomicAssetsContext): Promise<QueryResult<{
    collection_name: string;
    assets: string;
    template_id: null | string;
}>> {
    const templateQuery = new QueryBuilder(
        'SELECT collection_name, template_id, COUNT(*) as assets ' +
        'FROM atomicassets_assets asset'
    );
    templateQuery.equal('contract', ctx.coreArgs.atomicassets_account);
    templateQuery.equal('owner', ctx.pathParams.account);

    buildGreylistFilter(params, templateQuery, {collectionName: 'asset.collection_name'});
    buildHideOffersFilter(params, templateQuery, 'asset');

    templateQuery.group(['contract', 'collection_name', 'template_id']);
    templateQuery.append('ORDER BY assets DESC');

    return ctx.db.query(templateQuery.buildString(), templateQuery.buildValues());
}

async query<T = any>(queryText: string, values?: any[]): Promise<QueryResult<T>> {
        const startTime = Date.now();

        logger.debug(queryText, values);

        try {
            const result = await this.database.query(queryText, values);
            const duration = Date.now() - startTime;

            if (this.config.slow_query_threshold && duration >= this.config.slow_query_threshold) {
                logger.warn('Query took ' + duration + ' ms', {
                    queryText, values
                });
            }

            return result;
        } catch (error) {
            logger.warn('Query exception', {
                message: String(error), error, queryText, values
            });

            throw error;
        }
    }

async query(queryStr: string, values: any[] = [], lock: boolean = true): Promise<QueryResult> {
        await this.acquireLock(lock);

        try {
            await this.begin();

            return await this.clientQuery(queryStr, values);
        } finally {
            this.releaseLock(lock);
        }
    }

async delete(
        table: string, condition: Condition, reversible: boolean = true, lock: boolean = true
    ): Promise<QueryResult> {
        await this.acquireLock(lock);

        try {
            await this.begin();

            let selectQuery;
            if (this.currentBlock && reversible) {
                selectQuery = await this.clientQuery(
                    'SELECT * FROM ' + this.client.escapeIdentifier(table) + ' WHERE ' + condition.str + ';', condition.values
                );
            }

            const queryStr = 'DELETE FROM ' + this.client.escapeIdentifier(table) + ' WHERE ' + condition.str + ';';
            const query = await this.clientQuery(queryStr, condition.values);

            this.stats.operations += selectQuery ? selectQuery.rowCount : 1;

            if (selectQuery && selectQuery.rows.length > 0) {
                const rollback = this.buildRollbackQuery('insert', table, selectQuery.rows);

                await this.saveRollbackQueries([rollback]);
            }

            return query;
        } finally {
            this.releaseLock(lock);
        }
    }

private async clientQuery(queryText: string, values: any[] = []): Promise<QueryResult> {
        try {
            logger.debug('contract db query: ' + queryText, values);

            return await this.client.query(queryText, values);
        } catch (error) {
            logger.error('Failed to execute SQL query ', {queryText, values, error});

            throw error;
        }
    }

// sets 'isActive' field to false for many objects at once
app.patch(
  "/deactivate-many",
  authorizer({ anyAdmin: true }),
  validatePost("deactivate-many-payload"),
  async (req, res) => {
    let upRes: QueryResult;
    try {
      upRes = await db.stream.markIsActiveFalseMany(req.body.ids);

      // trigger the webhooks
      try {
        await triggerManyIdleStreamsWebhook(req.body.ids, req.queue);
      } catch (err) {
        console.error(`error while triggering the many idle webhooks`, err);
      }

      if (upRes.rowCount) {
        console.log(
          `set isActive=false for ids=${req.body.ids} rowCount=${upRes.rowCount}`
        );
      }
    } catch (e) {
      console.error(
        `error setting stream active to false ids=${req.body.ids} err=${e}`
      );
      upRes = { rowCount: 0 } as QueryResult;
    }

    res.status(200);
    return res.json({ rowCount: upRes?.rowCount ?? 0 });
  }
);

// Postgres

    public postgresQuery<R extends QueryResultRow = any, I extends any[] = any[]>(
        queryString: string,
        values: I | undefined,
        tag: string,
        client?: PoolClient
    ): Promise<QueryResult<R>> {
        return instrumentQuery(this.statsd, 'query.postgres', tag, async () => {
            let fullQuery = ''
            try {
                fullQuery = getFinalPostgresQuery(queryString, values as any[])
            } catch {}
            const timeout = timeoutGuard('Postgres slow query warning after 30 sec', {
                queryString,
                values,
                fullQuery,
            })

            // Annotate query string to give context when looking at DB logs
            queryString = `/* plugin-server:${tag} */ ${queryString}`
            try {
                if (client) {
                    return await client.query(queryString, values)
                } else {
                    return await this.postgres.query(queryString, values)
                }
            } finally {
                clearTimeout(timeout)
            }
        })
    }

public async fetchPerson(
        teamId: number,
        distinctId: string,
        client?: PoolClient,
        options: { forUpdate?: boolean } = {}
    ): Promise<Person | undefined> {
        let queryString = `SELECT
                posthog_person.id, posthog_person.created_at, posthog_person.team_id, posthog_person.properties,
                posthog_person.properties_last_updated_at, posthog_person.properties_last_operation, posthog_person.is_user_id, posthog_person.is_identified,
                posthog_person.uuid, posthog_persondistinctid.team_id AS persondistinctid__team_id,
                posthog_persondistinctid.distinct_id AS persondistinctid__distinct_id
            FROM posthog_person
            JOIN posthog_persondistinctid ON (posthog_persondistinctid.person_id = posthog_person.id)
            WHERE
                posthog_person.team_id = $1
                AND posthog_persondistinctid.team_id = $1
                AND posthog_persondistinctid.distinct_id = $2`
        if (options.forUpdate) {
            // Locks the teamId and distinctId tied to this personId + this person's info
            queryString = queryString.concat(` FOR UPDATE`)
        }
        const values = [teamId, distinctId]

        const selectResult: QueryResult = await this.postgresQuery(queryString, values, 'fetchPerson', client)

        if (selectResult.rows.length > 0) {
            const rawPerson: RawPerson = selectResult.rows[0]
            return {
                ...rawPerson,
                created_at: DateTime.fromISO(rawPerson.created_at).toUTC(),
                version: Number(rawPerson.version || 0),
            }
        }
    }

public async updatePerson(
        client: PoolClient,
        personId: number,
        createdAt: DateTime,
        properties: Properties,
        propertiesLastUpdatedAt: PropertiesLastUpdatedAt,
        propertiesLastOperation: PropertiesLastOperation
    ): Promise<number> {
        const updateResult: QueryResult = await this.postgresQuery(
            `UPDATE posthog_person SET
            created_at = $1,
            properties = $2,
            properties_last_updated_at = $3,
            properties_last_operation = $4,
            version = COALESCE(version, 0)::numeric + 1
        WHERE id = $5
        RETURNING version`,
            [
                createdAt.toISO(),
                JSON.stringify(properties),
                JSON.stringify(propertiesLastUpdatedAt),
                JSON.stringify(propertiesLastOperation),
                personId,
            ],
            'updatePersonProperties',
            client
        )

        if (updateResult.rows.length === 0) {
            // this function should always be called in a transaction with person fetch locking for update before
            throw new RaceConditionError('Failed updating person properties')
        }
        return Number(updateResult.rows[0].version)
    }

public async createPersonalApiKey({
        id,
        user_id,
        label,
        value,
        created_at,
    }: CreatePersonalApiKeyPayload): Promise<QueryResult> {
        return await this.postgresQuery(
            `INSERT INTO posthog_personalapikey (id, user_id, label, value, created_at)
            VALUES ($1, $2, $3, $4, $5)
            RETURNING value`,
            [id, user_id, label, value, created_at.toISOString()],
            'createPersonalApiKey'
        )
    }

public async fetchGroup(
        teamId: TeamId,
        groupTypeIndex: GroupTypeIndex,
        groupKey: string,
        client?: PoolClient,
        options: { forUpdate?: boolean } = {}
    ): Promise<Group | undefined> {
        let queryString = `SELECT * FROM posthog_group WHERE team_id = $1 AND group_type_index = $2 AND group_key = $3`

        if (options.forUpdate) {
            queryString = queryString.concat(` FOR UPDATE`)
        }

        const selectResult: QueryResult = await this.postgresQuery(
            queryString,
            [teamId, groupTypeIndex, groupKey],
            'fetchGroup',
            client
        )

        if (selectResult.rows.length > 0) {
            const rawGroup: RawGroup = selectResult.rows[0]
            return {
                ...rawGroup,
                created_at: DateTime.fromISO(rawGroup.created_at).toUTC(),
                version: Number(rawGroup.version || 0),
            }
        }
    }

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;
}

parseTransactionRows = (result: QueryResult<FindTransaction>): Transaction[] =>
  result.rows.map(row => ({
    hash: hexFormatter(row.hash),
    blockHash: hexFormatter(row.blockHash),
    blockNo: row.blockNo,
    fee: row.fee,
    size: row.size,
    scriptSize: row.scriptSize,
    validContract: row.validContract
  }))

public async moveDistinctIds(source: Person, target: Person, client?: PoolClient): Promise<ProducerRecord[]> {
        let movedDistinctIdResult: QueryResult<any> | null = null
        try {
            movedDistinctIdResult = await this.postgresQuery(
                `
                    UPDATE posthog_persondistinctid
                    SET person_id = $1, version = COALESCE(version, 0)::numeric + 1
                    WHERE person_id = $2
                      AND team_id = $3
                    RETURNING *
                `,
                [target.id, source.id, target.team_id],
                'updateDistinctIdPerson',
                client
            )
        } catch (error) {
            if (
                (error as Error).message.includes(
                    'insert or update on table "posthog_persondistinctid" violates foreign key constraint'
                )
            ) {
                // this is caused by a race condition where the _target_ person was deleted after fetching but
                // before the update query ran and will trigger a retry with updated persons
                throw new RaceConditionError(
                    'Failed trying to move distinct IDs because target person no longer exists.'
                )
            }

            throw error
        }

        // this is caused by a race condition where the _source_ person was deleted after fetching but
        // before the update query ran and will trigger a retry with updated persons
        if (movedDistinctIdResult.rows.length === 0) {
            throw new RaceConditionError(
                `Failed trying to move distinct IDs because the source person no longer exists.`
            )
        }

        const kafkaMessages = []
        if (this.kafkaProducer) {
            for (const row of movedDistinctIdResult.rows) {
                const { id, version: versionStr, ...usefulColumns } = row as PersonDistinctId
                const version = Number(versionStr || 0)
                kafkaMessages.push({
                    topic: KAFKA_PERSON_DISTINCT_ID,
                    messages: [
                        {
                            value: Buffer.from(
                                JSON.stringify({ ...usefulColumns, version, person_id: target.uuid, is_deleted: 0 })
                            ),
                        },
                    ],
                })

                if (await this.fetchWriteToPersonUniqueId()) {
                    kafkaMessages.push({
                        topic: KAFKA_PERSON_UNIQUE_ID,
                        messages: [
                            {
                                value: Buffer.from(
                                    JSON.stringify({ ...usefulColumns, person_id: target.uuid, is_deleted: 0 })
                                ),
                            },
                            {
                                value: Buffer.from(
                                    JSON.stringify({ ...usefulColumns, person_id: source.uuid, is_deleted: 1 })
                                ),
                            },
                        ],
                    })
                }
            }
        }
        return kafkaMessages
    }