Data Types

Introduction

Velocity provides a unified type system that abstracts database-specific data types into a consistent set of portable types. This abstraction allows you to write Schemas once and deploy them across any supported database without modification.

Philosophy: Schema-First Development

The Velocity Approach:

Instead of thinking in terms of database-specific types like VARCHAR2, NVARCHAR, BIGINT, or NUMBER(19), you define your schema using Velocity's portable data types. Velocity automatically translates these to the optimal native type for each database.

// Schema-first: Define once, deploy anywhere
Table productsTable = schema.CreateTable("products");
productsTable.AddColumn("product_id", DataType.Long, autoGenerate: true, notNull: true);
productsTable.AddColumn("product_name", DataType.VarChar, size: 200, notNull: true);
productsTable.AddColumn("price", DataType.Decimal, precision: 10, scale: 2);
productsTable.AddColumn("created_at", DataType.DateTime);

This same schema deploys correctly to SQL Server (as BIGINT, NVARCHAR(200), DECIMAL(10,2), DATETIME2), PostgreSQL (as BIGINT, VARCHAR(200), DECIMAL(10,2), TIMESTAMP), MySQL, Oracle, and all other supported databases—each using its native optimal types.

Benefits:

Write Once, Deploy Anywhere - No database-specific schema code
Type Safety - Consistent behavior across all databases
Maintainability - Single source of truth for your data model
Future-Proof - Easy migration between database vendors
No Vendor Lock-in - Change databases without changing schemas

Golden Rule: Define your schemas using Velocity data types and let Velocity handle the database-specific translation. Don't worry about the underlying database types—Velocity optimizes them for you.

Velocity Data Types

Velocity provides 20 DataTypes organized into several categories:

Numeric Types

Type	.NET Type	Description	Range/Precision
`Byte`	`byte`	8-bit unsigned integer	0 to 255
`Short`	`short`	16-bit signed integer	-32,768 to 32,767
`Integer`	`int`	32-bit signed integer	-2,147,483,648 to 2,147,483,647
`Long`	`long`	64-bit signed integer	-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
`Float`	`float`	Single-precision floating-point (32-bit)	~7 digits precision
`Double`	`double`	Double-precision floating-point (64-bit)	~15-16 digits precision
`Decimal`	`decimal`	Fixed-precision decimal number	Exact precision, configurable scale
`Boolean`	`bool`	Logical true/false value	true or false

String Types

Type	.NET Type	Description	Size
`Char`	`string`	Fixed-length character string	Specify size (e.g., 10)
`VarChar`	`string`	Variable-length character string	Specify maximum size
`Clob`	`string`	Character Large Object for long text	Unlimited

Date/Time Types

Type	.NET Type	Description	Precision
`Date`	`DateOnly`	Date only (no time component)	Year, month, day
`DateTime`	`DateTime`	Date and time without timezone	Year to millisecond
`Interval`	`TimeSpan`	Duration of time (not anchored to a date)	Days, hours, minutes, seconds
`Time`	`TimeOnly`	Time of day without date component	Hour, minute, second
`Timestamp`	`DateTimeOffset`	Date and time with timezone offset	Year to millisecond with timezone

Binary and Large Object Types

Type	.NET Type	Description	Use Cases
`Blob`	`byte[]`	Binary Large Object	Images, files, binary data

Geospatial Types

Type	.NET Type	Description	Coordinate System
`Geometry`	`NetTopologySuite.Geometries.Geometry`	Planar/Cartesian spatial data	Flat plane coordinates
`Geography`	`YndigoBlue.Velocity.Model.Geography`	Geodetic/Spherical spatial data	Earth surface (latitude/longitude)

Special Types

These types are used internally by Velocity and are not typically specified in schema definitions:

Type	Description	Usage
`String`	Generic string type from queries	Internal use for query results
`Query`	Subquery result value	Internal use for subquery values
`Expression`	Computed expression value	Internal use for calculated columns
`Guid`	Globally Unique Identifier	Data binding context only
`Unsupported`	Vendor-specific unsupported type	Appears when loading schemas with unsupported types

Type Mappings by Database

The following tables show how Velocity data types map to native database types. You should never need to reference these mappings when writing schemas—Velocity handles the translation automatically.

DB2 Type Mappings

