Replace object param keys for number ref to use with postgres driver

postgres-sql-with-json-parameters.ts

// Example
// https://www.typescriptlang.org/play?#code/MYewdgziA2CmB0w4EMBOAKAlAKG6SALgATCqzIGwBKsAZrGWMLEQLxHpm0BcRYArgFsARg0y8IBVAEswAczYA+IgAMAOgBINAby4BfFXnCSSZCrAAKaZINiVUAaVgBPNhwDWLiVNlzxRSRl5JVVuHU9nA1x8EwIQAGUARQAZNwAeABUiWAAPSjAAEwgiEGEAK1hgYnZkMGdFdAgAR2hvILkAGiIAB2tbewheDP8AbUDfLtrnEYBdGZDtbCJlojhiLgBJQty3AEYllbW+WAB3eJa3ZugD5ZjiWDAfWGL2AHlyyoJ4B6eIdF7UDY7AwIDgViRjMQ0KgrIDBC8iLNcODaCAMHdEREugA3ZDQfiweYgWjZR4yZ6YIiLcHgjEAoH2NykciUWEMhhOZzoCJgmm3SFEMhyHbsMCnIg0OQAURy3X+fWBqC6AHI5MreXyMVwGA9mEyzJQaPRGMxOHQtgVcjgbjSxWcLqLTudoPAyN1oMhTULcl1tSbYNa+ctoWz4fBuvwIAALdC4-EBm3gzbbHIAalTNr0yJWZAI-FQYERdudk1QML6EBm2Cz2Fk9lonpYoeK1JWYCBbV8NrRltQvGVAEF4gBhZVEAA+RGVABEpSPlTboNJBNICLwBCIGNXcAB6HdECJ8IFEWREWjSVAmbogCCr6TgIyEHoV3a8ZtuVvLdu2fuCZA5ZUOm7VBe37IdRyA8ElxXNciF2AAGICaz3A8XCPWwT0LD0rxvO8HzpCsACY3wrD9gNAqdwMAxdl1XXgEMgtsOynP8AKQ6IBSuNxDHBCBYDgKoiETFYACpGL5bhoNXIghxPfIIAAfW6BglOQYUbVoVAQEEISgyIAhkGEOBdJWE4ox1Ey+TAbSWHYbhv1gciGCIYRXAcohuB7LcoNo4hJN87BDEfEwyAgfhoAIXY3DiJJkjSZsGiuLp6XhXYcC1Z5woIQjooSFJ4orRKWmSojrRiGAEGgEA5HQZUqDnABVZIsl2dVgoq+Aqpq0KsrS9q4E66ravq+ImqyQi2vKgautqgBZEACmkWhnF8fTzMFTKIqIApwDUZViGEMx3ACC5UVQbaZGxZzr0kORQsm4wOpmnqIsIzAgA

const createReference = (ref: number): string => `\$${ref}`
const createParameterKey = (key: string): string => `:${key}`

const toSQL = <T extends object = any>(sql: string, parameters: T): [string, any[]] => {
    let refIndex = 1
    let newSql = sql
    const entries = Object.entries(params)
    const arrParams = []

    for(const [key, value] of entries) {
        const parameter = createParameterKey(key)
        const regex = new RegExp(parameter, 'g')
        const reference = createReference(refIndex)

        newSql = newSql.replace(regex, reference)

        arrParams.push(value)
        refIndex++
    }

    return [newSql, arrParams]
}

Example Typescript Online