# Best Practices for Adding Fields to Persistent Records

**URL:** <https://forum.inductiveautomation.com/t/best-practices-for-adding-fields-to-persistent-records/62673>\
**Category:** Module Development\
**Created:** [August 4, 2022, 1:59pm UTC](https://forum.inductiveautomation.com/t/best-practices-for-adding-fields-to-persistent-records/62673 "2022-08-04T13:59:37Z")\
**Posts on this page:** 12\
**Page:** 1

<div class="post-metadata">

**Author:** ![nicholas.robinson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nicholas.robinson/32/22188_2.png) [@nicholas.robinson](https://forum.inductiveautomation.com/u/nicholas.robinson)\
**Post date:** [August 4, 2022, 1:59pm UTC](https://forum.inductiveautomation.com/t/best-practices-for-adding-fields-to-persistent-records/62673/1 "2022-08-04T13:59:37Z")

</div>

When making a persistent record with lets say 10 fields, when that module is first installed on the gateway it will create a SQLITE table that is 10 columns. If someone else who uses the module and then says hey, it’d be nice to have these additional config fields too, can you please add them?

So then you add 1 field (for example) and the new module now has 11 fields. If you then try to install that new version of the module on the same gateway it will throw an error because the column for the new field doesn’t exist in the SQLITE table.

What seems like it needs to happen is this:

1. check if the table with that name already exists, if not make it
2. if the table exists, read in its values then drop the table
3. Create the table and pass in the old values plus any for the new field

I’d like to hear what everyone’s best practices are for handling this and if possible see some code examples.

This sort of functionality is not in the SDK examples so if we can understand how best to handle this, I’d like to make a pull request to add it since it seems like a pretty important feature to have assuming that modules will change over time.

Thanks,

Nick

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [August 4, 2022, 2:03pm UTC](https://forum.inductiveautomation.com/t/best-practices-for-adding-fields-to-persistent-records/62673/2 "2022-08-04T14:03:35Z")

</div>

When your gateway hook’s `.setup()` method is called, it should be calling:

```auto
context.getSchemaUpdater().updatePersistentRecords(SomePersistentRecord.META);

```

That will add new columns to your tables. I don’t think it will **change** any existing columns.

---

<div class="post-metadata">

**Author:** ![Kevin.Herron](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kevin.herron/32/7259_2.png) [@Kevin.Herron](https://forum.inductiveautomation.com/u/Kevin.Herron)\
**Post date:** [August 4, 2022, 2:08pm UTC](https://forum.inductiveautomation.com/t/best-practices-for-adding-fields-to-persistent-records/62673/3 "2022-08-04T14:08:05Z")

</div>

What @pturmel said

> <https://github.com/inductiveautomation/ignition-sdk-examples/blob/ddf234b4a900f28835bf16a350faf265e39f93df/gateway-webpage/gateway-webpage-gateway/src/main/java/com/inductiveautomation/ignition/examples/hce/GatewayHook.java#L127-L128>

> <https://github.com/inductiveautomation/ignition-sdk-examples/blob/ddf234b4a900f28835bf16a350faf265e39f93df/gateway-webpage/gateway-webpage-gateway/src/main/java/com/inductiveautomation/ignition/examples/hce/GatewayHook.java#L160-L166>

---

<div class="post-metadata">

**Author:** ![nicholas.robinson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nicholas.robinson/32/22188_2.png) [@nicholas.robinson](https://forum.inductiveautomation.com/u/nicholas.robinson)\
**Post date:** [August 4, 2022, 2:12pm UTC](https://forum.inductiveautomation.com/t/best-practices-for-adding-fields-to-persistent-records/62673/4 "2022-08-04T14:12:56Z")

</div>

OK, I do see that but I did see an error raised when adding a new field. I will re-verify:

```auto
  private void verifySchema(GatewayContext context) {
      try {
          context.getSchemaUpdater().updatePersistentRecords(KafkaSettingsRecord.META);
      } catch (SQLException e) {
          log.error("Error verifying persistent record schemas for KafkaConnect records.", e);
      }
  }

```

The behavior I saw was after adding a new field, the module would install in a faulted state.

Nick

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [August 4, 2022, 2:14pm UTC](https://forum.inductiveautomation.com/t/best-practices-for-adding-fields-to-persistent-records/62673/5 "2022-08-04T14:14:26Z")

</div>

Are you sure you **just** added the one column? Did you assign an appropriate default value to that new column? When the module faulted, what showed up in the wrapper log?

---

<div class="post-metadata">

**Author:** ![nicholas.robinson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nicholas.robinson/32/22188_2.png) [@nicholas.robinson](https://forum.inductiveautomation.com/u/nicholas.robinson)\
**Post date:** [August 4, 2022, 2:26pm UTC](https://forum.inductiveautomation.com/t/best-practices-for-adding-fields-to-persistent-records/62673/6 "2022-08-04T14:26:01Z")

</div>

What was added was one field to specify minimum alarm priority:

```auto
  public static final EnumField<alarmPriorities> MinimumPriority =
          new EnumField<>(META, "MinimumPriority", alarmPriorities.class, SFieldFlags.SMANDATORY).
                  setDefault(alarmPriorities.Medium);

```

I will try to find the error, need to go back in the log a bit

Nick

---

<div class="post-metadata">

**Author:** ![nicholas.robinson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nicholas.robinson/32/22188_2.png) [@nicholas.robinson](https://forum.inductiveautomation.com/u/nicholas.robinson)\
**Post date:** [February 17, 2023, 5:42pm UTC](https://forum.inductiveautomation.com/t/best-practices-for-adding-fields-to-persistent-records/62673/7 "2023-02-17T17:42:14Z")

</div>

@pturmel yesterday when this topic came back to mind (still having issues with it) I realized I never posted what the error was. Here I am just doing a test to show what I am seeing:

**Current Persistent Record Columns**

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/8/5/85a4b51e97a9d3eeb09338ecc89d585e9f430346.png)

**Add one dummy field**

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/5/d/5d30d7f00c2dc58d6425f6c53529417b10387a51.png)

**Verify schema and maybe create are defined and called from gateway hook setup:**

```auto
    private void verifySchema(GatewayContext context) {
        try {
            context.getSchemaUpdater().updatePersistentRecords(KafkaSettingsRecord.META);
        } catch (SQLException e) {
            log.error("Error verifying persistent record schemas for KafkaConnect records.", e);
        }
    }

    public void maybeCreateKafkaSettings(GatewayContext context) {
        log.trace("Attempting to create Kafka Settings Record");

        try {
            KafkaSettingsRecord settingsRecord = context.getLocalPersistenceInterface().createNew(KafkaSettingsRecord.META);
            settingsRecord.setId(0L);
            settingsRecord.setBrokerList("127.0.0.1:9092");
            settingsRecord.setTagHistoryTopic("ignition-PROD-tag-history");
            settingsRecord.setEnabled(false);
            settingsRecord.setUseStoreAndFwd(false);
            settingsRecord.setUseSSL(false);
            settingsRecord.setAlarmsTopic("ignition-PROD-alarm-event");
            settingsRecord.setDefaultAlarmPriority();
            settingsRecord.setSource("");
            settingsRecord.setAlarmsEnabled(false);
            settingsRecord.setAuditTopic("ignition-PROD-audit-event");
            settingsRecord.setAuditEnabled(false);
            settingsRecord.setScriptingEnabled(false);
            settingsRecord.setEquipmentStateTopic("ignition-PROD-equipment-state");
            settingsRecord.setDispositionTopic("ignition-PROD-disposition");
            settingsRecord.setTest("Hey hey");

            // This doesn't override existing settings, it only sets the above if there is no existing settings

            context.getSchemaUpdater().ensureRecordExists(settingsRecord);
        } catch (Exception e) {
            log.error("Failed to establish Kafka Record exists", e);
        }
        log.trace("Kafka Settings Record Established");
    }

```

**After installing the updated module with the added field, the module status goes to faulted:**

![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/e/7/e7ca297477e80afa6dd6e77101aa9ec080887e4f.png)

There are 3 messages about it in the log:

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/2/7/271d28ad5a7a503b5bb640602d4ad93a51002bba.png)

The 1st one about `InternalDatabaseManager` is missing the test field.

The 2nd is is about non null constraint failed. I believe this may be the issue but not sure what is the correct action to make it non-null

```auto
org.sqlite.SQLiteException: [SQLITE_CONSTRAINT_NOTNULL] A NOT NULL constraint failed (NOT NULL constraint failed: KAFKASETTINGSRECORD.TEST)

```

Finally, the 3rd message is about the fact that it failed to update the record:

```auto
simpleorm.utils.SException$Jdbc: Preparing 'SELECT KafkaSettingsRecord.KafkaSettingsRecord_ID, KafkaSettingsRecord.Brokers, KafkaSettingsRecord.TagHistoryTopic, KafkaSettingsRecord.Enabled, KafkaSettingsRecord.UseStoreAndFwd, KafkaSettingsRecord.UseSSL, KafkaSettingsRecord.AlarmsTopic, KafkaSettingsRecord.MinimumPriority, KafkaSettingsRecord.Source, KafkaSettingsRecord.DispPath, KafkaSettingsRecord.SrcPath, KafkaSettingsRecord.AlarmsEnabled, KafkaSettingsRecord.AuditTopic, KafkaSettingsRecord.AuditEnabled, KafkaSettingsRecord.ScriptingEnabled, KafkaSettingsRecord.DispositionTopic, KafkaSettingsRecord.EquipmentStateTopic, KafkaSettingsRecord.Test FROM KafkaSettingsRecord WHERE KafkaSettingsRecord_ID = ? '

```

So the issue _seems_ to be about NOT NULL.

Nick

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [February 17, 2023, 5:45pm UTC](https://forum.inductiveautomation.com/t/best-practices-for-adding-fields-to-persistent-records/62673/8 "2023-02-17T17:45:38Z")

</div>

That makes sense, as any existing records would have nulls for the new column. The default only applies to new rows, not existing rows.

Basically, you cannot put a "not null" constraint on a new column.

{ This is pretty typical behavior for databases. }

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [February 17, 2023, 5:46pm UTC](https://forum.inductiveautomation.com/t/best-practices-for-adding-fields-to-persistent-records/62673/9 "2023-02-17T17:46:16Z")

</div>

[https://files.inductiveautomation.com/sdk/javadoc/ignition81/8.1.25/com/inductiveautomation/ignition/gateway/localdb/SchemaUpdater.html#updatePersistentRecords(com.inductiveautomation.ignition.gateway.localdb.persistence.RecordMeta...)](https://files.inductiveautomation.com/sdk/javadoc/ignition81/8.1.25/com/inductiveautomation/ignition/gateway/localdb/SchemaUpdater.html#updatePersistentRecords(com.inductiveautomation.ignition.gateway.localdb.persistence.RecordMeta...))

> This will update the internal database's underlying database schema to support the given records. If this is the first time a record has been registered, then the table representing it will be created. If the record has been changed from the last time it was seen, the table will be modified to support any new columns. **Note that new fields added to existing records that have the [`SFieldFlags.SMANDATORY`](https://files.inductiveautomation.com/sdk/javadoc/ignition81/8.1.25/simpleorm/dataset/SFieldFlags.html#SMANDATORY) flag (a.k.a. "NOT NULL") must have a default value, as existing records will be unable to set the new column to NULL.**

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [February 17, 2023, 5:47pm UTC](https://forum.inductiveautomation.com/t/best-practices-for-adding-fields-to-persistent-records/62673/10 "2023-02-17T17:47:18Z")

</div>

Are you sure that ever worked?

---

<div class="post-metadata">

**Author:** ![nicholas.robinson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nicholas.robinson/32/22188_2.png) [@nicholas.robinson](https://forum.inductiveautomation.com/u/nicholas.robinson)\
**Post date:** [February 17, 2023, 5:50pm UTC](https://forum.inductiveautomation.com/t/best-practices-for-adding-fields-to-persistent-records/62673/11 "2023-02-17T17:50:38Z")

</div>

I will modify to the following and re-test:

```auto
public static final StringField testTest = new StringField(META, "Test", SFieldFlags.SMANDATORY).setDefault("Hey hey");

```

Thanks,

Nick

---

<div class="post-metadata">

**Author:** ![nicholas.robinson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nicholas.robinson/32/22188_2.png) [@nicholas.robinson](https://forum.inductiveautomation.com/u/nicholas.robinson)\
**Post date:** [February 17, 2023, 5:57pm UTC](https://forum.inductiveautomation.com/t/best-practices-for-adding-fields-to-persistent-records/62673/12 "2023-02-17T17:57:10Z")

</div>

@paul-griffith you are right, thanks for the advice. Setting the default made it work:

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/2/9/293b7d93ad7aae90a11620a1646b583a45eafa60.png)

This is a big deal for modules that evolve over time and need to be installed smoothly. Much appreciated!

For anyone else who may see this in the future, the confusion I had is believing the default was set by this:

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/2/7/27cdc66c8bea04dcc7bc303d4c5271385e930e3e.png)

It had to be set on the actual settings record.

Thanks,

Nick