Constraints, locks, and advanced SQL

Two people join the same group at nearly the same time. You want one membership, not duplicates and not a check-then-insert race. Give the membership a unique key and let an upsert create it or update the existing row:

let membership = client
    .upsert_membership(membership::unique::user_id_group_id)
    .user_id(user.id)
    .group_id(group.id)
    .role("owner")
    .save()
    .await?;

The conflict target names the fields that identify one membership. Use the generated unique key shown here. It identifies a declared primary key or unique constraint, rather than an arbitrary set of fields. See upserts for choosing what happens on a conflict.

Keep the database responsible for integrity

Use foreign keys for relationships, unique constraints for identities, and checks for values that must always be valid. Cascades and SET NULL describe what happens to dependent records when a parent changes. Custom scalar IDs, compound unique keys, and composite join-table keys let you match the identity to your domain. The constraints tutorial shows these rules in use.

Read the amount of data you need

Use typed tuple projections for a small response, aggregates for totals, offset or keyset pagination for pages, and bounded streams for larger reads. These are ordinary generated query methods. See queries.

When a decision depends on a value remaining unchanged until you save, use a row lock, such as FOR UPDATE, inside a transaction.

Describe database-owned behavior

The migration catalog covers PostgreSQL schemas, indexes, foreign keys, checks, views, generated columns, triggers, extensions, row-level security, and its policies. Schema options shows how to declare them.

When a specialized query needs SQL directly, query_raw and execute_raw accept statements built with rent::sql!. Keep that trusted access explicit. The raw SQL reference explains the boundary.