Sqlboiler生成Go模型Upsert时Null字符串零值失效问题咨询

Let me break down exactly what's happening here and why uncommenting those Name/Alias initializations fixes your issue:

1. SQLBoiler ignores uninitialized null-type fields #

The null.String type from volatiletech/null has a zero value of null.String{Valid: false} — this signals to SQLBoiler that the field hasn't been explicitly set. When generating INSERT/UPDATE statements, SQLBoiler automatically filters out any null-type fields where Valid is false, even if you include them in your column whitelists.

In your code, when you comment out the Name and Alias lines, those fields stay in their zero-value state. So even though you specified name and alias in your insertColumns whitelist, SQLBoiler skips them entirely when building the INSERT query. That's why your generated SQL only includes id and type.

2. Database constraints and scan errors collide #

If your devices table allows name or alias to be NULL (no NOT NULL constraint), skipping those fields in the INSERT will leave them as NULL in the database. Now, here's where the scan error comes in:

SQLBoiler's Upsert method automatically scans the resulting row back into your original dev struct. While null.String is designed to handle NULL values, the error converting NULL to string is unsupported usually points to one of two issues:

• Hidden uninitialized fields: Your Device struct has regular string fields like Fleet, Hardware, and DeviceToken. If those fields have NOT NULL constraints in the database but no default values, skipping them in the INSERT would cause a database error — and during error handling, the scan logic might try to map NULL to a regular string (which isn't allowed).
• Version compatibility bugs: Older versions of SQLBoiler or the volatiletech/null package had edge cases where uninitialized null-type fields couldn't properly map database NULL values back to the struct's zero state.

When you uncomment Name: null.StringFrom("") and Alias: null.StringFrom(""), you're explicitly setting those fields to have Valid: true (even with empty string values). SQLBoiler includes them in the INSERT query, which either satisfies a NOT NULL constraint or avoids leaving the fields as NULL — eliminating the scan error entirely.

Fixes you can apply #

• Explicitly initialize all required null fields: For any field you want included in your Upsert (even empty ones), use the null.XFrom() methods to set Valid: true. This ensures SQLBoiler doesn't skip them.
• Audit your database constraints: Check if name, alias, or other fields have NOT NULL constraints. If they do, either provide explicit values (like empty strings) or set a default value in the database.
• Update your dependencies: Make sure you're using the latest stable versions of SQLBoiler (v4+) and volatiletech/null to avoid old bugs related to null handling.

Quick note on Upsert parameter order #

Just a sanity check: your Upsert call uses the correct parameter order (skipInsert, conflict columns, update columns, insert columns) — keep that in mind if you adjust the logic later, since mixing up update/insert columns can cause unexpected behavior.

内容的提问来源于stack exchange，提问作者Patryk