DSL for avoiding plpgsql?

[JAForbes]

I'm normally very anti ORM, but its really easy to create a security hole in plpgsql if you forget the write function config, and leads to a lot of boilerplate. It might be nice to have a DSL that handles everything except the body. So you get a nice autocompleting API for things like security definer/invoker, search_path, language, function parameters with defaults, all the various permutations of return values.

Things like enforcing naming conventions to avoid name shadowing with the query and the arg name (I prefix with _, others do their own pattern, but you need some kind of pattern or its a huge footgun.

And to do the same for triggers, and boilerplate like, granting execute on the function as soon as you create it, we could have a .grantExecuteTo(user)

I'm not sure, but logging the idea anyway e.g.

func('is_assigned_task')
.arg('_task_id', 'uuid') // warn if no prefix? throw if no type param passed
.returns('boolean')
.securityDefiner()
.searchPath('')
.as( sql => 
  sql`
    select * 
    from tasks 
    where task_id = _task_id
    and user_id = current_setting('app.user_id',true)::uuid
  `
)

You could have things like returnsTable:

func('assigned_permissions')
.returnsTable({
  permission_id: 'uuid',
  permission_name: 'text'
})

I'll dogfood a bit and see if it feels warranted in practice