Multi-column foreign key?

[elliotcourant]

@vmihailenco what are your thoughts on a multi column foreign key?

I'm using go-pg for a multi tenant application so everything is separated by an account_id column.

I'd like to create certain tables that ensure the same account_id for the items. For example:

type Account struct {
	tableName string `sql:"accounts"`

	AccountID uint64 `json:"accountId" sql:"account_id,pk,type:bigint"`
	Name      string `json:"name" sql:"name"`
}

type Product struct {
	tableName string `sql:"products"`

	ProductID uint64   `json:"productId" sql:"product_id,pk,type:bigint"`
	AccountID uint64   `json:"accountId" sql:"account_id,notnull,on_delete:CASCADE"`
	Account   *Account `json:"account"`
	Title     string   `json:"title" sql:"title"`
}

type Variation struct {
	tableName string `sql:"variations"`

	VariationID uint64   `json:"variationId" sql:"variation_id,pk,type:bigint"`
	AccountID   uint64   `json:"accountId" sql:"account_id,notnull,on_delete:CASCADE"`
	Account     *Account `json:"-" `
	ProductID   uint64   `json:"productId" sql:"product_id,notnull,on_delete:CASCADE"`
	Product     *Product `json:"-"`
	SKU         string   `json:"sku" sql:"sku"`
}

These models will generate the following queries:

CREATE TABLE IF NOT EXISTS accounts
(
    "account_id" bigserial,
    "name"       text,
    PRIMARY KEY ("account_id")
);

