Go项目使用sqlboiler生成代码遇类型不兼容错误求助

开发Go项目时，使用sqlboiler从setup.sh创建的SQLite3数据库生成代码，遇到编译错误：

graph/db/repositories.go:998:23: cannot use o.CreatedAt (variable of type string) as driver.Valuer value in argument to queries.MustTime: string does not implement driver.Valuer (missing method Value)
graph/db/repositories.go:999:23: cannot use &o.CreatedAt (value of type *string) as sql.Scanner value in argument to queries.SetScanner: *string does not implement sql.Scanner (missing method Scan)

错误触发于graph/db/repositories.go中尝试使用time.Time值时，项目基于golang:1.21-alpine3.18 Docker镜像。已尝试将repositories表的created_at列改为TEXT和TIMESTAMP类型，问题依旧。

相关文件内容：

repositories.go（生成的代码片段） #

func (o *Repository) Insert(ctx context.Context, exec boil.ContextExecutor, columns boil.Columns) error {
    if o == nil {
        return errors.New("db: no repositories provided for insertion")
    }

    var err error
    if !boil.TimestampsAreSkipped(ctx) {
        currTime := time.Now().In(boil.GetLocation())

        if queries.MustTime(o.CreatedAt).IsZero() {
            queries.SetScanner(&o.CreatedAt, currTime)
        }
    }

    if err := o.doBeforeInsertHooks(ctx, exec); err != nil {
        return err
    }

    nzDefaults := queries.NonZeroDefaultSet(repositoryColumnsWithDefault, o)

    key := makeCacheKey(columns, nzDefaults)
    repositoryInsertCacheMut.RLock()
    cache, cached := repositoryInsertCache[key]
    repositoryInsertCacheMut.RUnlock()

setup.sh（数据库初始化脚本） #

#!/usr/local/bin/sh

set -eu

readonly DBFILE_NAME="mygraphql.db"

# Create DB file
if [ ! -e ${DBFILE_NAME} ];then
  echo ".open ${DBFILE_NAME}" | sqlite3
fi

# Create DB Tables
echo "creating tables..."
sqlite3 ${DBFILE_NAME} "
PRAGMA foreign_keys = ON;

CREATE TABLE IF NOT EXISTS users(
    id TEXT PRIMARY KEY NOT NULL,
    name TEXT NOT NULL,
    project_v2 TEXT
);

CREATE TABLE IF NOT EXISTS repositories(
    id TEXT PRIMARY KEY NOT NULL,
    owner TEXT NOT NULL,
    name TEXT NOT NULL,
    created_at TEXT NOT NULL DEFAULT (DATETIME('now','localtime')),
    FOREIGN KEY (owner) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS issues(
    id TEXT PRIMARY KEY NOT NULL,
    url TEXT NOT NULL,
    title TEXT NOT NULL,
    closed INTEGER NOT NULL DEFAULT 0,
    number INTEGER NOT NULL,
    repository TEXT NOT NULL,
    CHECK (closed IN (0, 1)),
    FOREIGN KEY (repository) REFERENCES repositories(id)
);

CREATE TABLE IF NOT EXISTS projects(
    id TEXT PRIMARY KEY NOT NULL,
    title TEXT NOT NULL,
    url TEXT NOT NULL,
    owner TEXT NOT NULL,
    FOREIGN KEY (owner) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS pullrequests(
    id TEXT PRIMARY KEY NOT NULL,
    base_ref_name TEXT NOT NULL,
    closed INTEGER NOT NULL DEFAULT 0,
    head_ref_name TEXT NOT NULL,
    url TEXT NOT NULL,
    number INTEGER NOT NULL,
    repository TEXT NOT NULL,
    CHECK (closed IN (0, 1)),
    FOREIGN KEY (repository) REFERENCES repositories(id)
);

CREATE TABLE IF NOT EXISTS projectcards(
    id TEXT PRIMARY KEY NOT NULL,
    project TEXT NOT NULL,
    issue TEXT,
    pullrequest TEXT,
    FOREIGN KEY (project) REFERENCES projects(id),
    FOREIGN KEY (issue) REFERENCES issues(id),
    FOREIGN KEY (pullrequest) REFERENCES pullrequests(id),
    CHECK (issue IS NOT NULL OR pullrequest IS NOT NULL)
);
"

# Insert initial data
echo "inserting initial data..."
sqlite3 ${DBFILE_NAME} "
PRAGMA foreign_keys = ON;

INSERT INTO users(id, name) VALUES
    ('U_1', 'hsaki')
;

INSERT INTO repositories(id, owner, name) VALUES
    ('REPO_1', 'U_1', 'repo1')
;

INSERT INTO issues(id, url, title, closed, number, repository) VALUES
    ('ISSUE_1', 'http://example.com/repo1/issue/1', 'First Issue', 1, 1, 'REPO_1'),
    ('ISSUE_2', 'http://example.com/repo1/issue/2', 'Second Issue', 0, 2, 'REPO_1'),
    ('ISSUE_3', 'http://example.com/repo1/issue/3', 'Third Issue', 0, 3, 'REPO_1')
;

INSERT INTO projects(id, title, url, owner) VALUES
    ('PJ_1', 'My Project', 'http://example.com/project/1', 'U_1')
;

INSERT INTO pullrequests(id, base_ref_name, closed, head_ref_name, url, number, repository) VALUES
    ('PR_1', 'main', 1, 'feature/kinou1', 'http://example.com/repo1/pr/1', 1, 'REPO_1'),
    ('PR_2', 'main', 0, 'feature/kinou2', 'http://example.com/repo1/pr/2', 2, 'REPO_1')
;
"

sqlboiler.toml配置 #

pkgname="db"
output="graph/db"
wipe=true
add-global-variants=false
no-tests=true

[sqlite3]
  dbname = "./mygraphql.db"

环境：MacBook Pro M1 Pro

1. SQLite类型特性限制：SQLite没有原生TIMESTAMP类型，即使定义为TIMESTAMP，底层仍以TEXT/INTEGER存储。sqlboiler默认不会将TEXT类型自动映射为Go的time.Time，而是映射为string。
2. 生成代码的逻辑冲突：sqlboiler自动生成的时间字段处理逻辑（queries.MustTime、queries.SetScanner）要求参数实现driver.Valuer和sql.Scanner接口，但string类型不满足这两个接口，导致编译报错。
3. 缺少自定义类型映射配置：未告知sqlboiler将created_at这类TEXT列视为时间类型，因此生成的代码中CreatedAt字段为string，无法兼容时间处理方法。

步骤1：优化数据库时间字段默认值（可选） #

保持created_at为TEXT类型，将默认值改为标准UTC时间格式，避免时区解析问题，修改setup.sh中repositories表的定义：

CREATE TABLE IF NOT EXISTS repositories(
    id TEXT PRIMARY KEY NOT NULL,
    owner TEXT NOT NULL,
    name TEXT NOT NULL,
    created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
    FOREIGN KEY (owner) REFERENCES users(id)
);

步骤2：添加sqlboiler类型映射配置 #

在sqlboiler.toml中新增[sqlite3.types]节点，指定created_at列映射为Go的time.Time：

pkgname="db"
output="graph/db"
wipe=true
add-global-variants=false
no-tests=true

[sqlite3]
  dbname = "./mygraphql.db"

[sqlite3.types]
  "created_at" = "time.Time"

如果其他表也有时间字段，可在此统一添加映射，例如"updated_at" = "time.Time"

步骤3：重新生成代码并验证 #

1. 删除旧数据库文件，确保表结构更新：

rm mygraphql.db

2. 重新执行初始化脚本创建数据库：

chmod +x setup.sh && ./setup.sh

3. 重新运行sqlboiler生成代码：

sqlboiler sqlite3

4. 编译项目，确认错误已消除。

替代方案：启用sqlboiler时间戳插件 #

若需自动处理created_at、updated_at等字段，可启用sqlboiler的timestamps插件，在sqlboiler.toml中添加：

plugins = ["timestamps"]

[plugin.timestamps]
  created_at = "created_at"
  updated_at = "updated_at"

插件会自动识别这些字段，生成正确的time.Time类型和处理逻辑，无需手动配置类型映射。

内容的提问来源于stack exchange，提问作者REX BOV