Forge SQL data types

Forge SQL is built on TiDB and supports MySQL-compatible data types. This page describes the supported types, explains how Forge SQL returns query results, and covers known limitations.

Supported data types

Forge SQL supports all MySQL-compatible data types that TiDB supports. The following table shows the most commonly used types, the format they're returned in, and how to handle them in your app.

Numeric types

String types

Binary types

Date and time types

Forge SQL supports the following date and time types. Each type has a specific input and return format — ensure your values match the format shown below.

Because Forge SQL returns date and time values as strings, you must parse them in your app code. We recommend using a date library such as moment or the native Date object:

1
2
import sql from '@forge/sql';
import moment from 'moment';

interface LoginDetails {
  id: number;
  CreatedAt: string;
  LastLoginTimestamp: string;
}

try {
  const result = await sql
    .prepare<LoginDetails>('SELECT * FROM LoginDetails WHERE id = ?')
    .bindParams(1)
    .execute();

  if (result.rows.length === 0) {
    throw new Error('No login details found');
  }

  const row = result.rows[0];
  const createdAt = moment(row.CreatedAt, 'YYYY-MM-DD').toDate();
  const lastLogin = new Date(row.LastLoginTimestamp);
} catch (error) {
  console.error('Error fetching login details:', error);
  throw error;
}

Boolean type

JSON type

If you store JSON directly in a JSON column, the value is returned as a string. Deserialise it with JSON.parse():

1
2
import sql from '@forge/sql';

interface Product {
  id: number;
  metadata: string; // raw JSON string
}

const result = await sql
  .prepare<Product>('SELECT id, metadata FROM products WHERE id = ?')
  .bindParams(42)
  .execute();

if (result.rows.length === 0) {
  throw new Error('Product not found');
}

let metadata;
try {
  metadata = JSON.parse(result.rows[0].metadata);
  // Use metadata
} catch (error) {
  console.error('Failed to parse metadata JSON:', error);
  throw new Error('Invalid metadata format');
}

Handling BIGINT values

JavaScript's number type is a 64-bit floating-point value (IEEE 754). It can only safely represent integers up to 2^53 - 1 (Number.MAX_SAFE_INTEGER). BIGINT columns can hold values larger than this, so Forge SQL returns BIGINT values as strings to preserve precision.

To work with large integers, use JavaScript's native BigInt type:

1
2
import sql from '@forge/sql';

interface Counter {
  id: number;
  count: string; // BIGINT returned as string
}

const result = await sql
  .prepare<Counter>('SELECT id, count FROM event_counters WHERE id = ?')
  .bindParams(1)
  .execute();

if (result.rows.length === 0) {
  throw new Error('Counter not found');
}

const count = BigInt(result.rows[0].count);
const incremented = count + 1n;

await sql
  .prepare('UPDATE event_counters SET count = ? WHERE id = ?')
  .bindParams(incremented.toString(), 1)
  .execute();

Type conversion summary

Because all Forge SQL query results are JSON-normalised, some database types are not returned in their native form. The following table summarises what to expect:

Hidden fields

The Table data tab within the Schema viewer won't display fields with the following database data types:

• BLOB
• MEDIUMBLOB
• LONGBLOB
• BINARY
• VARBINARY
• CLOB
• TEXT
• IMAGE
• XML
• JSON

These field are hidden to prevent the display of possibly large data payloads. These fields won't be included in the records provided through the Download button either.

The following types are hidden in the schema viewer:

• BLOB, MEDIUMBLOB, LONGBLOB
• BINARY, VARBINARY
• CLOB
• TEXT, IMAGE, XML, JSON

These types are hidden to prevent displaying large data payloads. They are also excluded from data downloaded through the Download button in the Developer Console.

Related pages

• Manage database schemas — define tables using the supported types
• Execute SQL operations — query and manipulate data
• Forge SQL tutorial — end-to-end example using common data types
• Error handling for Forge SQL — handle errors from SQL operations