Epic: Migrate Policy SQL database queries to sqlc

[ryanulit]

Problem Statement

Most of our SQL queries are dynamically generated using squirrel to build the queries at runtime. The sqlc library allows you write pure SQL and have Golang code generated for you, and we've had good experiences using this library in COP and elsewhere.

Depends On

Object Queries

• [ ] #1421
• [ ] #1426
• [ ] #1434
• [ ] #1435
• [ ] #1436
• [ ] #1437
• [ ] #1438

Testing Infrastructure

• [ ] #1411

Sqlc Improvements

• [ ] #1378
• [ ] #1413

Background

All of policy in the opentdf/platform was built with a tool called squirrel to write PostgreSQL queries programmatically in Go. A new tool called sqlc to write queries directly in SQL and generate the Go code was identified, tested in COP and proven to be useful, and advocated for by larger companies like ngrok at Gophercon.

Rationale

• squirrel got us to the initial release of platform v2 in the time needed
• Squirrel was familiar to our team and not a new technology under a tight time constraint
• Sqlc was proven out in COP and has already been utilized in policy for kas-registry and the GetAttributesByValueFqns service to service (KAS/authz) API, and resource-mapping-groups
• The authoring of SQL queries directly when utilizing sqlc means it’s better to read and better to write
• Due to directly writing SQL, we can share specific queries with concerned and poky customers
• Due to directly writing SQL, we can write really performant queries without the limitations we have with squirrel (no support for CTEs or subqueries leads to not using them or challenging spaghetti code)