Sqac table-declarations are informed by go structs with json-style struct-tags indicating column attributes. Two tags are used: ‘db:' and ‘sqac:'; the ‘db:' tag is used to declare the database column name. This is typically the snake_case conversion of the go struct field-name. The ‘sqac:' tag is used to declare column attributes.
A list of the supported ‘sqac:’ tag attributes follows:
| sqac tag | Description |
|---|---|
| “primary_key:" | This tag is used to declare that the specified column should be used as a primary-key in the generated database table. There are a few variations in its use: “primary_key:inc” declares the primary-key as auto-incrementing from 0 in the database table schema: db:"depot_num" sqac:"primary_key:inc" **“primary_key:"** declares the primary-key as a non-auto-incrementing primary-key in the database schema: db:"depot_num" sqac:"primary_key:" **“primary_key:inc;start:90000000”** declares the primary-key as auto-incrementing starting from 900000000: db:"depot_num" sqac:"primary_key:inc;start:90000000" It is possible to assign the “primary_key:” tag to more than one column in a table’s model declaration. The column containing the first occurrence of the tag (top-down) will be created as the actual primary-key in the database. The collection of column declarations containing the “primary_key:” tag will be used to create a unique index on the DB table. This is useful in cases where one is migrating data from a source system that has the concept of compound table keys. For example, the following model excerpt would result in the creation of “depot_num” as the table’s primary-key as well as the creation of a unique index containing “depot_num”, “depot_bay”, “create_date”: DepotNum int db:"depot_num" sqac:"primary_key:inc;start:90000000" DepotBay int db:"depot_bay" sqac:"primary_key:" CreateDate time.Time db:"create_date" sqac:"nullable:false;default:now();index:unique"**Notes:** auto-incrementing primary-keys increment by 1 and must always be declared as go-type **int**. |
| “nullable:" | This tag is used to declare that the specified column is nullable in the database. Allowed values are true or false. db:"region" sqac:"nullable:false" or db:"region" sqac:"nullable:true" Notes: If this tag is not specified, the column is declared as nullable with the exception of columns declared with the “primary_key:” tag. |
| “default: | The “default:” tag is used to declare a default value for the column in the database table schema. Default values are used as per the implementation of the SQL DEFAULT keyword in the target DBMS. db:"region" sqac:"nullable:false;default:YYC" Notes: This tag supports the use of static values for all column-types, as well as a small set of date-time functions: “default:now()” / “default:make_timestamptz(9999, 12, 31, 23, 59, 59.9)” / “default:eot()” |
| “index:" | Single column indexes can be declared via the “index:” tag. The example index declarations require only the “index:unique / non-unique” pair in the column’s sqac-tag. The following column declaration results in the creation of a unique index on table column “create_date”: db:"create_date" sqac:"nullable:false;default:now();index:unique" A non-unique single column index for the same column is declared as follows: db:"create_date" sqac:"nullable:false;default:now();index:non-unique" Multi-column indexes can also be declared via the index tag. The following example illustrates the declaration of a non-unique two-column index containing columns “new_column1” and “new_column2”: db:"new_column1" sqac:"nullable:false;index:idx_depot_new_column1_new_column2" db:"new_column2" sqac:"nullable:false;default:0;index:idx_depot_new_column1_new_column2" |
| “fkey:" | Foreign-keys can be declared between table columns. The following example results in the creation of a foreign-key between the table’s “warehouse_id” column and reference column “id” in table “warehouse”. WarehouseID uint64 db:"warehouse_id" json:"warehouse_id" sqac:"nullable:false;fkey:warehouse(id)" This example is not very clear - see the full example code excerpt in the Table Declaration Examples section. |
| "-" | There are scenarios where model columns may not be persisted in the database. If a column is to be determined at runtime by the consuming application (for example), the following syntax may be used: NonPersistentColumn string db:"non_persistent_column" sqac:"-" |