Egon ORM

Appearance

Soft deletes ​

Keep deleted rows, marked with the time they were deleted, and leave them out of every listing.

Setup ​

The code of this recipe depends on these declarations: the imports, the schema classes, the repo, and the migration that creates the tables. Run the recipe in an empty database of its own: each recipe creates its own tables, and some have the same names (posts) with other columns.

ts
import { postgres } from "@egon-orm/postgres";
import { cast, change, createRepo, fragment, from, type QueryWith, Schema, t } from "egon-orm";
import { createMigrator, defineMigration } from "egon-orm/migration";

const repo = createRepo({ adapter: postgres({ connectionString: process.env.DATABASE_URL }) });

// The tables, created by a migration.
const tables = defineMigration({
  up(m) {
    m.createTable("posts", (table) => {
      table.id({ type: "integer" });
      table.text("title").notNull();
      table.timestamptz("deleted_at");
    });
    // A unique title among the posts that are not deleted: a partial unique index, named
    // posts_title_key, the name uniqueConstraint("title") looks for.
    m.createIndex("posts", ["title"], { unique: true, where: fragment`deleted_at is null` });
  },
  down(m) {
    m.dropTable("posts");
  },
});

/** Creates the tables, in the database that DATABASE_URL names. */
export async function createTables(): Promise<void> {
  const admin = postgres({ connectionString: process.env.DATABASE_URL });
  await createMigrator({
    adapter: admin,
    migrations: [{ version: 20261021001001, name: "soft_deletes_tables", migration: tables }],
  }).up();
  await admin.close();
}

The field ​

ts
class Post extends Schema("posts", {
  id: t.integer().primaryKey().dbGenerated(),
  title: t.string(),
  deletedAt: t.timestamp().nullable(),
}) {
  // A second live post with the same title is a field error, not an exception.
  static changeset(post: Post | typeof Post, params: unknown) {
    return cast(post, params, ["title"]).validateRequired(["title"]).uniqueConstraint("title");
  }

  get isDeleted(): boolean {
    return this.deletedAt !== null;
  }
}

The table's unique index on title is partial (where deleted_at is null, in the migration above) and named posts_title_key, the name uniqueConstraint("title") looks for: a title is unique among the live posts only.

Leaving deleted rows out ​

A scope is a function from query to query, applied with .pipe(…). QueryWith<{ p: typeof Post }> is the type of any query that has a binding p of Post, so the scope works on every such query and returns the same query type (Reusable pieces). Every listing starts from a base query that applies it:

ts
// A reusable scope: any query with a binding `p` of Post.
const live = <Q extends QueryWith<{ p: typeof Post }>>(q: Q): Q => q.where(({ p }) => p.deletedAt.isNull());

export const posts = from(Post, "p").pipe(live); // the base query every listing starts from

There is no default scope that applies by itself: repo.all(Post) and repo.get(Post, id) still see deleted rows. That is deliberate (what a query does is written in the query), and it is what an admin page or a restore needs. Start listings from posts, or from from(Post, "p").pipe(live).

Deleting, restoring, purging ​

ts
export function softDelete(post: Post) {
  return repo.updateOrThrow(change(post, { deletedAt: new Date() }));
}

// Restoring can meet the same index: a live post may have taken the title meanwhile. The
// result reports it on the field, { title: ["has already been taken"] }.
export function restore(post: Post) {
  return repo.update(change(post, { deletedAt: null }).uniqueConstraint("title"));
}

// Remove for good what was deleted more than 30 days ago.
export function purge(before = new Date(Date.now() - 30 * 86_400_000)) {
  return repo.deleteAll(from(Post, "p").where(({ p }) => p.deletedAt.lt(before)));
}

A soft delete is an update, so it goes through a changeset, sets updatedAt if the schema has timestamps, and can carry an optimistic lock.

The example below starts from these rows, after creating the tables:

ts
await createTables();
await repo.insertAll(Post, [{ title: "One" }, { title: "Two" }]);

It deletes a post, which leaves the listing but stays in the table. Both the changeset and restore declare the unique index, so a clash is a field error: a deleted post's title is free for a new post, and restoring the old one then meets the index.

ts
const deleted = await softDelete(await repo.getByOrThrow(Post, { title: "One" }));
deleted.isDeleted; // => true
// The listing leaves it out; the table still has it.
(await repo.all(posts)).map((p) => p.title); // => ["Two"]
await repo.count(Post); // => 2

// While "One" is deleted, its title is free for a new post...
const again = await repo.insert(Post.changeset(Post, { title: "One" }));
again.success; // => true
// ...so the deleted one cannot come back under it.
const back = await restore(deleted);
back.success ? null : back.error.errorMessages(); // => { title: ["has already been taken"] }

const twin = await repo.insert(Post.changeset(Post, { title: "Two" })); // "Two" is live
twin.success ? null : twin.error.errorMessages(); // => { title: ["has already been taken"] }

Good to know

  • Unique values. A unique index would count deleted rows too: make it partial, as the migration does. With another name than posts_title_key, pass it: uniqueConstraint("title", { name }).
  • Associations. A preload of posts loads deleted posts too. Filter in the callback form: preload("posts", (q) => q.where(({ posts }) => posts.deletedAt.isNull())), or give the association a where.
  • Foreign keys still point at a soft-deleted row; ON DELETE does nothing until it is purged.

See also ​