An easy-use SQL builder.

Overview

EQL

An easy-use SQL builder.

Design

We are not English native speaker, so we use Chinese to write the design documents. We plan to translate them to English. Or you can use some software to translate it.

Here is a good one: https://www.deepl.com/en/translator

设计思路 B站视频

Contribution

You must follow these rules:

  • One commit one PR
  • You must add change log to .CHANGELOG.md file
  • You must add license header to every new files

style guide

Comments
  • eorm: LIKE 查询

    eorm: LIKE 查询

    仅限中文

    使用场景

    在搜索列的时候,我们会考虑检索字符串是否符合某个特征,在这种情况下,我们可以考虑使用 LIKE 查询。 例如:

    SELECT * FROM XXX WHERE XXX LIKE '%abc'
    

    行业分析

    LIKE 查询在很多中间件里面都提供了支持,并且它们还进一步封装了:

    • HasPrefix:等效于 LIKE '%abc',用户使用起来是 HasPrefix(abc)。注意,%是框架帮助添加上去的
    • HasSuffix: 等效于 LIKE '%abc',用户使用起来是 HasSuffix(abc)。注意,%是框架帮助添加上去的

    可行方案

    提供一个 LIKE 调用,接近于 #101 #102 中的设计:

    C("name").Like("abc")
    C("name").NotLike("abc")
    

    这里 Like 和 NotLike 接收字符串类型。另外就是我们也不对 name 的类型进行校验,例如即便用户使用的是 int 类型,他要用 Like 也是可以的。

    其它

    任何你觉得有利于解决问题的补充说明

    你使用的是 eorm 哪个版本?

    你设置的的 Go 环境?

    上传 go env 的结果

    good first issue feature 
    opened by flycash 4
  • all: Field 取代 FieldByName

    all: Field 取代 FieldByName

    1. mode.go 添加Index字段
    2. mode_test.go 测试Index字段
    3. reflect.go 使用自定义field方法(本质reflect.Value.Field)替换reflect.Value.FieldByName方法
    4. unsafe_test.go 添加测试用例NullBoolPtr
    opened by longyue0521 4
  • Buffer pool

    Buffer pool

    English Only

    Please check existing issues first to avoid duplication and answer the questions below before submitting your issue.

    Use case(s)

    When we are building a query, we can use strings.Builder. But in fact, we should think about reuse the buffer to avoid GC and improve the performance.

    Usually, we will use sync.Pool to cache the buffer. But there is a problem that the buffer's capacity may be expanded when it was put back.

    For example, the original capacity of buffer is 128, and it means that we can only store 128 bytes. However, we reuse this buffer to build a long query which has more than 128 bytes, e.g. 200 bytes, and then the buffer capacity was expanded to 256 bytes.

    If most of the queries only need 50 bytes, it means that we waste a lot memory. A typical solution is avoiding cache big buffer.

    Proposed Solution

    (Please describe your proposed design/solution, if any)

    Alternatives Considered

    (Other possible options for solving the problem?)

    Additional Context

    (Paste any relevant logs - please use code blocks (```) to format console output, logs, and code as it's very hard to read otherwise.)

    (If you can, link to the line of code that might be helpful to explain the context)

    feature 
    opened by flycash 4
  • eorm: Delete 的执行

    eorm: Delete 的执行

    仅限中文

    使用场景

    很显然,到目前为止我们只支持了 SELECT 和 INSERT,在 DELETE 语句上,我们只支持了构造出来 DELETE 语句,但是没有支持发起 DELETE 查询并且返回结果。

    DELETE 语句和 INSERT 语句在执行上并没有什么差距,所以可以直接将 Inserter 的执行代码复制一份过来。

    但是你需要提供:

    • 基于 sqlmock 的单元测试
    • 集成测试
    feature 
    opened by flycash 3
  • Predicates Support

    Predicates Support

    English Only

    Please check existing issues first to avoid duplication and answer the questions below before submitting your issue.

    Use case(s)

    (Please state use cases, what problem will this feature solve?)

    Predicates are used to filter rows. Predicate can be used in WHERE clause.

    Proposed Solution

    (Please describe your proposed design/solution, if any) Here are the major APIs and you can find them in predicate.go:

    // P creates a Predicate
    // left could be string or Expr
    func P(left interface{}) Predicate {
    	panic("implement me")
    }
    
    // Not indicates "NOT"
    func Not(p Predicate) Predicate {
    	panic("implement me")
    }
    
    // And indicates "AND"
    func (p Predicate) And(pred Predicate) Predicate {
    	panic("implement me")
    }
    
    // Or indicates "OR"
    func (p Predicate) Or(pred Predicate) Predicate {
    	panic("implement me")
    }
    
    // EQ =
    func (p Predicate) EQ(val interface{}) Predicate {
    	panic("implement")
    }
    
    // LT <
    func (p Predicate) LT(val interface{}) Predicate {
    	panic("implement me")
    }
    
    // GT >
    func (p Predicate) GT(val interface{}) Predicate {
    	panic("implement me")
    }
    

    But the most important things is to convert the predicates to string. For example:

    P("id").LT(10)// `id` < 10
    

    Alternatives Considered

    (Other possible options for solving the problem?)

    Additional Context

    It relies on #10

    (Paste any relevant logs - please use code blocks (```) to format console output, logs, and code as it's very hard to read otherwise.)

    (If you can, link to the line of code that might be helpful to explain the context)

    feature 
    opened by flycash 3
  • eorm: Distinct 关键字支持

    eorm: Distinct 关键字支持

    仅限中文

    使用场景

    使用 Distinct 关键字主要有两个地方:

    • SELECT DISTINCT xxx, xx FROM
    • SELECT COUNT(DISTINCT) :这种形态可以称之为在聚合函数中使用 Distinct 关键字

    第一种形态最为常见,而第二种形态则不那么常见,也是我们设计和实现的一个难点。

    行业分析

    实际上,在 Distinct 支持上有几种风格:

    • 直接 db.Distinct(xxx) 这种,即 xxx 代表的是一些列,往往都是字符串。GORM 就是这种风格
    • 只是简单拼接一个 DISTINCT 关键字,Beego ORM 中的 QueryBuilder 就是这种风格

    而对于第二种形态来说,大多数 ORM 框架都是在解决 Select xxx 列的时候一并解决的。普遍来说,他们的方案是:

    db.Select("COUNT(DISTINTCT id)")
    

    也就是用字符串来作为输入,那么在这种情况下,用户可以输入任意的东西,自然也就包含了带 Distinct 关键字的聚合函数。

    可行方案

    按照行业惯例和我们已有的设计,第一种形态我们可以直接添加一个新的 Distinct 方法:

    func (s *Selector) Distinct()  *Selector {
    
    }
    

    执行了这个调用之后生成的 Select 语句,会带上 DISTINCT 关键字:SELECT DISTINCT xxx...

    而对于第二种形态来说,我们有两种选择: 第一种, 什么也不做。因为目前用户可以直接使用我们的 RawExpr 来达成目标:`s.Select(Raw("COUNT(DISTINCT id)"))

    第二种,我们考虑帮助用户解决问题,那么也有两条路:

    
    // 我们自己内部利用 RawExpr 来封装一下。在这里 col 必须是数据库内部的列名
    func CountDistinct(col...string) RawExpr {
        return Raw("COUNT DISTINCT cols....")
    }
    
    // 这种设计形态下, col 实际上是字段名,同时我们可以对 col 进行校验,确保用户不会输错。
    func CountDistinct(col string) Aggregate {
         return Aggregate{
                  fn: "COUNT",
                  arg: col,
                  distinct: true // 这是在 Aggregate 中新加的字段。这一类的 Aggregate 才会设置为 true
         }
    }
    
    

    当我们要做的时候,就需要对 Count, Avg, Sum 都提供一个对应的方法,而 Max 和 Min 则不需要。

    其它

    你需要验证一个东西,能不能使用类似的 SELECT 语句:

    SELECT * FROM xx HAVING COUNT(DISTINCT id) > 10
    

    即在 HAVING 子句中使用这种东西。

    此外:

    • 你需要设计单元测试和集成测试
    • 如果可以使用在 HAVING 子句中,那么集成测试要包含该用例

    你使用的是 eorm 哪个版本?

    你设置的的 Go 环境?

    上传 go env 的结果

    feature 
    opened by flycash 2
  • eorm: 支持基本类型作为返回值

    eorm: 支持基本类型作为返回值

    仅限中文

    使用场景

    在有一些场景下我们会在 SELECT 里面使用聚合函数,例如:

    SELECT AVG(`age`) FROM users 
    

    在这种情况下,我们会希望 Selector 能够返回基本类型:

    avgAge := NewSelector[int].Select(xxx).From(&User{}).Where(xxx).Get(ctx)
    

    相比我们已经支持的用法,它的改动主要涉及到:

    • 表名:早期我们是可以直接从泛型的类型参数里面解析得来,现在我们只能考虑引入 From 方法,并且 From 里面不再是接收一个 string 参数,而是一个结构体参数
    • 结果集处理:在这种情况下,我们只能允许用户查询一个列,多余的列我们会返回错误。而这个单独的列,就要转化为基本类型

    除了 Get,显然还需要支持 MultiGet。

    而要支持的类型包括:

    • string
    • 基本类型
    • []byte

    行业分析

    在 Go 里面基本没有什么可以参考的东西,因为这是第一次采用泛型来解决聚合函数的问题。

    可行方案

    实际上,如果我们不考虑已有的 valuer 抽象的话,实现起来还是很容易的,我们只需要在 Get 和 MultiGet 里面进行类型判断,而后直接调用 Scan 方法,这也就是最简单的方法。

    我大概评估了一下,如果我们希望将这个逻辑下沉到 valuer.SetFields 方法里面,那么还是比较棘手的。这种做法的好处是保持住了 valuer 抽象。

    其它

    • 你可以先提供一个草稿合并请求,它不需要有测试,然后我们在这个合并请求下讨论你的设计的优缺点
    • 如果你是极客时间中实战训练营的学员,你可以直接联系我,我可以约一个会议详细解释这个需求。否则你可以考虑给我发邮件,预约一个时间,我也可以详细解释这个需求

    你使用的是 eorm 哪个版本?

    你设置的的 Go 环境?

    上传 go env 的结果

    feature 
    opened by flycash 2
  • Ignore Columns

    Ignore Columns

    English Only

    Please check existing issues first to avoid duplication and answer the questions below before submitting your issue.

    Use case(s)

    (Please state use cases, what problem will this feature solve?)

    Sometimes users define a complicate structure but some of its fields should not be inserted into databases. It indicates one thing: some fields can not be mapped to columns.

    In some small applications, users do not use entity or PO. They only have one layer named model, and they use models in their business code as well as DAO.

    So we need to allow users to declare that they want EQL to ignore some field.

    Proposed Solution

    (Please describe your proposed design/solution, if any)

    We already have a MetaRegistry named tagMetaRegistry, so we can use the syntax: eql:"-" to ignore the columns. Besides, we need to provide an option to ignore some fields when users register models.

    Alternatives Considered

    (Other possible options for solving the problem?)

    Additional Context

    (Paste any relevant logs - please use code blocks (```) to format console output, logs, and code as it's very hard to read otherwise.)

    (If you can, link to the line of code that might be helpful to explain the context)

    feature 
    opened by flycash 2
  • Precalculate offset of field and use `unsafe` to read data.

    Precalculate offset of field and use `unsafe` to read data.

    English Only

    Please check existing issues first to avoid duplication and answer the questions below before submitting your issue.

    Please take note Enhancement is a suggestion to existing features and codebase. If you're requesting for a new feature, please select Feature.

    Summary

    Now, several APIs rely on reflection package to fetch the value by field name. But I think we can use unsafe instead. Here is the point:

    • When we initiate model's metadata, we know the offset of each fields
    • When we read from data of specific field, we use this offset and rebuild the data based on its type
    • We need benchmark tests to compare unsafe and reflection

    (State a summary of your enhancement suggestion)

    What version of EQL are you using?

    What version of Go are you using?

    (Get your go version by go version)

    What is the current design/solution/implementation?

    (If you can, link to the line of code that could be improved)

    What could be made better?

    (What you think should be enhanced and how?)

    enhancement inactive-issue 
    opened by flycash 2
  • Upserter for MySQL

    Upserter for MySQL

    English Only

    Please check existing issues first to avoid duplication and answer the questions below before submitting your issue.

    Use case(s)

    Support ON DUPLICATE KEY clause for MySQL dialect.

    Although we define a function to generate upsert for PgSQL, you don't need to handle it now, let's support PgSQL in next phase.

    (Please state use cases, what problem will this feature solve?)

    Proposed Solution

    (Please describe your proposed design/solution, if any)

    Alternatives Considered

    (Other possible options for solving the problem?)

    Additional Context

    (Paste any relevant logs - please use code blocks (```) to format console output, logs, and code as it's very hard to read otherwise.)

    (If you can, link to the line of code that might be helpful to explain the context)

    feature inactive-issue 
    opened by flycash 2
  • Like clause

    Like clause

    English Only

    Please check existing issues first to avoid duplication and answer the questions below before submitting your issue.

    Use case(s)

    LIKE is simple. But I am thinking about, should we provide functions "HasSuffix, HasPrefix, Like".

    (Please state use cases, what problem will this feature solve?)

    Proposed Solution

    At least we need one function:

    func (p Predicate) Like(str string)
    

    These functions are open to discuss:

    func (p Predicate) HasPrefix(str string)
    func (p Predicate) HasSuffix(str string)
    func (p Predicate) Contains(str string)
    

    (Please describe your proposed design/solution, if any)

    Alternatives Considered

    (Other possible options for solving the problem?)

    Additional Context

    (Paste any relevant logs - please use code blocks (```) to format console output, logs, and code as it's very hard to read otherwise.)

    (If you can, link to the line of code that might be helpful to explain the context)

    feature inactive-issue 
    opened by flycash 2
  • eorm: 插入忽略主键

    eorm: 插入忽略主键

    仅限中文

    使用场景

    在插入的时候,如果用户主键使用的是基本类型,那么我们依旧会插入基本类型的零值,例如:

    type User struct {
        Id uint64
    }
    NewInserter(db).Values(&User{})... // 会插入主键为 0 的 user
    

    而实际上用户是希望忽略主键的。

    行业分析

    如果你知道有框架提供了类似功能,可以在这里描述,并且给出文档或者例子

    可行方案

    核心是要告诉 Inserter 忽略主键。从理论上来说有两种思路:

    • 在用户指定 Columns 的时候,提供一个辅助方法,叫做 NonPKColumns(entity)[]string,返回 entity 对应的模型的所有的非主键的列;
    • 一个是在 Inserter 上引入一个叫做 IgnorePk 的选项

    从我个人的使用偏好上来说,我更加喜欢第一个方案,但是可能用户更加喜欢第二个选项

    其它

    任何你觉得有利于解决问题的补充说明

    你使用的是 eorm 哪个版本?

    你设置的的 Go 环境?

    上传 go env 的结果

    feature 
    opened by flycash 0
  • eorm: 利用 AOP 接口打印 SQL

    eorm: 利用 AOP 接口打印 SQL

    仅限中文

    使用场景

    在日常开发或者 DEBUG 过程中,为了判断 ORM 框架是否执行如预期,那么会尝试将 SQL 输出。

    但是在输出 SQL 的时候,一个核心的问题是要不要输出查询参数。

    • 不输出查询参数
    • 输出查询参数
    • 输出查询参数,但是一些敏感字段会用 *** 取代

    另外一方面,考虑到不同的用户使用不同的日志组件(框架),所以我们打印 SQL 应该允许用户设置输出的目标位置。

    正常来说,我们只能在开发或者测试环境里面打印 SQL,因为数据库查询是一个高频的,如果我们在线上也开启这个功能,那么对于日志组件来说,可能不堪重负。

    而要在线上环境打印查询,那么就要考虑敏感字段的问题,例如用户的账号密码,邮箱或者手机号码等。这一类信息不能被打印出去,对于稍微不那么敏感的数据,经过 **** 掩码之后可以输出到日志里面,方便定位问题。

    行业分析

    就打印SQL而言,Beego ORM 和 GORM 都是采用的侵入式的方案,即在全局维护了一个 DEBUG 的标记位。如果设置了改标记位,那么就会打印 SQL,并且打印查询参数。总体来说,这种方案可行,但是不够优雅。

    就我个人观察来说:

    • 用户很难知道需要设置这么一个标记位,对新手来说尤其如此
    • 用户也难以指定我这个 SQL 输出到哪里。所以类似这种中间件往往都会设计一个伴随的日志接口,或者日志模块。这也是我极力想要避免的

    可行方案

    基本上,我们只需要利用 AOP 接口提供一个实现就可以。参考 #114 。

    该实现需要:

    • 允许用户指定输出的方式,默认情况下,输出到控制台
    • 允许用户指定是否输出参数,默认情况下不输出参数。用户可以自己考虑在测试环境开启输出参数。注意,这里默认不输出参数是为了避免有一些用户并不清楚数据加密的重要性,而导致无意间将线上敏感数据输出到了日志中

    其它

    • 你需要提供单元测试

    你使用的是 eorm 哪个版本?

    你设置的的 Go 环境?

    上传 go env 的结果

    feature 
    opened by flycash 0
Releases(v0.0.1)
Owner
Go微服务
Go微服务
Go微服务
a golang library for sql builder

Gendry gendry is a Go library that helps you operate database. Based on go-sql-driver/mysql, it provides a series of simple but useful tools to prepar

DiDi 1.4k Dec 26, 2022
Database Abstraction Layer (dbal) for Go. Support SQL builder and get result easily (now only support mysql)

godbal Database Abstraction Layer (dbal) for go (now only support mysql) Motivation I wanted a DBAL that No ORM、No Reflect、Concurrency Save, support S

徐佳军 56 Nov 17, 2022
SQL builder and query library for golang

__ _ ___ __ _ _ _ / _` |/ _ \ / _` | | | | | (_| | (_) | (_| | |_| | \__, |\___/ \__, |\__,_| |___/ |_| goqu is an expressive SQL bu

Doug Martin 1.8k Dec 30, 2022
SQL query builder for Go

GoSQL Query builder with some handy utility functions. Documentation For full documentation see the pkg.go.dev or GitBook. Examples // Open database a

Travis Harmon 27 Dec 12, 2022
Type safe SQL builder with code generation and automatic query result data mapping

Jet Jet is a complete solution for efficient and high performance database access, consisting of type-safe SQL builder with code generation and automa

null 940 Jan 6, 2023
Type safe SQL query builder and struct mapper for Go

sq (Structured Query) ?? ?? sq is a code-generated, type safe query builder and struct mapper for Go. ?? ?? Documentation • Reference • Examples This

null 185 Dec 19, 2022
Fast SQL query builder for Go

sqlf A fast SQL query builder for Go. sqlf statement builder provides a way to: Combine SQL statements from fragments of raw SQL and arguments that ma

Vlad Glushchuk 92 Dec 23, 2022
golang orm and sql builder

gosql gosql is a easy ORM library for Golang. Style: var userList []UserModel err := db.FetchAll(&userList, gosql.Columns("id","name"), gosql.

RushTeam 169 Dec 22, 2022
gosq is a parsing engine for a simplicity-focused, template-based SQL query builder for Go.

gosq is a parsing engine for a simplicity-focused, template-based SQL query builder for Go.

Sang-gon Lee 51 Oct 24, 2022
Write your SQL queries in raw files with all benefits of modern IDEs, use them in an easy way inside your application with all the profit of compile time constants

About qry is a general purpose library for storing your raw database queries in .sql files with all benefits of modern IDEs, instead of strings and co

Sergey Treinis 26 Dec 25, 2022
Command line tool to generate idiomatic Go code for SQL databases supporting PostgreSQL, MySQL, SQLite, Oracle, and Microsoft SQL Server

About xo xo is a command-line tool to generate Go code based on a database schema or a custom query. xo works by using database metadata and SQL intro

XO 3.3k Jan 8, 2023
Go fearless SQL. Sqlvet performs static analysis on raw SQL queries in your Go code base.

Sqlvet Sqlvet performs static analysis on raw SQL queries in your Go code base to surface potential runtime errors at build time. Feature highlights:

QP Hou 460 Dec 19, 2022
Go database query builder library for PostgreSQL

buildsqlx Go Database query builder library Installation Selects, Ordering, Limit & Offset GroupBy / Having Where, AndWhere, OrWhere clauses WhereIn /

Arthur Kushman 88 Dec 23, 2022
Simple query builder for MongoDB

?? greenleaf - simple, type safe and easy to use query builder for MongoDB Installation To install use: go get github.com/slavabobik/greenleaf Quick

Slava 75 Nov 27, 2022
PirateBuilder - Pirate Builder For Golang

PirateBuilder Builder You need to extract the file "PirateBuilder.rar". Start "P

Lowki 2 Jun 10, 2022
Nerdlight-firmwarebuilder - Nerdlight NodeMCU Firmware Builder CLI

nerdlight-firmwarebuilder ⚒ ⚡️ Nerdlight NodeMCU Firmware Builder CLI ⚒ Descript

Marco Palmisano 1 Feb 12, 2022
A Golang library for using SQL.

dotsql A Golang library for using SQL. It is not an ORM, it is not a query builder. Dotsql is a library that helps you keep sql files in one place and

Gustavo Chaín 653 Dec 27, 2022
A Go (golang) package that enhances the standard database/sql package by providing powerful data retrieval methods as well as DB-agnostic query building capabilities.

ozzo-dbx Summary Description Requirements Installation Supported Databases Getting Started Connecting to Database Executing Queries Binding Parameters

Ozzo Framework 589 Dec 31, 2022
💥 A lightweight DSL & ORM which helps you to write SQL in Go.

sqlingo is a SQL DSL (a.k.a. SQL Builder or ORM) library in Go. It generates code from the database and lets you write SQL queries in an elegant way.

Qishuai Liu 274 Jan 2, 2023