使用Go和SQLC库创建API时,我的数据在PostgreSQL中自动加密。

huangapple go评论85阅读模式
英文:

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

问题

我刚刚开发了一个用于获取PostgreSQL表中所有字段的API。

当我在pgAdmin4上使用SQL查询表'information_schema.columns'时,它的工作方式如下图所示(没有加密)!

enter image description here

然而,我刚刚使用go、sqlc和chi-router库开发了一个API,而在Postman中的响应如下图所示。

enter image description here

我需要在Postman中获得与pgAdmin4相同的结果。请帮忙。

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

我需要在Postman中获得与pgAdmin4相同的结果。在这种情况下,结果应该如下图所示:

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

英文:

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!

enter image description here

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"
}
]

答案1

得分: 2

参考用户:mkopriva 谢谢!!

修复 information_schema.columns.sql 中的 SQL 语句

SELECT table_name, column_name FROM information_schema.columns

修改为

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

然后运行 "sqlc generate"。

英文:

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"

huangapple
  • 本文由 发表于 2023年7月22日 12:52:37
  • 转载请务必保留本文链接:https://go.coder-hub.com/76742354.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定