🤷‍♀️ 🤷‍♂️ PostgreSQL error with using over jackc/pgx driver in Golang: cannot convert X to Y

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:

1. An explanation of the cause of the error;
2. A description of the input data in which the error occurred to me;
3. My solution to the error;
4. 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
• Input data when an error occurs
• Resolving the error
• Conclusions

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.

↑ Table of contents

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.

↑ Table of contents

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! 🎉

↑ Table of contents

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! 😉

↑ Table of contents

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! 😻

❗️ You can support me on Boosty, both on a permanent and on a one-time basis. All proceeds from this way will go to support my OSS projects and will energize me to create new products and articles for the community.

And of course, you can help me make developers' lives even better! Just connect to one of my projects as a contributor. It's easy!

My main projects that need your help (and stars) 👇

• 🔥 gowebly: A next-generation CLI tool that makes it easy to create amazing web applications with Go on the backend, using htmx, hyperscript or Alpine.js and the most popular CSS frameworks on the frontend.
• ✨ create-go-app: Create a new production-ready project with Go backend, frontend and deploy automation by running one CLI command.

Other my small projects: yatr, gosl, json2csv, csv2api.