Table of Contents

Native Types (Vendor-Specific)

The Right Starting Point

Velocity is designed around the schema-first philosophy: you define your data model once using Velocity's portable DataTypes and let Velocity translate them into optimal native types for every supported database. That approach gives you full portability and the complete Velocity read/write API at every step.

However, the real world is not always schema-first. You may need to integrate with a database that already exists — one designed by someone else, years ago, with types that are entirely native to a single vendor. When you call LoadSchema against such a database, Velocity reads the vendor catalog and maps every column it finds into the closest Velocity DataType. For the common types, this works seamlessly. For the exotic ones it is a best effort, and you need to understand where the cracks are.

Important

If you are starting a new project and have a choice, use schema-first. LoadSchema is a good starting point for understanding an existing database, but it should be the beginning of your work with Velocity — not the end. Review the mapped schema, refine the types, and move toward a portable model that Velocity can manage fully.

What LoadSchema Does

When Velocity loads a schema it reads the vendor's system catalog (e.g. INFORMATION_SCHEMA, DBC.ColumnsV, Oracle's ALL_TAB_COLUMNS) and maps each native column type to a Velocity DataType. Types that Velocity recognizes are mapped to the appropriate portable type. Types that have no reasonable mapping are represented as Unsupported.

Once the schema is in memory you can:

The key limitation is this: reading almost always works; writing does not always work. Many vendor-specific types have no implicit cast from the parameter type that Velocity sends. For those columns you must use raw SQL via RunSql to insert or update data.

Type Mappings by Database

The tables below show every native type covered by Velocity's LoadSchema method, what Velocity maps it to, and whether you can write values to that column through the Velocity API.

Legend

  • ✅ Full support — read and write through the Velocity API
  • ⚠️ Read only — Retrieve works; AddRecord/UpdateRecord require raw SQL
  • 📖 Auto-generated — managed by the database; cannot be set at all

DB2

Native Type Velocity Type .NET Type Write Support
BIGINT Long long ✅
BLOB, BINARY(n), VARBINARY(n) Blob byte[] ✅
BOOLEAN Boolean bool ✅
CHAR(n), CHARACTER(n), GRAPHIC(n), NCHAR(n) Char string ✅
CLOB, DBCLOB, NCLOB Clob string ✅
DATE Date DateOnly ✅
DB2GSE.ST_GEOMETRY Geometry Geometry ✅
DECIMAL, NUMERIC, DECFLOAT Decimal decimal ✅
DOUBLE, FLOAT Double double ✅ (DB2 FLOAT is double-precision)
INTEGER, INT Integer int ✅
JSON Clob string ✅ (requires Unicode database codepage)
LONG VARCHAR Clob string ✅ (deprecated but still accepted)
REAL Float float ✅
SMALLINT Short short ✅
TIME Time TimeOnly ✅
TIMESTAMP DateTime DateTime ✅ (DB2 TIMESTAMP has no timezone; use DateTime)
VARCHAR(n), VARGRAPHIC(n), NVARCHAR(n) VarChar string ✅
XML Clob string ✅ (send as XML string)

DB2 note: The Timestamp Velocity type (timezone-aware) is not supported by DB2. If your schema uses DataType.Timestamp, DB2 falls back to TIMESTAMP (no timezone). See Datasource Limitations for details.

DB2 — Interval: DB2 has no native interval column type. Velocity stores DataType.Interval columns as BIGINT (100-nanosecond ticks). LoadSchema maps any BIGINT column to Long, so an interval column created by Velocity will be loaded back as Long. Use schema-first to preserve the Interval type across the lifecycle.

MySQL

Native Type Velocity Type .NET Type Write Support
bigint Long long ✅
binary, varbinary Blob byte[] ✅
bit, bool Boolean bool ✅
char Char string ✅
date Date DateOnly ✅
datetime DateTime DateTime ✅
decimal, numeric Decimal decimal ✅
double Double double ✅
enum, set VarChar string ✅ (send a valid member value as string)
float Float float ✅
geometry, point, linestring, polygon Geometry Geometry ✅
int, integer, mediumint Integer int ✅
multipoint, multilinestring, multipolygon, geometrycollection Geometry Geometry ✅
smallint Short short ✅
text, mediumtext, longtext, json Clob string ✅
time Time TimeOnly ✅
timestamp Timestamp DateTimeOffset ✅
tinyblob, blob, mediumblob, longblob Blob byte[] ✅
tinyint Byte byte ✅
varchar, tinytext VarChar string ✅
year Short short ✅ (send the four-digit year as a short)

