About

Needle takes one configuration file of SQL schema(s) and SQL DML (queries and mutations) as input, and generate an efficient Go file that is (can)

• strongly-typed
• human-readable
• provides the ability to use memory and Redis to cache results.
• Invalidate query results upon mutation.

Internally, needle is powered by TiDB’s SQL parser.

Install

# version tag might not be up-to-date, please change before execution.
curl -sSfL https://raw.githubusercontent.com/Stumble/needle/master/install.sh | sh -s -- -b $(go env GOPATH)/bin v0.4.3

Clients

Since v0.4.1, clients have been moved out to https://github.com/Stumble/needle-clients.

Usage

TODO: usage has not been updated for a long time. See example directory for demo.

Introduction

Imagine, your PM told you to add a feature to store the preferred language of a user. Here are the three steps:

1. generate a needle configuration of needle by:

# stumble @ yxia in /tmp/example/langrepo [16:51:00] 
$ needle -t Language -o lang.xml

1. edit this xml file accroding to your needs, e.g.

<needle>
  <schema name="Languages" hiddenFields="" mainObj="Language">
    <sql>
      CREATE TABLE Languages (
        LanguageID      int NOT NULL
      )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    </sql>
    <!-- add references here if query or mutation joined other tables. -->
    <!-- <ref src="foo/bar.xml"></ref> -->
  </schema>
  <stmts>
    <query name="GetLanguages" type="many" cacheDuration="5m">
      <sql>
        SELECT * FROM Languages;
      </sql>
    </query>
    <query name="GetLanguageByID" type="single" cacheDuration="5m">
      <sql>
        SELECT * FROM Languages WHERE LanguageID = ?;
      </sql>
    </query>
    <mutation name="InsertLanguage" invalidate="GetLanguageByID,GetLanguages">
      <sql>
        INSERT INTO Languages
        (LanguageID)
        VALUES(?);
      </sql>
    </mutation>
  </stmts>
</needle>

1. generate idiomatic go codes by run `needle -f lang.xml -o lang.go`. It will give you a go file with some key functions:

package languagesrepo
...
// Languages - main interface.
type Languages interface {
	GetUserLang(ctx context.Context, args *GetUserLangArgs, options ...Option) (*Language, error)
	InsertLanguage(ctx context.Context, args *Language, options ...Option) (sql.Result, error)
	Check(ctx context.Context) error
}
....
// NewLanguages - nil cache indicates nocache.
func NewLanguages(c Cache, exec DBExecuter) Languages {
	return &languages{cache: c, exec: exec}
}
....

That’s it. Now this repo will take care of the data access layer for you.

Note that all attributes are case-sensitive.

WARNINGs

1. When no records found, Returns `nil` error and `nil` object.

Schema

• name: prefix of repository, generated file will be `name`+repo, lowercased.
• mainObj: name of a generated struct that contains all fileds in this table except for hiddenFields.
• hiddenFields: a list of fields that will not be included in mainObj, separated by `,`.

Query

• name: name of query function.
• type: [single|many] query result of only one record or many.
• cacheDuration: golang style time duration string(see https://golang.org/pkg/time/#ParseDuration), e.g. 5s, 10m. use `forever` to cache forever. If absent, cache is not enabled for this query.

Mutation

• name: name of the mutation function.
• invalidate: a list of query names that needs to be invalidated on success of this mutation., `,` separated, e.g. “GetLanguageByID,GetLanguages”.

Spec

Support mysql SQL statements with several minor changes.

Wildcard in select

we define that wildcard symbol in select statement will be converted into main table struct. For example,

select * from users;

will become

select users.username, users.userid from users;

A main table struct is all fields of the table schema of the configuration, except for hidden_fields. Note that the above case is the same as SQL standard. However, when you join tables, it is different. In SQL standard, * will include all tables, while in Needle spec, only the main table fields. For example, for a query defined in users.xml

select * from users inner join orders on users.id = orders.id;

will become

select users.username, users.userid from users inner join orders on
users.id = orders.id;

but NOT

select users.username, users.userid, order.orderid from users inner
join orders on users.id = orders.id;

SQL InPattern

For list match(e.g. where username in (“alice”, “bob”)), you can use simply put it as

select * from users where username in (?);

needle will identify this case and generate an array as parameter of this query(CURD). while in the runtime, (?) will be expanded to the number of parameters, with NO MAXIMUM. It’s caller’s duty to ensure that he does not construct a crazy large list that will crash DB.

The array argument passed in CANNOT be nil or an empty list.

Limitations

Function result in select must be renamed by as.

Output

1. a single file named by main schema’s name filed, with `repo` suffix.
2. one interface named by `package+Repo` that provides functions generated by queries.
3. an implementation of the interface, with a constructor a cache client as input.
4. CURDs require a sql executor as parameter.

Insert

Insert queries will have a default

Roadmap

v0.1.0

tasks

• front-end
  • checker: lint name cannot be duplicated.
• cache policy (xxx)
• disallowing * when doing join.

done jobs

• input schema v1.
• SQL parsing.
• Table info parse.
• Name resolve.
• XML input of schema and queries.
• Schema parsed to table.
• Query parsed to Query.
• logging and error visitor infra.
• type assignment.
• type inference.
• add more schema properties
  • type parsing: “single”/”many”
  • cache duration, duration golang string.
  • hidden fields.
  • mutation: invalidate=”[queryNames]”
• midend
  • paramMarker ordering.
  • * resolves to a list of column refs, except for hidden fields.
• struct gen
  • one general struct for each table, all column except for hidden fields.
  • one input and one output struct for each mutation and query.
• code gen
  • query code
  • support variable lenth pattern in(no maximum length limit).
• code gen
  • mutation code + invalidate cache

v0.x

1. test visitors.

NOT v0.1.x

1. sub-query type inference. // limited support since v0.3.0
2. gen mongodb backed repository.
3. schema + online DDL.

Docs

Frontend

Config Reader

Config package provide a loader from xml to NeedleConfig.

1. Unmarshal from xml file to `config.NeedleConfig` struct.
2. Check name, mainObj of main schema.
3. Recursively loading referenced tables(xml files), with maximum depth = 1.
4. For queries, check: query name validity, type in (“single”, “many”), cache duration validity.
5. Check Mutation/Query name duplication.
6. For mutations, valid mutation name, valid invalidate query name.

Parsing

Driver package maintains the main struct of all asts that latter passes is applied on. This dirver convert a needle config and convert it to Driver.Repo.

1. parse table SQL.
2. column name duplications.
3. existence of hidden fileds’ names.

Midend

NameResolve

TypeInference

Backend

MysqlCodegen

Test utils

All generated repositories will have two functions for testing.

Load

Read JSON bytes, unmarshal, and save them to database.

Dump

Dump the whole table into JSON bytes.

Unsupported

1. `BETWEEN` clause, replace it with `a >= xx AND a <= yy`
2. `Alter Table` is not supported for now.
3. Experimentally support sub-query.

Release Notes

v0.4.0

1. go version >= 1.6.0
2. update to SQL parser.