Column Mapping

The sqlr package obtains the column name and other information about a database column from the name and struct tag of the corresponding Go struct field.

Note: The examples in this section use the snake_case naming convention. The column names will change with a different naming convention, but the principles remain the same.

Simple structs

type User struct {
        ID        int64 `sql:"primary key"`
        Name      string
        UpdatedAt time.Time
        CreatedAt time.Time
}

// Column names generated by sqlr:
// * id
// * name
// * updated_at
// * created_at

Note the use of the struct tag to specify the primary key. The struct tag can also be used to override the column name:

type User struct {
        ID        int64     `sql:"user_id primary key"`
        Name      string
        UpdatedAt time.Time
        CreatedAt time.Time
        DOB       time.Time `sql:"date_of_birth"`
}

// Column names generated by sqlr:
// * user_id
// * name
// * updated_at
// * created_at
// * date_of_birth

If you need to override the column name to be an SQL keyword, (which is rarely a good idea), you can use quotes to specify the column name:

// Not recommended
type User struct {
        ID int64 `sql:"'primary' primary key"` // setting column name to SQL keyword
        // ... rest of struct here
}

Anonymous structs

Sometimes there are a set of common columns, used by each table. Anonymous structs provide a way to ensure consistency across the Go structs:

type Entity struct {
        ID        int64 `sql:"primary key autoincrement"`
        UpdatedAt time.Time
        CreatedAt time.Time
}

type User struct {
        Entity
        Name  string
        Email string
}

// Column names generated by sqlr:
// * id
// * updated_at
// * created_at
// * name
// * email

type Vehicle struct {
        Entity
        Make string
        Model string
}

// Column names generated by sqlr:
// * id
// * updated_at
// * created_at
// * make
// * model

Embedded structs

In some cases it is useful to use embedded structures when representing components in a structure:

type Address struct {
        Street   string
        Locality string
        City     string
        Postcode string
        Country  string
}

type CustomerContact struct {
        CustomerID    int64 `sql:"primary key"`
        HomeAddress   Address
        PostalAddress Address
}

// Column names generated by sqlr:
// * customer_id
// * home_address_street
// * home_address_locality
// * home_address_city
// * home_address_postcode
// * home_address_country
// * postal_address_street
// * postal_address_locality
// * postal_address_city
// * postal_address_postcode
// * postal_address_country

Go struct tags

The sqlr package inspects the Go struct tag with the sql tag key. The format of the tag key consists of an optional column name followed by keywords.

example	column name	primary key	autoincrement
sql:”user_id primary key”	user_id	yes	no
sql:”user_id”	user_id	no	no
sql:”primary key”	Use naming convention	yes	no
sql:”primary key autoincrement”	Use naming convention	yes	yes
sql:“‘primary’ primary key”	primary	yes	no
sql:” ‘key’ “	key	no	no

If first name in the tag column name is not one of the keywords it is interpreted to be a column name. In the rare (and inadvisable) situation where a column is required to be a keyword, it can be enclosed in single quotes (').

The full list of accepted keywords is:

keyword	synonyms	description
primary key	pk, primary_key	Column is the primary key, or part of a composite primary key
autoincrement	autoincr, identity	Column is an auto-increment (aka identity) column
natural key	natural_key	Column is a natural key, or part of a composite natural key: used in error messages to assist in identifying the row
json	jsonb	Column is marshaled as JSON
null	omitempty	Empty value is stored in the DB as NULL, NULL is scanned as empty value