MySQL — Interval: MySQL has no native interval column type. Velocity stores DataType.Interval columns as BIGINT (100-nanosecond ticks). LoadSchema maps any BIGINT column to Long, so an interval column created by Velocity will be loaded back as Long. Use schema-first to preserve the Interval type across the lifecycle.

Oracle

Native Type Velocity Type .NET Type Write Support
BFILE Blob byte[] ⚠️ External file locator — requires a pre-created DIRECTORY object; cannot insert as raw bytes
BINARY_DOUBLE, FLOAT(n) Double double ✅
BINARY_FLOAT Float float ✅
BLOB Blob byte[] ✅
BOOLEAN Boolean bool ✅ (Oracle 23c and later only)
CHAR(n), NCHAR(n) Char string ✅
CLOB, NCLOB, LONG Clob string ✅
DATE Date DateOnly ✅
INTERVAL DAY(0) TO SECOND(6) Time TimeOnly ✅
INTERVAL DAY(9) TO SECOND(6) Interval TimeSpan ✅
INTERVAL YEAR TO MONTH VarChar string ⚠️ Read as string representation; writing requires an explicit INTERVAL literal or CAST
NUMBER(p,s) Decimal decimal ✅
RAW(n) Blob byte[] ✅
ROWID, UROWID VarChar string ⚠️ Physical row addresses — system-managed, cannot be set freely
SDO_GEOMETRY Geometry Geometry ✅
TIMESTAMP(n) DateTime DateTime ✅
TIMESTAMP(n) WITH TIME ZONE Timestamp DateTimeOffset ✅
VARCHAR2(n), VARCHAR(n), NVARCHAR2(n) VarChar string ✅
XMLTYPE Clob string ✅ (send as XML string)

Oracle note: The BOOLEAN type was introduced in Oracle 23c. On earlier versions the column will be present in the loaded schema but writes will fail with a type error.

PostgreSQL

Native Type Velocity Type .NET Type Write Support
bigint Long long ✅
bit(n), bit varying(n) Blob byte[] ⚠️ Read as packed byte[]; writing requires explicit ::bit cast
boolean Boolean bool ✅
bytea Blob byte[] ✅
character(n) Char string ✅
character varying(n), citext VarChar string ✅
date Date DateOnly ✅
double precision Double double ✅
geography (PostGIS) Geography Geometry ✅
geometry (PostGIS) Geometry Geometry ✅
hstore VarChar string ⚠️ Read as key=>value string; explicit cast required to write
inet, cidr, macaddr, macaddr8 VarChar string ⚠️ Read as string representation; require explicit ::inet / ::cidr cast to write
int4range, int8range, numrange, tsrange, tstzrange, daterange VarChar string ⚠️ Read as string representation; explicit cast required to write
integer Integer int ✅
interval Interval TimeSpan ✅ Native INTERVAL type; maps directly via NpgsqlDbType.Interval
json, jsonb, xml Clob string ⚠️ PostgreSQL requires an explicit cast for untyped parameters; use raw SQL
money Decimal decimal ✅
name VarChar string ✅
numeric Decimal decimal ✅
oid Long long ✅
pg_lsn VarChar string ⚠️ Read as string; explicit cast required to write
point, line, lseg, box, path, polygon, circle VarChar string ⚠️ Native PG geometry (not PostGIS); read as string representation; explicit cast required to write
real Float float ✅
smallint Short short ✅
text Clob string ✅
time with time zone Time TimeOnly ✅
time without time zone Time TimeOnly ✅
timestamp with time zone Timestamp DateTimeOffset ✅
timestamp without time zone DateTime DateTime ✅
tsvector, tsquery VarChar string ⚠️ Read as string; writing requires to_tsvector() / to_tsquery()
uuid VarChar string ⚠️ No implicit varchar → uuid cast; use raw SQL with gen_random_uuid() or ::uuid

SQL Server

Native Type Velocity Type .NET Type Write Support
bigint Long long ✅
binary, varbinary, image Blob byte[] ✅
bit Boolean bool ✅
char, nchar Char string ✅
date Date DateOnly ✅
datetime, datetime2, smalldatetime DateTime DateTime ✅
datetimeoffset Timestamp DateTimeOffset ✅
decimal, numeric Decimal decimal ✅
float Double double ✅
geography Geography Geometry ✅
geometry Geometry Geometry ✅
hierarchyid VarChar string ✅ (send as path string, e.g. "/")
int Integer int ✅
money, smallmoney Decimal decimal ✅
real Float float ✅
rowversion / timestamp Blob byte[] 📖 Auto-generated
smallint Short short ✅
sql_variant VarChar string ✅ (send as string representation)
time Time TimeOnly ✅
tinyint Byte byte ✅
uniqueidentifier VarChar string ✅ (send as Guid.ToString())
varbinary(max) Blob byte[] ✅
varchar, nvarchar VarChar string ✅
varchar(max), nvarchar(max), text, ntext Clob string ✅
xml Clob string ✅ (send as XML string)

