BlogGoLang. Dealing with time.Time in a Database Agnostic MannerThe different SQL databases may have common elements but they all have different way of storing data internally This is most certainly true when date and time is stored. For instance SQLite does not have a date or time type just TEXT or NUMBER. MySQL has the DATETIME type. Postgres has TIMESTAMPTZ(6). In view of this retrieving a value of time.Time and *time.Time can be quite problematic with Golang. Especially if you use an ORM, and the ORM has decided its not so important to implement this feature agnostic of the database engine implementation. Looking at you GORM :) See the only reason one would use an ORM instead of native SQL is to be able to use multiple datastores interchangeably. For instance do your test with in memory database like SQLite on your local machine or Github actions to keep them fast, and run production on Postgres, MySQL, or SQL Server Then you find yourself fighting incomprehensible errors like this:
Searching the internet does not come with anything helpful. Ok, ok. Here is the example of how this can be achieved without an ORM and agnostic of the database engine. Language: javascript
As you can see I am using GOQU here. GOQU is a query builder for Golang, which can build your query with any SQL dialect you may need. This allows you to change or swap your database at any point. In this example you have a type Attribute with CreatedAt and UpdatedAt required fields. And an optional DeletedAt field to be used for soft deletes. The CreatedAt and UpdatedAt fields are pretty sytaight forward. This is because they will always be NOT NULL values (as long as you have set these as a database constraint as you should) The most problematic field is the optional DeletedAt field. The reason is it can be coming as NULL from the database. Which is why there is an extra check that the field is not nil, before parsing it to a time.Time data type With the above in place we have achieved the following. We have successfully converted our database data to GoLang types regardless of how it is stored inernally (i.e. strings). We have kept our code agnostic, and can switch between SQLite, Postgres, MySQL or SQLServer in a blink of an eye. |