# Data types

> Every column type of MongoDB, MySQL, MariaDB, PostgreSQL, SQL Server and Oracle through the APIs of API Maker - vectors, JSON, bytes, shapes, intervals and arrays as plain JSON, written and read the same way by generated APIs, schema APIs and g.sys.db in every language.

Source: https://docs.apimaker.dev/v4/docs/schema/data-types.html

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](https://docs.apimaker.dev/v4/docs/apis-all/schema-apis/auto-generated-schema-based-save-single-or-multiple-api.html#without-a-schema), the [schema APIs](https://docs.apimaker.dev/v4/docs/apis-all/schema-apis/auto-generated-schema-based-save-single-or-multiple-api.html) 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](https://docs.apimaker.dev/v4/docs/schema/schema.html) 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**

```text
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](https://docs.apimaker.dev/v4/docs/apis-all/schema-apis/auto-generated-schema-based-save-single-or-multiple-api.html#without-a-schema) 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](https://docs.apimaker.dev/v4/examples/sys/db/saveSingleOrMultiple.html#save-vectors-json-and-bytes) for the code of all eight languages.

## Related

- [Table schema](https://docs.apimaker.dev/v4/docs/schema/schema.html) · [Connection strings](https://docs.apimaker.dev/v4/docs/Database-connection-string/mysql-connection-strings.html) · [Save single or multiple](https://docs.apimaker.dev/v4/examples/sys/db/saveSingleOrMultiple.html)