PreInsert/PostInsert

[carlisia]

I didn't find it documented anywhere that preinsert/postinsert is supported. Do you have plans to add this feature?

[xiam]

Hello @carlisia,

We currently have no plans to add preinsert/postinsert to upper-db, we believe that would fall in the app designing realm and we want upper-db to focus only on easy data storage, retrieval and mapping.

But, we're also working on a package that provides such patterns, it extends upper-db, this package is called bond, see the preinsert call for instance: https://github.com/upper/bond/blob/7b9b12ad858a12231a6322751e1f40c16c4ec40a/bond.go#L25 and a small example from the test file https://github.com/upper/bond/blob/7b9b12ad858a12231a6322751e1f40c16c4ec40a/bond_test.go#L53

We've been using bond internally for a while but we haven't started working on docs yet. We're glad you asked, that encourages us to continue working on bond and on its documentation!

[carlisia]

@xiam I'm liking the approach you are taking to upper-db more and more. I'm gonna rip out gorp and use upper instead.

Any pointer for how to use pre/post insert with bond?

[xiam]

Thanks for giving this project a try @carlisia! I'm going to work on basic docs for bond during the weekend. I just opened an issue there, it would be great if you could help us review this documentation. I'll ping you soon.

[carlisia]

@xiam I've made progress using upper db. I'm at a point where I could use some helpers for things like fetching relations. Is this something that bond would also provide? I'm starting to look through that code to try and figure it out.

[xiam]

Hey @carlisia!

I haven't been able to start this task as I wanted... been pretty full lately, I'm sorry.

I think that going thru the bond's test file (https://github.com/upper/bond/blob/master/bond_test.go) could be useful in the meantime.

Basically we create a struct that represents the objects we have on a database:

type Account struct {
    ID        int64     `db:"id,omitempty,pk"`
    Name      string    `db:"name"`
    Disabled  bool      `db:"disabled"`
    CreatedAt time.Time `db:"created_at"`
}

This struct can have the hooks you were referring to:

func (a *Account) CollectionName() string {
    return DB.Account.Name() // We'll define this `DB` variable at the end.
}

func (a Account) AfterCreate(sess bond.Session) error {
    message := fmt.Sprintf("Account %q was created.", a.Name)
    return sess.Save(&Log{Message: message}) // This Log is another struct that was omitted here but it's on the example.
}

All the hooks are defined here: https://github.com/upper/bond/blob/master/bond.go

Next step is creating a store, which is like a helper for the above struct, and with this struct you can describe custom operations, like the FindOne method below which returns an Account:

type AccountStore struct {
    bond.Store
}

func (s AccountStore) FindOne(cond db.Cond) (*Account, error) {
    var a *Account
    err := s.Find(cond).One(&a)
    return a, err
}

This Store can be bound to a struct like Account, or to a relation, etc. We currently don't provide the usual ORM automatic relations, like BelongsTo, HasOne, etc. We expect the user to define them manually, this sometimes requires writing SQL, but upper-db already provides tools for that, like: https://upper.io/db.v2/lib/sqlbuilder

Since AccountStore has a bond.Store inside, we can use any of the bond.Store or db.Collection methods. See https://github.com/upper/bond/blob/master/store.go#L9.

Finally, we use a special pattern to connect everything together when the program starts, this looks like:

type AppData struct {
  bond.Session

  Account AccountStore `collection:"accounts"`
  Other OtherStore `collection:"other"` // ...

}
connSettings = postgresql.ConnectionURL{
 // ...
}

// You don't need to call this DB, but it's a good idea that this is exported.
DB = &AppData{}

sess, err := postgresql.Open(connSettings)
if err != nil {
    panic(err)
}

DB.Session = bond.New(sess) // Initializes the actual database backend.
DB.Account = AccountStore{Store: DB.Store("accounts")} // Initializes the store.

Using this DB variable (that can be defined as a exported value and be used by any package that imports it), you will be able to access all AccountStore methods from outside.

