# pyquickbase API reference: Schema changes

This describes version 1.0.0rc13. Other pages: [Quickbase](https://matthew-williams-solutions.github.io/pyquickbase/reference/quickbase.txt), [App](https://matthew-williams-solutions.github.io/pyquickbase/reference/app.txt), [Table](https://matthew-williams-solutions.github.io/pyquickbase/reference/table.txt), [Report](https://matthew-williams-solutions.github.io/pyquickbase/reference/report.txt), [Operation](https://matthew-williams-solutions.github.io/pyquickbase/reference/operation.txt), [SchemaOperation](https://matthew-williams-solutions.github.io/pyquickbase/reference/schema-operation.txt), [Queries](https://matthew-williams-solutions.github.io/pyquickbase/reference/queries.txt), [Records and results](https://matthew-williams-solutions.github.io/pyquickbase/reference/records.txt), [Metadata](https://matthew-williams-solutions.github.io/pyquickbase/reference/metadata.txt), [Policies](https://matthew-williams-solutions.github.io/pyquickbase/reference/policies.txt), [Rules](https://matthew-williams-solutions.github.io/pyquickbase/reference/rules.txt), [Retries](https://matthew-williams-solutions.github.io/pyquickbase/reference/retries.txt), [Tools for models](https://matthew-williams-solutions.github.io/pyquickbase/reference/tools.txt), [Errors](https://matthew-williams-solutions.github.io/pyquickbase/reference/errors.txt).

Describe changes to an app's tables, fields, and relationships, then pass them to [`App.preview_schema_changes()`](https://matthew-williams-solutions.github.io/pyquickbase/reference/app/#preview_schema_changes), which says what each would do without making it, and [`App.apply_schema_changes()`](https://matthew-williams-solutions.github.io/pyquickbase/reference/app/#apply_schema_changes), which makes them if the app hasn't changed since. A change the app already has counts as done.

The examples below use `tasks`, made as shown here, and `app`, which is `qb.app("bq1234567")`. `Job Tasks` has `Title`, `Status`, `Hours`, `Due Date`, `Code`, which is unique, and `Attachment`. It starts with three records, and each example starts from those again. A live test runs every example with both clients and checks its output. `projects` is a table named `Job Projects`, with a `Name` field. The examples only plan, unless they say otherwise.

Sync:

```python
from pyquickbase import Quickbase

with Quickbase(
    realm="acme", token="your_user_token"
) as qb:
    tasks = qb.app("bq1234567").table("Job Tasks")
```

Async:

```python
from pyquickbase import AsyncQuickbase

async with AsyncQuickbase(
    realm="acme", token="your_user_token"
) as qb:
    tasks = qb.app("bq1234567").table("Job Tasks")
```

## Tables

For the full picture, see [Changing the schema](https://matthew-williams-solutions.github.io/pyquickbase/guides/schema/).

### `CreateTable`

Make a table. A plan treats a table with this name as already made.

```python
CreateTable(
    *,
    name: str,
    description: str | None = None,
    single_record_name: str | None = None,
    plural_record_name: str | None = None,
)
```

**Parameters**

- **`name`** `str`, required  
  The table's name.
- **`description`** `str | None`, optional  
  What it holds.
- **`single_record_name`** `str | None`, optional  
  What it calls one record, such as `Project`.
- **`plural_record_name`** `str | None`, optional  
  What it calls several, such as `Projects`.

Sync example:

```python
from pyquickbase import CreateTable

plan = app.preview_schema_changes(
    [CreateTable(name="Job Sites", single_record_name="Site")]
)
print(plan.changes[0].summary)
```

Async example:

```python
from pyquickbase import CreateTable

plan = await app.preview_schema_changes(
    [CreateTable(name="Job Sites", single_record_name="Site")]
)
print(plan.changes[0].summary)
```

Output:

```text
Create table Job Sites.
```

### `UpdateTable`

Rename a table, or change its descriptions. Anything left as None stays as it is.

```python
UpdateTable(
    *,
    table: str,
    name: str | None = None,
    description: str | None = None,
    single_record_name: str | None = None,
    plural_record_name: str | None = None,
)
```

**Parameters**

- **`table`** `str`, required  
  The table, by name or ID.
- **`name`** `str | None`, optional  
  A new name.
- **`description`** `str | None`, optional  
  A new description.
- **`single_record_name`** `str | None`, optional  
  A new name for one record.
- **`plural_record_name`** `str | None`, optional  
  A new name for several.

Sync example:

```python
from pyquickbase import UpdateTable

change = UpdateTable(table="Job Tasks", description="Work.")
plan = app.preview_schema_changes([change])
print(plan.changes[0].settings["description"].after)
```

Async example:

```python
from pyquickbase import UpdateTable

change = UpdateTable(table="Job Tasks", description="Work.")
plan = await app.preview_schema_changes([change])
print(plan.changes[0].settings["description"].after)
```

Output:

```text
Work.
```

### `DeleteTable`

Delete a table and all its records.

```python
DeleteTable(*, table: str)
```

**Parameters**

- **`table`** `str`, required  
  The table, by name or ID.

Sync example:

```python
from pyquickbase import DeleteTable

plan = app.preview_schema_changes(
    [DeleteTable(table="Job Tasks")]
)
change = plan.changes[0]
print(change.risk, change.affected_records)
```

Async example:

```python
from pyquickbase import DeleteTable

plan = await app.preview_schema_changes(
    [DeleteTable(table="Job Tasks")]
)
change = plan.changes[0]
print(change.risk, change.affected_records)
```

Output:

```text
destructive 3
```

## Fields

For the full picture, see [Changing the schema](https://matthew-williams-solutions.github.io/pyquickbase/guides/schema/).

### `CreateField`

Add a field, or a formula field when `formula` is set. A plan treats a field with this label, type, and formula as already made.

```python
CreateField(
    *,
    table: str,
    label: str,
    type: str,
    choices: tuple[str, ...] | None = None,
    required: bool | None = None,
    unique: bool | None = None,
    help_text: str | None = None,
    formula: str | None = None,
)
```

**Parameters**

- **`table`** `str`, required  
  The table, by name or ID.
- **`label`** `str`, required  
  The new field's label.
- **`type`** `str`, required  
  Its type, such as `text` or `numeric`. For a formula, the type of its result.
- **`choices`** `tuple[str, ...] | None`, optional  
  The options of a `text-multiple-choice` or `multitext` field.
- **`required`** `bool | None`, optional  
  Whether it must have a value. Quickbase refuses this on a table with records.
- **`unique`** `bool | None`, optional  
  Whether each record must have a different value.
- **`help_text`** `str | None`, optional  
  The help shown beside it on forms.
- **`formula`** `str | None`, optional  
  Makes it a formula field, calculated from the record's other fields.

Sync example:

```python
from pyquickbase import CreateField

plan = app.preview_schema_changes(
    [
        CreateField(
            table="Job Tasks",
            label="Cost",
            type="numeric",
            formula="[Hours] * 40",
        )
    ]
)
print(plan.changes[0].summary)
```

Async example:

```python
from pyquickbase import CreateField

plan = await app.preview_schema_changes(
    [
        CreateField(
            table="Job Tasks",
            label="Cost",
            type="numeric",
            formula="[Hours] * 40",
        )
    ]
)
print(plan.changes[0].summary)
```

Output:

```text
Add formula field "Cost" (numeric) to Job Tasks.
```

### `UpdateField`

Change a field's settings. Anything left as None stays as it is. Its type can't change.

```python
UpdateField(
    *,
    table: str,
    field: str | int,
    label: str | None = None,
    choices: tuple[str, ...] | None = None,
    required: bool | None = None,
    unique: bool | None = None,
    help_text: str | None = None,
    formula: str | None = None,
)
```

**Parameters**

- **`table`** `str`, required  
  The table, by name or ID.
- **`field`** `str | int`, required  
  The field, by label or ID.
- **`label`** `str | None`, optional  
  A new label.
- **`choices`** `tuple[str, ...] | None`, optional  
  A new list of options, replacing the old one.
- **`required`** `bool | None`, optional  
  Whether it must have a value.
- **`unique`** `bool | None`, optional  
  Whether each record must have a different value.
- **`help_text`** `str | None`, optional  
  New help text.
- **`formula`** `str | None`, optional  
  A new formula, for a formula field only.

Sync example:

```python
from pyquickbase import UpdateField

plan = app.preview_schema_changes(
    [
        UpdateField(
            table="Job Tasks",
            field="Status",
            choices=("Open", "Done", "Blocked"),
        )
    ]
)
choices = plan.changes[0].settings["choices"]
print(choices.before, choices.after)
```

Async example:

```python
from pyquickbase import UpdateField

plan = await app.preview_schema_changes(
    [
        UpdateField(
            table="Job Tasks",
            field="Status",
            choices=("Open", "Done", "Blocked"),
        )
    ]
)
choices = plan.changes[0].settings["choices"]
print(choices.before, choices.after)
```

Output:

```text
['Open', 'Done'] ['Open', 'Done', 'Blocked']
```

### `DeleteField`

Delete a field and all its values.

```python
DeleteField(*, table: str, field: str | int)
```

**Parameters**

- **`table`** `str`, required  
  The table, by name or ID.
- **`field`** `str | int`, required  
  The field, by label or ID.

Sync example:

```python
from pyquickbase import DeleteField

plan = app.preview_schema_changes(
    [DeleteField(table="Job Tasks", field="Hours")]
)
print(plan.changes[0].warnings[0])
```

Async example:

```python
from pyquickbase import DeleteField

plan = await app.preview_schema_changes(
    [DeleteField(table="Job Tasks", field="Hours")]
)
print(plan.changes[0].warnings[0])
```

Output:

```text
3 records lose a value in it.
```

## Relationships

For the full picture, see [Changing the schema](https://matthew-williams-solutions.github.io/pyquickbase/guides/schema/).

### `CreateRelationship`

Link a child table to a parent, with a new field on the child that holds the parent's Record ID#. A plan treats a link with this foreign key as already made.

```python
CreateRelationship(
    *, child_table: str, parent_table: str, foreign_key: str
)
```

**Parameters**

- **`child_table`** `str`, required  
  The child table, by name or ID, such as `Tasks`.
- **`parent_table`** `str`, required  
  The parent table, such as `Projects`.
- **`foreign_key`** `str`, required  
  The label of the new field on the child, such as `Project`.

Sync example:

```python
from pyquickbase import CreateRelationship

plan = app.preview_schema_changes(
    [
        CreateRelationship(
            child_table="Job Tasks",
            parent_table="Job Projects",
            foreign_key="Project",
        )
    ]
)
print(plan.changes[0].summary)
```

Async example:

```python
from pyquickbase import CreateRelationship

plan = await app.preview_schema_changes(
    [
        CreateRelationship(
            child_table="Job Tasks",
            parent_table="Job Projects",
            foreign_key="Project",
        )
    ]
)
print(plan.changes[0].summary)
```

Output:

```text
Link Job Tasks to Job Projects through a new field "Project" on Job Tasks.
```

### `CreateLookupField`

Show a parent's field on each child record.

```python
CreateLookupField(
    *,
    child_table: str,
    foreign_key: str,
    parent_field: str | int,
    label: str | None = None,
)
```

**Parameters**

- **`child_table`** `str`, required  
  The child table, by name or ID.
- **`foreign_key`** `str`, required  
  The label of the relationship's foreign key on the child.
- **`parent_field`** `str | int`, required  
  The parent's field to show, by label or ID.
- **`label`** `str | None`, optional  
  The lookup's label. Quickbase names one `<foreign key> - <field>` without it.

Sync example:

```python
from pyquickbase import (
    CreateLookupField,
    CreateRelationship,
)

plan = app.preview_schema_changes(
    [
        CreateRelationship(
            child_table="Job Tasks",
            parent_table="Job Projects",
            foreign_key="Project",
        ),
        CreateLookupField(
            child_table="Job Tasks",
            foreign_key="Project",
            parent_field="Name",
        ),
    ]
)
print(plan.changes[1].summary)
```

Async example:

```python
from pyquickbase import (
    CreateLookupField,
    CreateRelationship,
)

plan = await app.preview_schema_changes(
    [
        CreateRelationship(
            child_table="Job Tasks",
            parent_table="Job Projects",
            foreign_key="Project",
        ),
        CreateLookupField(
            child_table="Job Tasks",
            foreign_key="Project",
            parent_field="Name",
        ),
    ]
)
print(plan.changes[1].summary)
```

Output:

```text
Show "Name" from Job Projects on each Job Tasks record, as "Project - Name".
```

### `CreateSummaryField`

Total, average, or count a child's records on each parent record.

```python
CreateSummaryField(
    *,
    child_table: str,
    foreign_key: str,
    label: str,
    function: SummaryFunction = "sum",
    child_field: str | int | None = None,
    where: str | None = None,
)
```

**Parameters**

- **`child_table`** `str`, required  
  The child table, by name or ID.
- **`foreign_key`** `str`, required  
  The label of the relationship's foreign key on the child.
- **`label`** `str`, required  
  The label of the new field on the parent.
- **`function`** `SummaryFunction`, default `'sum'`  
  How to combine the values, such as `sum`. `count` counts the child records, and takes no `child_field`.
- **`child_field`** `str | int | None`, optional  
  The child's field to combine, by label or ID.
- **`where`** `str | None`, optional  
  Only combine the child records that match, in Quickbase's query language with field IDs, such as `{7.EX.true}`. The plan checks it against the child's fields, as Quickbase saves one it can't use and the summary is then always empty.

Sync example:

```python
from pyquickbase import (
    CreateRelationship,
    CreateSummaryField,
)

plan = app.preview_schema_changes(
    [
        CreateRelationship(
            child_table="Job Tasks",
            parent_table="Job Projects",
            foreign_key="Project",
        ),
        CreateSummaryField(
            child_table="Job Tasks",
            foreign_key="Project",
            label="Total Hours",
            child_field="Hours",
        ),
    ]
)
print(plan.changes[1].summary)
```

Async example:

```python
from pyquickbase import (
    CreateRelationship,
    CreateSummaryField,
)

plan = await app.preview_schema_changes(
    [
        CreateRelationship(
            child_table="Job Tasks",
            parent_table="Job Projects",
            foreign_key="Project",
        ),
        CreateSummaryField(
            child_table="Job Tasks",
            foreign_key="Project",
            label="Total Hours",
            child_field="Hours",
        ),
    ]
)
print(plan.changes[1].summary)
```

Output:

```text
Add "Total Hours" to Job Projects: the sum of "Hours" on its Job Tasks records.
```

### `DeleteRelationship`

Unlink a child table from its parent. Quickbase also deletes the relationship's lookups and summaries, and keeps the foreign key field, with its values, as a plain number.

```python
DeleteRelationship(
    *, child_table: str, foreign_key: str
)
```

**Parameters**

- **`child_table`** `str`, required  
  The child table, by name or ID.
- **`foreign_key`** `str`, required  
  The label of the relationship's foreign key on the child.

Sync example:

```python
from pyquickbase import DeleteRelationship

change = DeleteRelationship(
    child_table="Job Tasks", foreign_key="Project"
)
plan = app.preview_schema_changes([change])
print(plan.changes[0].summary)
```

Async example:

```python
from pyquickbase import DeleteRelationship

change = DeleteRelationship(
    child_table="Job Tasks", foreign_key="Project"
)
plan = await app.preview_schema_changes([change])
print(plan.changes[0].summary)
```

Output:

```text
Unlink Job Tasks from Job Projects.
```

### `SummaryFunction`

How [`CreateSummaryField`](https://matthew-williams-solutions.github.io/pyquickbase/reference/schema-changes/#CreateSummaryField) combines a child's values. `count` counts the child records.

```python
SummaryFunction = Literal[
    "sum",
    "avg",
    "max",
    "min",
    "std_dev",
    "count",
    "distinct_count",
    "combined_text",
    "combined_user",
]
```

## Plans and results

For the full picture, see [Changing the schema](https://matthew-williams-solutions.github.io/pyquickbase/guides/schema/).

### `SchemaPlan`

What a list of changes would do to an app, checked against it as it is now.

Pass it to [`App.apply_schema_changes()`](https://matthew-williams-solutions.github.io/pyquickbase/reference/app/#apply_schema_changes), which refuses it if the app has changed since.

**Attributes**

- **`app_id`** `str`  
  The app it's for.
- **`changes`** `tuple[PlannedChange, ...]`  
  What each change would do, in order.
- **`fingerprint`** `str`  
  A digest of everything the plan was checked against.
- **`confirmation`** `str`  
  From a `preview_schema_changes` operation, what its `apply_schema_changes` operation needs. Empty otherwise.
- **`blockers`** `tuple[str, ...]`  
  Every reason the plan can't be applied, from all its changes.
- **`warnings`** `tuple[str, ...]`  
  Everything its changes would also do or affect.
- **`has_changes`** `bool`  
  Whether applying it would change anything.

Sync example:

```python
from pyquickbase import CreateField

change = CreateField(
    table="Job Tasks", label="Code", type="date"
)
plan = app.preview_schema_changes([change])
print(plan.has_changes)
print(plan.blockers[0])
```

Async example:

```python
from pyquickbase import CreateField

change = CreateField(
    table="Job Tasks", label="Code", type="date"
)
plan = await app.preview_schema_changes([change])
print(plan.has_changes)
print(plan.blockers[0])
```

Output:

```text
False
Job Tasks already has a field labeled "Code" (text), so it can't be added. Choose another label, or change that field.
```

### `PlannedChange`

What one change would do, checked against the app as it is now.

**Attributes**

- **`change`** `SchemaChange`  
  The change asked for.
- **`status`** `Literal['ready', 'already_done', 'blocked']`  
  `ready` if it would change the app, `already_done` if the app already has it, and `blocked` if it can't be applied, which stops the whole plan.
- **`risk`** `Risk`  
  `additive`, `modifying`, or `destructive`.
- **`summary`** `str`  
  What it does, in a sentence, such as `Add field "Due" (date) to Tasks.`
- **`settings`** `Mapping[str, SettingChange]`  
  What it would set, as `{setting: SettingChange}`.
- **`warnings`** `tuple[str, ...]`  
  What it would also do or affect, such as formulas that use a deleted field.
- **`blockers`** `tuple[str, ...]`  
  Why it can't be applied.
- **`affected_records`** `int | None`  
  How many records it would affect, such as those that would lose a deleted field's values, if counted.
- **`link`** `str | None`  
  A Quickbase page to check it on, if there is one yet.
- **`policy_effects`** `tuple[str, ...]`  
  What the client's policy would do once the change is made: who can read and write a new field, and its classes, and which rules lose a deleted field or table. A rule it would leave matching nothing is also a blocker.

Sync example:

```python
from pyquickbase import UpdateField

change = UpdateField(
    table="Job Tasks", field="Code", unique=True
)
plan = app.preview_schema_changes([change])
planned = plan.changes[0]
print(planned.status, planned.risk)
```

Async example:

```python
from pyquickbase import UpdateField

change = UpdateField(
    table="Job Tasks", field="Code", unique=True
)
plan = await app.preview_schema_changes([change])
planned = plan.changes[0]
print(planned.status, planned.risk)
```

Output:

```text
already_done modifying
```

### `SettingChange`

One setting before and after a change, with a setting that isn't there as None.

**Attributes**

- **`before`** `Any`  
  The setting now.
- **`after`** `Any`  
  What the change would set.

Sync example:

```python
from pyquickbase import UpdateField

plan = app.preview_schema_changes(
    [UpdateField(table="Job Tasks", field=6, label="Task")]
)
setting = plan.changes[0].settings["label"]
print(setting.before, "->", setting.after)
```

Async example:

```python
from pyquickbase import UpdateField

plan = await app.preview_schema_changes(
    [UpdateField(table="Job Tasks", field=6, label="Task")]
)
setting = plan.changes[0].settings["label"]
print(setting.before, "->", setting.after)
```

Output:

```text
Title -> Task
```

### `SchemaChangeResult`

What applying a plan did.

**Attributes**

- **`applied`** `tuple[PlannedChange, ...]`  
  The changes made, in order.
- **`already_done`** `tuple[PlannedChange, ...]`  
  The changes the app already had.
- **`failed`** `PlannedChange | None`  
  The change that failed, if one did. The ones after it weren't attempted.
- **`not_attempted`** `tuple[PlannedChange, ...]`  
  The changes after one that failed.
- **`error`** `str | None`  
  Why it failed, if it did.

Sync example:

```python
from pyquickbase import CreateField, UpdateField

plan = app.preview_schema_changes(
    [
        CreateField(
            table="Job Tasks", label="Crew", type="text"
        ),
        UpdateField(
            table="Job Tasks", field="Code", unique=True
        ),
    ]
)
result = app.apply_schema_changes(plan)
print(len(result.applied), len(result.already_done))
```

Async example:

```python
from pyquickbase import CreateField, UpdateField

plan = await app.preview_schema_changes(
    [
        CreateField(
            table="Job Tasks", label="Crew", type="text"
        ),
        UpdateField(
            table="Job Tasks", field="Code", unique=True
        ),
    ]
)
result = await app.apply_schema_changes(plan)
print(len(result.applied), len(result.already_done))
```

Output:

```text
1 1
```

### `Risk`

How much a planned change can lose: `additive` only adds, `modifying` changes what's there, and `destructive` deletes data or fields.

```python
Risk = Literal["additive", "modifying", "destructive"]
```

## Any change

For the full picture, see [Using pyquickbase from an AI agent](https://matthew-williams-solutions.github.io/pyquickbase/agents/).

### `SchemaChange`

Any one of the changes on this page, as [`App.preview_schema_changes()`](https://matthew-williams-solutions.github.io/pyquickbase/reference/app/#preview_schema_changes) takes them.

```python
SchemaChange = (
    CreateTable
    | UpdateTable
    | DeleteTable
    | CreateField
    | UpdateField
    | DeleteField
    | CreateRelationship
    | CreateLookupField
    | CreateSummaryField
    | DeleteRelationship
)
```

### `SchemaAction`

The `action` of a change in the JSON form a model sends, such as `create_field`.

```python
SchemaAction = Literal[
    "create_table",
    "update_table",
    "delete_table",
    "create_field",
    "update_field",
    "delete_field",
    "create_relationship",
    "create_lookup_field",
    "create_summary_field",
    "delete_relationship",
]
```