Velocity Type	DB2 Native Type	Notes
`Blob`	`BLOB`	Binary Large Object
`Boolean`	`BOOLEAN`	Native boolean support
`Byte`	`SMALLINT`	16-bit integer
`Char`	`CHAR(size)`	Fixed-length
`Clob`	`CLOB`	Character Large Object
`Date`	`DATE`	Date only
`DateTime`	`TIMESTAMP`	With microsecond precision
`Decimal`	`DECIMAL(p,s)`	Fixed precision
`Double`	`DOUBLE`	64-bit float
`Float`	`REAL`	32-bit float
`Geometry`	`DB2GSE.ST_GEOMETRY`	DB2 Spatial Extender
`Geography`	`DB2GSE.ST_GEOMETRY`	Treated as ST_GEOMETRY
`Integer`	`INT`	32-bit integer
`Interval`	`BIGINT`	Stored as ticks (100-nanosecond units)
`Long`	`BIGINT`	64-bit integer
`Short`	`SMALLINT`	16-bit integer
`Time`	`TIME`	Time of day
`Timestamp`	`TIMESTAMP`	With microsecond precision
`VarChar`	`VARCHAR(size)`	Variable-length

MySQL Type Mappings

Velocity Type	MySQL Native Type	Notes
`Blob`	`LONGBLOB`	Up to 4GB
`Boolean`	`BOOL`	Alias for TINYINT(1)
`Byte`	`TINYINT UNSIGNED`	0-255
`Char`	`CHAR(size)`	Fixed-length
`Clob`	`LONGTEXT`	Up to 4GB text
`Date`	`DATE`	YYYY-MM-DD
`DateTime`	`DATETIME`	Precision to microseconds
`Decimal`	`DECIMAL(p,s)`	Fixed precision
`Double`	`DOUBLE`	64-bit float
`Float`	`FLOAT`	32-bit float
`Geometry`	`GEOMETRY`	MySQL spatial
`Geography`	`GEOMETRY`	Treated as GEOMETRY
`Integer`	`INT`	32-bit integer
`Interval`	`BIGINT`	Stored as ticks (100-nanosecond units)
`Long`	`BIGINT`	64-bit integer
`Short`	`SMALLINT`	16-bit integer
`Time`	`TIME`	Time of day
`Timestamp`	`TIMESTAMP`	With automatic timezone handling
`VarChar`	`VARCHAR(size)`	Variable-length

Oracle Type Mappings

Velocity Type	Oracle Native Type	Notes
`Blob`	`BLOB`	Binary Large Object
`Boolean`	`BOOLEAN`	NUMBER(1) can be used for Oracle 19
`Byte`	`NUMBER(3)`	0-255 range
`Char`	`CHAR(size)`	Fixed-length
`Clob`	`CLOB`	Character Large Object
`Date`	`DATE`	Includes time component
`DateTime`	`TIMESTAMP`	Default precision 6
`Decimal`	`DECIMAL(p,s)`	Fixed precision
`Double`	`BINARY_DOUBLE`	64-bit IEEE 754
`Float`	`BINARY_FLOAT`	32-bit IEEE 754
`Geometry`	`SDO_GEOMETRY`	Oracle Spatial
`Geography`	`SDO_GEOMETRY`	Treated as SDO_GEOMETRY
`Integer`	`NUMBER(10)`	32-bit range
`Interval`	`INTERVAL DAY(3) TO SECOND(6)`	Native interval; up to 999 days with microsecond precision
`Long`	`NUMBER(18)`	64-bit range
`Short`	`NUMBER(5)`	16-bit range
`Time`	`INTERVAL DAY(0) TO SECOND`	Oracle has no native TIME type; stored as a zero-day interval
`Timestamp`	`TIMESTAMP WITH TIME ZONE`	With timezone support
`VarChar`	`VARCHAR2(size)`	Variable-length

PostgreSQL Type Mappings

Velocity Type	PostgreSQL Native Type	Notes
`Blob`	`BYTEA`	Variable-length binary
`Boolean`	`BOOLEAN`	Native boolean support
`Byte`	`SMALLINT`	PostgreSQL has no 8-bit type
`Char`	`CHAR(size)`	Fixed-length
`Clob`	`TEXT`	Unlimited length
`Date`	`DATE`	Date only
`DateTime`	`TIMESTAMP`	Without timezone
`Decimal`	`DECIMAL(p,s)`	Arbitrary precision
`Double`	`DOUBLE PRECISION`	64-bit float
`Float`	`REAL`	32-bit float
`Geometry`	`GEOMETRY`	PostGIS extension
`Geography`	`GEOGRAPHY`	PostGIS geodetic type
`Integer`	`INTEGER`	32-bit integer
`Interval`	`INTERVAL`	Native interval type; maps directly to `TimeSpan`
`Long`	`BIGINT`	64-bit integer
`Short`	`SMALLINT`	16-bit integer
`Time`	`TIME`	Time of day
`Timestamp`	`TIMESTAMP WITH TIME ZONE`	With timezone
`VarChar`	`VARCHAR(size)`	Variable-length

