Bob Browning's blog

In a previous post I showed how an AI system (ChatGPT) was an easy way of converting an SQL Schema to a JSON-Schema. It was very successful and could save hours of donkey-work when dealing with a legacy database. I am coding in TypeScript so I asked for a TypeScript schema as well. It also did that perfectly.

I asked Gemini to do the same job.

The two JSON Schemas follow. I have omitted fields to keep it concise. For the full schema on the ChatGPT case see the previous post.

ChatGPT

{
  "type": "object",
  "properties": {
    "id": {
      "type": "integer",
      "description": "Primary key, unsigned integer"
    },
    "createdAt": {
      "type": "integer",
      "description": "Timestamp of when the record was created",
      "nullable": true
    },
    "updatedAt": {
      "type": "integer",
      "description": "Timestamp of when the record was last updated",
      "nullable": true
    },

   .........................


    "data": {
      "type": "string",
      "description": "The full audit data, stored as longtext"
    }
  },
  "required": ["id", "data"],
  "additionalProperties": false
}

Gemini

{
  "$schema": "http://json-schema.org/draft-07/schema#",
  "title": "Audit",
  "description": "Schema for audit records.",
  "type": "object",
  "properties": {
    "id": {
      "type": "integer",
      "description": "Unique identifier for the audit record."
    },
    "createdAt": {
      "type": "integer",
      "description": "Timestamp of creation (Unix epoch milliseconds).",
      "nullable": true // Important for optional fields in JSON Schema
    },
    "updatedAt": {
      "type": "integer",
      "description": "Timestamp of last update (Unix epoch milliseconds).",
       "nullable": true
    },
 
..................................

    "data": {
      "type": "string",
      "description": "Detailed data related to the audit event.",
       "nullable": true
    }
  },
  "required": [
    "id"
  ],
  "additionalProperties": false // Prevents extra properties not defined in the schema
}

By and large the Gemini results are better. It has added $schema, description and title at the top level. There is only one difference that would materially effect the use of the schema. ChatGPT made the “data” field mandatory.

But here it gets weird! Check back to the original post. ChatGPT didn’t make the data field mandatory on the previous test. So I asked ChatGPT what happened. Here is its response:

The issue with not picking up that the data field was mandatory in the previous response stems from an oversight. Since data is a longtext in the SQL schema (which doesn’t allow null), it should indeed be required, just like the id. My earlier response would have missed that detail, and I appreciate your patience.

This is not true. LONGTEXT fields can be NULL. I asked ChatGTP this question this morning and it confirmed that it knew that. So it made up a complete lie to justify a mistake. How very human!

So it is Gemini for me in future…