sql

package module
v0.0.0-...-cfd2687 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: Aug 14, 2020 License: Apache-2.0 Imports: 15 Imported by: 1

README

sql GoDoc

A spirit of spring boot repositories for go/golang. It helps you to write clean architecture and domain driven design styles. Define your repository just as an interface and enrich it with SQL annotations. This is a slight violation of the clean architecture mantra but just keep in mind, that the interface is your clean contract and the SQL annotation is just a part of the documentation for a concrete implementation. There may be other annotations to allow non-sql-backends and they may co-exists with the sql annotations as well. So nothing to feel dirty here.

sql dialect abstraction

The notation of prepared statements is dialect specific. Even if go provides some helpers, it is in practice not useable, because common drivers like MySQL do not support that (see Issue 561). Therefore, we bring our own named query support, which translates to the correct placeholder syntax for each dialect (e.g. MariaDB oder PostgreSQL).

that's so unidiomatic and java-ish

Well, it tries to keep the best of both worlds. Go is not a good fit for all use cases and we try hard to push that border a bit further. A good article about idiomaticity can be found in the blog of Dave Cheney.

usage

This library makes heavy usage of reflectplus, so be sure, that it is configured correctly. Feels best with a go generate ./... before launching from your IDE. Besides that, the current implementation is reflection based, which may be changed to a generating approach to be more performant and debugger-friendly.

annotation example

You can only define a very limited set of methods. This is best explained by an example. Other kinds of method specifications will not work. We do our best to validate the configuration at construction time and give hints to solve that problem.

package sms

import (
	"context"
	"github.com/golangee/uuid"
	"time"
)

type SMS struct {
	ID        uuid.UUID `ee.sql.Name:"id"`
	CreatedAt time.Time `ee.sql.Name:"created_at"`
	Text      string    `ee.sql.Name:"text"`
}

// @ee.stereotype.Repository
type Repository interface {
	// @ee.sql.Query("SELECT id,created_at,text FROM sms LIMIT :limit")
	FindAll(ctx context.Context, limit int) ([]SMS, error)

	// @ee.sql.Query("SELECT id,created_at,text FROM sms WHERE id = :id")
	FindById(ctx context.Context, id uuid.UUID) (SMS, error)

	// @ee.sql.Query("INSERT INTO sms (id,created_at,recipient,text) VALUES (:uuid, :createdAt, :recipient, :text)")
	Create(ctx context.Context, uuid uuid.UUID, createdAt time.Time, recipient string, text string) error
}

Don't forget to (re-)generate reflectplus information and proxies:

go generate ./...

Instantiation example

package main

import (
	"context"
	"database/sql"
	"fmt"
	_ "github.com/lib/pq"
	sql2 "github.com/golangee/sql"
	"github.com/golangee/uuid"
	"github.com/myproject/mymodule/service/sms"
	"net/http"
	"time"
)
import _ "github.com/go-sql-driver/mysql"
import _ "github.com/myproject/mymodule" // load generated reflectplus


func main() {

	db, err := sql.Open("mysql", "user:pwd@/mydb?parseTime=true")
	if err != nil {
		panic(err)
	}

	err = db.Ping()
	if err != nil {
		panic("failed to connect to db: " + err.Error())
	}

	repos, err := sql2.MakeSQLRepositories(sql2.MySQL)
	if err != nil {
		panic(err)
	}
	ctx := sql2.WithContext(context.Background(), db)
	smsRepo := repos[0].(sms.Repository)
	if err := smsRepo.Create(ctx, uuid.New(), time.Now(), "1234", "hello sms"); err != nil {
		panic(err)
	}

	users, err := smsRepo.FindAll(ctx, 5)
	if err != nil {
		panic(err)
	}
	fmt.Println(users)

	usr, err := smsRepo.FindById(ctx, uuid.MustParse("04f85469-5985-48c1-91a1-bb512a71b1cf"))
	if err != nil {
		panic(err)
	}
	fmt.Println(usr)

}

