Create a record with email address as the unique identifier [JavaScript client (Node.js Package) - SurrealDB]

Question

I am trying to create a person record with the following code:

import Surreal from 'surrealdb.js';
...
const peopleFromSomewhere = await someProvider.getAllPeople();

// [{ email: '[email protected]', fullName: 'Some Name' }, ... ]

for (const person of peopleFromSomewhere) {
  const created = await db.create('person', {
    email: person.email,
    fullName: person.fullName
  });
})

This creates a record with a randomly generated unique identifier, such as:

{ 
  email: '[email protected]',
  fullName: 'Some Name',
  id: 'person:b5pfovinz7zkxlz3s8er'
}

But if I run the same code again, the same data will be inserted, just with a different UID.

I would like to use the email as a 'primary key' (using relational database terms) - to avoid inserting the same person twice, with the exact same details (email address & full name). How can I do this?

  const created = await db.create(`person:${person.email}`, {
    email: person.email,
    fullName: person.fullName
  });

Only gives me:

{ 
  email: '[email protected]',
  fullName: 'Some Name',
  id: '⟨`person:[email protected]`⟩:w9m4l31bwti4472tdoax'
}

Which doesn't look quite right to me.

I expected:

{ 
  email: '[email protected]',
  fullName: 'Some Name',
  id: 'person:'[email protected]''
}

How can I create a person record with the unique ID of an email address, so that I don't insert the same data twice?

Answer 1

To create Record IDs with complex characters (such as the @ symbol) you have to surround the ID either with the ` or the ⟨ and ⟩ characters.

A query to create a record with such an ID would look like this:

CREATE user:⟨[email protected]⟩ SET
  email = '[email protected]',
  fullName = 'Some Name'
;

Which would translate to the following query with the node.js driver:

let created = await db.create(`person:⟨${person.email}⟩`, {
  email: person.email,
  fullName: person.fullName
})

The record returned by the database then always includes the ⟨ and ⟩, even if you surrounded the ID with the ` character.

[
    {
        "time": "73µs",
        "status": "OK",
        "result": [
            {
                "email": "[email protected]",
                "fullName": "some name",
                "id": "person:⟨[email protected]⟩"
            }
        ]
    }
]