import "/path/to/appdata"

...
account, err := appdata.Account.FindOne(...)

The DB.Session above also allows you to access the upper-db's SQLbuilder, so you can do things like DB.Session.Query() whenever you need to.

This is basically what we do in bond, I'm sure this could be explained better with proper docs... but we'll definitely need more time to get that right.

Please let me know if you tried this out! it would be pretty helpful for us to know if you had any problems.

[xiam]

Hah, looks like we were here at similar times!

I'm at a point where I could use some helpers for things like fetching relations. Is this something that bond would also provide? I'm starting to look through that code to try and figure it out.

Bond doesn't provide automatic relations, the problem is that most of the time doing relations right requires some SQL tricks so we prefer to do them using the SQL builder, see https://upper.io/db.v2/postgresql#sql-builder and https://upper.io/db.v2/lib/sqlbuilder#select-statement-and-joins

I think that if you already started using upper-db you can skip bond for now, bond is a pattern we're using but we still need more work to polish and publish it.

[xiam]

Another example (that we're clearly missing):

type Profile struct {
  ID int `db:"id"`
  Name string `db:"name"`
}

// A relation between accounts and profiles.
iter := sess.Select("a.id", "p.name").From("accounts AS a").
  Join("profiles AS p").
  On("p.account_id = a.id").Iterator()

var profiles []Profile
if err := iter.All(&profiles); err != nil {
  // ...
}

[carlisia]

Ok awesome. I appreciate the explanation. I wanted to explore what else there was. This is all I need to get this part going. Thank you so much.

[carlisia]

Ok, I wonder what I'm doing wrong. I will ultimately need to use a select with a join and conditions (similar to your example above.) But to get started, I'm trying to run the simplest select and am getting an error. Here's the code equivalent to what I'm trying to do:

    var profile Profile
    res := sess.Select("p.id").From("profile as p").Where("p.id = 1")

    if err := res.One(&profile); err != nil {
        log.WithError(err).Error("Failed to fetch a profile")
    }

And here's the error I'm getting:

ERRO[0004] Failed to fetch a profile                      error=Argument must be a slice address.

Is it complaining about the &profile argument? That is not supposed to be a slice. If not that, then what?

[xiam]

No you're right! that should have worked, I've just pushed a fix and a test case so it doesn't happen again. Thanks for catching that!

Please see the demo here:
https://demo.upper.io/p/7d9604d4fa2018298c919d99b07662b9ab7de6e9

We have two ways of doing that, one uses sess.Collection and Find and the other uses the SQLBuilder. You can use the one that you feel is more suitable for your case, I usually go with sess.Collection unless doing a complex query, the good thing about using Collection is that you can use a variable to point to a table and use it later in different queries:

// Pointing to the accounts table
accounts := sess.Collection("accounts")

// Simple search on the accounts table
accounts.Find(....)

// Deleting a row
err := accounts.Find(...).Delete()

// Updating some rows
err := accounts.Find(...).Update(...)

// Inserting a row
id, err := accounts.Insert(item)

[carlisia]

Question: I like the idea of using Collections whenever possible too. Is there a way to use Collections as an alternative way to do this:

// A relation between accounts and profiles.
iter := sess.Select("a.id", "p.name").From("accounts AS a").
  Join("profiles AS p").
  On("p.account_id = a.id").Iterator()

I'm not finding anything that looks equivalent to a Join that could be used on a Collection.

[xiam]

No, currently a collection can be bound only to one table.

We used to have something like that in v1 but that was removed in v2 because it started to become too inflexible, it was something like this:

relation := sess.Collection("accounts a", "profiles p")

That syntax worked for simple queries but it became too awkward when trying to do special LEFT, RIGHT or OUTER joins, so we decided to drop that option in v2.

If you can come up with a nice syntax for relations between collections let's discuss it, it's still an open problem, we haven't found the right answer yet.