Question: How to upsert a model and it's relationship

[Joshswooft]

Hi, I have a schema that looks like this:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    roles role[],
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP,
    google_user_id VARCHAR(255),
    UNIQUE (email),
    UNIQUE (google_user_id)
);

CREATE TABLE tokens (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    access_token TEXT NOT NULL, -- access token is the short lived token you use to interact with services
    refresh_token TEXT, -- use the refresh token to get a new access token
    id_token TEXT, -- id token is for OIDC and provides user information
    provider VARCHAR(50), -- provider e.g. google
    scopes TEXT, -- Store scopes as space-separated values
    token_type VARCHAR(50), -- e.g. token_type e.g. BEARER, JWT
    expiry TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP
);

I was wondering what would be the best way to upsert a new user AND it's token(s)?

I have seen there's an Upsert method but that only applies for the user model. I'm wondering if there is an operation that supports what I'm trying to do or to resort to raw SQL

[stephenafamo]

Not in a single method call.

[Joshswooft]

Thanks for your response, I was also wondering how one might get the updated model back when done a simple Update method? As the comment says it doesn't update the reflecting model

[stephenafamo]

If you want to load from the DB, you can use the Reload method on the model.

Note: The values passed in the Setter are written into the model, but any extra values from the DB (e.g. if you have generated columns) are not.

Also, for drivers that support the Returning clause, refreshing DB values should be possible by default in the future.