How to use `timestamp` #2514

prestonbourne · 2023-07-25T00:09:14Z

prestonbourne
Jul 25, 2023

Overview

Hello i'm new to this lib and looking for a way to use the sql timestamp type and have sqlc generate give me the stdlib time.Time type instead of the of pgtype.Timestamp

Note: If there's something wrong with my understanding and i should be using the pgtypes,
let me know.

Current state

Here's my config file:

version: "2"
sql:
  - engine: "postgresql"
    queries: "store/sql/queries.sql"
    schema: "store/sql/schemas.sql"
    gen:
      go:
        package: "store"
        out: "store"
        sql_package: "pgx/v5"
        emit_json_tags: true
        json_tags_case_style: "camel"
        emit_interface: true
        emit_pointers_for_null_types: true

My Schema

CREATE TABLE IF NOT EXISTS users (
		id SERIAL PRIMARY KEY,
		first_name TEXT NOT NULL,
		last_name TEXT NOT NULL,
		user_name TEXT NOT NULL,
		encrypted_password TEXT NOT NULL,
		created_at TIMESTAMP NOT NULL DEFAULT NOW()
	);

The output from the generate cmd

type User struct {
	ID                int32            `json:"id"`
	FirstName         string           `json:"firstName"`
	LastName          string           `json:"lastName"`
	UserName          string           `json:"userName"`
	EncryptedPassword string           `json:"encryptedPassword"`
	CreatedAt         pgtype.Timestamp `json:"createdAt"`
}

What I'm trying to do

In my /users route I want to allow someone to create a user via post req, however the generated query for AddUser tries to query using the pgtype instead of the actual time value

-- name: AddUser :one
INSERT INTO users
(first_name, last_name, user_name, encrypted_password, created_at)
VALUES ($1, $2, $3, $4, $5)
RETURNING *;

Generated code

const addUser = `-- name: AddUser :one
INSERT INTO users
(first_name, last_name, user_name, encrypted_password, created_at)
VALUES ($1, $2, $3, $4, $5)
RETURNING id, first_name, last_name, user_name, encrypted_password, created_at
`

type AddUserParams struct {
	FirstName         string           `json:"firstName"`
	LastName          string           `json:"lastName"`
	UserName          string           `json:"userName"`
	EncryptedPassword string           `json:"encryptedPassword"`
	CreatedAt         pgtype.Timestamp `json:"createdAt"`
}

func (q *Queries) AddUser(ctx context.Context, arg AddUserParams) (User, error) {
	row := q.db.QueryRow(ctx, addUser,
		arg.FirstName,
		arg.LastName,
		arg.UserName,
		arg.EncryptedPassword,
		arg.CreatedAt,
	)
	var i User
	err := row.Scan(
		&i.ID,
		&i.FirstName,
		&i.LastName,
		&i.UserName,
		&i.EncryptedPassword,
		&i.CreatedAt,
	)
	return i, err
}

How would I call this method from my route handler and pass in the AddUserParams in the way to get my desired effect?
Here's a snippet of what i'm currently trying.

func (c *UserController) Add(w http.ResponseWriter, r *http.Request) error {

	utils.LogRequest(r)

	addUserReq := &models.AddUserRequest{}

	if err := utils.DecodeAndWrite(r, addUserReq); err != nil {
		return fmt.Errorf("%w", err)
	}
	newUser, _ := models.NewUser(
		addUserReq.FirstName,
		addUserReq.LastName,
		addUserReq.UserName,
		addUserReq.Password,
	)

	addedUser, err := c.store.AddUser(r.Context(), *newUser)

	fmt.Printf("%+v", newUser)

	if err != nil {
		utils.WriteJSON(w, http.StatusInternalServerError, err) /* go always errors here because of course, `created_at` is invalid */
	}

	return utils.WriteJSON(w, http.StatusOK, addedUser)
}