# Database
# Introduction
Most web applications use a database. In this section, we are going to see how Goyave applications can query a database, using the awesome Gorm ORM (opens new window).
Database connections are managed by the framework and are long-lived. When the server shuts down, the database connections are closed automatically. So you don't have to worry about creating, closing or refreshing database connections in your application.
All functions below require the database
and the gorm
packages to be imported.
import (
"goyave.dev/goyave/v4/database"
"gorm.io/gorm"
)
# Configuration
Very few code is required to get started with databases. There are some configuration options that you need to change though:
database.connection
database.host
database.port
database.name
database.username
database.password
database.options
database.maxOpenConnection
database.maxIdleConnection
database.maxLifetime
TIP
database.options
represents the additional connection options. For example, when using MySQL, you should use the parseTime=true
option so time.Time
can be handled correctly. Available options differ from one driver to another and can be found in their respective documentation.
# Options
This section gives an example of the value for the database.options
configuration entry for each supported driver.
# MySQL
"options": "charset=utf8mb4&collation=utf8mb4_general_ci&parseTime=true&loc=Local"
Find more information about the MySQL options here (opens new window).
# PostgreSQL
"options": "sslmode=disable application_name=goyave"
Find more information about the PostgreSQL options here (opens new window).
# SQLite
"options": "cache=shared&mode=memory"
Find more information about the SQLite options here (opens new window).
# MSSQL
"options": "encrypt=disable"
Find more information about the MSSQL options here (opens new window).
# Drivers
The framework supports the following sql drivers out-of-the-box:
none
(Disable database features)mysql
postgres
sqlite3
mssql
Change the database.connection
config entry to the desired driver.
In order to be able connect to the database, Gorm needs a database driver to be imported. Add the following import to your main.go
:
import _ "goyave.dev/goyave/v4/database/dialect/mysql"
// import _ "goyave.dev/goyave/v4/database/dialect/postgres"
// import _ "goyave.dev/goyave/v4/database/dialect/sqlite"
// import _ "goyave.dev/goyave/v4/database/dialect/mssql"
TIP
For SQLite, only the database.name
config entry is required.
You can register more dialects for GORM. Start by implementing or importing it, then tell Goyave how to build the connection string for this dialect:
import (
"goyave.dev/goyave/v4/database"
"example.com/user/mydriver"
)
func init() {
database.RegisterDialect("my-driver", "{username}:{password}@({host}:{port})/{name}?{options}", mydriver.Open)
}
TIP
See the GORM "Write driver" documentation (opens new window).
Template format accepts the following placeholders, which will be replaced with the corresponding configuration entries automatically:
{username}
{password}
{host}
{port}
{name}
{options}
You cannot override a dialect that already exists.
# database.RegisterDialect
Parameters | Return |
---|---|
name string | void |
template string | |
initializer DialectorInitializer |
TIP
DialectorInitializer
is an alias for func(dsn string) gorm.Dialector
# Getting a database connection
# database.GetConnection
Returns the global database connection pool. Creates a new connection pool if no connection is available.
By default, the PrepareStmt
(opens new window) option is enabled.
The connections will be closed automatically on server shutdown so you don't need to call Close()
when you're done with the database.
Parameters | Return |
---|---|
*gorm.DB |
Example:
db := database.GetConnection()
db.First(&user)
# database.Conn
Conn()
is a short alias for GetConnection()
.
Parameters | Return |
---|---|
*gorm.DB |
Example:
db := database.Conn()
db.First(&user)
TIP
Learn how to use the CRUD interface and the query builder in the Gorm documentation (opens new window).
# database.Close
If you want to manually close the database connection, you can do it using Close()
. New connections can be re-opened using GetConnection()
as usual. This function does nothing if the database connection is already closed or has never been created.
Parameters | Return |
---|---|
error |
Example:
database.Close()
# Connection initializers
You can modify the global instance of *gorm.DB
when it's created (and re-created, after a Close()
for example) using Initializer
functions. This is useful if you want to set global settings such as gorm:table_options
and make them effective for your whole application. It is recommended to register initializers before starting the application.
Initializer functions are called in order, meaning that functions added last can override settings defined by previous ones.
database.AddInitializer(func(db *gorm.DB) {
db.Config.SkipDefaultTransaction = true
db.Statement.Settings.Store("gorm:table_options", "ENGINE=InnoDB")
})
# database.AddInitializer
Parameters | Return |
---|---|
initializer database.Initializer | void |
TIP
database.Initializer
is an alias forfunc(*gorm.DB)
- Useful link related to initializers: GORM config (opens new window)
# database.ClearInitializers
Remove all database connection initializer functions.
Parameters | Return |
---|---|
void |
# Replacing the connection
Since v4.1.0
You may need to replace or setup a connection manually, for example when using database mock libraries for testing. You can use database.SetConnection()
for this purpose. Using this function is not recommended outside of tests. Prefer using a custom dialect instead.
# database.SetConnection
Manually replace the automatic DB connection. If a connection already exists, closes it before discarding it.
Parameters | Return |
---|---|
dialector gorm.Dialector | *gorm.DB |
error |
# Models
A model is a structure reflecting a database table structure. An instance of a model is a single database record. Each model is defined in its own file inside the database/model
directory.
# Defining a model
Models are usually just normal Golang structs, basic Go types, or pointers of them. sql.Scanner
and driver.Valuer
interfaces are also supported.
func init() {
database.RegisterModel(&User{})
}
type User struct {
gorm.Model
Name string
Age sql.NullInt64
Birthday *time.Time
Email string `gorm:"type:varchar(100);uniqueIndex"`
Role string `gorm:"size:255"` // set field size to 255
MemberNumber *string `gorm:"unique;not null"` // set member number to unique and not null
Num int `gorm:"autoIncrement"` // set num to auto incrementable
Address string `gorm:"index:addr"` // create index with name `addr` for address
IgnoreMe int `gorm:"-"` // ignore this field
}
TIP
All models should be registered in an init()
function inside their model file. To ensure the init()
functions are executed before the server starts, import the models
package in your main.go
.
import _ "database/model"
Learn more about model declaration in the Gorm documentation (opens new window).
# database.RegisterModel
Registers a model for auto-migration.
Parameters | Return |
---|---|
model interface{} | void |
# database.GetRegisteredModels
Get the registered models. The returned slice is a copy of the original, so it cannot be modified.
Parameters | Return |
---|---|
[]interface{} |
# database.ClearRegisteredModels
Unregister all models.
Parameters | Return |
---|---|
void |
# Hidden fields
Sometimes you may wish to exclude some fields from your model's JSON form, such as passwords. To do so, you can add the json:"-"
tag to the field you want to hide.
type User struct {
Username string
Password string `json:"-"`
}
# Automatic migrations
If the database.autoMigrate
config option is set to true, all registered models will be automatically migrated when the server starts.
WARNING
Automatic migrations create tables, missing foreign keys, constraints, columns and indexes, and will change existing column’s type if it’s size, precision or nullable changed. They wont't delete unused columns.
If you would like to know more about migrations using Gorm, read their documentation (opens new window).
# Views
Since v4.2.0
If you define a model that represents a SQL view, you may want to make it implement database.IView
so it can be identified as a view. This is especially useful in TestSuite
so ClearDatabase()
doesn't try (and fail) to delete records of that model.
You can use composition and database.View
to easily make your models implement the database.IView
interface.
type Model struct{
database.View
ID int
//...
}
# Pagination
Since v3.4.0
database.Paginator
is a tool that helps you paginate records. This structure contains pagination information (current page, maximum page, total number of records), which is automatically fetched. You can send the paginator directly to the client as a response.
Example:
articles := []model.Article{}
db := database.Conn()
paginator := database.NewPaginator(db, page, pageSize, &articles)
result := paginator.Find()
if response.HandleDatabaseError(result) {
response.JSON(http.StatusOK, paginator)
}
When calling paginator.Find()
, the paginator
struct will be automatically updated with the total and max pages. The destination slice passed to NewPaginator()
is also updated automatically. (articles
in the above example)
You can add clauses to your SQL query before creating the paginator. This is especially useful if you want to paginate search results. The condition will be applied to both the total records count query and the actual page query.
Full example:
func Index(response *goyave.Response, request *goyave.Request) {
articles := []model.Article{}
page := 1
if request.Has("page") {
page = request.Integer("page")
}
pageSize := DefaultPageSize
if request.Has("pageSize") {
pageSize = request.Integer("pageSize")
}
tx := database.Conn()
if request.Has("search") {
search := sqlutil.EscapeLike(request.String("search"))
tx = tx.Where("title LIKE ?", "%"+search+"%")
}
paginator := database.NewPaginator(tx, page, pageSize, &articles)
result := paginator.Find()
if response.HandleDatabaseError(result) {
response.JSON(http.StatusOK, paginator)
}
}
TIP
The above example assumes its route is validated using the following RuleSet
:
var IndexRequest = validation.RuleSet{
"page": validation.List{"integer"},
"pageSize": validation.List{"integer", "min:1"},
"search": validation.List{"string"},
}
# database.NewPaginator
Create a new Paginator
.
Given DB transaction can contain clauses already, such as WHERE, if you want to filter results.
Parameters | Return |
---|---|
db *gorm.DB | *database.Paginator |
page int | |
pageSize int | |
dest interface{} |
Paginator
definition:
type Paginator struct {
Records interface{} `json:"records"`
MaxPage int64 `json:"maxPage"`
Total int64 `json:"total"`
PageSize int `json:"pageSize"`
CurrentPage int `json:"currentPage"`
}
# paginator.Find
Find requests page information (total records and max page) and executes the transaction. The Paginate struct is updated automatically, as well as the destination slice given in NewPaginate()
.
Parameters | Return |
---|---|
*gorm.DB |
# Paginating raw queries
Since v4.1.0
By default, the paginator uses the model given when calling NewPaginator
and automatically builds the query. But this is not flexible enough for some use-cases involving more specific queries. You would then need to use a raw query.
# paginator.Raw
The Paginator will execute the raw queries instead of automatically creating them. The raw query should not contain the "LIMIT" and "OFFSET" clauses, they will be added automatically. The count query should return a single number (COUNT(*)
for example). Returns the same Paginator instance.
Parameters | Return |
---|---|
query string | *database.Paginator |
vars []interface{} | |
countQuery string | |
countVars []interface{} |
Example:
rawQuery := "SELECT * FROM articles WHERE title LIKE ?"
rawCountQuery := "SELECT COUNT(*) FROM articles WHERE title LIKE ?"
vars := []interface{}{"%" + sqlutil.EscapeLike("interesting") + "%"}
paginator := NewPaginator(db, 1, 5, &results).Raw(rawQuery, vars, rawCountQuery, vars)
# Setting up SSL/TLS
# MySQL
If you want to make your database connection use a TLS configuration, create database/tls.go
. In this file, create an init()
function which will load your certificates and keys.
Don't forget to blank import the database package in your main.go
: import _ "myproject/database"
. Finally, for a configuration named "custom", add &tls=custom
at the end of the database.options
configuration entry.
package database
import (
"crypto/tls"
"crypto/x509"
"io/ioutil"
"goyave.dev/goyave/v4"
"github.com/go-sql-driver/mysql"
)
func init() {
rootCertPool := x509.NewCertPool()
pem, err := ioutil.ReadFile("/path/ca-cert.pem")
if err != nil {
goyave.ErrLogger.Fatal(err)
}
if ok := rootCertPool.AppendCertsFromPEM(pem); !ok {
goyave.ErrLogger.Fatal("Failed to append PEM.")
}
clientCert := make([]tls.Certificate, 0, 1)
certs, err := tls.LoadX509KeyPair("/path/client-cert.pem", "/path/client-key.pem")
if err != nil {
goyave.ErrLogger.Fatal(err)
}
clientCert = append(clientCert, certs)
mysql.RegisterTLSConfig("custom", &tls.Config{
RootCAs: rootCertPool,
Certificates: clientCert,
})
}
# PostgreSQL
For PostgreSQL, you only need to add a few options to the database.options
configuration entry.
sslmode=verify-full sslrootcert=root.crt sslkey=client.key sslcert=client.crt
Replace root.crt
, client.key
and client.crt
with the paths to the corresponding files.
# MSSQL
Refer to the driver's documentation (opens new window).
← Responses Validation →