Using Kysely

MikroORM provides first-class integration with Kysely, a type-safe SQL query builder. Through this integration, you can get a configured Kysely instance directly from EntityManager, leveraging MikroORM's metadata to drive Kysely's query transformation and type inference.

This allows you to write lower-level SQL queries while maintaining type safety and reusing entity relationships and hooks defined in MikroORM.

Initializing Kysely Instance

You can get a Kysely instance through the em.getKysely() method.

const kysely = orm.em.getKysely();

Automatic Type Inference with defineEntity

When you define entities using defineEntity and pass them to the ORM, getKysely() automatically infers table structures from entity metadata, providing full type safety without manually defining interfaces.

import { MikroORM, defineEntity, p } from '@mikro-orm/core';

const User = defineEntity({
  name: 'User',
  tableName: 'users', // optional, will be generated by naming strategy if omitted
  properties: {
    name: p.string().primary(),
    email: p.string().nullable(),
  },
});

// Pass entities directly to the ORM
const orm = new MikroORM({
  dbName: ':memory:',
  entities: [User],
});

// getKysely() automatically infers table structure, no need to manually specify types
const kysely = orm.em.getKysely();

// Fully type-safe query: TypeScript will auto-complete 'users' table and 'email' column
const result = await kysely
  .selectFrom('users')
  .selectAll()
  .where('email', 'is not', null)
  .execute();

Mixing InferKyselyTable with Manual Type Declarations

For tables not defined with defineEntity, you can manually declare their table structure.

import { MikroORM, InferKyselyTable } from '@mikro-orm/postgresql';

// Plugin options consistent with getKysely (adjust as needed)
const mikroKyselyPluginOptions = {
  tableNamingStrategy: 'entity',
  convertValues: true,
} as const;

// Manually declare table structure not inferred by `defineEntity`
interface ManualViewTable {
  view_id: number;
  view_count: number;
}

// Build complete database interface
interface MixedDatabase {
  user: InferKyselyTable<typeof User, typeof mikroKyselyPluginOptions>; // use inferred type
  post: InferKyselyTable<typeof Post, typeof mikroKyselyPluginOptions>; // use inferred type
  view_stats: ManualViewTable;                                    // use manual type
}

// Get instance with mixed types
const kysely = orm.em.getKysely<MixedDatabase>(mikroKyselyPluginOptions);

// Now you can type-safely query all tables
const user = await kysely.selectFrom('user').selectAll().executeTakeFirst();
const post = await kysely.selectFrom('post').selectAll().executeTakeFirst();
const stats = await kysely.selectFrom('view_stats').selectAll().executeTakeFirst();

MikroKyselyPlugin

The instance returned by em.getKysely() has MikroKyselyPlugin built-in. This plugin intercepts Kysely's query compilation and result processing phases to support MikroORM-specific features.

You can enable these features by passing a configuration object to getKysely():

const kysely = orm.em.getKysely({
  tableNamingStrategy: 'entity',
  columnNamingStrategy: 'property',
  processOnCreateHooks: true,
  processOnUpdateHooks: true,
  convertValues: true,
});

tableNamingStrategy

This option controls how you reference tables in Kysely queries.

• 'table' (default): Use the actual table name in the database (e.g., user_profiles). This is Kysely's standard behavior.
• 'entity': Use the entity name (e.g., UserProfile). The plugin will convert it to the corresponding table name before generating SQL.

// Assuming entity name is 'User' and database table name is 'users'

// Default case (tableNamingStrategy: 'table')
await kysely.selectFrom('users').selectAll().execute();

// Using entity name strategy (tableNamingStrategy: 'entity')
await kysely.selectFrom('User').selectAll().execute();
// Generated SQL: select * from "users"

columnNamingStrategy

This option controls how you reference columns in Kysely queries and how results are mapped.

• 'column' (default): Use the actual column name in the database (e.g., first_name).
• 'property': Use the entity property name (e.g., firstName). The plugin will convert property names to column names when generating SQL, and map column names back to property names in returned results.

// Assuming property name is 'firstName' and database column name is 'first_name'

// Enable property naming strategy
const kysely = orm.em.getKysely({ columnNamingStrategy: 'property' });

const users = await kysely
  .selectFrom('user')
  .select(['firstName', 'lastName']) // use property names
  .where('firstName', '=', 'John')
  .execute();

// Generated SQL: select "first_name", "last_name" from "user" where "first_name" = ?

// Result objects are automatically mapped back to property names
console.log(users[0].firstName); // 'John'

processOnCreateHooks

Boolean, defaults to false.

When enabled, INSERT queries automatically process onCreate hooks defined on entity properties. If your insert data is missing certain properties configured with onCreate (e.g., createdAt), the plugin will automatically generate and add them to the query.

// Entity definition:
// createdAt: p.datetime().onCreate(() => new Date())

const kysely = orm.em.getKysely({ processOnCreateHooks: true });

// Insert without createdAt
await kysely.insertInto('user').values({ name: 'John' }).execute();

// Generated SQL automatically includes created_at
// insert into "user" ("name", "created_at") values (?, ?)

processOnUpdateHooks

Boolean, defaults to false.

When enabled, UPDATE queries automatically process onUpdate hooks defined on entity properties. For example, automatically updating the updatedAt timestamp field.

// Entity definition:
// updatedAt: p.datetime().onUpdate(() => new Date())

const kysely = orm.em.getKysely({ processOnUpdateHooks: true });

await kysely
  .updateTable('user')
  .set({ name: 'Johnny' })
  .where('id', '=', 1)
  .execute();

// Generated SQL automatically includes updated_at
// update "user" set "name" = ?, "updated_at" = ? where "id" = ?

convertValues

Boolean, defaults to false.

When enabled, the plugin uses MikroORM's type system to convert query parameters and result values. This is important for handling driver-specific types (such as Date stored as numbers/strings in SQLite) or custom types.

• Input conversion: Converts JavaScript objects (e.g., Date) to database-supported formats.
• Output conversion: Converts raw values returned from the database back to JavaScript objects or custom types.

const kysely = orm.em.getKysely({ convertValues: true });

// 1. Input conversion: Date objects are automatically handled
await kysely
  .insertInto('user')
  .values({
    name: 'John',
    bornAt: new Date('1990-01-01') // automatically converted to database format
  })
  .execute();

// 2. Output conversion: automatically converted back to Date objects when reading
const user = await kysely
  .selectFrom('user')
  .selectAll()
  .executeTakeFirst();

console.log(user.bornAt instanceof Date); // true