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. A vector is a list of numbers, a JSON column holds any JSON value, bytes are a Buffer object, a shape is GeoJSON or the form the driver of the database reads it in. The same values work in the generated APIs, the schema APIs and g.sys.db from every language : save, get by id, update, query.
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.

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.