Introduction of xo to generate model of golang directly from DB

When developing a web application, I think that there are various ways of DB model definition method.

xo is, DB direct a tool that automatically generates a model definition of golang.

  • PostgreSQL
  • MySQL
  • Oracle
  • Microsoft SQL Server
  • SQLite

I think that it covers almost commonly used RDB.

Installation

Because it is a go of the tool, go get can be installed in.

$ go get -u golang.org/x/tools/cmd/goimports (for dependency)
$ go get -u github.com/knq/xo

This xo I think the command is installed that.

How to use

Let's use it now. The DB you use is PostgreSQL.

CREATE TABLE users (
    id   BIGSERIAL PRIMARY KEY,
    name TEXT,
    age  INT NOT NULL,
    weight INT,
    created_at timestamptz NOT NULL,
    updated_at timestamptz
);

CREATE INDEX users_name_idx ON users(name);

Let's assume that there is such a table and index.

xo run the.

$ mkdir -p models  # create directory
$ xo pgsql://localhost/example -o models

Then, below models

  • User.xo.go
  • Xo_db.xo.go

Two files are created. file generated by the xo *.xo.go It is easy to understand. Therefore.

The following contents are generated in user.xo.go. NOT NULL Notice where the type is different with or not with or wearing. Also, since the json tag is also generated, it can be output as it is as JSON.

// User represents a row from 'public.users'.
type User struct {
        ID        int64          `json:"id"`         // id
        Name      sql.NullString `json:"name"`       // name
        Age       int            `json:"age"`        // age
        Weight    sql.NullInt64  `json:"weight"`     // weight
        CreatedAt *time.Time     `json:"created_at"` // created_at
        UpdatedAt pq.NullTime    `json:"updated_at"` // updated_at

        // xo fields
        _exists, _deleted bool
}

For this generated User type, the following function is generated.

  • Func (u * User) Exists () bool
  • Func (u * User) Deleted () bool
  • Func (u * User) Insert (db XODB) error
  • Func (u * User) Update (db XODB) error
  • Func (u * User) Save (db XODB) error
  • Func (u * User) Delete (db XODB) error
  • Func (u * User) Upsert (db XODB) error (PostgreSQL 9.5+ and above)

XODB型 is the interface to the db that are defined in the xo_db.xo.go.

ID is Primary Key, and index is pasted on name. Therefore, the following two functions are also generated.

  • Func UserByID (db XODB, id int64) (* User, error)
  • Func UsersByName (db XODB, name sql.NullString) ([] * User, error)

It is a flow to SELECT using these functions. UsersByName person is, it is also the point that the return value Slice.

Implementation

It is easy if it is automatically generated so far. The following implementation can be done immediately.

db, err := sql.Open("postgres", "dbname=example sslmode=disable")
if err != nil {
   panic(err)
}

now := time.Now()
u := &User{
   Age:       18,
   CreatedAt: &now,
}
err = u.Insert(db)
if err != nil {
   panic(err)
}

user, err := UserByID(db, u.ID)  // Insertでu.IDがセットされている
if err != nil {
   panic(err)
}
fmt.Println(user.Age)  // -> returnes 18

SQL

Insert and Update and say if you have made the contents of the functions, such as,

// sql query
const sqlstr = `INSERT INTO public.users (` +
        `name, age, weight, created_at, updated_at` +
        `) VALUES (` +
        `$1, $2, $3, $4, $5` +
        `) RETURNING id`

// run query
XOLog(sqlstr, u.Name, u.Age, u.Weight, u.CreatedAt, u.UpdatedAt)
err = db.QueryRow(sqlstr, u.Name, u.Age, u.Weight, u.CreatedAt, u.UpdatedAt).Scan(&u.ID)
if err != nil {
        return err
}

And so on, SQL is generated as it is. Behavior is easy to understand, I like this person.

function

Xo does not deal with table definitions. It also deals with functions.

CREATE FUNCTION say_hello(text) RETURNS text AS $$
BEGIN
    RETURN CONCAT('hello ' || $1);
END;
$$ LANGUAGE plpgsql;

Let's say that we defined a function called. That way, sp_sayhello.xo.go file that will be generated. It is Stored Procedure.

This includes SayHello are defined functions of golang that.

  • Func SayHello (db XODB, v0 string) (string, error)

This is what

// sql query
const sqlstr = `SELECT public.say_hello($1)`

// run query
var ret string
XOLog(sqlstr, v0)
err = db.QueryRow(sqlstr, v0).Scan(&ret)
if err != nil {
        return "", err
}

For example, we call the defined say_hello function in SQL. Therefore,

SayHello(db, "hoge")

As you can see, you can call from golang.

Summary

Us to produce a code of golang from DB of metadata, xo introduced the.

Besides this, it's pretty nervous, such as converting the type defined in PostgreSQL to golang's type. In addition, the code is created with template, you can define this template yourself, so you can change SQL statements, add functions, etc. freely.

I just create almost same thing , but, because the people of xo is far advanced, I think it is better to use the xo.