Documentation

Index

Constants

View Source 
const (
	SSLPreferred      SSLMode = 0 // mysql: PREFERRED, postgres: prefer
	SSLDisable                = 1 // mysql: DISABLED, postgres: disable
	SSLRequired               = 2 // mysql: REQUIRED, postgres: require
	SSLVerifyCA               = 3 // mysql: VERIFY_CA, postgres: verify-ca
	SSLVerifyIdentify         = 4 // mysql: VERIFY_IDENTITY, postgres: verify-full

)
View Source 
const (
	SQL       MigrationType   = "sql"
	Success   MigrationStatus = "success"
	Failed    MigrationStatus = "failed"
	Pending   MigrationStatus = "pending"
	Executing MigrationStatus = "executing"
)
View Source 
const (
	// returns error
	Stmt sqlResultType = 0

	// returns []T,error
	List sqlResultType = 1

	// returns T,error
	Single sqlResultType = 2
)
View Source 
const AnnotationName = "ee.sql.Name"

AnnotationName must be used for field names to define a mapping of sql columns to fields, which is usually required because the notation differs (e.g. camel case vs snake case). This is SQL specific, because other repository providers require a different name-mapping. Example: 

type MyEntity struct {
   ID uuid.UUID `ee.sql.Name:"id"`
}
View Source 
const AnnotationQuery = "ee.sql.Query"

AnnotationQuery defines a named SQL query which is translated into dialect specific positional prepared statement notations. Only one annotation per Method is allowed. Each Method must have exactly one annotation per dialect and all named parameters must be matched to actual method parameters. Queries without dialect are candidates for all dialects, however if a specific dialect is defined, it has a higher priority and will not be considered a duplicate. Example: 

	@ee.sql.Query("SELECT id FROM myTable WHERE col = :param") //generic named query
 @ee.sql.Query("dialect":"mysql", "value":"SELECT BIN_TO_UUID(id) FROM myTable WHERE col = :param") // mysql query
View Source 
const AnnotationRepository = "ee.stereotype.Repository"

AnnotationRepository defines a stereotype and is currently only a marker annotation so that #MakeSQLRepositories() can detect repository interfaces to implement. Actually this annotation is not sql specific and may be shared by different repository providers (e.g. no-sql). Example: 

// @ee.stereotype.Repository("myTable")
type MyRepository interface {
   // @ee.sql.Query("SELECT id FROM myTable WHERE id = :id")
   FindById(ctx context.Context, id uuid.UUID) (MyEntity, error)
}
View Source 
const AnnotationSchema = "ee.sql.Schema"

AnnotationSchema contains generic or dialect specific statements like "CREATE TABLE" statements. They are enumerated by order, if nothing else has been set. Internally, the migration information is kept in the following table: 

  CREATE TABLE IF NOT EXISTS "migration_schema_history"
  (
   	"group"              VARCHAR(255) NOT NULL,
   	"version"            BIGINT       NOT NULL,
   	"script"             VARCHAR(255) NOT NULL,
   	"type"               VARCHAR(12)  NOT NULL,
   	"checksum"           CHAR(64)     NOT NULL,
   	"applied_at"         TIMESTAMP    NOT NULL,
   	"execution_duration" BIGINT       NOT NULL,
   	"status"             VARCHAR(12)  NOT NULL,
   	"log"                TEXT         NOT NULL,
   	PRIMARY KEY ("group", "version")
	 )

Usage examples: 

@ee.sql.Schema("CREATE TABLE IF NOT EXISTS `my_table` (`id` BINARY(16), PRIMARY KEY (`id`)") // generic schema
@ee.sql.Schema("dialect":"postgresql", "value":"CREATE TABLE `my_table` (`id` UUID, PRIMARY KEY (`id`)") // specific schema