SQL Server Type Mappings

Velocity Type	SQL Server Native Type	Notes
`Blob`	`VARBINARY(MAX)`	Maximum 2GB
`Boolean`	`BIT`	0 or 1
`Byte`	`TINYINT`	0-255
`Char`	`NCHAR(size)`	Unicode fixed-length
`Clob`	`NVARCHAR(MAX)`	Unicode CLOB type
`Date`	`DATE`	YYYY-MM-DD
`DateTime`	`DATETIME2`	Precision to 100 nanoseconds
`Decimal`	`DECIMAL(p,s)`	p = precision, s = scale
`Double`	`FLOAT(53)`	64-bit float
`Float`	`REAL`	32-bit float
`Geometry`	`GEOMETRY`	SQL Server spatial
`Geography`	`GEOGRAPHY`	SQL Server geodetic spatial
`Integer`	`INT`	32-bit integer
`Interval`	`BIGINT`	Stored as ticks (100-nanosecond units)
`Long`	`BIGINT`	64-bit integer
`Short`	`SMALLINT`	16-bit integer
`Time`	`TIME`	Time of day, precision to 100 nanoseconds
`Timestamp`	`DATETIMEOFFSET`	With timezone offset
`VarChar`	`NVARCHAR(size)`	Unicode variable-length

SQLite Type Mappings

Velocity Type	SQLite Native Type	Notes
`Blob`	`BLOB`	Dynamic typing
`Boolean`	`BOOLEAN`	Stored as 0/1
`Byte`	`SMALLINT`	Stored as INTEGER
`Char`	`CHAR(size)`	Stored as TEXT
`Clob`	`CLOB`	Stored as TEXT
`Date`	`DATE`	Stored as TEXT
`DateTime`	`DATETIME`	Stored as TEXT
`Decimal`	`DECIMAL(p,s)`	Stored as REAL
`Double`	`DOUBLE`	Stored as REAL
`Float`	`FLOAT`	Stored as REAL
`Geometry`	`BLOB`	WKB format in BLOB
`Geography`	`BLOB`	WKB format in BLOB
`Integer`	`INTEGER`	Native integer
`Interval`	`BIGINT`	Stored as ticks (100-nanosecond units)
`Long`	`LONG`	Stored as INTEGER
`Short`	`SMALLINT`	Stored as INTEGER
`Time`	`TIME`	Stored as TEXT
`Timestamp`	`TIMESTAMP`	Stored as TEXT
`VarChar`	`VARCHAR(size)`	Stored as TEXT

Note: SQLite uses dynamic typing with storage classes (INTEGER, REAL, TEXT, BLOB). Type names are advisory and enforce type affinity.

Teradata Type Mappings

Velocity Type	Teradata Native Type	Notes
`Blob`	`BLOB`	Binary Large Object
`Boolean`	`BYTEINT`	Teradata has no native boolean
`Byte`	`SMALLINT`	16-bit integer
`Char`	`CHAR(size)`	Fixed-length
`Clob`	`CLOB`	Character Large Object
`Date`	`DATE`	Date only
`DateTime`	`TIMESTAMP`	Default precision 6
`Decimal`	`DECIMAL(p,s)`	Fixed precision
`Double`	`DOUBLE PRECISION`	64-bit float
`Float`	`FLOAT`	Configurable precision
`Geometry`	`ST_GEOMETRY`	Teradata Geospatial
`Geography`	`ST_GEOMETRY`	Treated as ST_GEOMETRY
`Integer`	`INTEGER`	32-bit integer
`Interval`	`BIGINT`	Stored as ticks (100-nanosecond units)
`Long`	`BIGINT`	64-bit integer
`Short`	`SMALLINT`	16-bit integer
`Time`	`TIME`	Time of day
`Timestamp`	`TIMESTAMP WITH TIME ZONE`	With timezone support
`VarChar`	`VARCHAR(size)`	Variable-length

Choosing the Right Data Type

When designing your Schema, select Velocity data types based on your data requirements, not the target database:

Numeric Data

// Counting, IDs, quantities - use integers
table.AddColumn("quantity", DataType.Integer);
table.AddColumn("user_id", DataType.Long, autoGenerate: true);

// Financial calculations - use Decimal for exact precision
table.AddColumn("price", DataType.Decimal, precision: 10, scale: 2);
table.AddColumn("tax_rate", DataType.Decimal, precision: 5, scale: 4);

