Database Access Layer for Golang - Testable, Extendable and Crafted Into a Clean and Elegant API

Overview

REL

GoDoc Build Status Go Report Card Maintainability Codecov Gitter chat

Modern Database Access Layer for Golang.

REL is golang orm-ish database layer for layered architecture. It's testable and comes with its own test library. REL also features extendable query builder that allows you to write query using builder or plain sql.

Features

  • Testable repository with builtin reltest package.
  • Seamless nested transactions.
  • Elegant, yet extendable query builder with mix of syntactic sugar.
  • Supports Eager loading.
  • Composite Primary Key.
  • Multi adapter.
  • Soft Deletion.
  • Pagination.
  • Schema Migration.

Install

go get github.com/go-rel/rel

Getting Started

Examples

License

Released under the MIT License

Issues
  • Execute arbitrary sql statements

    Execute arbitrary sql statements

    I have an update statement that looks like:

    update matches m
        set viewed = true
        where m.request_id in (
            select r.id
            from requests r
                        join users u on r.user_id = u.id
            where u.username = $1)
    

    And I need to execute via rel but I couldn't. ( and I don't want to use rel dsl here 😄 )

    If it is a select query, I could use SQL function and pass it to FindAll function. I tried the Update function but doesn't seem to work.

    I had to use the underlaying *sql/DB Exec method.

    help wanted hacktoberfest 
    opened by mhewedy 15
  • Support embeddable structs

    Support embeddable structs

    Hi! I've added support for embedded structs as discussed in this issue.

    Both pointers and plain fields are supported.

    type Person struct {
    	ID   int
    	Name string
    }
    
    type Employee struct {
    	Person
    	Salary int
    }
    

    By default embedded field names are added without prefixes. Name prefixes can be set like for other fields:

    type Employee struct {
    	Person   `db:"person_"`
    	Salary int
    }
    

    If two fields have the same name, REL panics during construction:

    What I've changed

    • documentData indices now store full field paths as []int
    • documentDatas are merged for embedded fields during creation
    • Embedded pointers are initialized during document creation

    Tests

    I've added tests for all main cases

    opened by dranikpg 11
  • Add alternative soft delete scheme

    Add alternative soft delete scheme

    Allows to use alternative soft delete scheme with boolean field Deleted. When record is deleted it will update Deleted to true and if there is UpdatedAt field it will also update it with deletion timestamp.

    Sample:

    type Test struct {
      ID int64
      Name string
      CreatedAt time.Time
      UpdatedAt *time.Time
      Deleted bool
    }
    
    opened by lafriks 10
  • Add option for filtered/conditional index option

    Add option for filtered/conditional index option

    Add option that implements rel.KeyOption for index WHERE condition.

    As far as I know only MySQL does not support this.

    PostgreSQL: https://www.postgresql.org/docs/9.1/sql-createindex.html SQLite3: https://www.sqlite.org/partialindex.html MSSQL: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-index-transact-sql?view=sql-server-ver15

    so that it could be specified for example:

    t.Unique([]string{"column"}, rel.KeyCondition("NOT deleted"))
    

    That would generate:

    CREATE UNIQUE INDEX ON table (column) WHERE NOT deleted;
    

    technically this could be easily done by simple type but I don't know how to implement this conditionally so not to break MySQL provider that does not support this

    // KeyCondition filter option for unique key.
    type KeyCondition string
    
    func (kc KeyCondition) applyKey(key *Key) {
    	key.Options += fmt.Sprintf(" WHERE (%s)", kc)
    }
    
    opened by lafriks 10
  • [Subquery?] How to query user in any of groups.

    [Subquery?] How to query user in any of groups.

    table User {
      id int
      name varchar(32)
    }
    
    table GroupUser struct {
      user_id int
      group_id int
    }
    
    table Group {
      id int
      name varchar(32)
    }
    

    How to find unique user in groups [1,2,3] ? Follow code does not work:

    groups := []int{1,2,3}
    subQuery := rel.From("group_users").Select("DISTINCT user_id").Where(where.InInt("group_id", groups))
    repo.FindAll(ctx, &users, where.In("id",  subQuery))
    
    opened by xpol 10
  • Problem migrating

    Problem migrating

    Hello,

    I am attempting to run rel migrate on a blank, fresh database. If you observe the screenshot below you'll see I have a migration file, but I am getting an error:

    go: updates to go.sum needed, disabled by -mod=readonly
    

    This is in a simple, test project with a freshly initialized go.mod. Any thoughts? Thanks!

    Screen Shot 2020-10-14 at 11 32 00 AM help wanted good first issue 
    opened by adampresley 10
  • Cascade delete support?

    Cascade delete support?

    hi!

    just found your project on reddit and I'm very interested in trying it out. i've used gorm before but couldn't get myself to like it, this seems like a viable alternative.

    as for my question, does rel support cascade delete as of this point?

    opened by fr3fou 8
  • Optimistic locking

    Optimistic locking

    Hi! I've implemented optimistic locking as discussed here.

    It's automatically enabled by including a LockVersion int field into the struct.

    It adds a where lock_version = $ clause to select only valid records and uses set lock_version = lock_version + 1 for updating locks.

    What I've changed

    Mainly repository.update() and repository.delete(). They now include custom logic for applying locks.

    Because using SQL expressions like += requires reloading the whole struct, I've added a NoReload flag to Mutates to surpress triggering reloads for lock updates. In case no reload is required, the locks are incremented manually.

    Tests

    I've added simple tests for updating and deleting actual and stale records.

    opened by dranikpg 7
  • Non-returning insert statements

    Non-returning insert statements

    Hi! Thank you for the amazing library - I have been really enjoying it so far!

    The issues is following - the insert query builder assumes that the primary key is never inserted, but generated, and attaches RETURNING statement to every insert query.

    The point is - I wanted to reproduce the table with UUID primary key, and the key being generated on the go side - just want to insert both data and primary key. Currently, the primary key policy for the builder is either to consume a primary key name and attach returning statement, or to panic (if no primary key specified). Specifically I talk about this function, which always sets the value because of this function's behaviour. Therefore, this branch is always executed.

    I know these are not standard practices, however, it might be an interesting idea to add support for them. And so far I have not found a logical solution to this problem, and it seems that there should be a discussion on it.

    Thank you for all!

    opened by talkanbaev-artur 7
  • Add option to specify addtional filters for joins

    Add option to specify addtional filters for joins

    Currently it's not possible to specify additional conditions for query joins (for example to specify to join only not deleted records). Only workaround for this is to use Joinf that's not too nice to use as it won't provide a way to do per-adapter field escaping etc.

    I added filter as optional last parameter and as side effect of this is that you can provide multiple filters that will be converted to And filter as the result.

    Still need to implement changes in sql, so currently marking this as WIP

    opened by lafriks 7
  • Rows Modified Support

    Rows Modified Support

    Hey there , just wanted to say nice job on this package , it solves a lot of my concerns having a layered architecture . :)

    I just wanted to ask is there any way to get the RowsModified from a sql query , i know gorm has it as

    rowsAffected := db.
                                Where("field=?",someVal).
                                Where("otherField=?", somVal2).
                                Update(&record).
                                RowsAffected
    
    opened by baderkha 7
  • Bump github.com/stretchr/testify from 1.7.5 to 1.8.0

    Bump github.com/stretchr/testify from 1.7.5 to 1.8.0

    Bumps github.com/stretchr/testify from 1.7.5 to 1.8.0.

    Commits

    Dependabot compatibility score

    Dependabot will resolve any conflicts with this PR as long as you don't alter it yourself. You can also trigger a rebase manually by commenting @dependabot rebase.


    Dependabot commands and options

    You can trigger Dependabot actions by commenting on this PR:

    • @dependabot rebase will rebase this PR
    • @dependabot recreate will recreate this PR, overwriting any edits that have been made to it
    • @dependabot merge will merge this PR after your CI passes on it
    • @dependabot squash and merge will squash and merge this PR after your CI passes on it
    • @dependabot cancel merge will cancel a previously requested merge and block automerging
    • @dependabot reopen will reopen this PR if it is closed
    • @dependabot close will close this PR and stop Dependabot recreating it. You can achieve the same result by closing it manually
    • @dependabot ignore this major version will close this PR and stop Dependabot creating any more for this major version (unless you reopen the PR or upgrade to it yourself)
    • @dependabot ignore this minor version will close this PR and stop Dependabot creating any more for this minor version (unless you reopen the PR or upgrade to it yourself)
    • @dependabot ignore this dependency will close this PR and stop Dependabot creating any more for this dependency (unless you reopen the PR or upgrade to it yourself)
    dependencies 
    opened by dependabot[bot] 1
  • Embedded structs are broken(ish)

    Embedded structs are broken(ish)

    The documentation states that embedded structs are supported, but they are actually only supported if they are anonymous. That's contrary to the example in the docs.

    Is this that intended behavior?

    opened by jpicht 2
  • Fetching joined records alongside with main entity

    Fetching joined records alongside with main entity

    Hey,

    I wonder what would be suggested approach to fetch composite records (those combined of entity and a join entity). In the documentation there is example on how to perform join and then filter results by values in joined tables, but there is nothing about fetching these joined records.

    As an example, I want to describe a simplified version of issue I'm having. Data model here does not make much sense, but it's just to illustrate the problem. Let's say we have a table of users (id, name) and a separate table with number of user followers (user_id, followers_count), it's a one-to-one relation.

    I want to be able to find users with certain number of followers so I can use join and then where clause, but I would like to also fetch number of followers in the same query (it's joined already anyway) instead of using prefetch.

    var users []Users
    err := repo.FindAll(ctx, &users, rel.Join("follower_stats").Where(where.Gt("follower_stats.followers_count", 100)))
    

    This code example above will only populate user data, then I can preload follower_stats for each user, but it seems somewhat wasteful as number of users can be pretty high.

    opened by skolodyazhnyy 3
  • Stable column order for generated SQL statements

    Stable column order for generated SQL statements

    Currently looks like because of map usage for storing column data each time insert/update/select SQL statements are generated column order is different, that makes grouping of SQL statements for perf stats in logs impossible and also this makes it unusable for prepared statement caching being polluted and unusable that hurts performance.

    enhancement 
    opened by lafriks 25
  • Add `rel.True`, `rel.False`, `rel.NotTrue`, `rel.NotFalse` filters

    Add `rel.True`, `rel.False`, `rel.NotTrue`, `rel.NotFalse` filters

    In PG correct way to generate boolean comparison via predicateIS, like IS TRUE, IS NOT TRUE, etc.

    https://www.postgresql.org/docs/9.0/datatype-boolean.html

    enhancement 
    opened by qRoC 5
  • Add Entity manager

    Add Entity manager

    The entity manager is an implementation of the Facade pattern based on rel.Changeset.

    All entities have 3 states:

    • new: base state for new entity - not managed by the entity manager.
    • managed: entity in new state passed to the Persist method of the entity manager or loaded from the database using method from the entity manager like Find
    • removed: Entity must by removed after flush

    Method Flush - commits changes for all managed entities in transaction by rel.Changeset, and reset rel.Changeset state.

    Additionally:

    • Find method with id criteria (find by id) should check if entity is already loaded and return it if is true (do not make a new request)
    • Transactional write-behind allows to replace many Insert in code to one InsertAll
    • Lazy users can wrap in middleware with automatic call flush
    • Use one time ctx context.Conext

    API

    Persist(record interface{})

    Make an entity managed and persistent. The record will be inserted into the database as a result of the Flush operation. Only for new entities

    Remove(record interface{})

    Removes an entity. A removed entity will be removed from the database as a result of the Flush operation.

    Refresh(ctx context.Context, record interface{}) error

    Refreshes the persistent state of an entity from the database, overriding any local changes that have not yet been persisted.

    Flush() error

    Flushes all changes to an entities that have been queued up to now to the database.

    Select methods from rel.Repository

    Retrieve the records, persist them in the entity manager, and return to the user.

    API may by changed like:

    Find[T any](ctx context.Context, queriers ...Querier) (*T, error)
    

    Example

    Current way:

    func (t Todos) MoveToBasket(ctx context.Context, repo rel.Repository, id int) {
    	var todo todos.Todo
    	if err := repo.Find(ctx, &todo, where.Eq("id", id)); err != nil {
    		panic(err)
    	}
    	changesTodo = rel.NewChangeset(&todo)
    
    	var basket todos.Category
    	if err := repo.Find(ctx, &basket, where.Eq("name", "basket")); err != nil {
    		panic(err)
    	}
    	changesBasket = rel.NewChangeset(&basket)
    
            // domain code
    	todo.Category = basket
    	basket.Total += 1
    
    	repo.Transaction(ctx, func(ctx context.Context) error {
    		err := repo.Update(c, &todo, changesTodo)
    			if err != nil {
    				return err
    			}
    
            	err := repo.Update(c, &basket, changesBasket)
    			if err != nil {
    				return err
    			}
    
    			return nil
    	})
    	if err != nil {
    		render(ctx, "Server error", 500)
    	}
    
    	render(ctx, todo, 200)
    }
    

    If you don't want to get a problems, you should always call Update in the same method where entity fetched. Very often it turns out the controller. Very often this place is the controller

    Entity manager:

    func (t Todos) DoSomething(ctx context.Context, em rel.EntityManager, id int) {
    	var todo todos.Todo
    	if err := em.Find(ctx, &todo, where.Eq("id", id)); err != nil {
    		panic(err)
    	}
    
    	var basket todos.Category
    	if err := em.Find(ctx, &basket, where.Eq("name", "basket")); err != nil {
    		panic(err)
    	}
    
    	// domain code
    	todo.Category = basket
    	basket.Total += 1
    
    	render(ctx, todo, 200)
    }
    
    func MiddlewareForLazyUsers(ctx iris.Context, repo rel.Repository) {
    	em := rel.NewEntityManager(repo)
    
    	// request dependency
    	ctx.RegisterDependency(em)
    
    	ctx.Next()
    
    	err := em.Flush(ctx.Context())
    	if err != nil {
    		render(ctx, "Server error", 500)
    	}
    }
    

    Improvements

    1. Dedicated repositories for entity, this will improve the user experience and allow use of generics:
    • rel.Repository: Universal repository(current implementation)
    • rel.EntityRepository[T any]: Repository for entity. Similar to rel.Repository, but with generics and managed by entity manager.
    • rel.EntityManager: Entity manager with methods:
      • Persist(record interface{})
      • Remove(record interface{})
      • Refresh(ctx context.Context, record interface{}) error
      • Flush() error
      • repository(...) any: internal method for rel.Repository[T any].
    func (t Todos) DoSomething(ctx context.Context, em rel.EntityManager, id int) {
    	todo, err := rel.Repository[todos.Todo](em).Find(ctx, where.Eq("id", id))
    	if err != nil {
    		panic(err)
    	}
    
    	basket, err := rel.Repository[todos.Category](em).Find(ctx, where.Eq("name", "basket"))
    	if err != nil {
    		panic(err)
    	}
    
    	// domain code
    	todo.Category = basket
    	basket.Total += 1
    
    	render(ctx, todo, 200)
    }
    
    1. User can implement own repository and register than in entity manager.
    2. Association managed by entity manager
    • when the primary key (id) is not zero. does not work for client side id (like UUID). Entity manager resolve this issue.

    Summary

    Entity Manager allows you to keep the domain clean, and removes the complexity of working with the database

    opened by qRoC 3
Releases(v0.36.0)
Owner
REL
Database Access Layer for Golang - Testable, Extendable and Crafted Into a Clean and Elegant API
REL
Golang REST Layer SQL Storage Handler

This REST Layer resource storage backend stores data in a SQL Database using database/sql.

Hyper38 GmbH 0 Feb 15, 2022
Sqlair - SQLite Query Layer With Golang

sqlair SQLite Query Layer Creates an abstract over the go sql package to provide

Simon Richardson 1 Feb 18, 2022
Library for scanning data from a database into Go structs and more

scany Overview Go favors simplicity, and it's pretty common to work with a database via driver directly without any ORM. It provides great control and

Georgy Savva 590 Jun 21, 2022
[mirror] the database client and tools for the Go vulnerability database

The Go Vulnerability Database golang.org/x/vulndb This repository is a prototype of the Go Vulnerability Database. Read the Draft Design. Neither the

Go 43 May 28, 2022
Database - Example project of database realization using drivers and models

database Golang based database realization Description Example project of databa

Denis 1 Feb 10, 2022
A Go rest API project that is following solid and common principles and is connected to local MySQL database.

This is an intermediate-level go project that running with a project structure optimized RESTful API service in Go. API's of that project is designed based on solid and common principles and connected to the local MySQL database.

Kıvanç Aydoğmuş 21 Jun 6, 2022
CRUD API example is written in Go using net/http package and MySQL database.

GoCrudBook CRUD API example is written in Go using net/http package and MySQL database. Requirements Go MySQL Code Editor Project Structure GoCrudBook

Serhat Karabulut 3 May 15, 2022
Database migrations. CLI and Golang library.

migrate Database migrations written in Go. Use as CLI or import as library. Migrate reads migrations from sources and applies them in correct order to

null 9k Jun 29, 2022
database to golang struct

中文文档 mysql database to golang struct conversion tools base on gorm(v1/v2),You can automatically generate golang sturct from mysql database. big Camel-

xxj 1.9k Jun 29, 2022
Constant Database native golang implementation

CDB golang implementation cdb is a fast, reliable, simple package for creating and reading constant databases see docs for more details Advantages Ite

Alexander Petrov 31 Dec 20, 2021
Golang restAPI crud project with mySql database.

Golang RestAPI using gorilla/mux Golang restAPI crud project with mySql database. Test Api with Thunder Client vs code beautiful Extension. and use Be

Md Abu. Raihan 6 Mar 26, 2022
A simple Golang-based application that queries a PostgreSQL database

Qwik-E-Mart Demo App A simple Golang-based application that queries a PostgreSQL database named qwikemart to read and return customer data stored in t

Joe Garcia 0 Nov 6, 2021
Implementasi database oracle kedalam golang

Go with Oracle database Implementasi database oracle kedalam golang How to using swagger Install generator swagger menggunakan perintah : go get -u gi

Muhammad Rais Adlani 0 Nov 20, 2021
Golang database driver for SQLite

go-sqlite Golang database driver for SQLite. Does not use cgo. This driver is based on pure-Go SQLite implementation (https://gitlab.com/cznic/sqlite)

glebarez 43 Jun 23, 2022
BigQuery database/sql golang driver

BigQuery SQL Driver This library is compatible with Go 1.17+ Please refer to CHA

Viant, Inc 5 Jun 7, 2022
CloudQuery extracts, transforms, and loads your cloud assets into normalized PostgreSQL tables.

The open-source cloud asset inventory backed by SQL. CloudQuery extracts, transforms, and loads your cloud assets into normalized PostgreSQL tables. C

CloudQuery 2.3k Jun 21, 2022
Sync MySQL data into elasticsearch

go-mysql-elasticsearch is a service syncing your MySQL data into Elasticsearch automatically. It uses mysqldump to fetch the origin data at first, the

siddontang 3.8k Jun 25, 2022
An experimental toolkit for injecting alternate authentication strategies into a PostgreSQL-compatible wire format.

PG Auth Proxy This is an experimental toolkit for injecting alternate authentication strategies into a PostgreSQL-compatible wire format. This is a pr

Cockroach Labs 1 Jan 20, 2022