✅ - how to setup createdAt, updatedAt in postgresql table to use them in amplify gen 2 project

[last lion]

Hello, I need some help with connecting Postgresql and amplify gen 2 project.

How to correctly create postress Table with createdAt and updatedAt fields, to make them same as createdAt updatedAt as native.

Here is my current sql query for table creation

CREATE TABLE account (
    id VARCHAR(255) PRIMARY KEY,
    "createdAt" TIMESTAMP default CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMP default CURRENT_TIMESTAMP
);

In documentation stated that amplify automaticaly handle those fields if they are presented in schema, however, its not true, there are two issues

1 amplify do not auto update those fields on record update (tried to define them with with names like updated_at or "updatedAt")

2. wrong type generated in schema (createdAt or updatedAt fields) doesn't have default values, and stated as Nullable after record creation

file schema.sql.ts
....
.schema({
    "account": a.model({
        id: a.string().required(),
        createdAt: a.datetime(),
        updatedAt: a.datetime()
    }).identifier([
        "id"
    ])

How to define these fields (createdAt, updatedAt) in postgresql to make them behave like fields when using dynamo db tabels?

[dusk girder]

Hi can you run this command add share the schema file generated . npx ampx generate schema-from-database --connection-uri-secret SQL_CONNECTION_STRING --out amplify/data/schema.sql.ts

for more details refer to this docs section: https://docs.amplify.aws/react/reference/cli-commands/#npx-ampx-generate-schema-from-database

[last lion]

@dusk girder
sure, this basicly code that i shared in question

[fringe tide]

bump

createdAt updatedAt field not being populated, schema:
"channel_meta": a.model({ channel_id: a.string(), channel_meta_id: a.integer().required(), attribute_name: a.string(), attribute_value: a.string(), updatedAt: a.datetime(), createdAt: a.datetime() }).identifier([ "channel_meta_id" ]),

[fringe tide]

I have figured this one out. You need to have the createdAt, updatedAt fields allow null and set their values to undefined in the create call.
Furthermore you probably want your updatedAt SQL schema to includeDEFAULT_GENERATED on update CURRENT_TIMESTAMP. Dont forget to include these fields in the selection set of your create.

await client.models.ChannelAlerts.create( { alert_id: uuid(), email: email, channel_id: values.channel, greater_than: values.lower_bound, less_than: values.upper_bound, notified_today: 0, owner: username, parameter: channels?.find((channel) => channel.channel_id === values.channel) ?.parameterName ?? '', updatedAt: undefined, createdAt: undefined }, { selectionSet: [ 'email', 'alert_id', 'channel_id', 'greater_than', 'less_than', 'notified_today', 'owner', 'parameter', 'updatedAt', 'createdAt', ], }, );

[tropic iron]

✅ - how to setup createdAt, updatedAt in postgresql table to use them in amplify gen 2 project