Extended SQL Format¶
The key goal of the sqlr package is to simplify some of the more tedious aspects of preparing SQL queries in Go code. One of the most tiresome chores (at least in the author’s opinion) is preparing SQL queries that have a large number of columns and placeholders, and matching them against fields in a Go language struct.
One of the appealing aspects of an ORM is that it will map the inputs and outputs of an SQL query into the corresponding fields of a Go struct, but in doing so it creates the SQL as part of the process.
Package sqlr takes a different approach. It assumes that the programmer knows the SQL that they want to execute, they just don’t want to mess around with the tedium of getting the column lists correct and in the correct order for the placeholders and struct fields.
To this end, the sqlr package knows how to tokenize a string containing
SQL, and it looks for curly braces {}. Anytime it finds them it knows
it has to substitute a list of columns.
-- Examples of "extended" SQL
select {} from table_name where {} order by {};
insert into table_name({}) values({});
update table_name set {} where {};
delete from table_name where {};
Column lists in each SQL clause¶
SQL Clause Columns Format SELECT {}All columns col1,col2,...SELECT ... FROM ... WHERE {}Primary key col1=? and col2=? ...SELECT ... FROM ... WHERE ... ORDER BY {}Primary key col1,col2,...UPDATE ... SET {}Updateable col1=?,col2=?,...UPDATE ... SET ... WHERE {}Primary key col1=? and col2=? ...INSERT INTO ...({})Insertable col1,col2,...INSERT INTO ... VALUES ({})Insertable ?,?,...DELETE FROM ... WHERE {}Primary key col1=? and col2=? ...
The columns that are included in each list are determined from the contents of the Go struct field tags.
| Columns | Inclusion criteria from struct field tag |
|---|---|
| All columns | Does not contain - |
| Primary key | Contains primary key |
| Updateable | Contains neither primary key nor autoincrement |
| Insertable | Does not contain autoincrement |
The {} can be modified with a small set of simple modifiers
| Symbol | Meaning |
|---|---|
{} |
Column list as per the SQL clause |
{alias x} |
Prefix the column list with
|
{pk} |
Override column list to contain only primary key columns |
{all} |
Override column list to contain all columns |
Example of using aliases:
select {alias u}
from users u
inner join user_search_terms t on t.user_id = u.id
where t.search_term like ?