Be careful when mixing specific and non-specific schema declaration: they are just filtered and have no precedence. Migration order is as specified, but can be overloaded. Also each migration belongs to a group, which is by default the name of the stereotype. Complex example: 

  @ee.sql.Schema("""
  "dialect":"mysql", "version":1, "group":"some_name", "value":
  "CREATE TABLE IF NOT EXISTS `some_table_name`
  (
   	`group`              VARCHAR(255) NOT NULL,
   	`version`            BIGINT       NOT NULL,
   	`script`             VARCHAR(255) NOT NULL,
   	`type`               VARCHAR(12)  NOT NULL,
   	`checksum`           CHAR(64)     NOT NULL,
   	`applied_at`         TIMESTAMP    NOT NULL,
   	`execution_duration` BIGINT       NOT NULL,
   	`status`             VARCHAR(12)  NOT NULL,
   	`log`                TEXT         NOT NULL,
   	PRIMARY KEY (`group`, `version`)
	 )"
  """)
View Source 
const DialectValue = "dialect"

DialectValue can be used by ee.sql.Schema and ee.sql.Query

View Source 
const GroupValue = "group"

GroupValue can be used by ee.sql.Schema to override the default group assignment

View Source 
const VersionValue = "version"

VersionValue can be used by ee.sql.Schema to override the default version assignment (which is its applicable index)

Variables

View Source 
var ErrEntityNotFound = fmt.Errorf("entity not found")

Functions

func ApplyMigrations 

func ApplyMigrations(dialect Dialect, db DBTX, migrations ...Migration) error

ApplyMigrations calculates which migrations needs to be applied and tries to apply the missing ones.

func MakeSQLRepositories 

func MakeSQLRepositories(dialect Dialect) ([]interface{}, error)

MakeSQLRepositories scans for @ee.stereotype.Repository annotated interfaces and tries to create a proxy instance for each.

func Migrate 

func Migrate(dialect Dialect, dbtx DBTX) error

Migrate grabs all @ee.sql.Schema() annotations from all available repositories and tries to apply them. Each schema statement is verified to proof that the migration process is repeatable.

func MustMakeSQLRepositories 

func MustMakeSQLRepositories(dbtx DBTX) []interface{}

MustMakeSQLRepositories tries to autodetect and implement all interfaces marked with @ee.stereotype.Repository.

func MustMigrate 

func MustMigrate(db *sql.DB)

MustMigrate panics, if the migrations cannot be applied. Creates a transaction and tries a rollback, before bailing out. Delegates to #Migrate() and auto detects dialect.

func MustOpen 

func MustOpen(opts Opts) *sql.DB

MustOpen bails out, if it cannot connect

func NewRepository 

func NewRepository(dialect Dialect, dst interface{}) error

NewRepository tries to instantiate the given interface, so it must be a pointer to a concrete (nil) interface. Example: 

var repo MyRepo
err := sql.NewRepository(sql.ParseDialect("mysql"), &repo)

func Open 

func Open(opts Opts) (*sql.DB, error)

Open is a delegate to #sql.Open() and assembles the correct connection string automatically. You still need to import the driver, you want to support, e.g. 

import _ "github.com/go-sql-driver/mysql" // for mysql
import _ "github.com/lib/pq" // for postgres

func WithContext 

func WithContext(ctx context.Context, db DBTX) context.Context

WithContext creates a new context containing the given DBTX. Use this to implement orthogonal requirements like a scoped transaction. To learn more about when and how to use context, take a look at https://tip.golang.org/pkg/context/. It is a performance decision to reuse a repository, instead of creating the entire dependency chain for each request.

func WithTransaction 

func WithTransaction(db *sql.DB) func(Handler) Handler

Types

type DBTX 

type DBTX interface {
	ExecContext(ctx context.Context, query string, args ...interface{}) (sql.Result, error)
	QueryContext(ctx context.Context, query string, args ...interface{}) (*sql.Rows, error)
}

func FromContext 

func FromContext(ctx context.Context) (DBTX, error)

FromContext is the counterpart of WithContext and returns a DBTX instance from the request-scoped context.

type Dialect 

