# Bases

> Share columns between tables with bases, change a base and every table that extends it, and build bases on bases.

A base is a named set of columns that tables inherit, like a parent class. One query reads every table that extends a base, on the base’s columns, and a new table that extends it appears in the results without changes to any view.

The examples on this page keep a diary of classic films in its own catalog, `diary`.

## Share columns between tables with bases

A base is a named set of columns that tables inherit, like a parent class. Every table that extends `entry` gets `added_on` and `note` with the same meaning. `select_rows` on `base:entry` reads all those tables at once, here films and actors.

**`define_base`**

```json
{
  "name": "entry",
  "description": "A note of the film diary",
  "columns": [
    { "name": "added_on", "type": "date", "description": "When I added it to the diary" },
    { "name": "note", "type": "string", "description": "Free text" }
  ]
}
```

Answer:

```json
{
  "ok": true,
  "op": "define_base",
  "planned_texts": ["datanotes/metastore.yaml"],
  "migration_id": "20261012T165235-define_base-create-base-entry"
}
```

**`set_table_bases`**

```json
{ "table": "film", "bases": ["entry"] }
```

Answer:

```json
{
  "ok": true,
  "op": "set_table_bases",
  "planned_texts": ["datanotes/metastore.yaml"],
  "commits": [
    { "table": "diary.classics.film", "version": 6, "operation": "SET TABLE BASES" }
  ],
  "migration_id": "20261012T165235-set_table_bases-diary-classics-film-extends-en"
}
```

**`create_table`**

```json
{
  "catalog": "diary",
  "schema": "classics",
  "name": "actors",
  "description": "Actors of the films in the diary",
  "columns": [
    {
      "name": "name",
      "type": "string",
      "required": true,
      "description": "The actor's name, as credited"
    }
  ],
  "filename": "{name}"
}
```

**`set_table_bases`**

```json
{ "table": "actors", "bases": ["entry"] }
```

**`insert_row`**

```json
{
  "table": "actors",
  "values": {
    "name": "Max Schreck",
    "added_on": "2026-10-05",
    "note": "The face of Nosferatu"
  }
}
```

**`update_row`**

```json
{
  "table": "film",
  "path": "datanotes/diary/classics/film/Nosferatu.md",
  "set": { "added_on": "2026-10-05" }
}
```

**`select_rows`**

```json
{ "table": "base:entry", "order_by": "path" }
```

Answer:

```json
{
  "table": "base:entry",
  "total": 2,
  "rows": [
    {
      "path": "datanotes/diary/classics/actors/Max Schreck.md",
      "id": "01a13107-c616-7110-8f4d-019ced2f41b9",
      "added_on": "2026-10-05",
      "note": "The face of Nosferatu",
      "_source": "diary.classics.actors"
    },
    {
      "path": "datanotes/diary/classics/film/Nosferatu.md",
      "id": "01a13107-c599-7aa2-824d-cebe56b78f22",
      "added_on": "2026-10-05",
      "note": null,
      "_source": "diary.classics.film"
    }
  ]
}
```

## Change a base and every table that extends it

Replacing a base changes every table that extends it, in one operation. `rename_columns` renames a column on their rows. `define_base` refuses to drop a column unless `drop_columns` names it, and then the engine removes the column’s values from the rows too.

**`define_base`**

```json
{
  "name": "entry",
  "columns": [
    { "name": "added_on", "type": "date", "description": "When I added it to the diary" },
    { "name": "comment", "type": "string", "description": "Free text" }
  ],
  "rename_columns": { "note": "comment" }
}
```

Answer:

```json
{
  "ok": true,
  "op": "define_base",
  "notes_changed": 1,
  "planned_edits": [
    {
      "path": "datanotes/diary/classics/actors/Max Schreck.md",
      "set": { "comment": "The face of Nosferatu" },
      "remove": ["note"]
    }
  ],
  "planned_texts": ["datanotes/metastore.yaml"],
  "commits": [
    { "table": "diary.classics.actors", "version": 3, "operation": "DEFINE BASE" }
  ],
  "migration_id": "20261012T165235-define_base-alter-base-entry"
}
```

**`select_rows`**

```json
{ "table": "actors", "columns": ["name", "comment"] }
```

Answer:

```json
{
  "table": "diary.classics.actors",
  "total": 1,
  "rows": [
    {
      "path": "datanotes/diary/classics/actors/Max Schreck.md",
      "name": "Max Schreck",
      "comment": "The face of Nosferatu"
    }
  ]
}
```

`define_base` refuses a base without `comment` until the call says the values may go:

**`define_base`**

```json
{
  "name": "entry",
  "columns": [
    { "name": "added_on", "type": "date", "description": "When I added it to the diary" }
  ]
}
```

Refused:

```text
base "entry" would lose column "comment": list it in drop_columns (its values are removed from the rows) or rename_columns
Hint: Check the arguments of define_base: name (string, required), columns (list of objects, required), bases (list of strings), description (string), rename_columns (object), drop_columns (list of strings), dry_run (boolean).
Docs: https://datanotes.dev/reference/operations/schema.md#define_base (offline: read_docs("reference/operations/schema#define_base"))
```

**`define_base`**

```json
{
  "name": "entry",
  "columns": [
    { "name": "added_on", "type": "date", "description": "When I added it to the diary" }
  ],
  "drop_columns": ["comment"]
}
```

Answer:

```json
{
  "ok": true,
  "op": "define_base",
  "notes_changed": 1,
  "planned_edits": [
    {
      "path": "datanotes/diary/classics/actors/Max Schreck.md",
      "set": {},
      "remove": ["comment"]
    }
  ],
  "planned_texts": ["datanotes/metastore.yaml"],
  "commits": [
    { "table": "diary.classics.actors", "version": 4, "operation": "DEFINE BASE" }
  ],
  "migration_id": "20261012T165235-define_base-alter-base-entry-2"
}
```

## Build bases on bases and link a table to itself

`bases` builds a base on others. A table that extends `credited` gets `films`, plus `added_on` from `entry`, and `base:entry` still reads it. In a base, `ref: "self"` means the table that inherits it, so `tree` gives each table a `parent` link to its own rows.

**`define_base`**

```json
{
  "name": "credited",
  "bases": ["entry"],
  "description": "Someone in the credits of a film",
  "columns": [
    {
      "name": "films",
      "type": "list",
      "items": "link",
      "ref": "diary.classics.film",
      "description": "The films they’re credited in"
    }
  ]
}
```

Answer:

```json
{
  "ok": true,
  "op": "define_base",
  "planned_texts": ["datanotes/metastore.yaml"],
  "migration_id": "20261012T165235-define_base-create-base-credited"
}
```

**`set_table_bases`**

```json
{ "table": "actors", "bases": ["credited"] }
```

Answer:

```json
{
  "ok": true,
  "op": "set_table_bases",
  "planned_texts": ["datanotes/metastore.yaml"],
  "commits": [
    { "table": "diary.classics.actors", "version": 5, "operation": "SET TABLE BASES" }
  ],
  "migration_id": "20261012T165235-set_table_bases-diary-classics-actors-extends--2"
}
```

**`define_base`**

```json
{
  "name": "tree",
  "description": "Rows arranged in a tree",
  "columns": [
    {
      "name": "parent",
      "type": "link",
      "ref": "self",
      "hierarchy": true,
      "description": "The row above this one"
    }
  ]
}
```

Answer:

```json
{
  "ok": true,
  "op": "define_base",
  "planned_texts": ["datanotes/metastore.yaml"],
  "migration_id": "20261012T165235-define_base-create-base-tree"
}
```

**`create_table`**

```json
{
  "catalog": "diary",
  "schema": "classics",
  "name": "genre",
  "description": "Eras and genres of cinema, as a tree",
  "columns": [
    {
      "name": "name",
      "type": "string",
      "required": true,
      "description": "The name of the era or genre"
    }
  ],
  "filename": "{name}"
}
```

**`set_table_bases`**

```json
{ "table": "genre", "bases": ["tree"] }
```

Answer:

```json
{
  "ok": true,
  "op": "set_table_bases",
  "planned_texts": ["datanotes/metastore.yaml"],
  "commits": [
    { "table": "diary.classics.genre", "version": 1, "operation": "SET TABLE BASES" }
  ],
  "migration_id": "20261012T165235-set_table_bases-diary-classics-genre-extends-t"
}
```

**`insert_row`**

```json
{ "table": "genre", "values": { "name": "Cinema" } }
```

**`insert_row`**

```json
{ "table": "genre", "values": { "name": "Silent film", "parent": "[[Cinema]]" } }
```

In `genre`, `parent` links rows of `diary.classics.genre`:

**`describe_table`**

```json
{ "table": "genre" }
```

Answer:

```json
[
  { "name": "id", "type": "string" },
  { "name": "parent", "type": "link", "ref": "diary.classics.genre", "hierarchy": true },
  { "name": "name", "type": "string" }
]
```

**`select_rows`**

```json
{ "table": "base:entry", "columns": ["added_on"], "order_by": "path" }
```

Answer:

```json
{
  "table": "base:entry",
  "total": 2,
  "rows": [
    {
      "path": "datanotes/diary/classics/actors/Max Schreck.md",
      "added_on": "2026-10-05",
      "_source": "diary.classics.actors"
    },
    {
      "path": "datanotes/diary/classics/film/Nosferatu.md",
      "added_on": "2026-10-05",
      "_source": "diary.classics.film"
    }
  ]
}
```

`bases: []` removes a table’s bases. `set_table_bases` refuses it while rows hold values of the inherited columns, like the `parent` of Silent film:

**`set_table_bases`**

```json
{ "table": "genre", "bases": [] }
```

Refused:

```text
set_table_bases refused — nothing was written:
invalid datanotes/diary/classics/genre/Silent film.md: parent is not a column of table "genre"
Hint: Write only the table's columns: describe_table lists them. To keep a new front matter key, add_column first. Then call set_table_bases again (dry_run: true checks without writing).
Docs: https://datanotes.dev/guides/database-objects/tables.md#describe-a-table-before-writing-to-it (offline: read_docs("guides/database-objects/tables#describe-a-table-before-writing-to-it"))
```

On a table with no such values, `set_table_bases` removes the inherited columns from the definition. Here that table is an empty copy of `genre`:

**`clone_table`**

```json
{ "source": "genre", "target": "genre_draft", "copy_rows": false }
```

**`set_table_bases`**

```json
{ "table": "genre_draft", "bases": [] }
```

Answer:

```json
{
  "ok": true,
  "op": "set_table_bases",
  "planned_texts": ["datanotes/metastore.yaml"],
  "commits": [
    {
      "table": "diary.classics.genre_draft",
      "version": 1,
      "operation": "SET TABLE BASES"
    }
  ],
  "migration_id": "20261012T165235-set_table_bases-diary-classics-genre_draft-ext"
}
```