CREATE TABLE IF NOT EXISTS products
(
    "product_id" bigserial,
    "account_id" bigint NOT NULL,
    "title"      text,
    PRIMARY KEY ("product_id"),
    FOREIGN KEY ("account_id") REFERENCES accounts ("account_id") ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS variations
(
    "variation_id" bigserial,
    "account_id"   bigint NOT NULL,
    "product_id"   bigint NOT NULL,
    "sku"          text,
    PRIMARY KEY ("variation_id"),
    FOREIGN KEY ("account_id") REFERENCES accounts ("account_id") ON DELETE CASCADE,
    FOREIGN KEY ("product_id") REFERENCES products ("product_id") ON DELETE CASCADE
);

I'd like to make it so that when a variation is inserted it guarantees that the product is for the same account. Would it make sense to add support for a foreign tag? Something like this?

type Variation struct {
	tableName string `sql:"variations"`

	VariationID uint64   `json:"variationId" sql:"variation_id,pk,type:bigint"`
	AccountID   uint64   `json:"accountId" sql:"account_id,notnull,on_delete:CASCADE,foreign:account_product_fk"`
	Account     *Account `json:"-" `
	ProductID   uint64   `json:"productId" sql:"product_id,notnull,on_delete:CASCADE,foreign:account_product_fk"`
	Product     *Product `json:"-"`
	SKU         string   `json:"sku" sql:"sku"`
}

And then the create query would be something like:

CREATE TABLE IF NOT EXISTS variations
(
    "variation_id" bigserial,
    "account_id"   bigint NOT NULL,
    "product_id"   bigint NOT NULL,
    "sku"          text,
    PRIMARY KEY ("variation_id"),
    FOREIGN KEY ("account_id") REFERENCES accounts ("account_id") ON DELETE CASCADE,
    FOREIGN KEY ("product_id", "account_id") REFERENCES products ("product_id", "account_id") ON DELETE CASCADE
);

This would be ideal since it will validate that any record will always need to relate to another record within the same account. This also wouldn't need to affect relations for querying at all, this would only enforce the constraint for writes.

What are your thoughts?

[elliotcourant]

Or if there is a way to do this now that I'm missing that would also be fantastic. I would like to use go-pg to create tables in the database since we have a ton of different models and managing the SQL for it would be a pain and go-pg makes it incredibly easy to do version control and migrations.

[vmihailenco]

This

package main

import (
	"github.com/go-pg/pg/v9"
	"github.com/go-pg/pg/v9/orm"
)

type Account struct {
	tableName string `sql:"accounts"`

	AccountID uint64 `json:"accountId" sql:"account_id,pk,type:bigint"`
	Name      string `json:"name" sql:"name"`
}

type Product struct {
	tableName string `sql:"products"`

	ProductID uint64   `json:"productId" sql:"product_id,pk,type:bigint"`
	AccountID uint64   `json:"accountId" sql:"account_id,pk,on_delete:CASCADE"`
	Account   *Account `json:"account"`
	Title     string   `json:"title" sql:"title"`
}

type Variation struct {
	tableName string `sql:"variations"`

	VariationID uint64   `json:"variationId" sql:"variation_id,pk,type:bigint"`
	AccountID   uint64   `json:"accountId" sql:"account_id,notnull,on_delete:CASCADE"`
	Account     *Account `json:"-" `
	ProductID   uint64   `json:"productId" sql:"product_id,notnull,on_delete:CASCADE"`
	Product     *Product `json:"-"`
	SKU         string   `json:"sku" sql:"sku"`
}

func main() {
	db := pg.Connect(&pg.Options{
		User: "postgres",
	})

	err := db.CreateTable(&Variation{}, &orm.CreateTableOptions{
		FKConstraints: true,
	})
	if err != nil {
		panic(err)
	}
}

generates

CREATE TABLE variations ("variation_id" bigserial, "account_id" bigint NOT NULL, "product_id" bigint NOT NULL, "sku" text,
PRIMARY KEY ("variation_id"),
FOREIGN KEY ("account_id") REFERENCES accounts ("account_id") ON DELETE CASCADE,
FOREIGN KEY ("product_id", "account_id") REFERENCES products ("product_id", "account_id") ON DELETE CASCADE)

Is that what you want?

[vmihailenco]

Closing as stale.

[frederikhors]

@elliotcourant can you answer @vmihailenco if this is what you were looking for?

Also I'm interested to the question (see: #1179).

Can you help me to achieve multi-tenancy table-column based?

[elliotcourant]

@frederikhors It did end up answering my question.

Also I am using go-pg for a multi-tenant database where every record has an accountId. The way I approached it was wrapping the entire ORM in another interface that has all the same methods but is created by calling a SafeQuery or UnsafeQuery method. The safe query method would guarantee that the records in the query (insert, update, deletes or selects) would include or restrict to the accountId passed to the safe query method. The unsafe query method would allow for querying anything within the database and is used sparingly in my application.

I would highly recommend trying to solve any problem with the existing go-pg feature-set before trying to go and make modifications, and try to use modifications to the actual library as a last resort.

Go allows you to easily wrap methods around others to act as "middleware" to achieve the functionality you want, I'd recommend that approach first when trying to achieve good multi-tenancy.

Hope this helps, but feel free to email me directly at: me@elliotcourant.dev if you have any more questions our would like more details.

[frederikhors]

@elliotcourant thanks a lot for your answer.

It would be amazing to see an example of SafeQuery here or in #1179.

I can write a Wiki page after and help this amazing project!

Thanks again! :)

[frederikhors]

@elliotcourant it would be amazing to see an example of you interface for SafeQuery and UnsafeQuery.

This will also be useful for #1179.

Please... 🥰🥰🥰

[elliotcourant]

@frederikhors sorry for the late reply. I completely lost track of this. Below is a very general description of how I've implemented it.

The way I have it setup is something like this. I have all my models inherit another struct. So I'll have a Product model like this:

type Product struct {
	tableName string `pg:"products"`

	ProductID   uint64 `json:"productId" pg:"product_id,pk,type:'bigserial'"`
	Title       string `json:"title" pg:"title"`
	Description string `json:"description" pg:"description"`
	Accounted
}

And the Accounted will be inherited from this model.

type Accounted struct {
	AccountID uint64   `json:"-" pg:"account_id,pk,notnull,on_delete:RESTRICT,type:'bigint'" field:"accountId"`
	Account   *Account `json:"-"`
}

func (accounted *Accounted) GetAccountId() uint64 {
	return accounted.AccountID
}

func (accounted *Accounted) SetAccountId(id uint64) {
	accounted.AccountID = id
}

This essentially adds the accountId and account model/relation onto every model I need. It also adds the two methods GetAccountId() and SetAccountId()

