Introduction
This post will document the process to verify and figure out the behaviour of SQLC, PostgreSQL and Go. I am writing this post and testing the code in parallel, which means I don't know what the finding and conclusion will be at the start of the post.
There are basically three arguments surrounding the database schema type, and they are kinda independent from each other:
- There is no need to write the verbose
time stamp with time zone, thetimestamptzis exactly the same thing. - Since those two types are the same, if a user configures the sqlc.yaml to override the type, it should apply to both of them equally, it makes no sense for the sqlc parser to treat them differently
- The types need to be overridden but Gemini suggested a flawed solution.
And so before the start of this test, I have a hypothesis: which is that Gemini make a mistake, Opus spot the mistake and propose a better solution, and I am trying to verify which solution is the truth.
I started a new project so that I can test it properly, and this is the project structure:
1234567891011121314151617181920.├── cmd│ └── marshal│ └── main.go├── db│ ├── queries│ │ └── query.sql│ └── schema│ └── schema.sql├── internal│ └── store│ ├── db.go│ ├── models.go│ └── query.sql.go├── go.mod├── go.sum├── README.md└── sqlc.yaml
8 directories, 10 filesDatabase Schema
This is the schema that I am testing:
1234567891011121314151617181920create table matrix ( id serial primary key,
-- the type-name axis (all three should resolve identically) ts_plain timestamp, tstz_alias timestamptz, tstz_verbose timestamp with time zone,
-- the not null axis tstz_alias_nn timestamptz not null, tstz_verbose_nn timestamp with time zone not null,
-- the default axis (note: default does not imply not null) tstz_default timestamptz default current_timestamp, tstz_default_nn timestamptz default current_timestamp not null,
-- uuid axis uuid_nullable uuid, uuid_nn uuid not null);The different variations are to make sure that I cover all cases, including the suspected behaviour of sqlc's parser might treat timestamptz and time stamp with time zone differently.
During the first development cycle, I simply used timestamptz for all the timestamp fields type, and all the timestamp fields up until that point were not null, because most fields are something like created_at which is supposed to be not nullable. Hence, LLMs would recommend users to change the config of sqlc.yaml to override the types to time.Time. Because the code in backend would be much simpler.
But then I reached a point in development where I had to deal with field like expires_at which by definition, if it is not expired, it should just be null. It cannot be any other value, because the field with type time.Time with no value being set will automatically fall back to its "zero" value : 0001-01-01, which is 2000 years ago.To solve this issue, I would need to use a pointer *time.Time which can accept a nil value.
12345func main() { var t time.Time fmt.Println(t)}//0001-01-01 00:00:00 +0000 UTCGenerated Types
And this is the sqlc.yaml config, where I use the pgx/v5 package instead of the default package database/sql.
12345678910version: "2"sql: - engine: "postgresql" schema: "db/schema/" queries: "db/queries/" gen: go: package: "store" out: "internal/store" sql_package: "pgx/v5"This basically means that sqlc will generate the type pgtype.Timestamptz instead of the default sql.NullTime. But they both have the same time and valid pattern, where the valid bool mechanism is used to represent null.
Here is the code generated by sqlc itself, after I run sqlc generate. I can see the different generated types, but the time and valid pattern exist in both types.
12345678910111213141516171819202122// Code generated by sqlc. DO NOT EDIT.// versions:// sqlc v1.31.1
package store
import ( "github.com/jackc/pgx/v5/pgtype")
type Matrix struct { ID int32 TsPlain pgtype.Timestamp TstzAlias pgtype.Timestamptz TstzVerbose pgtype.Timestamptz TstzAliasNn pgtype.Timestamptz TstzVerboseNn pgtype.Timestamptz TstzDefault pgtype.Timestamptz TstzDefaultNn pgtype.Timestamptz UuidNullable pgtype.UUID UuidNn pgtype.UUID}12345678910111213141516171819202122232425// Code generated by sqlc. DO NOT EDIT.// versions:// sqlc v1.31.1
package store
import ( "database/sql" "time"
"github.com/google/uuid")
type Matrix struct { ID int32 TsPlain sql.NullTime TstzAlias sql.NullTime TstzVerbose sql.NullTime TstzAliasNn time.Time TstzVerboseNn time.Time TstzDefault sql.NullTime TstzDefaultNn time.Time UuidNullable uuid.NullUUID UuidNn uuid.UUID}So based on this first test, there is nothing different between all those timestamptz variation, whether it is verbose, with null, or with default. Most will be generated as pgtype.Timestamptz. So at this point everything seems to be working perfectly, but there are two problems: compatibility and leaky abstraction.
Compatibility
The entire Go ecosystem works very well with time.Time, in most cases the library is expecting the data to be of type time.Time. The pgtype.Timestamptz is a custom database driver type, users will have to manually unwrap the inner struct everytime it interact with other Go packages
1timeSince := time.Since(post.CreatedAt)1timeSince := time.Since(post.CreatedAt.Time)Leaky Abstraction
It seems like it is a best practice that database driver types should not leak into domain logic. pgtype is good for the database as it covers more modern types in PostgreSQL, but once the data is retrieved from database, it should looks like native Go. Hence there is a need to override the type by configuring the sqlc.yaml
12345678910111213version: "2"sql: - engine: "postgresql" schema: "db/schema/" queries: "db/queries/" gen: go: package: "store" out: "internal/store" sql_package: "pgx/v5" overrides: - db_type: "pg_catalog.timestamptz" go_type: { import: "time", type: "Time" }Serialization
Looking at the return data, I assumed it will be the same after converting it to JSON payload and pass it to frontend. But this is where pgtype.Timestamptz has an advantages over the default sql.NullTime. The pgx library has customized its JSON Marshal() function, if the valid field is true, it will return the time field as a string. So the frontend code can simply extract the JSON payload normally.
12345678910func main() { now := time.Now().UTC() ts := pgtype.Timestamptz{ Time: now, Valid: true, } b, _ := json.Marshal(ts) fmt.Println(string(b)) //"2026-06-02T16:24:21.015843Z"}123456func main() { ts := pgtype.Timestamptz{Valid: false} b, _ := json.Marshal(ts) fmt.Println(string(b))}// nullType Override
After adding the override field in the sqlc.yaml, I ran sqlc generate to generate the models again.
123456789101112131415161718192021222324// Code generated by sqlc. DO NOT EDIT.// versions:// sqlc v1.31.1
package store
import ( "time"
"github.com/jackc/pgx/v5/pgtype")
type Matrix struct { ID int32 TsPlain pgtype.Timestamp TstzAlias pgtype.Timestamptz TstzVerbose pgtype.Timestamptz TstzAliasNn pgtype.Timestamptz TstzVerboseNn time.Time TstzDefault pgtype.Timestamptz TstzDefaultNn pgtype.Timestamptz UuidNullable pgtype.UUID UuidNn pgtype.UUID}The result shows that only the field with verbose time stamp with time zone not null are being overrided to time.Time, which is unexpected. Because based on the config, and our hypothesis that timestamptz and time stamp with time zone are the same — TstzAliasNn and TstzVerboseNn should be the same thing, but only time stamp with time zone not null is being override. So clearly the actual result of the sqlc does not support our hypothesis.
The first realisation is that it seems like the problem is that sqlc parser does recognise the pg_catalog.timestamptz, but it only match the field TstzVerboseNn which is the verbose name timestamp with time zone not null in the sql. For all the other timestamptz fields, I need to use the type directly based on how it is written in the schema.sql, so that I don't have to completely depend on the current buggy sqlc parser.
The second realisation is that I need to configure the nullable options and also the pointers options like this in the sqlc.yaml to override those type that are nullable. Because it seems like without these additional config, the initial config will only override the time stamp with time zone not null, but it ignores the time stamp with time zone which by default is nullable, which cannot be converted into time.Time.
After multiple attempts with different combinations. here is the final config.
1234567891011121314151617181920212223version: "2"sql: - engine: "postgresql" schema: "db/schema/" queries: "db/queries/" gen: go: package: "store" out: "internal/store" sql_package: "pgx/v5" overrides: - db_type: "pg_catalog.timestamptz" nullable: false go_type: { import: "time", type: "Time" } - db_type: "pg_catalog.timestamptz" nullable: true go_type: { import: "time", type: "Time", pointer: true } - db_type: "timestamptz" nullable: false go_type: { import: "time", type: "Time" } - db_type: "timestamptz" nullable: true go_type: { import: "time", type: "Time", pointer: true }With this config, the models.go will be like this:
123456789101112131415161718192021222324// Code generated by sqlc. DO NOT EDIT.// versions:// sqlc v1.31.1
package store
import ( "time"
"github.com/jackc/pgx/v5/pgtype")
type Matrix struct { ID int32 TsPlain pgtype.Timestamp TstzAlias *time.Time TstzVerbose *time.Time TstzAliasNn time.Time TstzVerboseNn time.Time TstzDefault *time.Time TstzDefaultNn time.Time UuidNullable pgtype.UUID UuidNn pgtype.UUID}The nullable time stamp field will be a pointer *time.Time, all the other time stamp field regardless of it is verbose or not , will be generated as type time.Time. For the normal time.Time fields, I don't need to check for nil because it will always be guaranteed to have value. For *time.Time field likes expires_at, I simply need to check for nil before accessing its value if (expiresAt != nil).
I can safely ignore the normal timestamp field because all data that I use in the actual project will need to have time zone.