// Scientific/measurements - use Float or Double
table.AddColumn("temperature", DataType.Float);
table.AddColumn("latitude", DataType.Double);

String Data

// Fixed-length codes, abbreviations
table.AddColumn("country_code", DataType.Char, size: 2);  // "US", "CA", "UK"
table.AddColumn("status", DataType.Char, size: 10);      // "ACTIVE    ", "INACTIVE  "

// Variable-length text with known maximum
table.AddColumn("username", DataType.VarChar, size: 50);
table.AddColumn("email", DataType.VarChar, size: 100);
table.AddColumn("title", DataType.VarChar, size: 200);

// Long text without size limit
table.AddColumn("description", DataType.Clob);
table.AddColumn("article_content", DataType.Clob);
table.AddColumn("user_bio", DataType.Clob);

Date and Time

// Date only (birthdate, hire date)
table.AddColumn("birth_date", DataType.Date);
table.AddColumn("invoice_date", DataType.Date);

// Time of day only (business hours, recurring schedules)
table.AddColumn("opening_time", DataType.Time);
table.AddColumn("shift_start", DataType.Time);

// Duration / elapsed time (task duration, response time, SLA window)
table.AddColumn("task_duration", DataType.Interval);
table.AddColumn("response_time", DataType.Interval);

// Date and time without timezone (events in local time)
table.AddColumn("meeting_time", DataType.DateTime);
table.AddColumn("created_at", DataType.DateTime);

// Date and time with timezone (global events, audit trails)
table.AddColumn("transaction_timestamp", DataType.Timestamp);
table.AddColumn("last_login_utc", DataType.Timestamp);

Binary Data

// Images, documents, files
table.AddColumn("profile_picture", DataType.Blob);
table.AddColumn("pdf_document", DataType.Blob);
table.AddColumn("encrypted_data", DataType.Blob);

Geospatial Data

// Geometry - planar coordinates (building layouts, game maps)
table.AddColumn("building_footprint", DataType.Geometry);

// Geography - Earth surface coordinates (real-world locations)
table.AddColumn("store_location", DataType.Geography);
table.AddColumn("delivery_route", DataType.Geography);

Complete Example: E-Commerce Schema

Here's a complete example showing how to use CreateTable, AddColumn, and the Manager to build a multi-table e-commerce schema:

using YndigoBlue.Velocity.Model;
using YndigoBlue.Velocity.Engine;
using YndigoBlue.Velocity.Enums;

// Create schema using portable Velocity data types
Schema ecommerceSchema = Schema.NewSchema("ecommerce");

// Customers table
Table customersTable = ecommerceSchema.CreateTable("customers");
customersTable.AddColumn("customer_id", DataType.Long, autoGenerate: true, notNull: true);
customersTable.AddColumn("email", DataType.VarChar, size: 100, notNull: true);
customersTable.AddColumn("first_name", DataType.VarChar, size: 50);
customersTable.AddColumn("last_name", DataType.VarChar, size: 50);
customersTable.AddColumn("phone", DataType.VarChar, size: 20);
customersTable.AddColumn("created_at", DataType.Timestamp, notNull: true);
customersTable.AddColumn("is_active", DataType.Boolean, notNull: true);
customersTable.CreatePrimaryKey("pk_customers", "customer_id");
customersTable.CreateUniqueConstraint("uq_customer_email", "email");

// Products table
Table productsTable = ecommerceSchema.CreateTable("products");
productsTable.AddColumn("product_id", DataType.Long, autoGenerate: true, notNull: true);
productsTable.AddColumn("sku", DataType.VarChar, size: 50, notNull: true);
productsTable.AddColumn("name", DataType.VarChar, size: 200, notNull: true);
productsTable.AddColumn("description", DataType.Clob);
productsTable.AddColumn("price", DataType.Decimal, precision: 10, scale: 2, notNull: true);
productsTable.AddColumn("weight_kg", DataType.Float);
productsTable.AddColumn("stock_quantity", DataType.Integer, notNull: true);
productsTable.AddColumn("created_at", DataType.DateTime);
productsTable.CreatePrimaryKey("pk_products", "product_id");
productsTable.CreateUniqueConstraint("uq_product_sku", "sku");

