0

I'm building a WebSocket API Gateway in AWS. When a device connects, API Gateway assigns the connection a unique connection ID. I need to store the relationship between the connection ID and device in a table:

Connection ID Device ID Created At
123 ABC 2024-10-11T21:48:08.500Z
456 DEF 2024-10-11T21:48:08.500Z

All connection IDs and device IDs are unique. Connection IDs have a one-to-one relationship with device IDs.

In AWS's examples, the connection ID is often used as the partition key in DynamoDB, allowing for fast lookups and deletions by connection ID. However, I also need fast lookups and deletions by device ID. If the connection ID is the partition key, finding a connection by device ID would require scanning the entire table.

In other words, the operations I need to support are (written using SQL):

SELECT connection_id FROM connections WHERE device_id = xxxx; SELECT device_id FROM connections WHERE connection_id = xxxx; DELETE FROM connections WHERE device_id = xxxx; DELETE FROM connections WHERE connection_id = xxxx;

I've looked into adding a Global Secondary Index on the device ID, but GSI updates are eventually consistent. This means if I delete a connection by connection ID, someone might still see the connection if, immediately after the delete, they look up the connection using a device ID.

My question: Is it possible to model this relationship efficiently in DynamoDB, or would a SQL database be more appropriate?

Improve this question
2
  • Let's dial this back a second: are you sure you need a 1-1 relationship? Specifically 1 on both sides, not 0..1? If you've answered "yes" twice, what would be the benefit of distinguishing between a device and a connection from a data structure perspective? If you answered "no" at least once, then you'll need to refine your question to explain exactly what relationship you're looking to model. Commented Oct 21, 2024 at 2:38
  • Also, I cannot confirm this from my end, but I want to point out that often when I hear people asking for 1-1 relationships, they tend to only think about current state and not historical state; so please do evaluate if you've glossed over this as well. For example, what happens if a device goes offline, and then reconnects? Commented Oct 21, 2024 at 2:39

1 Answer 1

Reset to default
-2

Hell yeah it is! Here's how:

Store two items per connection:

Item 1: PK = 'CONN#<connection_id>', SK = 'META', Attributes: DeviceID, CreatedAt

Item 2: PK = 'DEVICE#<device_id>', SK = 'META', Attributes: ConnectionID, CreatedAt

Operations:

Lookup by Connection ID: Use GetItem with PK = 'CONN#123', SK = 'META'.

Lookup by Device ID: Use GetItem with PK = 'DEVICE#ABC', SK = 'META'.

Delete: Use TransactWriteItems to atomically delete both of em

Should give pretty consistent lookup / deletions by either ID, without scans or GSIs. I think Dynamo is actually a good choice over SQL in this case since ops have immediate, strong consistency.

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.