> ## Documentation Index
> Fetch the complete documentation index at: https://powersync-docs-coolify-scaling-note.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# Types

> How Postgres, MongoDB, MySQL, SQL Server and Convex types map to PowerSync's SQLite-based sync column definitions.

The supported client-side SQLite types are:

1. `null`
2. `integer`: a 64-bit signed integer
3. `real`: a 64-bit floating point number
4. `text`: A UTF-8 text string
5. `blob`: Binary data

## <Icon icon="elephant" iconType="solid" size={32} /> Postgres Type Mapping

Postgres types are mapped to SQLite types as follows:

| Postgres Data Type | PowerSync / SQLite Column Type | Notes |
| - | - | - |
| `text`, `varchar` | `text` | |
| `int2`, `int4`, `int8` | `integer` | |
| `numeric` / `decimal` | `text` | These types have arbitrary precision in Postgres, so can only be represented accurately as text in SQLite |
| `bool` | `integer` | `1` for true, `0` for false. There is no dedicated boolean data type in SQLite. |
| `float4`, `float8` | `real` | |
| `enum` | `text` | |
| `uuid` | `text` | |
| `timestamptz` | `text` | Format: `YYYY-MM-DD hh:mm:ss.sssZ`. This is compatible with ISO8601 and SQLite's functions. Precision matches the precision used in Postgres. `-infinity` becomes `0000-01-01 00:00:00Z` and `infinity` becomes `9999-12-31 23:59:59Z`. |
| `timestamp` | `text` | Format: `YYYY-MM-DD hh:mm:ss.sss`. In most cases, timestamptz should be used instead. `-infinity` becomes `0000-01-01 00:00:00` and `infinity` becomes `9999-12-31 23:59:59`. |
| `date`, `time` | `text` | |
| `json`, `jsonb` | `text` | `json` and `jsonb` values are treated as `text` values in their serialized representation. [JSON functions and operators](/sync/supported-sql#operators) operate directly on these `text` values. |
| `interval` | `text` | |
| `macaddr` | `text` | |
| `inet` | `text` | |
| `bytea` | `blob` | Cannot sync directly to client — convert to hex or base64 first. See [Operators & Functions](/sync/supported-sql). |
| `geometry` (PostGIS) | `text` | Hex string of the binary data. Use the [ST functions](/sync/supported-sql#functions) to convert to other formats |
| Arrays | `text` | JSON array. |
| `DOMAIN` types | `text` / depends | Depending on [compatibility options](/sync/advanced/compatibility#custom_postgres_types), inner type or raw wire representation (legacy). |
| Custom types | `text` | Depending on [compatibility options](/sync/advanced/compatibility#custom_postgres_types), JSON object or raw wire representation (legacy). |
| (Multi-)ranges | `text` | Depending on [compatibility options](/sync/advanced/compatibility#custom_postgres_types), JSON object (array for multi-ranges) or raw wire representation (legacy). |

<Note>
  Binary data can be selected in sync queries, but cannot be used as a parameter. To sync binary columns/fields to clients, convert them to a hex or base64 representation using the relevant [functions](/sync/supported-sql#functions).
</Note>

## <Icon icon="database" iconType="solid" size={32} /> Convex Type Mapping

<Warning>
  The Convex replicator is currently released as an [experimental feature](/resources/feature-status). APIs and
  behavior may change, and we can't yet guarantee continued support or long-term stability.
</Warning>

Convex values are mapped to SQLite types as follows:

| Convex Type | TS/JS Type | PowerSync / SQLite Column Type | Notes |
| - | - | - | - |
| `Id` | `string` | `text` | Convex document IDs are exposed as `_id` and can be synced as `text`. For synced client tables, use client-side ID mapping with a stable UUID column as `id` instead of relying on Convex-generated `_id` values. |
| `Null` | `null` | `null` | |
| `Int64` | `base-10 string` | `text` | Cast to `INTEGER` in Sync Streams when you want to sync the value as a SQLite integer. |
| `Float64` | `number` | `real` | |
| `Boolean` | `boolean` | `integer` | `1` for true, `0` for false. There is no dedicated boolean data type in SQLite. |
| `String` | `string` | `text` | |
| `Bytes` | `base64 string` | `text` | Decode from base64 in your app if you need binary data on the client. |
| `Array` | `Array` | `text` | Converted to a JSON string. |
| `Object` | `Object` | `text` | Converted to a JSON string. |
| `Record` | `Record` | `text` | Converted to a JSON string. |

* Convex documents are converted to a flat list of columns, one column per top-level field.
* Nested objects and arrays are converted to JSON, and [JSON functions and operators](/sync/supported-sql#operators) can be used to query them in Sync Streams or on the client-side SQLite database.
* Cast Convex `Int64` fields to `INTEGER` in Sync Streams when you want SQLite integer values on the client, for example `CAST(an_int64_column AS INTEGER) AS an_int64_column`.

## <Icon icon="leaf" iconType="solid" size={32} /> MongoDB Type Mapping

MongoDB types are mapped to SQLite types as follows:

| BSON Type | PowerSync / SQLite Column Type | Notes |
| - | - | - |
| `String` | `text` | |
| `Int`, `Long` | `integer` | |
| `Double` | `real` | |
| `Decimal128` | `text` | |
| `Object` | `text` | Converted to a JSON string |
| `Array` | `text` | Converted to a JSON string |
| `ObjectId` | `text` | Lower-case hex string |
| `UUID` | `text` | Lower-case hex string |
| `Boolean` | `integer` | `1` for true, `0` for false. There is no dedicated boolean data type in SQLite. |
| `Date` | `text` | Format: `YYYY-MM-DD hh:mm:ss.sssZ` |
| `Null` | `null` | |
| `Binary` | `blob` | Cannot sync directly to client — convert to hex or base64 first. See [Operators & Functions](/sync/supported-sql). |
| Regular Expression | `text` | JSON text in the format `{"pattern":"...","options":"..."}` |
| `Timestamp` | `integer` | Converted to a 64-bit integer |
| `Undefined` | `null` | |
| `DBPointer` | `text` | JSON text in the format `{"collection":"...","oid":"...","db":"...","fields":...}` |
| `JavaScript` | `text` | JSON text in the format `{"code": "...", "scope": ...}` |
| `Symbol` | `text` | |
| `MinKey`, `MaxKey` | `null` | |

* Data is converted to a flat list of columns, one column per top-level field in the MongoDB document.
* Special BSON types are converted to plain SQLite alternatives. For example, `ObjectId`, `Date`, `UUID` are all converted to a plain `TEXT` column.
* Nested objects and arrays are converted to JSON, and [JSON functions and operators](/sync/supported-sql#operators) can be used to query them (in sync queries or in client-side SQLite statements).
* Binary data nested in objects or arrays is not supported.

<Note>
  Binary data can be selected in sync queries, but cannot be used as a parameter. To sync binary columns/fields to clients, convert them to a hex or base64 representation using the relevant [functions](/sync/supported-sql#functions).
</Note>

## <Icon icon="dolphin" iconType="solid" size={32} /> MySQL Type Mapping

<Note>MySQL support is currently in a [Beta release](/resources/feature-status).</Note>

MySQL types are mapped to SQLite types as follows:

| MySQL Data Type | PowerSync / SQLite Column Type | Notes |
| - | - | - |
| `tinyint`, `smallint`, `mediumint`, `bigint`, `integer`, `int` | `integer` | |
| `numeric`, `decimal` | `text` | |
| `bool`, `boolean` | `integer` | `1` for true, `0` for false. There is no dedicated boolean data type in SQLite. |
| `float`, `double`, `real` | `real` | |
| `enum` | `text` | |
| `set` | `text` | Converted to JSON array |
| `char`, `varchar` | `text` | |
| `tinytext`, `text`, `mediumtext`, `longtext` | `text` | |
| `timestamp` | `text` | ISO 8601 format: `YYYY-MM-DDTHH:mm:ss.sssZ` |
| `date` | `text` | Format: `YYYY-MM-DD` |
| `time`, `datetime` | `text` | ISO 8601 format: `YYYY-MM-DDTHH:mm:ss.sssZ` |
| `year` | `text` | |
| `json` | `text` | There is no dedicated JSON type in SQLite — JSON functions operate directly on text values. |
| `bit` | `blob` | \* See note below regarding syncing binary types |
| `binary`, `varbinary` | `blob` | |
| `image` | `blob` | |
| `geometry`, `geometrycollection` | `blob` | |
| `point`, `multipoint` | `blob` | |
| `linestring`, `multilinestring` | `blob` | |
| `polygon`, `multipolygon` | `blob` | |

<Note>
  Binary data can be selected in sync queries, but cannot be used as a parameter. To sync binary columns/fields to clients, convert them to a hex or base64 representation using the relevant [functions](/sync/supported-sql#functions).
</Note>

## <Icon icon="server" iconType="solid" size={32} /> SQL Server Type Mapping

<Note>SQL Server support is currently in a [Beta release](/resources/feature-status).</Note>

SQL Server types are mapped to SQLite types as follows:

| SQL Server Data Type | PowerSync / SQLite Column Type | Notes |
| - | - | - |
| `tinyint`, `smallint`, `int`, `bigint` | `integer` | |
| `numeric`, `decimal` | `text` | Numeric string |
| `float`, `real` | `real` | |
| `bit` | `integer` | |
| `money`, `smallmoney` | `text` | Numeric string |
| `xml` | `text` | |
| `char`, `nchar`, `ntext` | `text` | |
| `varchar`, `nvarchar`, `text` | `text` | |
| `uniqueidentifier` | `text` | |
| `timestamp` | `text` | ISO 8601 format: `YYYY-MM-DDTHH:mm:ss.sssZ` |
| `date` | `text` | Format: `YYYY-MM-DD` |
| `time` | `text` | Format: `HH:mm:ss.sss` |
| `datetime`, `datetime2`, `smalldatetime`, `datetimeoffset` | `text` | ISO 8601 format: `YYYY-MM-DDTHH:mm:ss.sssZ` |
| `json` | `text` | Only exists for Azure SQL Database and SQL Server 2025 |
| `geometry`, `geography` | `text` | `text` of JSON object describing the spatial data type |
| `binary`, `varbinary`, `image` | `blob` | \* See note below regarding binary types |
| `rowversion`, `timestamp` | `blob` | \* See note below regarding binary types |
| User Defined Types: `hiearchyid` | `blob` | \* See note below regarding binary types |

<Note>
  Binary data can be selected in sync queries, but cannot be used as a parameter. To sync binary columns/fields to clients, convert them to a hex or base64 representation using the relevant [functions](/sync/supported-sql#functions).
</Note>