--- title: "5: Automation | SurrealDB University" description: "DEFAULT, VALUE, COMPUTED, ASSERT, REFERENCE, UPSERT, permissions, and when to use DEFINE EVENT." url: https://surrealdb.com/learn/schemas/page-05 --- ![Course content preview](https://surrealdb.com/assets/static/course-schemas.D4CFbBhP.avif) [Back to Courses](https://surrealdb.com/learn) Course chapters [Schema internals and migrations](https://surrealdb.com/learn/schemas) Internals [1: Schemaless vs. schemafull](https://surrealdb.com/learn/schemas/page-01) [2: Schema internals](https://surrealdb.com/learn/schemas/page-02) [3: Migrations](https://surrealdb.com/learn/schemas/page-03) [4: Data types](https://surrealdb.com/learn/schemas/page-04) [5: Automation](https://surrealdb.com/learn/schemas/page-05) Migrations [6: SurrealKit and the first table](https://surrealdb.com/learn/schemas/page-06) [7: Activities and seed data](https://surrealdb.com/learn/schemas/page-07) [8: Computed and asserted fields](https://surrealdb.com/learn/schemas/page-08) [9: Graph dependencies](https://surrealdb.com/learn/schemas/page-09) [10: Sync vs rollouts](https://surrealdb.com/learn/schemas/page-10) [11: Milestones](https://surrealdb.com/learn/schemas/page-11) [12: People and indexes](https://surrealdb.com/learn/schemas/page-12) [13: Events and CI](https://surrealdb.com/learn/schemas/page-13) [14: Capstone](https://surrealdb.com/learn/schemas/page-14) # 5: Automation With the field types from [part 4](https://surrealdb.com/learn/schemas/page-04) in place, this chapter covers the clauses that sit beside them. After that, [part 6](https://surrealdb.com/learn/schemas/page-06) is where we will pick up SurrealKit for real. Earlier in the course we saw that `DEFINE EVENT` is the only `DEFINE` statement that creates genuinely reactive behaviour. But clauses in several other statements do something similar: they automatically assign or assert values. What they have in common is that they let you push logic that would otherwise live in an SDK or an external service down to the lowest possible level, the data itself. This chapter is mostly a grab bag of clauses you want to be aware of when designing a schema. They reduce the amount of application code you need to write, which narrows the behaviours you need to test outside the database. Each subsection below shows a pattern in isolation. In practice you'll combine them: a `user` table might use `VALUE` to normalise email, `ASSERT` for password length, `DEFAULT` for `created_at`, and field permissions so credentials never appear in API responses. ## The `DEFAULT` clause `DEFAULT` runs when a field isn't supplied on create (or on every write when you add `ALWAYS` to make `DEFAULT ALWAYS`). This clause tends to be used for timestamps, generated IDs, and fields you always want alongside a new record. Literals or simple function calls are quite commonly used with `DEFAULT`. ```surql DEFINE FIELD status ON ticket TYPE "open" | "done" DEFAULT "open"; DEFINE FIELD created ON order TYPE datetime DEFAULT time::now(); DEFINE FIELD id ON user TYPE string DEFAULT rand::ulid(); DEFINE FIELD id ON person TYPE uuid DEFAULT rand::uuid(); ``` `READONLY` disallows writes to a field, pairing well with `DEFAULT` when the client must not override the value: ```surql DEFINE FIELD owner ON order TYPE record READONLY DEFAULT $auth.id; ``` `ALWAYS` can be added after `DEFAULT` to ensure that a value is always present, even on updates that omit the field: ```surql DEFINE FIELD roles ON user TYPE array ASSERT $value ALLINSIDE ["user", "poweruser", "admin"] AND $value.len() IN 1..=3 DEFAULT ALWAYS ["user"]; ``` `DEFAULT` can even run a subquery and store the result. `CREATE ONLY` is common when each new parent record should own a freshly created child: ```surql DEFINE FIELD gift ON user DEFAULT CREATE ONLY gift SET amount = 50; DEFINE FIELD wallet ON user DEFAULT CREATE ONLY account SET balance = 0; ``` This next example shows the `$this` parameter used inside a subquery along with a `CREATE` statement: ```surql DEFINE FIELD profile ON user DEFAULT CREATE ONLY profile SET display_name = $this.name ?? 'Anonymous'; ``` These queries can be as complex as you like. This next example creates an edge for each new employee, returns the name of the company, and set it on the `works_at` field: ```surql CREATE organisation:my_company SET name = "My Company"; DEFINE FIELD works_at ON staff DEFAULT (RELATE ONLY $this->member_of->organisation:my_company).out.name; CREATE ONLY staff; ``` Output: ```surql { id: staff:5ik6h79i1ff6dw6r9vqq, works_at: 'My Company' } ``` ## VALUE The `VALUE` clause is one of the most widely used, because it lets you normalise or override client input. `VALUE` is recomputed on every write (create or update). If you need a value that's also computed on read, reach for `COMPUTED` instead. Force a timestamp on create and leave it alone afterwards with `READONLY`: ```surql DEFINE FIELD created ON order VALUE time::now() READONLY; ``` Recompute on every write when the field should track the latest change: ```surql DEFINE FIELD updated ON order VALUE time::now(); ``` Normalise client strings before they are stored: ```surql DEFINE FIELD email ON user TYPE string VALUE $value.lowercase(); DEFINE FIELD slug ON post TYPE string VALUE ($value).slug(); ``` Clamp or transform numbers: ```surql DEFINE FIELD balance ON account VALUE math::max(0, $value); DEFINE FIELD cents ON invoice TYPE int VALUE math::round($value * 100); ``` Derive a field from other fields on the same record: ```surql -- e.g. will display "Gandar the wizard" DEFINE FIELD official_name ON user VALUE name + ' the ' + class; ``` Bind ownership from the session so the client cannot pick another user: ```surql DEFINE FIELD user ON order TYPE record READONLY VALUE $auth.id; ``` ## COMPUTED `COMPUTED` fields are not stored, but derived when you access the record. You will want to use them when working with fields whose values depend on a function call, or might change over time or depend on another source. ```surql DEFINE FIELD accessed_at ON user COMPUTED time::now(); DEFINE FIELD is_adult ON person COMPUTED age >= 18; DEFINE FIELD line_total ON line_item COMPUTED quantity * unit_price; DEFINE FIELD duration ON session COMPUTED end - start; ``` `COMPUTED` fields are also often used when you have a value that you prefer to be available via a field instead of writing a query. ```surql DEFINE FIELD author ON comment COMPUTED <~person; DEFINE FIELD employers ON person COMPUTED <-works_at<-company; DEFINE FIELD progress ON project COMPUTED math::mean(tasks.progress); ``` ## References A bare `record` field stores a pointer and nothing more. If you delete the record it points at, the field still holds the old id. You can add a `REFERENCE` clause if you want to track that link from the other side. This allows incoming references become queryable, and you can choose what happens when a linked record is deleted. `REFERENCE` works on top-level fields of type `record` or `array>` (including `option<…>` forms): ```surql DEFINE FIELD author ON post TYPE record REFERENCE; DEFINE FIELD tags ON post TYPE array> REFERENCE; DEFINE FIELD manager ON employee TYPE option> REFERENCE; ``` Once a field is a reference, you can walk from the target back to the records that point at it with `<~`. That is the same incoming-link syntax used in the `COMPUTED` examples above: ```surql CREATE person:ada SET name = "Ada"; CREATE post SET title = "Hello", author = person:ada; SELECT *, <~post AS posts FROM person:ada; ``` By itself, `REFERENCE` defaults to `ON DELETE IGNORE`: deleting the target is allowed, and referring fields keep their ids. You can write the default explicitly if you want the schema to say so: ```surql DEFINE FIELD tags ON post TYPE array> REFERENCE ON DELETE IGNORE; ``` Other `ON DELETE` policies control what happens when the referenced record goes away: ```surql -- Clear the link when the target is deleted DEFINE FIELD assigned_to ON activity TYPE option> REFERENCE ON DELETE UNSET; -- Refuse to delete the target while anything still points at it DEFINE FIELD manager ON employee TYPE option> REFERENCE ON DELETE REJECT; -- Delete this record when the record it references is deleted DEFINE FIELD author ON comment TYPE record REFERENCE ON DELETE CASCADE; ``` You can even use `ON DELETE THEN { … }` for custom cleanup logic. ## ASSERT `ASSERT` is one of the most frequently used clauses, because it allows you to validate the incoming value after type checking. It runs on every create and update, so in general it's best to keep expressions cheap. ```surql DEFINE FIELD password ON user TYPE string ASSERT $value.len() >= 8; DEFINE FIELD age ON person TYPE int ASSERT $value >= 0 AND $value <= 130; DEFINE FIELD status ON task TYPE string ASSERT $value IN ['todo', 'doing', 'done']; DEFINE FIELD email ON user TYPE string ASSERT $value.is_email(); ``` You can also combine checks and compare against other fields on the same record: ```surql DEFINE FIELD sku ON product TYPE string ASSERT $value.len() = 8 AND $value.is_alphanum(); DEFINE FIELD end ON booking TYPE datetime ASSERT $value > $this.start; ``` One nice thing about `ASSERT` is that you can write a longer logic chain including `THROW` to customise an error message. Compare the output for a regular `ASSERT $value >= 0`: ```surql DEFINE FIELD quantity ON line_item TYPE int ASSERT $value >= 0; CREATE line_item SET quantity = -1; 'Found -1 for field `quantity`, with record `line_item:qv72v4gxsckqp8uyztbt`, but field must conform to: $value >= 0' ``` ...and this second example which uses `THROW` for a custom error output: ```surql DEFINE FIELD quantity ON line_item TYPE int ASSERT { IF $value > 0 { RETURN true } ELSE { THROW 'Tried to set ' + $value + ' on ' + id + ' but quantity must be positive' } }; CREATE line_item SET quantity = -1; 'An error occurred: Tried to set -1 on line_item:eclimcs6q5g11t3cxqqx but quantity must be positive' ``` ## `UPSERT` and `INSERT` with `ON DUPLICATE KEY UPDATE` Many applications implement "create if missing, otherwise update" in two round trips: a `SELECT`, then either `CREATE` or `UPDATE`. SurrealDB can fold that into a single statement using `UPSERT` or `INSERT`. ## UPSERT With a known record id, `UPSERT` creates the record or updates it in place: ```surql UPSERT product:sku_abc SET name = $name, price = $price, stock += $quantity; ``` ```surql UPSERT type::record('page_view', $session_id) SET hits += 1; ``` Match an existing record with `WHERE` when the id is not known up front: ```surql UPSERT person SET login_count += 1 WHERE email = $email; ``` Pair that with a unique index when the match key must be unique: ```surql DEFINE INDEX unique_email ON user FIELDS email UNIQUE; UPSERT user SET name = $name, email = $email WHERE email = $email; ``` Update nested structure when the record already holds a map of items: ```surql UPSERT cart SET items[$product_id] = { qty: $qty, price: $price } WHERE user = $auth.id; ``` ## INSERT + ON DUPLICATE KEY UPDATE For more complex logic when a duplicate key might exist, you can use `INSERT` with the `ON DUPLICATE KEY UPDATE` clause. ```surql INSERT INTO product { id: product:abc, name: 'Widget', stock: 10 } ON DUPLICATE KEY UPDATE stock += $input.stock; ``` ```surql INSERT INTO word_frequency { id: type::record('word', $word), count: 1 } ON DUPLICATE KEY UPDATE count += 1; ``` ```surql INSERT IGNORE INTO user (email, name) VALUES ($email, $name) ON DUPLICATE KEY UPDATE last_seen = time::now(); ``` ```surql INSERT RELATION INTO likes { in: person:alice, out: post:1, note: 'first' } ON DUPLICATE KEY UPDATE note = 'updated'; ``` That covers the main automation surface for schema design. Parts [6](https://surrealdb.com/learn/schemas/page-06)–[14](https://surrealdb.com/learn/schemas/page-14) apply these patterns to a version-controlled project planning schema with SurrealKit `sync`, `rollouts`, indexes, and `DEFINE EVENT`. Previous 4: Data types [Previous](https://surrealdb.com/learn/schemas/page-04) Next lesson 6: SurrealKit and the first table [Next lesson](https://surrealdb.com/learn/schemas/page-06) ```json {"@context":"https://schema.org","@type":"Course","name":"Schema internals and migrations","description":"Learn how SurrealDB stores schema metadata, how DEFINE statements shape your database, and how to migrate production data safely.","url":"https://surrealdb.com/learn/schemas","inLanguage":"en","isAccessibleForFree":true,"provider":{"@type":"Organization","name":"SurrealDB","url":"https://surrealdb.com"},"hasPart":[{"@type":"LearningResource","name":"Schema internals and migrations","url":"https://surrealdb.com/learn/schemas"},{"@type":"LearningResource","name":"1: Schemaless vs. schemafull","url":"https://surrealdb.com/learn/schemas/page-01"},{"@type":"LearningResource","name":"2: Schema internals","url":"https://surrealdb.com/learn/schemas/page-02"},{"@type":"LearningResource","name":"3: Migrations","url":"https://surrealdb.com/learn/schemas/page-03"},{"@type":"LearningResource","name":"4: Data types","url":"https://surrealdb.com/learn/schemas/page-04"},{"@type":"LearningResource","name":"5: Automation","url":"https://surrealdb.com/learn/schemas/page-05"},{"@type":"LearningResource","name":"6: SurrealKit and the first table","url":"https://surrealdb.com/learn/schemas/page-06"},{"@type":"LearningResource","name":"7: Activities and seed data","url":"https://surrealdb.com/learn/schemas/page-07"},{"@type":"LearningResource","name":"8: Computed and asserted fields","url":"https://surrealdb.com/learn/schemas/page-08"},{"@type":"LearningResource","name":"9: Graph dependencies","url":"https://surrealdb.com/learn/schemas/page-09"},{"@type":"LearningResource","name":"10: Sync vs rollouts","url":"https://surrealdb.com/learn/schemas/page-10"},{"@type":"LearningResource","name":"11: Milestones","url":"https://surrealdb.com/learn/schemas/page-11"},{"@type":"LearningResource","name":"12: People and indexes","url":"https://surrealdb.com/learn/schemas/page-12"},{"@type":"LearningResource","name":"13: Events and CI","url":"https://surrealdb.com/learn/schemas/page-13"},{"@type":"LearningResource","name":"14: Capstone","url":"https://surrealdb.com/learn/schemas/page-14"}]} ``` ```json {"@context":"https://schema.org","@type":"LearningResource","name":"5: Automation","description":"DEFAULT, VALUE, COMPUTED, ASSERT, REFERENCE, UPSERT, permissions, and when to use DEFINE EVENT.","url":"https://surrealdb.com/learn/schemas/page-05","learningResourceType":"lesson","isPartOf":{"@type":"Course","name":"Schema internals and migrations","url":"https://surrealdb.com/learn/schemas"},"position":7} ``` ```json {"@context":"https://schema.org","@type":"Organization","name":"SurrealDB","url":"https://surrealdb.com","logo":"https://surrealdb.com/assets/static/logo.BG7_TG2b.svg","description":"SurrealDB is the unified data layer for AI. A multi-model database for documents, graphs, vectors, and time-series.","foundingDate":"2022","legalName":"SurrealDB Ltd","identifier":{"@type":"PropertyValue","propertyID":"GB-COH","value":"13615201"},"address":{"@type":"PostalAddress","streetAddress":"3rd Floor, 1 Ashley Road","addressLocality":"Altrincham","addressRegion":"Cheshire","postalCode":"WA14 2DT","addressCountry":"GB"},"contactPoint":[{"@type":"ContactPoint","contactType":"customer support","email":"support@surrealdb.com","url":"https://surrealdb.com/contact","availableLanguage":"English"},{"@type":"ContactPoint","contactType":"sales","email":"info@surrealdb.com","url":"https://surrealdb.com/contact","availableLanguage":"English"},{"@type":"ContactPoint","contactType":"security","email":"security@surrealdb.com","url":"https://surrealdb.com/.well-known/security.txt","availableLanguage":"English"},{"@type":"ContactPoint","contactType":"legal","email":"legal@surrealdb.com","url":"https://surrealdb.com/legal","availableLanguage":"English"}],"hasCertification":[{"@type":"Certification","name":"SOC 2 Type 2"},{"@type":"Certification","name":"GDPR"},{"@type":"Certification","name":"Cyber Essentials Plus"},{"@type":"Certification","name":"ISO 27001"}],"owns":[{"@type":"SoftwareApplication","name":"SurrealDB","url":"https://surrealdb.com/surrealdb"},{"@type":"SoftwareApplication","name":"Agent Memory","url":"https://surrealdb.com/agent-memory"}],"knowsAbout":["multi-model databases","document databases","graph databases","vector search","time-series databases","SurrealQL","Agent Memory","real-time databases","embedded databases","context layer","graph ontology","distributed database","knowledge graphs","distributed transaction protocols","highly-scalable databases"],"sameAs":["https://www.wikidata.org/wiki/Q124316308","https://github.com/surrealdb/surrealdb","https://twitter.com/surrealdb","https://www.youtube.com/@surrealdb","https://www.linkedin.com/company/surrealdb","https://discord.gg/surrealdb","https://www.reddit.com/r/surrealdb","https://www.instagram.com/surrealdb","https://medium.com/surrealdb","https://dev.to/surrealdb"]} ``` ```json {"@context":"https://schema.org","@type":"BreadcrumbList","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https://surrealdb.com"},{"@type":"ListItem","position":2,"name":"Learn","item":"https://surrealdb.com/learn"},{"@type":"ListItem","position":3,"name":"Page 05","item":"https://surrealdb.com/learn/schemas/page-05"}]} ```