SingleStore foreign keys not supported
Foreign keys are not supported by SingleStore database.
drizzle-orm
281 notes in this subject, read out of this brain and free to use. This is page 4 of 5.
SingleStore INTERSECT ALL and EXCEPT ALL not supported
SingleStore does not support INTERSECT ALL and EXCEPT ALL operations.
Mock driver for testing without database connection
Every drizzle driver has a mock() method to create a mock database instance for testing: const db = drizzle.mock(). Optionally provide schema for types: const db = drizzle.mock({ schema }).
is() function for type checking instead of instanceof
Use the is() function from 'drizzle-orm' to check if an object is of a specific Drizzle type instead of using instanceof: if (is(value, Column)) { ... }. This should always be used instead of instanceof for Drizzle types.
getTableConfig for table metadata
Use getTableConfig(table) from 'drizzle-orm/singlestore-core' to retrieve table metadata. Returns an object with properties: columns, indexes, checks, primaryKeys, name, and schema.
Custom logger implementation
Implement the Logger interface with a logQuery(query: string, params: unknown[]): void method to create a custom logger: class MyLogger implements Logger { logQuery(query: string, params: unknown[]): void { console.log({ query, params }); } }. Pass the instance to drizzle: const db = drizzle({ logger: new MyLogger() }).
Convert query to SQL with toSQL()
Call toSQL() on a query builder to get the generated SQL and parameters: const query = db.select().from(users).toSQL() returns { sql: '...', params: [] }.
Execute raw SQL queries with db.execute
Use db.execute() to run raw parametrized SQL queries when Drizzle ORM cannot handle them: const statement = sql`select * from ${users} where ${users.id} = ${userId}`; const res: SingleStoreRawQueryResult = await db.execute(statement).
Standalone query builder without database instance
Use QueryBuilder from 'drizzle-orm/singlestore-core' to build queries without a database instance: const qb = new QueryBuilder(); const query = qb.select().from(users).where(eq(users.name, 'Dan')); const { sql, params } = query.toSQL().
getColumns for typed column map
Import getColumns from 'drizzle-orm' to get a typed columns map of a table. This is useful for omitting certain columns upon selection: const { password, role, ...rest } = getColumns(user); await db.select({ ...rest }).from(users). Available starting from drizzle-orm@1.0.0-beta.2.
singlestoreTableCreator for multi-project schemas
Use singlestoreTableCreator to customize table names for multiple projects in one database: const singlestoreTable = singlestoreTableCreator((name) => `project1_${name}`). This allows prefixing table names automatically.
InferInsertModel type helper for insert query types
To retrieve a type for insert queries, import InferInsertModel from 'drizzle-orm' and pass the table schema to it: type InsertUser = InferInsertModel<typeof users>. Alternatively, use typeof users.$inferInsert or typeof users._.$inferInsert.
Enable query logging with logger option
To enable default query logging, pass { logger: true } to the drizzle initialization function: const db = drizzle({ logger: true }).
Custom log writer with DefaultLogger
Create a custom log writer by implementing the LogWriter interface with a write(message: string) method, then pass it to DefaultLogger: const logger = new DefaultLogger({ writer: new MyLogWriter() }); const db = drizzle({ logger }).
Drizzle ORM overhead and thin layer philosophy
Drizzle ORM is a thin TypeScript layer on top of SQL with almost zero overhead. By using prepared statements, this overhead can be reduced to actual zero.
withReplicas with weighted replica selection example
const db = withReplicas(primaryDb, [read1, read2], (replicas) => { const weight = [0.7, 0.3]; let cumulativeProbability = 0; const rand = Math.random(); for (const [i, replica] of replicas.entries()) { cumulativeProbability += weight[i]!; if (rand < cumulativeProbability) return replica; } return replicas[0]! });
withReplicas function for read replicas
The withReplicas() function in Drizzle ORM allows you to manage SELECT queries from read replica instances while performing create, delete, and update operations on the primary instance. It accepts the primary database instance and an array of read replica instances as parameters.
withReplicas basic usage with multiple replicas
When using withReplicas(), Drizzle automatically routes SELECT queries to one of the available read replicas and routes all write operations (insert, delete, update) to the primary instance. The returned db instance can be used the same way as a regular Drizzle database instance.
Force primary instance with $primary key
To force read operations to use the primary instance instead of a read replica, use the $primary property on the db instance. For example: await db.$primary.select().from(usersTable)
Custom replica selection logic in withReplicas
withReplicas() accepts an optional third parameter that is a callback function for custom read replica selection logic. The callback receives the replicas array and must return a single replica instance. This allows you to implement weighted selection or any custom load-balancing strategy.
drizzle-typebox with SingleStore example
Example showing how to create insert, update, and select schemas from a SingleStore table using createInsertSchema, createUpdateSchema, and createSelectSchema from 'drizzle-orm/typebox', then validate data with Value.Check from the typebox library.
Use typebox Value.Check for schema validation
After creating a typebox schema from a Drizzle table using createInsertSchema or similar functions, use Value.Check(schema, data) to validate data against the schema. This returns a boolean indicating whether the data is valid.
Refine typebox schema fields before finalization
When creating schemas, you can refine fields by passing a function that receives the field schema and returns a modified version. This is useful for changing fields before they become nullable or optional in the final schema. For example: createInsertSchema(users, { id: (schema) => Type.Number({ ...schema, minimum: 0 }) }).
createUpdateSchema function creates update validation schema
The createUpdateSchema function imported from 'drizzle-orm/typebox' generates a typebox schema for validating data when updating records in a table. This schema can be used to validate API requests.
createInsertSchema function creates insert validation schema
The createInsertSchema function imported from 'drizzle-orm/typebox' generates a typebox schema for validating data when inserting records into a table. This schema can be used to validate API requests.
drizzle-typebox supported features
drizzle-typebox supports creating select schemas for tables, insert and update schemas for tables. The supported dialect is SingleStore.
drizzle-typebox install dependencies
To use drizzle-typebox for generating typebox schemas from Drizzle ORM schemas, install drizzle-orm@rc and typebox packages.
createSelectSchema function creates select validation schema
The createSelectSchema function imported from 'drizzle-orm/typebox' generates a typebox schema for validating data retrieved from a table. This schema can be used to validate API responses.
Drizzle is dialect-specific and serverless-ready
Drizzle ORM is dialect-specific, slim, performant, and serverless-ready by design with best-in-class SQL dialect support.
Drizzle ORM is a headless TypeScript ORM
Drizzle ORM is described as a headless TypeScript ORM with a head. It is a library and collection of complementary opt-in tools that lets you build projects the way you want without interfering with project structure.
Drizzle supports both relational and SQL-like query APIs
Drizzle is the only ORM with both relational and SQL-like query APIs, providing the best of both worlds when accessing relational data.
Drizzle ORM design characteristics
Drizzle ORM is lightweight, performant, typesafe, flexible, and serverless-ready by design. It has exactly 0 dependencies.
Valibot schema for char columns
PostgreSQL char({ length: ... }) and mysql.char({ length: ... }) map to the valibot schema pipe(string(), length(length)), enforcing an exact length match.
Valibot schema for MySQL longtext columns
MySQL longtext columns map to the valibot schema pipe(string(), maxLength(4_294_967_295)), reflecting the unsigned 32-bit integer limit.
drizzle-valibot deprecated since 1.0.0-beta.15
Starting from drizzle-orm@1.0.0-beta.15, the drizzle-valibot package has been deprecated in favor of first-class schema generation support within Drizzle ORM itself. The drizzle-valibot package can still be used, but all new updates will be added to Drizzle ORM directly.
createSelectSchema validates API responses with valibot
Use createSelectSchema to define the shape of data queried from the database, which can be used to validate API responses. The schema enforces that only fields actually returned by the query are present in the validated result. For example, if a query does not return the 'age' field, the validation will fail if 'age' is required in the schema.
createSelectSchema supports views and enums
createSelectSchema can generate valibot schemas not only for tables, but also for database views and enums. For enums, it creates a union type schema. For views, it creates a schema matching the view's selected columns.
createInsertSchema validates data before insertion
Use createInsertSchema to define the shape of data to be inserted into the database, which can be used to validate API requests. The schema respects column constraints like notNull and generatedAlwaysAsIdentity, rejecting data that violates these constraints.
createUpdateSchema validates data before updates
Use createUpdateSchema to define the shape of data to be updated in the database, which can be used to validate API requests. The schema generates optional fields for all columns except generated columns, which cannot be updated. Attempting to include a generated column in update data will cause validation to fail.
Schema refinements with callback or Valibot schema
Each createSelectSchema, createInsertSchema, and createUpdateSchema function accepts an optional parameter for refinements. Pass a callback function to extend or modify a field's schema using valibot pipes. Alternatively, provide a valibot schema directly to overwrite the field's schema entirely, including its nullability.
Valibot schema for boolean columns
Boolean columns map to the valibot boolean() schema type across all databases: pg.boolean(), mysql.boolean(), and sqlite.integer({ mode: 'boolean' }).
Valibot schema for date and timestamp columns
Date and timestamp columns that use mode: 'date' map to the valibot date() schema type. This applies to pg.date({ mode: 'date' }), pg.timestamp({ mode: 'date' }), mysql.date({ mode: 'date' }), mysql.datetime({ mode: 'date' }), mysql.timestamp({ mode: 'date' }), sqlite.integer({ mode: 'timestamp' }), and sqlite.integer({ mode: 'timestamp_ms' }).
Valibot schema for string columns
String and text columns map to the valibot string() schema type. This includes pg.text(), pg.date({ mode: 'string' }), pg.timestamp({ mode: 'string' }), pg.cidr(), pg.inet(), pg.interval(), pg.macaddr(), pg.macaddr8(), pg.numeric(), pg.sparsevec(), pg.time(), mysql.binary(), mysql.date({ mode: 'string' }), mysql.datetime({ mode: 'string' }), mysql.decimal(), mysql.time(), mysql.timestamp({ mode: 'string' }), mysql.varbinary(), and sqlite.numeric() and sqlite.text({ mode: 'text' }).
Valibot schema for bit columns
PostgreSQL bit columns with a specified dimensions parameter map to a valibot schema of pipe(string(), regex(/^[01]+$/), maxLength(dimensions)).
Valibot schema for UUID columns
PostgreSQL uuid columns map to the valibot schema pipe(string(), uuid()).
Valibot schema for varchar columns
PostgreSQL varchar({ length: ... }), mysql.varchar({ length: ... }), and sqlite.text({ mode: 'text', length: ... }) map to the valibot schema pipe(string(), maxLength(length)).
Valibot schema for MySQL tinytext columns
MySQL tinytext columns map to the valibot schema pipe(string(), maxLength(255)), reflecting the unsigned 8-bit integer limit of 255 characters.
Valibot schema for enum columns
Enum columns across all databases (pg.text({ enum: ... }), pg.char({ enum: ... }), pg.varchar({ enum: ... }), mysql text variants, mysql.mysqlEnum, sqlite.text({ enum: ... })) map to the valibot schema enum(enum), enforcing membership in the enum values.
Valibot schema for MySQL mediumint columns
MySQL mediumint columns map to the valibot schema pipe(number(), minValue(-8_388_608), maxValue(8_388_607), integer()), respecting the 24-bit signed integer limits.
Valibot schema for 32-bit integer columns
PostgreSQL integer() and serial(), and MySQL int() columns map to the valibot schema pipe(number(), minValue(-2_147_483_648), maxValue(2_147_483_647), integer()), respecting the 32-bit signed integer limits.
Valibot schema for 48-bit floating point columns
PostgreSQL doublePrecision(), MySQL double(), MySQL real(), and SQLite real() columns map to the valibot schema pipe(number(), minValue(-140_737_488_355_328), maxValue(140_737_488_355_327)), respecting the 48-bit integer limits.
Valibot schema for MySQL unsigned double columns
MySQL double({ unsigned: true }) columns map to the valibot schema pipe(number(), minValue(0), maxValue(281_474_976_710_655)), respecting the unsigned 48-bit integer limits.
Valibot schema for MySQL serial columns
MySQL serial() columns map to the valibot schema pipe(number(), minValue(0), maxValue(9_007_199_254_740_991), integer()), respecting JavaScript's maximum safe integer.
Valibot schema for 64-bit bigint columns
PostgreSQL bigint({ mode: 'bigint' }), PostgreSQL bigserial({ mode: 'bigint' }), MySQL bigint({ mode: 'bigint' }), and SQLite blob({ mode: 'bigint' }) columns map to the valibot schema pipe(bigint(), minValue(-9_223_372_036_854_775_808n), maxValue(9_223_372_036_854_775_807n)), respecting the full 64-bit signed integer limits.
Valibot schema for MySQL unsigned bigint columns
MySQL bigint({ mode: 'bigint', unsigned: true }) columns map to the valibot schema pipe(bigint(), minValue(0n), maxValue(18_446_744_073_709_551_615n)), respecting the unsigned 64-bit integer limits.
Valibot schema for MySQL year columns
MySQL year() columns map to the valibot schema pipe(number(), minValue(1_901), maxValue(2_155), integer()), respecting the valid year range for MySQL.
Valibot schema for PostgreSQL point geometry (tuple mode)
PostgreSQL geometry({ type: 'point', mode: 'tuple' }) and point({ mode: 'tuple' }) columns map to the valibot schema tuple([number(), number()]), representing a point as a two-element tuple of coordinates.
Valibot schema for PostgreSQL point geometry (xy mode)
PostgreSQL geometry({ type: 'point', mode: 'xy' }) and point({ mode: 'xy' }) columns map to the valibot schema object({ x: number(), y: number() }), representing a point as an object with x and y coordinates.
Valibot schema for PostgreSQL vector columns
PostgreSQL halfvec({ dimensions: ... }) and vector({ dimensions: ... }) columns map to the valibot schema pipe(array(number()), length(dimensions)), representing a vector as an array of numbers with a fixed length.
Valibot schema for PostgreSQL line (abc mode)
PostgreSQL line({ mode: 'abc' }) columns map to the valibot schema object({ a: number(), b: number(), c: number() }), representing a line as coefficients in the equation ax + by + c = 0.