Map mysql tinyint to golang bool

[deafwolf]

I have create a table like this

Create Table: CREATE TABLE `pilots` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `alive` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4

Then sqlboiler generate the golang model like this

type Pilot struct {
	ID        int64     `boil:"id" json:"id" toml:"id" yaml:"id"`
	Name      string    `boil:"name" json:"name" toml:"name" yaml:"name"`
	Alive     int8      `boil:"alive" json:"alive" toml:"alive" yaml:"alive"`
	CreatedAt time.Time `boil:"created_at" json:"created_at" toml:"created_at" yaml:"created_at"`
	UpdatedAt time.Time `boil:"updated_at" json:"updated_at" toml:"updated_at" yaml:"updated_at"`

	R *pilotR `boil:"-" json:"-" toml:"-" yaml:"-"`
	L pilotL  `boil:"-" json:"-" toml:"-" yaml:"-"`
}

What I want is

Alive     bool      `boil:"alive" json:"alive" toml:"alive" yaml:"alive"`

I change the int8 to bool manually, looks sqlboiler work good.

Is there a way change int8 to bool automatically?

[nullbio]

Hi @deafwolf, have you tried setting the type as BOOL or BOOLEAN in MySQL? I realize that they're synonyms for TINYINT(1), but I can't recall if our MySQL schema analysis query gets the alias type or the underlying type. We do have this in the structs:

case "boolean", "bool": c.Type = "bool"

So if a type of BOOL or BOOLEAN in your MySQL database is not working for you then this would potentially be considered a bug (providing we can get the alias type from MySQL). Anyway, that'd be my first suggestion. Let me know if that does the trick or not. Thanks.

[deafwolf]

@nullbio Thank you for your suggestion.

When I created table with "alive boolean", then ran show create table, it returned me tinyint(1).
Mysql use tinyint(1) as boolean, and most people use tinyint(4) for int8, what about map tinyint(1) to bool when generate the golang model?

[nullbio]

@deafwolf Thanks, and fair enough. Yeah, this is something we'll have to consider for MySQL. From what I gather, the problem with this is that tinyint(1) isn't limited to 0 or 1, since tinyint(1) is 8 bits the value can be stored in the database from 0 to 255 and reflected as 0-9 (due to being truncated to width 1). I think the use case of tinyint(1) being used for a bool is probably the most frequent use case, so I'm thinking we should map it to bool and then have a sqlboiler generation flag for MySQL generation that says something like --tinyint-not-bool. What do you think about this? In either case, if we adjust the mappings it will be a breaking change so will need to be released with 3.0.0.

[deafwolf]

@nullbio Thanks, what about use a --tinyint-to-bool flag with 2.1.4 to avoid make a breaking change?

[nullbio]

Haha, was just writing up a response with that idea as you said that. We'll add --tinyint-as-bool for now and then in v3 change it to --tinyint-not-bool. I'll try to get to this later today :)

[deafwolf]

👍

[nullbio]

@deafwolf This has now been released https://github.com/vattle/sqlboiler/releases/tag/v2.1.4 -- Let me know if you run into any problems. Thanks.

[nullbio]

Actually, I'll leave this issue open as a reminder that the default value and flag name should change in v3.0.0.

[deafwolf]

I have updated to 2.1.4 and worked fine, thanks.

[aarondl]

Flag is now removed. Uses tinyint_as_int as a mysql-level configuration option. It's false by default, so they will become bools by default. v3 branch.

[RichardLindhout]

Hmm. Looks like it's generated as int8 now. Could this be related to mariadb?

[RichardLindhout]

Ok, found it. Mysql workbench converted BOOLEAN to TINYINT but source code is mapped to

"full_db_type": "tinyint(1)",

So when I've changed TINYINT to TINYINT(1) it converted to bool 👍

[shravanshetty1]

I couldnt find a list of all possible configurations for sqlboiler.toml, am i missing something?

[tomscholz]

I couldnt find a list of all possible configurations for sqlboiler.toml, am i missing something?

Here is a list of generic configuration options: https://github.com/volatiletech/sqlboiler/tree/master#generic-config-options

Seems liketinyint_as_int isn't documented. Maybe open a PR with all driver specific configuration options documented in the readme.