ORM

Golang Query Executor and Database Connector

Overview

ORM is a package that facilitates execution of loukoum queries as well as migrations and scripts generate by prana.

Installation

$ go get -u github.com/phogolabs/orm

Introduction

Note that ORM is in BETA. We may introduce breaking changes until we reach v1.0.

Gateway API facilitates object relation mapping and query building by using loukoum and sqlx.

Let's first import all required packages:

import (
  lk "github.com/ulule/loukoum"
  "github.com/phogolabs/orm"
)

and then establish the connection:

gateway, err := orm.Open("sqlite3", "example.db")
if err != nil {
 return err
}

SQL Queries

All loukoum queries are complaint with orm.Query interface:

// Query returns the underlying query
type Query interface {
	// Query prepares the query
	Query() (string, []Param)
}

// NamedQuery returns the underlying query
type NamedQuery interface {
	// Query prepares the query
	NamedQuery() (string, map[string]Param)
}

That allows easy execution of all kind of queries.

Because the package is empowered by sqlx. It can perform field mapping of Golang structs by reading a db field tag. Let's assume that we have the following struct:

// Package model contains an object model of database schema 'default'
// Auto-generated at Thu Apr 19 21:36:35 CEST 2018
package model

import null "gopkg.in/volatiletech/null.v6"

// User represents a data base table 'users'
type User struct {
	// ID represents a database column 'id' of type 'INT PRIMARY KEY NOT NULL'
	ID int `db:"id,primary_key,not_null" json:"id" xml:"id" validate:"required"`

	// FirstName represents a database column 'first_name' of type 'TEXT NOT NULL'
	FirstName string `db:"first_name,not_null" json:"first_name" xml:"first_name" validate:"required"`

	// LastName represents a database column 'last_name' of type 'TEXT NULL'
	LastName null.String `db:"last_name,null" json:"last_name" xml:"last_name" validate:"-"`
}

Insert a new record

query := lk.Insert("users").
	Set(
		lk.Pair("first_name", "John"),
		lk.Pair("last_name", "Doe"),
	)

if _, err := gateway.Exec(query); err != nil {
  return err
}

Select all records

query := lk.Select("id", "first_name", "last_name").From("users")
users := []User{}

if err := gateway.Select(&users, query); err != nil {
  return err
}

Select a record

query := lk.Select("id", "first_name", "last_name").
	From("users").
	Where(lk.Condition("first_name").Equal("John"))

user := User{}

if err := gateway.SelectOne(&user, query); err != nil {
  return err
}

You can read more details about loukoum on their repository.

RQL Support

The package support RQL queries. RQL provides a simple and light-weight API for adding dynamic querying capabilities to web-applications that use SQL-based database.

Let's have the following query that is received via HTTP:

{
  "$or": [
    { "city": "TLV" },
    { "zip": { "$gte": 49800, "$lte": 57080 } }
  ]
}

Then we can process it in the following way:

param := &orm.RQLQuery{}

err := json.Unmarshal(body, param)
if err != nil {
  panic(err)
}

rows, err := gateway.Query(orm.RQL("addresses", param))

SQL Migrations with Prana

You can execute the migration generated by Prana. First, you should load the migration directory by using Parcello. You can load it from embedded resource or from the local directory:

if err := gateway.Migrate(parcello.Dir("./database/migration")); err != nil {
	return err
}

SQL Scripts and Routines with Prana

ORM provides a way to work with embeddable SQL scripts. It can understand SQL Scripts from file and execute them as standard SQL queries. Let's assume that we have SQL query named show-sqlite-master.

Let's first load the SQL script from file:

-- name: show-sqlite-master
SELECT * FROM sqlite_master;

-- named: show-table
SELECT * FROM {{table}};

if err = gateway.ReadFrom(file); err != nil {
  log.WithError(err).Fatal("Failed to load script")
}

Then you can execute the desired script by just passing its name:

_, err = gateway.Exec(orm.Routine("show-sqlite-master"))

Also you can Raw SQL Scripts from your code, you should follow this example:

rows, err := gateway.Query(orm.SQL("SELECT * FROM users WHERE id = ?", 5432))

Also you can use handlerbars to template your query. But be aware how you use it in order not to open possible SQL injections:

param := orm.Map{
  "table": "users",
}

_, err = gateway.Exec(orm.Routine("show-table", param))

Example

You can check our Getting Started Example.

For more information, how you can change the default behavior you can read the help documentation by executing:

Contributing

We are welcome to any contributions. Just fork the project.

logo made by Free Pik