proxy.golang.org : github.com/jjeffery/sqlr
Package sqlr is designed to reduce the effort required to work with SQL databases. It is intended for programmers who are comfortable with writing SQL, but would like assistance with the sometimes tedious process of preparing SQL queries for tables that have a large number of columns, or have a variable number of input parameters. This GoDoc summary provides an overview of how to use this package. For more detailed documentation, see https://jjeffery.github.io/sqlr. Preparing SQL queries with many placeholder arguments is tedious and error-prone. The following insert query has a dozen placeholders, and it is difficult to match up the columns with the placeholders. It is not uncommon to have tables with many more columns than this example, and the level of difficulty increases with the number of columns in the table. This package uses reflection to simplify the construction of SQL queries. Supplementary information about each database column is stored in the structure tag of the associated field. The calling program creates a schema, which describes rules for generating SQL statements. These rules include specifying the SQL dialect (eg MySQL, Postgres, SQLite) and the naming convention used to convert Go struct field names into column names (eg "GivenName" => "given_name"). The schema is usually created during program initialization. Once created, a schema is immutable and can be called concurrently from multiple goroutines. A session is created using a context, a database connection (eg *sql.DB, *sql.Tx, *sql.Conn), and a schema. A session is inexpensive to create, and is intended to last no longer than a single request (which might be a HTTP request, in the case of a HTTP server). A session is bounded by the lifetime of its context. Once a session has been created, it is possible to create simple row insert/update statements with minimal effort. The Exec method parses the SQL query and replaces occurrences of "{}" with the column names or placeholders that make sense for the SQL clause in which they occur. In the example above, the insert and update statements would look like: If the schema is created with a different dialect then the generated SQL will be different. For example if the Postgres dialect was used the insert and update queries would look more like: Inserting and updating a single row are common enough operations that the session has methods that make it very simple: Select queries can be performed using the session's Select method: The SQL queries prepared in the above example would look like the following: The examples are using a MySQL dialect. If the schema had been setup for, say, a Postgres dialect, a generated query would look more like: It is an important point to note that this feature is not about writing the SQL for the programmer. Rather it is about "filling in the blanks": allowing the programmer to specify as much of the SQL query as they want without having to write the tiresome bits. When inserting rows, if a column is defined as an autoincrement column, then the generated value will be retrieved from the database server, and the corresponding field in the row structure will be updated. Autoincrement column values work for all supported databases (PostgreSQL, MySQL, Microsoft SQL Server and SQLite). Most SQL database tables have columns that are nullable, and it can be tiresome to always map to pointer types or special nullable types such as sql.NullString. In many cases it is acceptable to map the zero value for the field a database NULL in the corresponding database column. Where it is acceptable to map a zero value to a NULL database column, the Go struct field can be marked with the "null" keyword in the field's struct tag. In the above example the `manager_id` column can be null, but if all valid IDs are non-zero, it is unambiguous to map the zero value to a database NULL. Similarly, if the `phone` column an empty string it will be stored as a NULL in the database. Care should be taken, because there are cases where a zero value and a database NULL do not represent the same thing. There are many cases, however, where this feature can be applied, and the result is simpler code that is easier to read. It is not uncommon to serialize complex objects as JSON text for storage in an SQL database. Native support for JSON is available in some database servers: in partcular Postgres has excellent support for JSON. It is straightforward to use this package to serialize a structure field to JSON: In the example above the `Cmplx` field will be marshaled as JSON text when writing to the database, and unmarshaled into the struct when reading from the database. While most SQL queries accept a fixed number of parameters, if the SQL query contains a `WHERE IN` clause, it requires additional string manipulation to match the number of placeholders in the query with args. This package simplifies queries with a variable number of arguments. When processing an SQL query, it detects if any of the arguments are slices: In the above example, the number of placeholders ("?") in the query will be increased to match the number of values in the `ids` slice. The expansion logic can handle any mix of slice and scalar arguments. A session can create type-safe query functions. This is a very powerful feature and makes it very easy to create type-safe data access. See the Session.MakeQuery function for examples.
Registry
-
Source
- Documentation
- JSON
purl: pkg:golang/github.com/jjeffery/sqlr
Keywords:
golang
, sql
License: MIT
Latest release: about 6 years ago
First release: almost 7 years ago
Namespace: github.com/jjeffery
Stars: 31 on GitHub
Forks: 1 on GitHub
See more repository details: repos.ecosyste.ms
Last synced: about 1 month ago