MySQL `bool` type generates `int32` golang type

[bquenin]

The following MySQL source code:

CREATE TABLE example
(
    id         integer      NOT NULL AUTO_INCREMENT PRIMARY KEY,
    successful bool         NOT NULL
);

Generated the following model:

type Example struct {
	ID         int32     `json:"id"`
	Successful int32     `json:"successful"`
}

Actually, all the boolean types (bool, boolean and tinyint) are behaving in this way.

I'm currently using this workaround to force the correct type:

version: "1"

packages: [...]
overrides:
- column: "example.successful"
  go_type: "bool"

This has already been mentioned by @markschmid in #666 (comment)_

[kyleconroy]

MySQL doesn't have a native boolean type. BOOL and BOOLEAN map to tinyint(1). We can't map tinyint(1) to boolean because you can actually store values other than 0 and 1 in a column marked tinyint(1).

The issue is that the MySQL parser we're using parsers BOOL and BOOLEAN (correctly) as tinyint(1) and offers no way to see the original type.

Since it might take a while to make the necessary upstream changes, we may want to convert any tinyint(1) column to a BOOL.

[kyleconroy]

we may want to convert any tinyint(1) column to a BOOL.

This is now done

[dangelov]

Using v1.7.0, it seems this issue is still there. I'm not using any overrides.

CREATE TABLE `images` (
    `is_main`    BOOL NOT NULL DEFAULT FALSE
)

type ImageGetRow struct {
	IsMain     int32     `json:"is_main"`
}

$ sqlc version
v1.7.0

Please let me know if there's any more info I can provide.

[kyleconroy]

@dangelov I can't reproduce this using v1.7.0

# sqlc.yaml
version: "1"
packages:
  - path: "db"
    queries: "query.sql"
    schema: "query.sql"
    engine: "mysql"

-- query.sql
CREATE TABLE `images` (
    `is_main`    BOOL NOT NULL DEFAULT FALSE
);

-- name: ListImages :many
SELECT * FROM images;

This outputs the following db/models.go:

// Code generated by sqlc. DO NOT EDIT.

package db

import ()

type Image struct {
	IsMain bool
}

[dangelov]

Sorry, in my effort to be concise I actually left out details. The generated code file I showed was in the generated .sql.go files. It's true that models.go contains the correct type (bool), but image.sql.go doesn't.

Updated the example with another field so the list method returns a list of structs, and ran it all locally, this is the result.

query.sql

CREATE TABLE `images` (
    `is_processed` BOOL NOT NULL DEFAULT FALSE,
    `is_main`      BOOL NOT NULL DEFAULT FALSE
) ENGINE=InnoDB;

-- name: ListImages :many
SELECT * FROM `images`;

image.sql.go (both IsProcessed and IsMain are int32 instead of bool)

// Code generated by sqlc. DO NOT EDIT.
// source: image.sql

package db

import (
	"context"
)

const listImages = `-- name: ListImages :many
SELECT is_processed, is_main FROM ` + "`" + `images` + "`" + `
`

type ListImagesRow struct {
	IsProcessed int32 `json:"is_processed"`
	IsMain      int32 `json:"is_main"`
}

func (q *Queries) ListImages(ctx context.Context) ([]ListImagesRow, error) {
	rows, err := q.query(ctx, q.listImagesStmt, listImages)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []ListImagesRow
	for rows.Next() {
		var i ListImagesRow
		if err := rows.Scan(&i.IsProcessed, &i.IsMain); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Close(); err != nil {
		return nil, err
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

models.go (this one is good)

// Code generated by sqlc. DO NOT EDIT.

package db

type Image struct {
	IsProcessed bool `json:"is_processed"`
	IsMain      bool `json:"is_main"`
}

So whenever I retrieve records from the database, I get an ListImagesRow struct, and it contains IsMain as int32, not bool.

[kyleconroy]

Ahh, thanks for the in-depth report. It looks like I fixed it for model generation but something is off when generating the methods. Will fix!