my data in postgresql is automatically encrypted when creating api with go and sqlc library

Question

I just develop an api to get all field of table of Postgresql.

When I use SQL query table 'information_schema.columns' on pgAdmin4 it works (no encryption) as below picture!

However, I just developed api using go, sqlc and chi-router library and the response in postman is encrypted as below picture.

enter image description here

I need the same result as my pgAdmin4 on my postman. Please help.

-- information_schema.columns.sql

-- name: GetTableDetail :many

SELECT table_name, column_name FROM information_schema.columns WHERE table_name = $1;

v1Router.Post("/table", apiCfg.handlerGetTableDetail)

func (apiCfg *apiConfig) handlerGetTableDetail(w http.ResponseWriter, r *http.Request) {

    type parameters struct {
        Table string `json:"table"`
    }

    decoder := json.NewDecoder(r.Body)

    params := parameters{}

    err := decoder.Decode(&params)
    if err != nil {
        respondWithError(w, 400, fmt.Sprintf("Error parsing JSON : %v", err))
        return
    }

    data, err := apiCfg.DB.GetTableDetail(r.Context(), params.Table)
    if err != nil {
        respondWithError(w, 400, fmt.Sprintf("Couldn't find any data : %v", err))
        return
    }

    respondWithJSON(w, 200, data)
}

// Code generated by sqlc. DO NOT EDIT.
// versions:
//   sqlc v1.19.1
// source: information_schema.columns.sql

package database

import (
    "context"
)

const getTableDetail = `-- name: GetTableDetail :many

SELECT table_name, column_name FROM information_schema.columns WHERE table_name = $1
`

type GetTableDetailRow struct {
    TableName  interface{}
    ColumnName interface{}
}

func (q *Queries) GetTableDetail(ctx context.Context, tableName interface{}) ([]GetTableDetailRow, error) {
    rows, err := q.db.QueryContext(ctx, getTableDetail, tableName)
    if err != nil {
        return nil, err
    }
    defer rows.Close()
    var items []GetTableDetailRow
    for rows.Next() {
        var i GetTableDetailRow
        if err := rows.Scan(&i.TableName, &i.ColumnName); err != nil {
            return nil, err
        }
        items = append(items, i)
    }
    if err := rows.Close(); err != nil {
        return nil, err
    }
    if err := rows.Err(); err != nil {
        return nil, err
    }
    return items, nil
}

I need the same result as my pgAdmin4 on my postman. In this case it should be like below picture

[ { "TableName" : "tablec", "ColumnName" : "firstname" }, { "TableName" : "tablec", "ColumnName" : "lastname" } ]

It's not encrypted, it's encoded. This is because you are using `interface{}` as the type of the fields into which you scan the database columns, because of that the data is kept as `[]byte` and `[]byte` when marshalled as JSON using the standard library will be base64 encoded by default. Do not use `interface{}` or `[]byte` for the struct fields if you want strings in the JSON, instead use the type `string` for those fields. — mkopriva, Jul 22 '23 at 04:57
I see now that `GetTableDetailRow` with the `interface{}` fields is generated by `sqlc`, this is likely due to the _special data type_ of the `table_name` and `column_name` columns, which is `sql_identifier` and which is probably not explicitly supported by `sqlc`. Perhaps you could fix the issue by casting that type to another, one that _is_ supported by `sqlc`, e.g. to `text`, i.e. in your `information_schema.columns.sql` file try doing `SELECT table_name::text, column_name::text FROM ...`. — mkopriva, Jul 22 '23 at 05:07

score 2 · Answer 1 · answered Jul 22 '23 at 05:40

Refer to USER : mkopriva Thanks!!

Fix the SQL Statement in information_schema.columns.sql

FROM

SELECT table_name, column_name FROM information_schema.columns

TO

SELECT table_name::text, column_name::text FROM information_schema.columns

Then "sqlc generate"

my data in postgresql is automatically encrypted when creating api with go and sqlc library

1 Answers1