type Dialect int
const (
	// MySQL examples
	//  WHERE col = ?
	//  VALUES(?, ?, ?)
	MySQL Dialect = 1

	// PostgreSQL examples
	//  WHERE col = $1
	//  VALUES($1, $2, $3)
	PostgreSQL Dialect = 2

	// Oracle examples
	//  WHERE col = :1
	//  VALUES(:1, :2, :3)
	Oracle Dialect = 3
)

func DetectDialect 

func DetectDialect(tx DBTX) (Dialect, error)

DetectDialect tries to auto detect the dialect from the given database connection

func ParseDialect 

func ParseDialect(str string) Dialect

ParseDialect guestimates the dialect from the given string or returns 0

func (Dialect) Matches 

func (d Dialect) Matches(name string) bool

func (Dialect) String 

func (d Dialect) String() string

type DialectStatement 

type DialectStatement struct {
	// contains filtered or unexported fields
}

func (DialectStatement) ExecContext 

func (s DialectStatement) ExecContext(db DBTX, ctx context.Context, args ...interface{}) (sql.Result, error)

Exec executes a statement filling in the arguments in the exact order as defined by prepare

func (DialectStatement) QueryContext 

func (s DialectStatement) QueryContext(db DBTX, ctx context.Context, args ...interface{}) (*sql.Rows, error)

func (DialectStatement) String 

func (s DialectStatement) String() string

type Handler 

type Handler = func(writer http.ResponseWriter, request *http.Request, params KeyValues) error

type KeyValues 

type KeyValues = interface {
	ByName(name string) string
}

type Migration 

type Migration = struct {
	Group      string
	Version    int64
	Statements []string
	ScriptName string
}

type MigrationStatus 

type MigrationStatus string

type MigrationStatusEntry 

type MigrationStatusEntry struct {
	Group             string
	Version           int64
	Script            string
	Type              MigrationType
	Checksum          string
	AppliedAt         time.Time
	ExecutionDuration time.Duration
	Status            MigrationStatus
	Log               string
}

func SchemaHistory 

func SchemaHistory(tx DBTX) ([]MigrationStatusEntry, error)

SchemaStatus returns all applied migration or schema scripts and their according states.

type MigrationType 

type MigrationType string

type NamedParameterStatement 

type NamedParameterStatement string

A NamedParameterStatement is like a prepared statement but cross SQL dialect capable. Example: 

"SELECT * FROM table WHERE x = :myParam AND y = :myParam OR z = :myOtherParam

func (NamedParameterStatement) Names 

func (s NamedParameterStatement) Names() []string

func (NamedParameterStatement) Prepare 

func (s NamedParameterStatement) Prepare(sql Dialect, argNames []string) (DialectStatement, error)

Prepare creates a dialect specific statement using the given argNames. Later you need to keep the exact same order.

func (NamedParameterStatement) Validate 

func (s NamedParameterStatement) Validate(names []string) error

Validate checks if the named parameters and given names are congruent

type Opts 

type Opts struct {
	Driver       string  `yaml:"driver"`
	Host         string  `yaml:"host"`
	Port         int     `yaml:"port"`
	User         string  `yaml:"user"`
	Password     string  `yaml:"password"`
	DatabaseName string  `yaml:"databaseName"`
	SSLMode      SSLMode `yaml:"sslMode"`
}

func (Opts) Dialect 

func (o Opts) Dialect() Dialect

Dialect tries to detect the dialect from the driver name

type Repository 

type Repository struct {
	// contains filtered or unexported fields
}

func (*Repository) HandleQuery 

func (r *Repository) HandleQuery(method string, args ...interface{}) []interface{}

type SSLMode 

type SSLMode int

The SSLMode provide a few cross platform modes, see also 

https://dev.mysql.com/doc/refman/5.7/en/connection-options.html#option_general_ssl-mode
https://www.postgresql.org/docs/9.1/libpq-ssl.html

Source Files

View all Source files

Directories

Path Synopsis

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.