Nested Writes

A nested write creates, links or changes related rows inside one create() or update() call. The whole tree runs in one transaction, and the call returns the row with the touched relations loaded.

const user = await db.users.create({
  data: {
    email: 'alice@example.com',
    posts: { create: [{ title: 'First post' }, { title: 'Second post' }] },
    organization: { connect: { id: 1 } },
  },
});
user.posts; // the two new posts

A key in data is a nested write when it names a relation and its value is a plain object. The examples assume users has many posts, has one profile and belongs to an organization, and posts has many comments.

Operations at a glance#

An update-only operation inside create(), or an unknown operation name, throws ValidationError (E003). Many-to-many relations take only connect, disconnect and set; see Many-to-many relations.

Create context#

create, insert and link#

await db.users.create({
  data: {
    email: 'grace@example.com',
    profile: { create: { bio: 'Building things with Postgres.' } },   // hasOne: one object
    organization: { create: { name: 'Acme Inc' } },                  // belongsTo
    posts: {
      create: { title: 'Hello', comments: { create: [{ body: 'First!' }] } },
    },
  },
});

For hasMany and hasOne, Turbine inserts the parent first, then fills each child's foreign key. For belongsTo, it writes the related row first, so a NOT NULL foreign key holds on the first insert. Trees nest up to depth 10; deeper throws CircularRelationError (E007).

connect, link existing rows#

await db.users.create({
  data: {
    email: 'bob@example.com',
    posts: { connect: [{ id: 10 }, { id: 11 }] },
    organization: { connect: { id: 1 } }, // sets users.org_id
  },
});

A target that does not exist throws ValidationError (E003).

Connecting a row someone else owns, scopedConnect#

On a to-many relation, connect: { id: 42 } moves row 42 to this parent, even when another parent owns it. A handler that forwards client-supplied ids into connect lets a caller take another tenant's rows:

// The caller owns the collection, but nothing checks who owns each item.
await db.collections.update({
  where: { id: collection.id },
  data: { items: { connect: req.body.itemIds.map((id) => ({ id })) } },
});

Authorize the child ids in the handler. As a backstop, turn on scopedConnect:

const db = turbine({ connectionString: process.env.DATABASE_URL, scopedConnect: true });
 
// ValidationError (E003): post 42 belongs to another user
await db.users.update({ where: { id: 7 }, data: { posts: { connect: { id: 42 } } } });

With it on, a connect or connectOrCreate on hasMany or hasOne that finds an existing child succeeds only when the child has no parent or already belongs to this one. Composite foreign keys are covered. Many-to-many connects add a junction row instead of moving one, so scopedConnect does not check them; authorize those ids yourself.

It is off by default because some code moves rows between parents on purpose. Turn it on in a test environment to find those call sites, and rewrite them as explicit updates.

connectOrCreate, connect or insert#

await db.users.create({
  data: {
    email: 'carol@example.com',
    posts: {
      connectOrCreate: {
        where: { slug: 'welcome' },
        create: { slug: 'welcome', title: 'Welcome' },
      },
    },
  },
});

Update context#

Inside update(), the operations above still work, and five more are available.

disconnect, null the foreign key#

await db.users.update({
  where: { id: 1 },
  data: {
    posts: { disconnect: [{ id: 10 }] },   // posts.user_id = NULL
    organization: { disconnect: true },    // users.org_id = NULL
  },
});

The foreign key must be nullable. Otherwise disconnect throws ValidationError (E003); use delete instead.

set, replace the whole collection#

await db.users.update({
  where: { id: 1 },
  data: { posts: { set: [{ id: 12 }, { id: 13 }] } }, // now the user's only posts
});

set disconnects every current child, then connects the listed rows.

delete, update and upsert#

await db.users.update({
  where: { id: 1 },
  data: {
    posts: {
      delete: { id: 10 },
      update: { where: { id: 11 }, data: { title: 'Edited title' } },
      upsert: {
        where: { slug: 'changelog' },
        create: { slug: 'changelog', title: 'Changelog' },
        update: { title: 'Changelog (updated)' },
      },
    },
    organization: { update: { data: { name: 'Acme LLC' } } }, // where comes from users.org_id
  },
});

Each takes one object or an array. These operations reach only rows that belong to the parent: a where outside the relation throws NotFoundError (E001), and an empty selector is refused.

belongsTo is one row#

A belongsTo relation is the single row the record points at:

• disconnect: true clears the foreign key. delete: true clears it, then deletes the row it pointed at; if other rows still reference that row, the write fails with ForeignKeyError (E009) and rolls back. Both need a nullable foreign key.
• create, connect, connectOrCreate, disconnect and delete each set what the record points at, so only one of them is allowed per write. update and upsert can sit beside it.
• Each operation takes one item; an array with more than one throws. There is no set.
• When nothing is connected, disconnect: true does nothing, and delete: true throws NotFoundError (E001).

await db.users.update({
  where: { id: 1 },
  data: {
    organization: {
      connectOrCreate: { where: { slug: 'acme' }, create: { slug: 'acme', name: 'Acme' } },
    },
  },
});

Many-to-many relations#

A many-to-many link lives in a junction row. Turbine writes those rows in the parent write's transaction, on every engine:

await db.posts.create({
  data: { title: 'Single-query relations', tags: { connect: [{ slug: 'postgres' }, { slug: 'orm' }] } },
});
 
await db.posts.update({
  where: { id: 1 },
  data: { tags: { connect: { slug: 'performance' }, disconnect: { slug: 'sql' } } },
});
 
await db.posts.update({
  where: { id: 1 },
  data: { tags: { set: [] } }, // remove every tag
});

• connect is idempotent: linking a tag that is already linked does nothing.
• disconnect and set touch only this parent's links.
• Within one payload, set runs first, then disconnect, then connect.
• A selector that matches no row throws ValidationError (E003).
• Composite junction keys throw. Write those junction rows directly.

create, connectOrCreate, update, upsert and delete throw ValidationError (E003) on a many-to-many relation, before the parent row is written. To create a target and link it, write it first:

await db.$transaction(async (tx) => {
  const tag = await tx.tags.create({ data: { slug: 'indexes', name: 'Indexes' } });
  await tx.posts.update({ where: { id: 1 }, data: { tags: { connect: { id: tag.id } } } });
});

For a junction with its own columns, write its rows through the junction's accessor or table():

await db.table('post_tags').createMany({
  data: [{ postId: 1, tagId: 7, addedBy: 'editor' }],
});

Guarantees#

• Atomic. A failure anywhere rolls the whole tree back.
• Ordered. belongsTo targets are written before the parent; hasMany and hasOne children after it.
• Validated first. Invalid operations throw ValidationError (E003) before anything commits.
• Scoped to the parent. delete, update, disconnect and upsert never touch a row outside the relation.

See also#

• Relations: how relations are inferred and read.
• Transactions: the transaction that wraps every nested write.
• Typed errors: ValidationError, CircularRelationError and the constraint errors.