import { PostgresClient } from '@jvalue/node-dry-pg' ; import { ClientBase , QueryResult } from 'pg' ; import { POSTGRES_SCHEMA } from '../env' ; import { InsertStorageContent , StorageContent , StorageContentRepository , } from './storageContentRepository' ; const EXISTS_TABLE_STATEMENT = ( table ) : string => `SELECT to_regclass('"${ POSTGRES_SCHEMA }"."${ table }"')` ; const GET_ALL_CONTENT_STATEMENT = ( table ) : string => `SELECT * FROM "${ POSTGRES_SCHEMA }"."${ table }"` ; const GET_CONTENT_STATEMENT = ( table ) : string => `SELECT * FROM "${ POSTGRES_SCHEMA }"."${ table }" WHERE id = $1` ; const INSERT_CONTENT_STATEMENT = ( table ) : string => `INSERT INTO "${ POSTGRES_SCHEMA }"."${ table }" ("data", "pipelineId", "timestamp") VALUES ($1, $2, $3) RETURNING *` ; /**
 * The QueryResultRow of a <code>SELECT to_regclass(...)</code> query
 */ interface ExistsTableResultRow { /**
     * PostgreSQL's Object Identifier as string of the requested table or null if the table does not exists
     */ to_regclass : string | null ; } function extractTableExistenceFromResult ( result ) { return result . rows [ 0 ] . to_regclass != null ; } interface DatabaseStorageContent { id : string ; data : unknown ; timestamp : Date ; pipelineId : string ; } async function existsTable ( client , tableIdentifier ) { const resultSet = await client . query ( EXISTS_TABLE_STATEMENT ( tableIdentifier ) ) ; return extractTableExistenceFromResult ( resultSet ) ; } export class PostgresStorageContentRepository implements StorageContentRepository { constructor ( private readonly postgresClient ) { } async getAllContent ( tableIdentifier ) { return await this . postgresClient . transaction ( async ( client ) => { const tableExists = await existsTable ( client , tableIdentifier ) ; if ( ! tableExists ) { console . debug ( `Table "${ tableIdentifier }" does not exist - returning no data` ) ; return undefined ; } const resultSet = await client . query ( GET_ALL_CONTENT_STATEMENT ( tableIdentifier ) ) ; return this . toStorageContents ( resultSet ) ; } ) ; } async getContent ( tableIdentifier , contentId ) { return await this . postgresClient . transaction ( async ( client ) => { const tableExists = await existsTable ( client , tableIdentifier ) ; if ( ! tableExists ) { console . debug ( `Table "${ tableIdentifier }" does not exist - returning no data` ) ; return undefined ; } const resultSet = await client . query ( GET_CONTENT_STATEMENT ( tableIdentifier ) , [ contentId ] ) ; const content = this . toStorageContents ( resultSet ) ; if ( content . length === 0 ) { console . debug ( `No content found for table "${ tableIdentifier }", id ${ contentId }` ) ; return undefined ; } return content [ 0 ] ; } ) ; } async saveContent ( tableIdentifier , content ) { return await this . postgresClient . transaction ( async ( client ) => { const tableExists = await existsTable ( client , tableIdentifier ) ; if ( ! tableExists ) { throw new Error ( `Table "${ tableIdentifier }" does not exist - can not save content` ) ; } /**
             * When passed an array as value, pg assumes the value is meant to be a native Postgres array
             * and therefore fails with a "invalid input syntax for type json" error when the target field
             * is actually of type jsob/jsonb.
             *
             * Ref: https://github.com/brianc/node-postgres/issues/2012
             */ const data = Array . isArray ( content . data ) ? JSON . stringify ( content . data ) : content . data ; const values = [ data , content . pipelineId , content . timestamp ] ; const { rows } = await client . query ( INSERT_CONTENT_STATEMENT ( tableIdentifier ) , values ) ; return Number . parseInt ( ( rows as Array < { id : string ; } > ) [ 0 ] . id , 10 ) ; } ) ; } private toStorageContents ( resultSet ) { const contents = resultSet . rows ; return contents . map ( ( x ) => ( { ... x , id : Number . parseInt ( x . id , 10 ) , pipelineId : Number . parseInt ( x . pipelineId , 10 ) , } ) ) ; } }