# 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 (
  "github.com/System-Glitch/goyave/v3/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.

# 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 kernel.go:

import _ "github.com/System-Glitch/goyave/v3/database/dialect/mysql"
// import _ "github.com/System-Glitch/goyave/v3/database/dialect/postgres"
// import _ "github.com/System-Glitch/goyave/v3/database/dialect/sqlite"
// import _ "github.com/System-Glitch/goyave/v3/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 (
  "github.com/System-Glitch/goyave/v3/database"
  "example.com/user/mydriver"
)

func init() {
  database.RegisterDialect("my-driver", "{username}:{password}@({host}:{port})/{name}?{options}", mydriver.Open)
}

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 you 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.ClearInitializers

Remove all database connection initializer functions.

Parameters Return
void

# 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);unique_index"`
    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:"AUTO_INCREMENT"` // 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 kernel.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

Since v2.9.0

Sometimes you may wish to exclude some fields from your model's JSON form, such as passwords. To do so, you can add the model:"hide" tag to the field you want to hide.

type User struct {
    Username string
    Password string `model:"hide" json:",omitempty"`
}

When a struct is sent as a response through response.JSON(), all its fields (including promoted fields) tagged with model:"hide" will be set to their zero value. Add the json:",omitempty" tag to entirely remove the field from the resulting JSON string.

You can also filter hidden fields by passing a struct to helper.RemoveHiddenFields().

# 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).

# 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 kernel.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"

    "github.com/System-Glitch/goyave/v3"
    "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,
    })
}

Reference (opens new window)

# 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.

Reference (opens new window)

# MSSQL

Refer to the driver's documentation (opens new window).