sqlx

sqlx - Comprehensive SQL Extensions For Go

Please refer to CHANGELOG.md if you encounter breaking changes.

• Motivation
• Usage
  • Dictionary metadata
  • Reader Service
  • Inserter Service
  • Validator Service
  • Updater Service
  • Merger Service
  • Loader Service
• Contibution
• License

Motivation

The goal of this library is to extend and simplify interaction with database/sql api. This library defines

• api to access database dictionary metadata
• services for reading/inserting/loading/updating/deleting

Usage

Dictionary metadata

package mypkg

import (
	"context"
	"database/sql"
	"github.com/viant/sqlx/metadata"
	"github.com/viant/sqlx/metadata/info"
	"github.com/viant/sqlx/metadata/sink"
	"github.com/viant/sqlx/option"
	"log"
)

func ExampleService_Info() {
	dsn := ""
	driver := ""
	db, err := sql.Open(driver, dsn)
	if err != nil {
		log.Fatalln(err)
	}

	meta := metadata.New()
	{
		tables := []sink.Table{}
		catalog := ""
		schema := "mydb"
		err := meta.Info(context.TODO(), db, info.KindTables, &tables, option.NewArgs(catalog, schema))
		if err != nil {
			log.Fatalln(err)
		}
		log.Println(tables)
	}

	{
		columnes := []sink.Column{}
		catalog := ""
		schema := "mydb"
		table := "myTable"
		err := meta.Info(context.TODO(), db, info.KindTable, &columnes, option.NewArgs(catalog, schema, table))
		if err != nil {
			log.Fatalln(err)
		}
		log.Println(columnes)
	}
}

The following info kind and sink are available:

• info.KindVersion: database version
• info.KindCatalogs: catalogs
• info.KindCatalog: catalog for provided catalog name
• info.KindCurrentSchema: current schema
• info.KindSchemas ([]sink.Schema): list of schema for provided catalog
• info.KindSchema ([]sink.Schema): list of schema for provided catalog, schema name
• info.KindTables ([]sink.Table): list of tables for provided catalog, schema
• info.KindTable ([]sink.Column): table columns info for provided catalog, schema, table name
• info.KindViews ([]sink.Table): list of views for provided catalog, schema
• info.KindView ([]sink.Column): view columns info for provided catalog, schema, view name
• info.KindPrimaryKeys ([]sink.Key) list of primary keys for provided catalog, schema, table name
• info.KindForeignKeys ([]sink.Key) list of foreign keys for provided catalog, schema, table name
• info.KindConstraints ([]sink.Key) list of constraints keys for provided catalog, schema, table name
• info.KindIndexes: ([]sink.Index) list of indexes for provided catalog, schema, table name
• info.KindIndex: ([]sink.Index) list of indexes for provided catalog, schema, table name, index name
• info.KindSequences:([]sink.Sequence) list of sequences values for catalog, schema
• info.KindFunctions: ([]sink.Function) list of functions for catalog, schema
• info.KindSession: ([]sink.Session) list of session

I/O Services

Reader Service

package mypkg

import (
	"context"
	"database/sql"
	"github.com/viant/sqlx/io/read"
	"log"
)

func ExampleReader_ReadAll() {
	dsn := ""
	driver := ""
	db, err := sql.Open(driver, dsn)
	if err != nil {
		log.Fatalln(err)
	}
	ctx := context.Background()
	type Foo struct {
		ID     int
		Name   string
		Active bool
	}
	newFoo := func() interface{} { return &Foo{} }
	reader, err := read.New(ctx, db, "SELECT * FROM foo", newFoo)
	if err != nil {
		log.Fatalln(err)
	}
	var foos []*Foo
	err =  reader.QueryAll(ctx, func(row interface{}) error {
		foo := row.(*Foo)
		foos = append(foos, foo)
		return nil
	})
	if err != nil {
		log.Fatalln(err)
	}
	log.Printf("read foos: %+v\n", foos)
}

Inserter Service

package insert_test

import (
  "context"
  "database/sql"
  "fmt"
  "github.com/viant/sqlx/io/insert"
  //Make sure to add specific databas product import
  _ "github.com/viant/sqlx/metadata/product/mysql"
  "github.com/viant/sqlx/option"
  "log"
)

