Can't use list() on external DB with composite identifier

[PaulSut]

Bug Summary

When using an external PostgreSQL database with a table that has a composite primary key (i.e., a primary key composed of multiple columns), the list() query fails.

---

Schema Definition

The generated model schema looks like this:

"overview": a.model({
  a: a.string().required(),
  b: a.date().required(),
  c: a.integer(),
  d: a.float(),
  e: a.float(),
}).identifier(["a", "b"]) // composite primary key

Query Used

client.models.overview.list({
  limit: 5,
});

Observed Behavior

The query fails with the following error:

Validation error of type UnknownType: Unknown type b

Expected Behavior

The query should return the first 5 entries from the overview table, just like it would for a table with a single-column primary key.

[Simone319]

Hello @PaulSut , thank you for reporting this issue. We will try to reproduce the error from our side and provide an update.

[Simone319]

Hello @PaulSut , I'm not able to reproduce the error you observed. Using the schema definition and the query you provided, I'm able to receive the first 5 entries from the overview table. I was using aws-amplify version 6.15.3 for the experiment.

Can you provide which amplify version you're using? Also if you can provide stack trace of the error you received, that would also help identify the issue.

[PaulSut]

Thanks for looking into this! I will try to provide a little more context:

Context

I am not using DynamoDB – my data source is PostgreSQL.
Those are the amplify related versions I am using:

npm list | grep 'amplify'
├── @aws-amplify/backend-cli@1.8.0
├── @aws-amplify/backend@1.16.1
├── @aws-amplify/ui-react@6.11.2
├── aws-amplify@6.15.4

My table schema looks like this:

CREATE TABLE test (
  a varchar NOT NULL,
  b varchar NOT NULL,
  c varchar NULL,
  CONSTRAINT test_pk PRIMARY KEY (a, b)
);

I'm using a sandbox environment, and after running npx ampx generate schema-from-database, the generated schema.sql.ts looks like:

"test": a.model({
  a: a.string().required(),
  b: a.string().required(),
  c: a.string()
}).identifier(["a", "b"])

After deploying to the sandbox environment, the resulting amplify_outputs.json contains:

"test": {
  "name": "test",
  "fields": {
    "a": { "name": "a", "isArray": false, "type": "String", "isRequired": true, "attributes": [] },
    "b": { "name": "b", "isArray": false, "type": "String", "isRequired": true, "attributes": [] },
    "c": { "name": "c", "isArray": false, "type": "String", "isRequired": false, "attributes": [] }
  },
  "syncable": true,
  "pluralName": "tests",
  "attributes": [
    { "type": "model", "properties": { "timestamps": null } },
    { "type": "key", "properties": { "fields": ["a", "b"] } },
    { "type": "auth", "properties": {
      "rules": [
        {
          "allow": "public",
          "provider": "apiKey",
          "operations": ["read"]
        }
      ]
    }}
  ],
  "primaryKeyInfo": {
    "isCustomPrimaryKey": true,
    "primaryKeyFieldName": "a",
    "sortKeyFieldNames": ["b"]
  }
}

---

Problem

Calling list() fails:

const { data, errors } = await client.models.test.list({ limit: 5 });

console.log("Test Data:", data);
console.log("Test Errors:", errors);

Results in:

Test Data: []
Test Errors: [
  {
    path: null,
    locations: [ [Object] ],
    message: "Validation error of type UnknownArgument: Unknown field argument b @ 'listTests'"
  }
]

---

Manual Fix

If I manually change schema.sql.ts to:

"test": a.model({
  a: a.string().required(),
  b: a.string().required(),
  c: a.string()
}).identifier(["a"]) // <- removed "b"

Then re-deploy, the amplify_outputs.json looks like this:

... 
    { "type": "key", "properties": { "fields": ["a"] } },
...
"primaryKeyInfo": {
  "isCustomPrimaryKey": true,
  "primaryKeyFieldName": "a",
  "sortKeyFieldNames": []
}

Even though the underlying database schema is unchanged (composite PK still present), the list() query now works.

---

Additional Observation

After running npx ampx generate schema-from-database again, the schema.sql.ts once again contains:

.identifier(["a", "b"])

However, even after re-deployment, amplify_outputs.json does not contain b as sortKeyFieldName, and the list() query continues working.

---

Let me know if any further details are needed :)

[ahmedhamouda78]

Hello @PaulSut,

We were able to reproduce the issue and confirm it is a bug. We are discussing with the team how to address this and will get back to you once we have an update.

Thanks!