Model structs in models.go vs. MyFooRow

[markschmid]

Maybe this is a very dumb question, but here's what I don't get:

In models.go, there's a per-table model struct, yet in the :one and :many selects, not those models but MyFooRow and []MyFooRow structs are used.

Can anybody quickly shed some light on this? Am I doing something wrong? Thanks!

[markschmid]

My question is basically:
When to use which struct?

[kyleconroy]

sqlc will generate per-query structs when they don't match any of the existing tables. Can you provide more information about your setup? What version of sqlc are you using? Can you post your schema, the problematic query, or the sqlc.json file?

[markschmid]

I'm using version 1.5 with the dolphin parser.
Here's an example:

schema/persons.sql:

CREATE TABLE `persons` (
  `id` int NOT NULL AUTO_INCREMENT,
  `firstname` varchar(255) DEFAULT NULL,
  `lastname` varchar(255) DEFAULT NULL,
  `role` varchar(255) DEFAULT NULL,
  `shortname` varchar(255) DEFAULT NULL,
  `visitorpass_id` varchar(255) DEFAULT NULL,
  `funktion` varchar(255) DEFAULT NULL,
  `device` int DEFAULT NULL,
  `assignment_active` tinyint(1) DEFAULT '0',
  `deleted` datetime DEFAULT NULL,
  `tenant` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  UNIQUE KEY `id_2` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

query/person.sql:

/* name: ListPersons :many */
select
  id,
  firstname,
  lastname,
  shortname,
  visitorpass_id,
  funktion,
  device,
  assignment_active
from persons
where deleted is null
and role = ?
and tenant = ?;

sqlc.yaml:

version: "1"
packages:
  - name: "db"
    path: "db/"
    queries: "./sql/query/"
    schema: "./sql/schema/"
    engine: "mysql:beta"
    emit_json_tags: true
    emit_prepared_queries: true
    emit_interface: false
    emit_exact_table_names: false

After sqlc generate, the compiled function for ListPersons looks like this:

func (q *Queries) ListPersons(ctx context.Context, arg ListPersonsParams) ([]ListPersonsRow, error) {
  [...]
}

So, it is returning []ListPersonsRow.
In db/models.go, there is a Person struct, but none of the generated code is using it. Is this model only for me to use?

[markschmid]

Hmm, so when looking into this in more detail, I realised:
If I select * or select all columns (and in the absolute same order as in the schema), ListPersons is returning []Person. In all other cases, it returns []ListPersonsRow. That's probably why (and doing so by design).

My main confusion is still coming from the fact that in case of passing on the data (e.g. via a JSON REST API), I still need to work with all the sql.NullXY datatypes (e.g. transform them to normal string / int64 etc.). At first I thought, this library (which I still like) helps to circumvent having to work with the sql.NullXY datatypes.

[kyleconroy]

You're correct. The generated code will only return Person structs if you've selected all fields from the person table. If you select a sub-set of those fields, sqlc will create a query-specific struct to return.

At first I thought, this library (which I still like) helps to circumvent having to work with the sql.NullXY datatypes.

sqlc provides a thin wrapper on top of your data model. That means that you'll need to handle NULL values and cases where you've written queries that return incomplete data. My suggestion is to have a separate set of structs that you return in your API instead of returning the database models directly. This layer of indirection allows you to change your database models without causing a change to your API.

I also understand if you're looking for a higher-level database package. If that's the case, sqlc may not be a fit for you currently.

[markschmid]

Thanks, this a great answer. sqlc is a great fit for me, just wanted to make sure I'm not missing something.