func ExampleService_Exec() {
  type Foo struct {
    ID   int
    Name string
  }
  dsn := ""
  db, err := sql.Open("mysql", dsn)
  if err != nil {
    log.Fatalln(err)
  }

  insert, err := insert.New(context.TODO(), db, "mytable", option.BatchSize(1024))
  if err != nil {
    log.Fatalln(err)
  }
  var records []*Foo
  //records = getAppRecords()

  affected, lastID, err := insert.Exec(context.TODO(), records)
  if err != nil {
    log.Fatalln(err)
  }
  fmt.Printf("affected: %v, last ID: %v\n", affected, lastID)
}

Validator Service

Validator service has ability to validate unique,foreign key and not null constraints, with the following tag:

• unique,table
• required
• refColumn,refTable
• errorMsg

db and refDb are optional SQL qualifiers for table and refTable; they do not select a connection. All four values retain authored SQL identifier quoting. For example, refDb=main,refTable=parents produces main.parents, whereas refDb="odd.schema",refTable=parents produces "odd.schema".parents. The latter names one schema containing a literal dot. For a BigQuery whole path, put the complete backtick or legacy bracket spelling in table/refTable and leave the separate qualifier empty. SQL execution still requires the appropriate database dialect/query mode.

Migration from earlier releases: these four values previously went through Go-string unquoting. This could erase SQL identifier delimiters, change the target, or produce invalid SQL. Quotes now remain in Tag.Db, Tag.RefDb, Tag.Table, Tag.RefTable, generated constraint SQL, and stored validator.Reference values. This is an intentional identifier-correctness change, not byte-for-byte metadata compatibility. If quotes were only used as Go-style value wrappers, remove that extra quoting: db="main" becomes db=main; a multi-part plain prefix written as refDb="project.dataset" becomes refDb=project.dataset. In a Go struct tag, this means removing the inner escaped quotes, not the enclosing sqlx:"...". Keep dialect-appropriate quotes when they actually delimit an SQL identifier. Simple main and "main" resolve to the same schema in SQLite, but raw metadata differs; use the native target matcher/returned native descriptor when comparing or retaining reference identities. Other SQLX tag values and Tagly's default MatchPairs decoding are unchanged.

For example:

type Record struct {
    Id     int              `sqlx:"name=ID,autoincrement,primaryKey"`
    Name   *string          `sqlx:"name=name,unique,table=myTable" json:",omitempty"`
    DeptId *int             `sqlx:"name=name,refColumn=id,refTable=dep" json:",omitempty"`
    StartDate *time.Time    `sqlx:"name=startData,required" json:",omitempty"`
}
var db *sql.Db = nil //populate you db
var rec := &Record{}
validator := New()
validation, err = validator.Validate(context.Background(), db, rec)

Updater Service

Merger Service

Deleter Service

Loader Service

package load_test

import (
	"context"
	"database/sql"
	"fmt"
	
	//Make sure to import specific database loader implementation
     _ "github.com/viant/sqlx/metadata/product/mysql/load"
	"github.com/viant/sqlx/io/load"
	"log"
)

func ExampleService_Exec() {
	type Foo struct {
		ID int
		Name string
	}
	dsn := ""
	db, err := sql.Open("mysql", dsn)
	if err != nil {
		log.Fatalln(err)
	}
	loader, err := load.New(context.Background(), db, "dest_table")
	if err != nil {
		log.Fatalln(err)
	}
	var data []Foo
	
	//data = getAppData()
	count,  err := loader.Exec(context.TODO(), &data)
	if err != nil {
		log.Fatalln(err)
	}
	fmt.Printf("loaded %v\n", count)
}

Supported tags (annotations)

Contribution

sqlx is an open source project and contributors are welcome!

See TODO list

License

The source code is made available under the terms of the Apache License, Version 2, as stated in the file LICENSE.

Individual files may be made available under their own specific license, all compatible with Apache License, Version 2. Please see individual files for details.

Authors

• Valery Carey
• Pawan Poudyal
• Kamil Larysz
• Adrian Witas