// Orders table
Table ordersTable = ecommerceSchema.CreateTable("orders");
ordersTable.AddColumn("order_id", DataType.Long, autoGenerate: true, notNull: true);
ordersTable.AddColumn("customer_id", DataType.Long, notNull: true);
ordersTable.AddColumn("order_date", DataType.Timestamp, notNull: true);
ordersTable.AddColumn("subtotal", DataType.Decimal, precision: 12, scale: 2, notNull: true);
ordersTable.AddColumn("tax_amount", DataType.Decimal, precision: 10, scale: 2);
ordersTable.AddColumn("total_amount", DataType.Decimal, precision: 12, scale: 2, notNull: true);
ordersTable.AddColumn("status", DataType.VarChar, size: 20, notNull: true);
ordersTable.AddColumn("shipped_at", DataType.Timestamp);
ordersTable.CreatePrimaryKey("pk_orders", "order_id");
ordersTable.CreateForeignKey(
    "fk_orders_customers",
    "customer_id",
    "ecommerce",
    "customers",
    "customer_id"
);

// Deploy to any database - SQL Server, PostgreSQL, MySQL, Oracle, etc.
// Each database gets optimal native types automatically
using (var manager = new Manager(connection))
{
    manager.BuildSchema(ecommerceSchema, overwrite: true);
}

This exact Schema deploys identically to all supported databases with appropriate native type mappings—no changes required.

Best Practices

1. Always Use Velocity Data Types in Schemas

Always use DataType when defining Table Columns:

Good:

table.AddColumn("user_id", DataType.Long, autoGenerate: true);
table.AddColumn("username", DataType.VarChar, size: 50);

Avoid:

// Don't think in database-specific terms
// "I need a BIGINT and NVARCHAR(50)"

2. Choose Types Based on Data Requirements

// For money: always use Decimal for exact precision
table.AddColumn("price", DataType.Decimal, precision: 10, scale: 2);

// For measurements: Float or Double are appropriate
table.AddColumn("temperature_celsius", DataType.Float);

// For IDs: Long provides ample range
table.AddColumn("record_id", DataType.Long, autoGenerate: true);

3. Use Boolean for True/False Data

// Clear, portable boolean type
table.AddColumn("is_active", DataType.Boolean, notNull: true);
table.AddColumn("email_verified", DataType.Boolean);

// Avoid using integers (0/1) for booleans

4. Select Appropriate Date/Time Types

// Date only - for birthdates, anniversaries
table.AddColumn("birth_date", DataType.Date);

// Time only - for time-of-day values without a date (store hours, schedules)
table.AddColumn("opening_time", DataType.Time);

// Interval - for durations (elapsed time, SLA windows, task length)
table.AddColumn("task_duration", DataType.Interval);

// DateTime - for application events in local time
table.AddColumn("created_at", DataType.DateTime);

// Timestamp - for global events, distributed systems
table.AddColumn("transaction_time", DataType.Timestamp);

5. Don't Worry About Database-Specific Optimizations

Velocity already chooses optimal native types for each database:

// This becomes NVARCHAR(200) in SQL Server, VARCHAR(200) in PostgreSQL,
// VARCHAR2(200) in Oracle, etc. - all optimized appropriately
table.AddColumn("product_name", DataType.VarChar, size: 200);

6. Trust Velocity's Type Mapping

You don't need to know that:

SQL Server uses DATETIME2 for DataType.DateTime
PostgreSQL uses TIMESTAMP for DataType.DateTime
Oracle uses TIMESTAMP(6) for DataType.DateTime

Velocity handles all of this automatically and ensures consistent behavior.

7. Test Across Databases

The beauty of Velocity's type system is that your Schema works identically everywhere:

// Same schema definition
Schema schema = manager.ReadSchemaFromFile("schema.json", ConfigType.Json);

// Test on SQL Server
using (var sqlManager = new Manager(sqlServerConnection))
{
    sqlManager.BuildSchema(schema);
    // Automatic: BIGINT, NVARCHAR, DATETIME2, etc.
}

// Test on PostgreSQL
using (var pgManager = new Manager(postgresConnection))
{
    pgManager.BuildSchema(schema);
    // Automatic: BIGINT, VARCHAR, TIMESTAMP, etc.
}

// Test on MySQL
using (var mysqlManager = new Manager(mysqlConnection))
{
    mysqlManager.BuildSchema(schema);
    // Automatic: BIGINT, VARCHAR, DATETIME, etc.
}

Summary

Velocity's data type system provides:

Portability - Write schemas once, deploy anywhere
Simplicity - 20 types cover all common scenarios
Consistency - Same behavior across all databases
Abstraction - No need to learn database-specific types
Optimization - Automatic selection of optimal native types
Type Safety - Strong typing throughout your application

Remember: Focus on your data requirements, choose the appropriate Velocity data type, and let Velocity handle the database-specific details. This is the essence of schema-first development with Velocity.

Table of Contents