Then I build a basic method that would take my database object for pg and would wrap it.

type Accountable interface {
	SetAccountId(uint64)
}

func NewSafeQuery(ctx context.Context, model interface{}) DBQuery {
	query := internalDBQuery(ctx, model)
	return query.EnforceAccountID()
}

func (q *baseQuery) EnforceAccountID() *baseQuery {
	if a, ok := q.model.(Accountable); ok {
		q.query = q.query.Where(fmt.Sprintf(`%s."account_id" = ?`, getTableName(q.query)), q.CurrentAccountId())
		a.SetAccountId(q.CurrentAccountId())
	} else {
		panic("query is not safe")
	}
	return q
}

This is just a small snippet of it but basically the internalDBQuery method would take the context and the model and build a wrapper around pg's query object. It would basically have all the same methods that pg does and most of the time it would just call them directly. But if we make sure that we are only building out queries here we can add wrappers around certain queries that get sent to the database.

For example. We can pretty much add this WHERE account_id = ? to every query. If the query is actually an insert then pg doesn't do anything with the where clause. But if its an update, delete or select we are now guaranteeing that our results will be limited to records for the provided model that have the accountId for the current session.

But we are also calling SetAccountId on the model. This means that if the query does happen to be an insert, we are modifying the model before the query is executed to make sure that pg sends a proper accountId.

This is a pretty old version of what I use (as you can see by the fmt.Sprintf in the where clause). But the same idea could easily be adapted to almost any workflow as long as when you're executing the query you have some way of determining the user's current account Id; then you can basically build a middleware that always inserts it into your queries.

[frederikhors]

This will be the "slowest" issue in Github history, I think. LOL! 🤣

To get a "comfortable" multi-tenancy-column-based I tried everything. Hooks too (they work - except for selects - but you write too much code and it's no longer comfortable).

I tried as you described too.

I couldn't quite figure out how to do it 100%. Specially:

1. a wrapper around pg's query object. It would basically have all the same methods that pg does and most of the time it would just call them directly.

   Is there a "safe" and fast way to do this?

2. What to do when I have slices? Example:

   var players []Player
   db.Model(&players)...

   In this case the interface in this code: func NewSafeQuery(ctx context.Context, model interface{}) DBQuery {} no longer works, right?

   Especially for bulk methods.

@elliotcourant thanks for your commitment. Please, don't be scared of all these questions! 🤣

---

@vmihailenco, one way built into the ORM to solve this problem would be the fulfilled wish of every man on this earth. 😄

But at least, can we have something like:

1.   db.AccountableModel()...

   which of course is something custom I can create?

2. Can we use something like Gorm's callbacks: https://gorm.io/docs/write_plugins.html#Callbacks?

[elliotcourant]

To keep with the pattern pretend that this response is many weeks later.

I'll see if I can throw together a more complete code example this weekend and post it here.

With slices i created another custom type for the interface.
So if I had:

type User struct {
  Id uint64
  AccountId uint64
  Email string
}

func (u User) Accountable() uint64 {
  return u.AccountId
}

Then I would also do:

type Users []*User

func (u Users) Accountable() uint64 {
  var accountId uint64
  for _, user := range u {
    if accountId == 0 {
      accountId = user.Accountable()
      continue
    }
    if user.Accountable() != accountId {
      panic("trying to update multiple accounts in a single array")
    }
  }

  return accountId
}

This would basically return the accountId for the entire array, this implementation has the limit of enforcing only a single accountId for a slice. But that could easily be changed. But I literally implemented something like this for all of my models. Eventually it became easier to just auto generate them. So I built something that just adds these methods using a go template file.

[frederikhors]

It's wonderful.

I'll wait for your example because this can be a recipe in docs.

Amazing, really.

Thanks a lot! ❤️❤️❤️

[elliotcourant]

https://github.com/elliotcourant/go-pg-multitenant

This is the start of an example, it currently works but has no additional features or much documentation. I'll try to add more to it but if you wanted to mess around then this is a good starting point.

[frederikhors]

@elliotcourant , your example is great! 😄

I will write you some of my observations in the issues there.