[backend] Avoid manual use of NEXTVAL() in postgres

[alxndrsn]

At various points, database IDs are manually generated:

• https://github.com/getodk/central-backend/blob/85e25af7e7775bf4626867bd96ccff7694856c92/lib/model/query/datasets.js#L26
  • datasets: use idiomatic PostgreSQL ID generation central-backend#1612
• https://github.com/getodk/central-backend/blob/85e25af7e7775bf4626867bd96ccff7694856c92/lib/model/query/datasets.js#L50
  • datasets: use idiomatic PostgreSQL ID generation central-backend#1612
• https://github.com/getodk/central-backend/blob/85e25af7e7775bf4626867bd96ccff7694856c92/lib/model/query/entities.js#L75
• https://github.com/getodk/central-backend/blob/85e25af7e7775bf4626867bd96ccff7694856c92/lib/model/query/entities.js#L101
• https://github.com/getodk/central-backend/blob/85e25af7e7775bf4626867bd96ccff7694856c92/lib/model/query/forms.js#L114
  • forms: restructure createNew() SQL central-backend#1613
• https://github.com/getodk/central-backend/blob/85e25af7e7775bf4626867bd96ccff7694856c92/lib/model/query/submissions.js#L30
  • Submissions.createNew(): return less data central-backend#1608
• https://github.com/getodk/central-backend/blob/85e25af7e7775bf4626867bd96ccff7694856c92/lib/model/query/submissions.js#L39
  • Submissions.createNew(): return less data central-backend#1608

Sometimes this is for the convenience of using the generated value at various points in the subsequent query, in which case the query can be inverted to create dependees before dependents.

In other cases, the reason for manual ID assignment is unclear, and can be removed.

[matthew-white]

Out of curiosity, what's the reason for wanting to avoid this usage? Is it for code clarity or data integrity or some other reason?

[alxndrsn]

Out of curiosity, what's the reason for wanting to avoid this usage? Is it for code clarity or data integrity or some other reason?

As above: the reason for manual ID assignment is unclear

Expanding on this: to me it seems very odd code, and gives pause for thought every time it's encountered.

It also makes optimising/refactoring queries more complicated.