You can try https://github.com/xwb1989/sqlparser which is a MySQL parser and writer which is extracted from YouTube's vitessio/vitess <https://github.com/vitessio/vitess>. We have it forked to support SQL-92 standard https://github.com/CovenantSQL/sqlparser Also, we have some examples in our simple sharding implementation for CovenantSQL Example 1: we parse an UPDATE query and do some filtering and convert it to multiple UPDATEs on shard tables: https://github.com/CovenantSQL/CovenantSQL/blob/feature/shard/shard/update.go
Example 2: we parse a SELECT query and convert it to multiple SELECTs on shard tables, INSERT the results to a temp table, SELECT from the temp table: https://github.com/CovenantSQL/CovenantSQL/blob/feature/shard/shard/select.go On Sat, Dec 22, 2018 at 9:43 PM Victor Giordano <vitucho3...@gmail.com> wrote: > Hi Nick!. > Allow to me provide you with some insights about working with a sql DBMS > as persistence layer for an app. > > - *Json.Marshal/Unmarshal functions works like a charm* with columns > of type JSON. As you state once you have a table with one column of then > you can store anything on in, so the whatever data type you use in you > golang app it will "fit" into that column, with the "help" of the magical > functions of Json.Marsha, and you will "parse" it using Unmarshal > - Pros: No need to update table schema if the type value in your > application changes. > - Cons: Not a good idea if you are planning to lookup a row for a > value stored into that json. > - *Focus on modeling first, and on technology later*. I uses to work > with Hibernate in Java. I really appreciate the efforts of the communitiy > to try to make the life easier regarding object relational mappig, but in > the end, when you need to build your aggregations (those complex objects > that are maded of more granular ones), plain SQL provess to be more staight > foward and simple for me, as Eric says somettimes the "relationships are > much more important than objects" and it that cases the ORM may not provide > an straight solution. The real thing in object relational mapping, is not > only the ORM behind, is also, and more important (to me), how you model the > problem's domain entities. I mean, modelling your data base schema is like > putting your starting building blocks for an slyscraper, from there you > create on top of it, thus if you fail to properly understand a domain's > problem in its very granular terms (the true nature of things), you > probably will have a lot of additional code regardless of the abstractions > you use to communitcate with the db. > - *If you chosse to work with plain SQL,* i may have some good proved > recipes that work for me, that glady i will share with you. > - > - *(A) *I find the operation of scanning a row regardless of the > query that they may come, to "have a common behaviour", that i encapsulated > in some convenient abstractions. See the code below > - > - > > // Scans a single row from a given query > type RowScanFunc func(rows scaner) (model.Scanable, error) > // Scans multiples rows using a scanner function > func ScanMultiples(rows *sql.Rows, rowScanFunc RowScanFunc) > (interface{}, error) { > records:= []interface{} > for rows.Next() { > record, err := rowScanFunc(rows) > if record== nil { > return nil, err > } > records= append(records, record) > } > err := rows.Err() > if err != nil { > return nil, err > } > return objects , nil > } > > So, for example, you could use like this: > - > // sql persistence layer > CREATE TABLE bank_account > ( > id serial NOT NULL, > type text NOT NULL, > balance int NOT NULL, > CONSTRAINT bank_account_pkey PRIMARY KEY (id) > ) > > // golang app > type BankAccount struct { > Id int `json:"id"` > Type int `json:"balance"` > Balance int `json:"balance"` // btw: i don't > use floats for currency values, they can not be trust. > Details BankAccountDetails `json:"bankAccountDetails"` // asumme that > bank account details is another struct declared elsewhere > } > > // Gets all the accounts that correspondt to the given list of ids > func GetAllBankAccounts(accountsIds ...int) ([]interface{}, error) { > rows, err := Dba.Query(` > SELECT c.id, c.type, a.balance, c.details > FROM accounts c > WHERE c.id = ANY($1)`, pq.Array(accountsIds)) > if err != nil { > return nil, err > } > defer rows.Close() > return ScanMultiples(rows, scanBankAccount) > } > > // Scan a single row interpreting it as a "BankAccount" object type. > func scanBankAccount() (interface{}, error) { > var ba BankAccount > var details []byte > err := rows.Scan(&ba.Id, &ba.Type, &ba.Balance, &details) > if err != nil { > return nil, err > } > err = json.Unmarshal(details, &ba.Details) // <-- the magic we talk > about! nice! > if err != nil { > logger.Errorf("error unmarshalling bank account details(='%s'), error > was: '%v'", string(details), err) > } > return &b, nil > } > - *For dealing with transactions* i do find this snippet very very > useful (taken and adapted from: > https://stackoverflow.com/a/23502629/903998) > - > > type TransactionFunc = func(*sql.Tx) (interface{}, error) > > func ExecuteTransactionFunc(db *sql.DB, txFunc TransactionFunc) (result > interface{}, err error) { > tx, err := db.Begin() > if err != nil { > return > } > defer func() { > if p := recover(); p != nil { > tx.Rollback() > panic(p) // re-throw panic after Rollback > } else if err != nil { > tx.Rollback() // err is non-nil; don't change it > } else { > err = tx.Commit() // err is nil; if Commit returns error > update err > } > }() > result, err = txFunc(tx) > return > } > > And you could employ that code in scenarios like below: > - > - > - func TransferMoney (fromBankAccountId int, toBackAccountId int, > amount int) (interface{}, error) { > anonymousTransactionFunc := func(tx *sql.Tx) (interface{}, error) { // > a.k.a. "clousure" > return doTransferMoney(tx, session, centersRoles) > } > return ExecuteTransactionFunc(Db,anonymousTransactionFunc) // assuming > Db contains an actual connection to an underlying sql DB > } > > > func doTransferMoney(tx *sql.Tx fromBankAccountId int, toBackAccountId > int, amount int) (result interface{}, err error) { > err := withdrawMoneyFrom(tx, fromBankAccountId, amount) > if err != nil { > return > } > err = putMoneyInto(tx, toBankAccount, amount) > if err != nil { > return > } > result = struct{}{} // here you build your answer accordenly to the > system well defined bussiness rules > return > } > > > Well i guess that is a LOT for a start. Forgive me if i may overwhelm a > little bit with so much large response, but you ask for it! and i just try > to share with you what i have learned and worked for me!. > Hope it really helps, and, of course, i will be eager to read any feedback > regarding good practices working with SQL. > Greetings > *V* > > P.D: PLease forgive me if the code has errors, it was not tested, only for > describing examples and improving overall comprehension. > > > El jueves, 20 de diciembre de 2018, 16:19:50 (UTC-3), er...@tibco.com > escribió: >> >> >> >> On Thursday, December 20, 2018 at 7:23:22 AM UTC-8, Nick wrote: >>> >>> Hi all, >>> >>> I'm newish to Go, and I've been really enjoying using it. I'm still >>> getting to grips with the best practices of the language. >>> >>> Up until now, I've used MarshalJSON methods on complex struct types >>> to easily save the state of data, using Marshal() from >>> encoding/json. This has worked really well, and I have started to >>> get the simple power of interfaces with how easy it was to do. >>> >>> I've got to the stage that it makes more sense to use SQL than JSON >>> for my data now, and I was thinking it would make most sense to have >>> a similar process to read and write SQL, using methods on my struct >>> data types, which can then be called by functions that take an >>> interface implementing those methods. >>> >> >> What you're describing roughly fits into the category of an >> "object-relational mapping". Go has a number of solutions for that. One >> that pops to mind is GORM. >> >> >>> >>> I'm wondering if anybody else has done anything similar (I presume >>> so!), and could point me to examples of ways people have done this. >>> Any other thoughts on the best ways to organise my code to do this >>> cleanly and reusably would be very welcome indeed - as I say, I'm >>> new enough to Go that maybe I'm still thinking about this all wrong! >>> >> >> There are two directions I've seen recommended in the Go community. >> Either use ORM, or sidestep it completely, and just implement the SQL >> directly. >> >> I've found, for the places where I need to use SQL, that relationships >> are much more important than objects. So efficient interaction with the >> database ends up working best by simply directly writing SQL code, >> querying, and then scanning the results. ORMs would get in the way, because >> they would then require multiple ORM-level interactions to answer specific >> query. There's a little bit of repetitiveness in the non-ORM approach, but >> typically by the time I'm done, not so much that I'm concerned. >> >> Unlike with Java, where introducing a new class of object requires a new >> source file, with the tailored query approach I can create a structure that >> is directly fit for purpose, and it is just a few lines of additional code >> in an existing file. This means my database layer can easily get exactly >> the columns I need, from exactly the tables I need, and map that into >> exactly the structure that I need. >> >> A package like sqlx <https://github.com/jmoiron/sqlx> might even >> simplify your work even further. >> >> Eric. >> >> >>> >>> Thanks in advance, >>> >>> Nick >>> >> -- > You received this message because you are subscribed to the Google Groups > "golang-nuts" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to golang-nuts+unsubscr...@googlegroups.com. > For more options, visit https://groups.google.com/d/optout. > -- You received this message because you are subscribed to the Google Groups "golang-nuts" group. To unsubscribe from this group and stop receiving emails from it, send an email to golang-nuts+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
