.. |br| raw:: html

   <br/>


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 <https://en.wikipedia.org/wiki/Snake_case>`_ naming 
convention. The column names will change with a different 
:ref:`naming convention <naming_conventions>`, but the principles remain the same.

Simple structs
--------------

.. code-block:: go

	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 |br|  |
|                   |                            | 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 |br| natural key: used in      |
|                   |                            | error messages to assist |br| in         |
|                   |                            | identifying the row                      |
+-------------------+----------------------------+------------------------------------------+
| ``json``          | ``jsonb``                  | Column is marshaled as JSON              |
+-------------------+----------------------------+------------------------------------------+
| ``null``          | ``omitempty``              | Empty value is stored in the DB as NULL, |
|                   |                            | |br| NULL is scanned as empty value      |
+-------------------+----------------------------+------------------------------------------+