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

• Migrate Resource Mapping object queries to sqlc #1421
• Migrate Namespace object queries to sqlc #1426
• Migrate Attribute Value object queries to sqlc #1434
• Migrate Attribute Definition object queries to sqlc #1435
• Migrate Subject Mapping object queries to sqlc #1436
• Migrate Subject Condition Set object queries to sqlc #1437
• Migrate Attribute FQN indexing queries to sqlc #1438

Testing Infrastructure

• Policy: migrate integration test fixtures to sqlc models #1411

Sqlc Improvements

• Investigate usage of NULLIF instead of COALESCE with pgx/v5/pgtype to simplify UUID query logic #1378
• Policy: trace level logs should show sql queries being run #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.

Unblocks

• Policy: Creation of an Attribute with Values (one RPC call) should employ a db transaction #1500
• Enhancement: Policy GetAttributeByFqn db query should employ fewer roundtrips #1357
• Policy: ERD should be generated #1518

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)

[cassandrabailey293]

this has been moved to tracking in Jira: https://virtru.atlassian.net/browse/DSP-68

closing this duplicate, please track status of this epic in Jira.