feat: add DATE, DATETIME, TIMESTAMP compatibility with libsql in Go

[braaar]

Edit: I have renamed the issue, as this is not really a bug, after all, but a compatibility problem. See the comments for more context.

Version

1.27.0

What happened?

For DATE, DATETIME and TIMESTAMP columns in SQLite, SQLC generates time.Time fields in the corresponding Go struct.

When I attempt to read data from such columns from an SQLite database with the generated SQLC code, I get an Unsupported Scan error, since the actual type of the data is NUMERIC (can be int or float, in my case I store unix epoch timestamps so the actual values will be int64).

SQLite has no true datetime datatype. A column named DATE, DATETIME, TIMESTAMP or SLARTIBARTFAST in SQlite is going to end up being NUMERIC, due to SQLite's type affinity system. It would be advisable to change the SQLC generated types of such columns to be either int64, which would apply well to cases like mine where an integer is stored in the database. I don't know if there is a Go type that corresponds well to SQLite's numeric, but I think realistically speaking dates are far more likely to be represented as ints in actual use (unix epoch), so the best compromise would be that.

Alternatively, removing the specific type mappings for timestamp could also be an acceptable solution, thus forcing users to make their own overrides.

Alternatively, one could write some kind of parser, but as far as I understand, this would not fall within the scope of SQLC.

I believe this is the relevant code that needs to be changed.

Relevant log output

ERROR An unexpected database error has occured error="sql: Scan error on column index 3, name \"my_date\": unsupported Scan, storing driver.Value type int64 into type *time.Time"

Database schema

CREATE TABLE
    my_table (
        my_date TIMESTAMP NOT NULL
    );

SQL queries

SELECT
    my_date
FROM
    my_table

Configuration

version: "2"
sql:
  - engine: "sqlite"
    queries:
      - "internal/storage/queries/*.sql"
    schema: "internal/storage/migrations"
    gen:
      go:
        package: "sql"
        out: "generated/sql"
        emit_interface: true

Playground URL

No response

What operating system are you using?

macOS

What database engines are you using?

SQLite

What type of code are you generating?

Go

[rishi-kulkarni]

I suspect this was done to keep compatibility with mattn's driver: mattn/go-sqlite3#748. It would be a breaking change to fix it at this point.

[braaar]

I see. I'm using tursodatabase/libsql-client-go, which doesn't do the timestamp thingy, or not in the same way, at least. So it's a compatibility issue, either way, I suppose.

Would it make sense to add a configuration field for modifying the datetime/timetamp logic?
The default option would preserve the current behaviour for the sake of backwards compatibility.

When I find the time, I could try to figure out exactly which types would work well with libsql-client-go and then we could perhaps move towards adding some kind of libsql-client-go compatibility option? I suppose there are other sqlite drivers to consider, as well, though.

[AxelRHD]

Is there solution or workaround for this issue (using SQLite)? I just started with sqlc and this is a real dealbreaker.

[rishi-kulkarni]

I typically use a time wrapper like this, which ensures the time is stored as an int64. The column dtype in my DDL is just INTEGER.

type UnixTime struct {
	time.Time
}

func (ut *UnixTime) Scan(src interface{}) error {
	if src == nil {
		ut.Time = time.Time{}
		return nil
	}
	
	switch v := src.(type) {
	case int64:
		ut.Time = time.Unix(v, 0)
	default:
		return fmt.Errorf("unsupported type for UnixTime: %T, expected int64", src)
	}
	return nil
}

func (ut UnixTime) Value() (driver.Value, error) {
	return ut.Unix(), nil
}

[AxelRHD]

I typically use a time wrapper like this, which ensures the time is stored as an int64. The column dtype in my DDL is just INTEGER.

type UnixTime struct {
time.Time
}

func (ut *UnixTime) Scan(src interface{}) error {
if src == nil {
ut.Time = time.Time{}
return nil
}

switch v := src.(type) {
case int64:
ut.Time = time.Unix(v, 0)
default:
return fmt.Errorf("unsupported type for UnixTime: %T, expected int64", src)
}
return nil
}

func (ut UnixTime) Value() (driver.Value, error) {
return ut.Unix(), nil
}

And your custom type is then usable with sqlc?

[AxelRHD]

Got it. Thank you! I changed the database format to TEXT instead of INTEGER to use the SQLite included datetime functions. Works like a charm.