Unsupported scan for date column in mysql

[andrei-dascalu]

Version

1.20.0

What happened?

I was running a very simple proof of concept, generating Go code for a single table that contains a Date field with default value.

Date inserted as per schema below:

insert into Account SET
	Relation = 11,
	Company = "whats";

Code:

package main

import (
	"context"
	"database/sql"
	"fmt"
	"log"

	"github.com/andrei-dascalu/api/accounts"
	_ "github.com/go-sql-driver/mysql"
)

func main() {
	ctx := context.Background()
	db, err := sql.Open("mysql", "root:root@tcp(127.0.0.1:3306)/gotest")
	if err != nil {
		log.Fatal(err)
	}

	acctData := accounts.New(db)

	accounts, err := acctData.ListAccounts(ctx)

	if err != nil {
		log.Fatal(err)
	}

	for _, item := range accounts {
		fmt.Printf("%d\n", item.Relation.Int64)
	}
}

Relevant log output

2023/09/03 19:18:30 sql: Scan error on column index 3, name "exceedoeinidate": unsupported Scan, storing driver.Value type []uint8 into type *time.Time

Database schema

CREATE TABLE `Account` (
  `Id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `Relation` bigint(20) unsigned DEFAULT NULL,
  `Company` varchar(50) DEFAULT NULL,
  `ExceedOEIniDate` date DEFAULT '2017-01-01',
  `CreateDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `ModifyDate` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SQL queries

-- name: ListAccounts :many
SELECT * FROM Account
ORDER BY CreateDate DESC;

Configuration

version: 1
packages:
  - path: "accounts"
    name: "accounts"
    engine: "mysql"
    schema: "schema/schema.sql"
    queries: "queries/queries.sql"

Playground URL

No response

What operating system are you using?

macOS

What database engines are you using?

MySQL

What type of code are you generating?

Go

[orisano]

This is not a sqlc problem, just a misuse of github.com/go-sql-driver/mysql.
Please use https://github.com/go-sql-driver/mysql#parsetime.

[andrei-dascalu]

@orisano At the core, that's true, but wouldn't it be possible that when New is called (passing the db pointer) to warn the user if they're not asking for parsedTime? SQLC takes the opinionated approach of generating code using time structs which isn't the default behaviour of the driver. At a minimum it feels that this should be mentioned in the docs themselves, right?

[kyleconroy]

At a minimum it feels that this should be mentioned in the docs themselves, right?

Yep, I'll add it to the getting started guide for MySQL.