SQL Server — Interval: SQL Server has no native interval column type. Velocity stores DataType.Interval columns as BIGINT (100-nanosecond ticks). LoadSchema maps any BIGINT column to Long, so an interval column created by Velocity will be loaded back as Long. Use schema-first to preserve the Interval type across the lifecycle.

SQLite

SQLite uses type affinity rather than strict types. Velocity maps affinity groups consistently and all of them support full read/write through the Velocity API.

Native Type(s) Velocity Type .NET Type Write Support
BLOB, BINARY(n), VARBINARY(n), LONGBLOB Blob byte[] ✅
BOOLEAN, BOOL Boolean bool ✅
CHAR(n), NCHAR(n), CHARACTER(n), CHARACTER VARYING(n) Char string ✅
CLOB, NTEXT, MEDIUMTEXT, LONGTEXT, JSON Clob string ✅
DATE Date DateOnly ✅
DATETIME DateTime DateTime ✅
FLOAT Float float ✅
GEOMETRY (SpatiaLite) Geometry Geometry ✅
INT2, SMALLINT, SHORT Short short ✅
INT8, BIGINT, LONG, UNSIGNED BIG INT Long long ✅
INTEGER, INT, MEDIUMINT Integer int ✅
NUMERIC, DECIMAL, NUMBER Decimal decimal ✅
REAL, DOUBLE, DOUBLE PRECISION Double double ✅
TEXT, TINYTEXT, STRING, VARCHAR(n), NVARCHAR(n), VARYING CHARACTER(n) VarChar string ✅
TIME Time TimeOnly ✅
TIMESTAMP Timestamp DateTimeOffset ✅
TINYINT, BYTE Byte byte ✅
UUID VarChar string ✅

SQLite — Interval: SQLite has no native interval column type. Velocity stores DataType.Interval columns as BIGINT (100-nanosecond ticks). LoadSchema maps any BIGINT-affinity column to Long, so an interval column created by Velocity will be loaded back as Long. Use schema-first to preserve the Interval type across the lifecycle.

Teradata

Native Type Velocity Type .NET Type Write Support
BIGINT Long long ✅
BLOB, BYTE(n), VARBYTE(n) Blob byte[] ✅
BYTEINT Byte byte ✅
CHAR(n) Char string ✅
CLOB, JSON(n) Clob string ✅
DATE Date DateOnly ✅
DECIMAL, NUMERIC Decimal decimal ✅
FLOAT Double double ✅ (Teradata FLOAT is double-precision)
INTEGER Integer int ✅
INTERVAL DAY TO SECOND (and DAY, DAY TO HOUR, DAY TO MINUTE, HOUR, HOUR TO MINUTE, HOUR TO SECOND, MINUTE, MINUTE TO SECOND, SECOND) Interval TimeSpan ✅
INTERVAL YEAR TO MONTH, INTERVAL YEAR, INTERVAL MONTH VarChar string ⚠️ Year/month intervals have variable-length months and cannot be expressed as TimeSpan; writing requires an explicit INTERVAL literal
PERIOD(DATE), PERIOD(TIMESTAMP) VarChar string ⚠️ Read as string representation; writing requires a PERIOD(...) constructor expression
SMALLINT Short short ✅
ST_GEOMETRY Geometry Geometry ✅
TIME(n) Time TimeOnly ✅
TIME(n) WITH TIME ZONE Timestamp DateTimeOffset ✅ (mapped to Timestamp; timezone offset is preserved)
TIMESTAMP(n) DateTime DateTime ✅
TIMESTAMP(n) WITH TIME ZONE Timestamp DateTimeOffset ✅
VARCHAR(n) VarChar string ✅

Reading Data Back

When Velocity retrieves rows from a table loaded with LoadSchema, it uses the mapped DataType to choose the right reader path for each column. For the vast majority of types this works transparently:

// Load an existing database schema
using Manager m = new Manager(connection);
Schema schema = m.LoadSchema("legacy_db");
Table orders = schema["orders"];

