This content originally appeared on DEV Community and was authored by Vic Shóstak
Introduction
Hey, DEV people! ? Today, I am starting a new series of short articles called "Simple Errors", where I will cover various dead ends and pitfalls that I have encountered so far in my programming practice.
I will always try to follow four rules when writing such articles:
- An explanation of the cause of the error;
- A description of the input data in which the error occurred to me;
- My solution to the error;
- Conclusions and words of motivation;
Don't judge too harshly, write what you think of such articles, suggest your own topics for parsing in the comments... Here we go! ?
? Table of contents
Explanation of the error
When developing a REST API (or any other product) in Golang with PostgreSQL database over the high-performance driver jackc/pgx, sometimes you may get a simple error that can be confusing to search for information. Especially, newcomers.
This simple error sounds like this: cannot convert [SOMETHING] to [SOMETHING]. In my case, it looked like cannot convert 1 to Text.
This error message comes from the jackc/pgx package and is caused by the combination of this package internally using prepared statements and PostgreSQL not being able to determine the type of the placeholders.
? Thanks for this explanation to author of this comment.
Input data when an error occurs
First, let's take a look at the migration file that creates the projects table in my application's database:
-- ./platform/migration/000001_create_init_tables.up.sql
-- Create projects table
CREATE TABLE "projects" (
"id" UUID PRIMARY KEY DEFAULT (uuid_generate_v4()),
"created_at" timestamp DEFAULT (now()),
"updated_at" timestamp,
"user_id" UUID NOT NULL,
"alias" varchar(255) UNIQUE NOT NULL,
"project_status" int NOT NULL,
"project_attrs" JSONB NOT NULL
);
// ...
We see that the fields have a very specific type, which will be validated by Postgres when creating/modifying data in this table.
Now, let's look at the Go model, which will fit this migration:
// ./app/controllers/project_model.go
// Project struct to describe project object.
type Project struct {
ID uuid.UUID `db:"id" json:"id"`
CreatedAt time.Time `db:"created_at" json:"created_at"`
UpdatedAt time.Time `db:"updated_at" json:"updated_at"`
UserID uuid.UUID `db:"user_id" json:"user_id"`
Alias string `db:"alias" json:"alias"`
ProjectStatus int `db:"project_status" json:"project_status"`
ProjectAttrs ProjectAttrs `db:"project_attrs" json:"project_attrs"`
}
// ProjectAttrs struct to describe project attributes.
type ProjectAttrs struct {
Title string `json:"title"`
Description string `json:"description"`
Picture string `json:"picture"`
URL string `json:"url"`
}
// ...
Yes, you're quite right that the JSOB field type in the Go model has become a regular structure and will be stored in JSON format in the project_attrs database field.
? Other fields are quite normal for any Go project.
Resolving the error
Let's move smoothly to the solution to this simple error.
All you need to know for the solution is that in PostgreSQL, you can specify types for placeholders directly in the query. Just simply add the types you specified in the migration file to the query.
The format for specifying the field type will be as follows: $N::<TYPE> (dollar sign + placeholder number + two colons + field type from DB).
// ./app/controllers/project_query.go
// CreateProject method for creating project by given Project object.
func (q *ProjectQueries) CreateProject(p *models.Project) error {
// Define query string.
// We define type for each field to solve this simple error.
query := `
INSERT INTO projects
VALUES ($1::uuid, $2::timestamp, $3::timestamp, $4::uuid, $5::varchar, $6::int, $7::jsonb)
`
// Send query to database.
_, err := q.Exec(query, p.ID, p.CreatedAt, p.UpdatedAt, p.UserID, p.Alias, p.ProjectStatus, p.ProjectAttrs)
if err != nil {
// Return only error.
return err
}
// This query returns nothing.
return nil
}
// ...
☝️ Once again, please note that we are specifying the PostgreSQL field type from the migration, not the Go structure from the model!
After building the project and requesting this endpoint, the error will no longer be displayed, and the request will succeed! ?
Conclusions
Personally, I like this kind of entry more than completely disabling this check in the jackc/pgx package config (more information here).
Just because it gives you an immediate understanding of the field type at a glance of the SQL query, without having to dive into the details of the implementation in the controller.
Choose the option that is convenient to you and use it, I just showed the way of the solution, which helped me personally. Have a successful work and let simple errors never stop you on the way to realizing your projects! ?
Photos and videos by
- Arun Prakash https://unsplash.com/photos/pE9mgLMwee0
- Kendall Ruth https://unsplash.com/photos/AijuW-HlE30
P.S.
If you want more articles like this on this blog, then post a comment below and subscribe to me. Thanks! ?
This content originally appeared on DEV Community and was authored by Vic Shóstak
Vic Shóstak | Sciencx (2021-08-10T10:54:06+00:00) ?♀️ ?♂️ PostgreSQL error with using over jackc/pgx driver in Golang: cannot convert X to Y. Retrieved from https://www.scien.cx/2021/08/10/%f0%9f%a4%b7%e2%80%8d%e2%99%80%ef%b8%8f-%f0%9f%a4%b7%e2%80%8d%e2%99%82%ef%b8%8f-postgresql-error-with-using-over-jackc-pgx-driver-in-golang-cannot-convert-x-to-y/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.