How to best implement read-only entities? (aka: SQL Views)

[schnz]

I am using ent for the first time and I really like the workflow and its privacy feature and gqlgen extension.

I have a use-case where I want to display the records of one entity in a table, joined together with data that resides in a relational entity. The fact that this relational data resides partly in a JSON column doesn't make things easier (if not impossible at the time). Also, when it comes to (server-side) sorting, there is just no way that I found, that I could possibly use, to sort records according to an attribute of the child relation (using the gqlgen extension and GraphQL server, I forgot to say)

So what I tried instead, was creating a VIEW in my DB schema and created an ent entity with matching fields and relations. And no surprise here: It works exactly as intended and makes sorting, filtering and pagination a breeze. However, there are two things that bother me here:

1. The migration engine does not work well together with views and there is no simple way to simply skip a migration for an entity. The best way I found to mitigate this with the least amount of effort, is to manually create the view and then override the Tables var in ent/migrate/schema.go. I also found no way to solve this via (custom/native) annotations (such as SkipTableMigration() or something along those lines) I wonder: Does anyone have a better solution for this? In particular, I would really love to be able to natively handle views inside a migration engine somehow. Does such a migration lib exists?
2. Less bothering, but still: Since ent assumes a native db table, it generates all the code required to apply write operations (such as create/update/delete). And while it doesn't hurt, I would really like to turn that off somehow. Does anyone have an idea on how to deal with that? Again: I would love to have an annotation for this, such as ReadOnly() or something similar.

Thanks in advance

[a8m]

Hey @Coksnuss,
Do you use automatic migrations (i.e., Schema.Create()) or versioned migration (Schema.Diff)? For the second question, currently, there's no simple way to remove CUD builders in codegen, but it will be supported at some point. For now, you can define a hook or a policy that rejects all mutations.

// Using hooks:
func (T) Hooks() []ent.Hook {
    return []ent.Hook{
        hook.Reject(ent.OpCreate|ent.OpUpdate|ent.OpenUpdateOne||ent.OpDelete|ent.OpDeleteOne)
    }
}

// Using privacy:
func (T) Policy() ent.Policy {
    return privacy.Policy{
        Mutation: privacy.MutationPolicy{
            privacy.AlwaysDenyRule(),
        },
        Query: privacy.QueryPolicy{
            // Allow any viewer to read anything.
            privacy.AlwaysAllowRule(),
        },
    }
}

[schnz]

Hey @a8m ,

Thanks for the quick response and your suggestion with hooks & policies. I guess I won't need them for now, since I am the only developer on the project and I am aware of the fact that some entities are backed by a db view.

Regarding migrations: I primarily rely on automatic migrations at the time. But I also already tried using versioned migrations with Altas. I am ready to use whatever tooling allows me to deal with the issue. When I tried Atlas, I couldn't get it to work either, because it also doesn't support views. If you have any advice, I am happy to hear it. Thanks!

[a8m]

Both Atlas and Ent do not support the creation of views at this stage. However, you can create it manually as you did, but just tell Ent/Atlas to skip them in migration (i.e., not create them). Using DiffHooks you can write code that removes a specific AddTable change from the set. For example:

schema.WithDiffHook(func(next schema.Differ) schema.Differ {
	return schema.DiffFunc(func(current, desired *atlas.Schema) ([]atlas.Change, error) {
		changes, err := next.Diff(current, desired)
		if err != nil {
			return nil, err
		}
		if idx := atlas.Changes(changes).IndexAddTable("<name>"); idx != -1 {
			changes = append(changes[:idx], changes[idx+1:]...)
		}
		return changes, nil
	})
}),

[schnz]

Thanks @a8m , I marked this as answer (realizing that I failed to reply properly to your first post). I am now using versioned migrations with Atlas, using the diff hook you suggested. This also allows me to provide a custom CREATE VIEW sql statement in the migration files. Until a native solution arrives, this works for me 👍

[godcong]

i used context for check create or read only permissions...

type Mutator struct {
	parent ent.Mutator
}

var MutatorFunc = func(mutator ent.Mutator) ent.Mutator {
	return &Mutator{
		parent: mutator,
	}
}

func UpdateContext(ctx context.Context) context.Context {
	return context.WithValue(ctx, "privacy", "update")
}

func (m *Mutator) Mutate(ctx context.Context, mutation ent.Mutation) (ent.Value, error) {
	value := ctx.Value("privacy")
	if value == nil || value == "query" {
		return nil, merr.New("mutate: privacy(query) not supported update")
	}
	return m.parent.Mutate(ctx, mutation)
}

in client:

client.Use(MutatorFunc)

[a8m]

@godcong, just verify you're aware of DecisionContext 😃