// Query — works for all mapped types
using var rs = m.Retrieve(new Query().SelectAll(orders));
foreach (Result row in rs)
{
    // Standard accessors work for cleanly-mapped types
    long   id        = row.GetFieldLong("order_id");
    string reference = row.GetFieldVarChar("reference");
    DateOnly placed  = row.GetFieldDate("placed_date");
    TimeOnly cutoff  = row.GetFieldTime("cutoff_time");

    // Vendor-specific types mapped to a portable type also work
    string xmlData   = row.GetFieldClob("config_xml");   // xml → Clob
    byte[] thumbnail = row.GetFieldBlob("thumbnail");    // image → Blob
}

For columns that map to Unsupported, GetField returns the raw CLR object from the driver. You will need to handle the casting yourself:

foreach (Column col in orders.Columns)
{
    if (col.DataType == DataType.Unsupported)
    {
        object raw = row.GetField(col.Name);
        // Cast or handle the vendor-specific CLR type manually
    }
}

Writing Data Back

Writing back through the Velocity API works whenever the database accepts an untyped (or text/binary) parameter for the column type. Where it fails is where the database requires an explicit type cast and does not accept an implicit one.

What works everywhere

All standard Velocity data types write without issue — the tables above mark these ✅. This covers the majority of columns in most real-world schemas.

Record r = new Record(orders);
r.SetFieldLong("order_id", 42L);
r.SetFieldVarChar("reference", "ORD-42");
r.SetFieldDate("placed_date", DateOnly.FromDateTime(DateTime.UtcNow));
r.SetFieldTime("cutoff_time", new TimeOnly(17, 0, 0));
r.SetFieldDecimal("total", 199.99M);
m.AddRecord(r);

What requires raw SQL

For columns marked ⚠️ in the tables above, use RunSql with a native SQL literal:

// PostgreSQL: json/jsonb columns need an explicit cast
m.RunSql(@"
    INSERT INTO legacy.events (id, payload)
    VALUES (1, '{""action"":""login""}'::jsonb)");

// PostgreSQL: uuid column
m.RunSql(@"
    INSERT INTO legacy.sessions (session_id, user_id)
    VALUES (gen_random_uuid(), 42)");

// Oracle: INTERVAL YEAR TO MONTH
m.RunSql(@"
    INSERT INTO contracts (contract_id, duration)
    VALUES (1, INTERVAL '1-06' YEAR TO MONTH)");

// Teradata: PERIOD column
m.RunSql(@"
    INSERT INTO projects (project_id, active_period)
    VALUES (1, PERIOD(DATE '2024-01-01', DATE '2024-12-31'))");

The Unsupported DataType

When LoadSchema encounters a native type it has no mapping for, it sets the column's DataType to Unsupported. The column still appears in the in-memory schema and the raw value is still returned by GetField, but:

  • No typed accessor (GetFieldLong, GetFieldDate, etc.) will work for that column
  • You cannot write to that column through the Velocity write API
  • The column will not survive a round-trip through BuildSchema — it will be skipped

Encountering Unsupported columns is a signal that those columns require vendor-specific handling entirely outside Velocity's portable layer.

When Velocity Is Not the Right Tool

Velocity is the right tool when portability is a goal — when your data model either already is, or can be expressed as, a set of types that make sense across multiple relational databases. The schema-first approach and the cross-database portability it enables are what Velocity is designed to deliver.

If your application relies heavily on vendor-specific types that have no portable equivalent, Velocity may not be the right choice for those parts of your system:

  • PostgreSQL-specific workloads built around hstore, native range types, network address types, tsvector/tsquery, or advanced jsonb operators are most naturally served by a tool that understands PostgreSQL natively — raw ADO.NET with the Npgsql driver, or a PostgreSQL-specific data access layer.
  • Oracle-specific workloads that depend on SDO_GEOMETRY topology analysis, XMLDB repository features, BFILE external storage, or fine-grained INTERVAL arithmetic are best handled directly through the Oracle ADO.NET provider.
  • SQL Server-specific workloads that rely on hierarchyid traversal functions, spatial method chains, or sql_variant dynamic typing are best served by a SQL Server-specific data access layer built on top of the Microsoft ADO.NET provider.
  • Teradata-specific workloads that depend heavily on PERIOD temporal logic, INTERVAL arithmetic, or TD-specific analytic functions belong in a Teradata-native SQL layer.

Using Velocity in a mixed architecture — where Velocity manages the portable parts of your data model and native drivers handle the vendor-specific parts — is a perfectly valid pattern. Velocity does not need to own every table in your database.

The guiding question: If you had to move this database to a different vendor tomorrow, which columns would cause the most pain? Those are the columns that Velocity cannot help you with if you stay in them deep. The columns that map cleanly — integers, strings, dates, booleans, decimals, blobs — are the ones where Velocity gives you full portability with no compromise.