Drizzle ORM - SQL Types

 

Integer

Signed 4 Bytes

=>

export const testTable = pgTable("testTable", {
qty: integer("qty")
})

 

Signed 2 Bytes

=>

export const testTable = pgTable("testTable", {
qty: smallint("qty")
})

 

Signed 8 Bytes

=>

export const testTable = pgTable("testTable", {
qty: bigint("qty", {mode: "number"})
// {mode: "number"} : 2^31 < number < 2^53
// {mode: "bigint"} : 2^63 < number < 2^63
})

 

serial

serial / serial4 : is an auto-incrementing 4-byte integer generally used for creating unique identifiers.

=>

export const testTable = pgTable("testTable", {
id: serial("id"").primaryKey(),
})

 

 

smallserial / serial2 : is an auto-incrementing 2-byte integer

=>

export const testTable = pgTable("testTable", {
id: smallserial("id")
})

 

bigserial / serial8 : is an auto-incrementing 8-byte integer suitable for larger ranges of integers.

=>

export const testTable = pgTable("testTable", {
id: bigserial("id", {mode: "bigint"}).primaryKey(),
})

 

 

decimal

The decimal type in Drizzle ORM is an alias for the numeric type, and it is used to represent exact numerics with selectable precision. This means that it can store numbers with a very large number of digits, up to 131072 digits before the decimal point and up to 16383 digits after the decimal point

=>

export const testTable = pgTable("testTable", {
price: decimal("price", {precision:7, scale:2}) // 12345.67
===
price: numeric("price", {precision:7, scale:2}) // 12345.67
})

 

 

real / float4

Single precision floating-point number (4 bytes)

IE-37 to IE+37

6 decimal digits precision

It follows IEEE 754, and some errors may occur when dealing with floating point types.
It cannot be converted accurately and is stored as an approximate value, so it is not suitable for exact calculations.

=> 

export const testTable = pgTable("testTable", {
	score: real("score")
})

 

double presition / float8

Double precision floating-point number (8 bytes)

15 decimal digits precision

=>

export const testTable = pgTable("testTable", {
	score: doublePrecision("score")
})