Skip to content
This page View Markdown Open in ChatGPT Open in Claude

Data types

Every column type of the six databases goes through API Maker as plain JSON. The same values work in the generated APIs, the schema APIs and g.sys.db from every language : save, get by id, update, query.

In short

  • A vector is a list of numbers. A JSON column holds any JSON value.
  • Bytes are a Buffer object : { "type": "Buffer", "data": [1, 2, 3] }.
  • A shape goes in as GeoJSON, and comes back in the form of the driver of the database.
Vectors VECTOR of MySQL 9, MariaDB 11.7, SQL Server 2025 and Oracle 23ai, vector and halfvec of pgvector : a list of numbers.
JSON JSON of MySQL, SQL Server 2025 and Oracle 21c+, json and jsonb of PostgreSQL : objects, lists and plain values.
Bytes { "type": "Buffer", "data": [1, 2, 3] } both ways, or its JSON text.
Shapes GeoJSON in, the form of the driver out.
Schemas A vector column is [EType.number], a JSON column EType.string that takes objects and lists.
Vectors, JSON, Bytes, Shapes and Intervals Through One REST API, on Six Databases11 chapters and the transcript

What a value looks like

Kind Send Read back
Vector [0.12, 0.5, 0.33], or the text "[0.12, 0.5, 0.33]" [0.12, 0.5, 0.33]
JSON any JSON value : { "color": "blue" }, [1, "two"] the same value (MariaDB : its text, see below)
Bytes { "type": "Buffer", "data": [137, 80] } { "type": "Buffer", "data": [137, 80] }
Shape (MySQL, MariaDB) GeoJSON, or the form it is read in { "x": 1, "y": 2 } for a point, lists of points for lines and polygons
Shape (SQL Server) GeoJSON, or WKT : "POINT (3 4)" the object mssql reads : srid, points, figures, shapes
Interval (PostgreSQL) { "days": 1, "hours": 2 }, or "3 days 04:05:06" { "days": 1, "hours": 2 }
Interval (Oracle) { "days": 1, "hours": 2 }, { "years": 1, "months": 2 }, or "+3-04" the same object, every part named
Array (PostgreSQL) [1, 2, 3], [[1, 2], [3, 4]], [{ "a": 1 }] for jsonb[] the same list

Numbers in a vector are floats of 4 bytes in most databases : 0.1 comes back as 0.10000000149011612. Use FLOAT64 vectors of Oracle when every digit counts.

By database

MongoDB

  • Lists and objects are stored as they are.
  • A BSON vector (binary data of subtype 9, as the vector search of Atlas stores it) is read as its numbers : floats, signed bytes, or bits as 0 and 1.

MySQL and MariaDB

Type Read as
VECTOR(n) list of numbers
JSON the JSON value on MySQL ; the text on MariaDB, where JSON is a LONGTEXT
BINARY, VARBINARY, BLOB (all sizes), BIT Buffer
GEOMETRY, POINT, LINESTRING, POLYGON, the multi kinds, GEOMETRYCOLLECTION points { x, y }, lines and polygons as lists of them
DECIMAL text, every digit kept
BOOLEAN 1 or 0 (it is a TINYINT)
  • A point of the system 4326 is written from GeoJSON as longitude, then latitude : { "type": "Point", "coordinates": [20, 10] } is read as { "x": 20, "y": 10 }.
  • MariaDB UUID, INET4 and INET6 are texts.

PostgreSQL

Type Read as
json, jsonb the JSON value
arrays (integer[], text[], jsonb[], bytea[], vector(2)[]…) lists
vector, halfvec (pgvector) list of numbers
sparsevec (pgvector) { "dimensions": 5, "indices": [0, 2], "values": [1.5, 2] }, places counted from 0
bytea Buffer
interval { "days", "hours", "minutes", "seconds", "milliseconds" }, the parts it has
point, circle { "x", "y" }, { "x", "y", "radius" }
line, lseg, box, path, polygon, ranges, tsvector, inet, uuid, xml… the text of PostgreSQL
  • A sparsevec is written from all of its numbers ([1.5, 0, 2, 0, 0]), from the object it is read as, or from its text {1:1.5,3:2}/5.

SQL Server

Type Read as
json (2025) the JSON value
vector(n) (2025) list of numbers
binary, varbinary, image Buffer
geometry, geography the object mssql reads
bigint text : a number of JavaScript does not hold every bigint
hierarchyid Buffer
  • Without a schema, a list or an object sent to an nvarchar column is stored as its JSON text.
  • A timestamp (rowversion) is made by the server : leave it out of what you send.

Oracle

Type Read as
JSON (21c+) the JSON value
VECTOR (23ai), FLOAT32, FLOAT64, INT8 list of numbers
BOOLEAN (23ai) true, false
RAW, BLOB Buffer
INTERVAL DAY TO SECOND { "days", "hours", "minutes", "seconds", "fseconds" }, fseconds in nanoseconds
INTERVAL YEAR TO MONTH { "years", "months" }
CLOB, NCLOB, LONG, XMLTYPE text
  • A JSON text longer than 32767 bytes is sent as a CLOB, so a large document fits.
  • The client libraries of Oracle before 23.4 send vectors as text : API Maker reads that text into numbers all the same.
  • BFILE is not read nor written.

Schemas

Get schema makes a field of each column :

Column Field Takes
Vector embedding: [EType.number] a list of numbers
JSON details: EType.string objects and lists too : they are stored as their JSON, and read back as JSON
Bytes photo: EType.string a Buffer object
Shape, interval place: EType.string GeoJSON, an interval object, or their texts
PostgreSQL array tags: [EType.string] a list
  • Bytes, JSON, intervals and the large objects of Oracle (CLOB, XMLTYPE) get no maxLength : the length their catalog gives counts what the row stores, not characters of what you send. The database still refuses what does not fit.
  • A list given to a text column which is not a JSON column is still refused : <field> should be array in schema as data is in array.
  • Schemas made before these types were supported keep their old fields. Generate them again, or change the field by hand.

Save, read and update over HTTP

A row of MySQL with a vector, JSON and bytes
POST /api/schema/admin/mysql_8/inventory/products/save-single-or-multiple
{ "id": 1, "name": "Mug", "embedding": [0.12, 0.5, 0.33],
  "details": { "color": "blue", "sizes": [250, 350] },
  "photo": { "type": "Buffer", "data": [137, 80, 78, 71] } }

GET /api/schema/admin/mysql_8/inventory/products/get-by-id/1
→ { "id": 1, "name": "Mug", "embedding": [0.12, 0.5, 0.33],
    "details": { "color": "blue", "sizes": [250, 350] },
    "photo": { "type": "Buffer", "data": [137, 80, 78, 71] } }

PUT /api/schema/admin/mysql_8/inventory/products/update-by-id/1
{ "embedding": [0.2, 0.1, 0.9], "details": ["clearance", 2026] }

The generated APIs take the same body at /api/gen/admin/mysql_8/inventory/products/….

From your code

g.sys.db sends the same values in every language : lists, maps and the Buffer object as a plain map. See save single or multiple for the code of all eight languages.