@kysera/timestamps

Automatic timestamp management plugin for Kysera - Zero-configuration created_at and updated_at tracking through @kysera/executor's Unified Execution Layer with powerful query helpers.

🎯 Features

• ✅ Zero Configuration - Works out of the box with sensible defaults
• ✅ Automatic Timestamps - created_at on insert, updated_at on update via @kysera/executor
• ✅ Batch Operations - Efficient createMany, updateMany, touchMany methods
• ✅ Custom Column Names - Use any column names you want
• ✅ Table Filtering - Whitelist or blacklist specific tables
• ✅ Date Formats - ISO strings, Unix timestamps, or Date objects
• ✅ Query Helpers - 13+ methods for timestamp-based queries
• ✅ Type-Safe - Full TypeScript support with inference
• ✅ Plugin Architecture - Integrates seamlessly via @kysera/executor's Unified Execution Layer
• ✅ Production Ready - Battle-tested with comprehensive test coverage

📥 Installation

# npm
npm install @kysera/timestamps @kysera/repository kysely

# pnpm
pnpm add @kysera/timestamps @kysera/repository kysely

# bun
bun add @kysera/timestamps @kysera/repository kysely

# deno
import { timestampsPlugin } from "npm:@kysera/timestamps"

🚀 Quick Start

1. Add Timestamp Columns to Your Database

-- PostgreSQL / MySQL / SQLite
ALTER TABLE users ADD COLUMN created_at TIMESTAMP;
ALTER TABLE users ADD COLUMN updated_at TIMESTAMP;

-- Or include in table creation
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) NOT NULL,
  name VARCHAR(255) NOT NULL,
  created_at TIMESTAMP,
  updated_at TIMESTAMP
);

2. Setup Plugin

import { Kysely, PostgresDialect } from 'kysely'
import { Pool } from 'pg'
import { createExecutor } from '@kysera/executor'
import { createORM, createRepositoryFactory } from '@kysera/repository'
import { timestampsPlugin } from '@kysera/timestamps'
import { z } from 'zod'

// Define database schema
interface Database {
  users: {
    id: Generated<number>
    email: string
    name: string
    created_at: Generated<Date>
    updated_at: Date | null
  }
}

// Create database connection
const db = new Kysely<Database>({
  dialect: new PostgresDialect({
    pool: new Pool({
      /* config */
    })
  })
})

// Step 1: Register timestamps plugin with Unified Execution Layer
const executor = await createExecutor(db, [
  timestampsPlugin() // ✨ That's it!
])

// Step 2: Create ORM with plugin-enabled executor
const orm = await createORM(executor, [])

// Step 3: Create repository
const userRepo = orm.createRepository(executor => {
  const factory = createRepositoryFactory(executor)
  return factory.create<'users', User>({
    tableName: 'users',
    mapRow: row => row as User,
    schemas: {
      create: z.object({
        email: z.string().email(),
        name: z.string()
      })
    }
  })
})

// Use repository - timestamps added automatically!
const user = await userRepo.create({
  email: '[email protected]',
  name: 'Alice'
})

console.log(user.created_at) // ✅ 2024-01-15T10:30:00.000Z
console.log(user.updated_at) // null (only set on update)

// Update - updated_at set automatically!
const updated = await userRepo.update(user.id, {
  name: 'Alice Smith'
})

console.log(updated.updated_at) // ✅ 2024-01-15T10:35:00.000Z

📚 Table of Contents

1. Configuration
   • Default Configuration
   • Custom Column Names
   • Table Filtering
   • Date Formats
   • Custom Timestamp Generator
2. Automatic Behavior
   • On Create
   • On Update
   • Bypass Timestamps
3. Query Helpers
   • Find by Creation Date
   • Find by Update Date
   • Recently Created/Updated
   • Touch Records
4. Batch Operations
   • createMany
   • updateMany
   • touchMany
5. Extended Methods
6. Advanced Usage
7. Multi-Database Support
8. Type Safety
9. API Reference
10. Best Practices
11. Performance
12. Troubleshooting

⚙️ Configuration

Default Configuration

The plugin works with zero configuration using sensible defaults:

const plugin = timestampsPlugin()

// Equivalent to:
const plugin = timestampsPlugin({
  createdAtColumn: 'created_at',
  updatedAtColumn: 'updated_at',
  setUpdatedAtOnInsert: false,
  dateFormat: 'iso',
  tables: undefined, // All tables
  excludeTables: undefined, // No exclusions
  primaryKeyColumn: 'id' // Default primary key
})

Custom Column Names

Use your own column naming conventions:

// Example: Use "created" and "modified"
const plugin = timestampsPlugin({
  createdAtColumn: 'created',
  updatedAtColumn: 'modified'
})

// Example: Use "createdDate" and "lastModified"
const plugin = timestampsPlugin({
  createdAtColumn: 'createdDate',
  updatedAtColumn: 'lastModified'
})

// Your database schema
interface Database {
  posts: {
    id: Generated<number>
    title: string
    created: Generated<Date> // ✅ Custom name
    modified: Date | null // ✅ Custom name
  }
}

Table Filtering

Control which tables get timestamps:

Whitelist (Only Specific Tables)

const plugin = timestampsPlugin({
  tables: ['users', 'posts', 'comments']
})

// ✅ users, posts, comments get timestamps
// ❌ config, settings, logs do NOT get timestamps

Blacklist (Exclude Specific Tables)

const plugin = timestampsPlugin({
  excludeTables: ['config', 'migrations', 'sessions']
})

// ✅ All tables get timestamps EXCEPT
// ❌ config, migrations, sessions

When to Use Each

Use Whitelist (tables) when:

• You have many tables but only a few need timestamps
• You want explicit control over timestamp tables
• You're migrating incrementally

Use Blacklist (excludeTables) when:

• Most tables need timestamps
• Only a few system tables should be excluded
• You want timestamps by default

Date Formats

Choose the timestamp format that matches your database:

ISO String (Default)

const plugin = timestampsPlugin({
  dateFormat: 'iso'
})

// Generates: "2024-01-15T10:30:00.000Z"
// Best for: PostgreSQL TIMESTAMP, MySQL DATETIME, SQLite TEXT

Unix Timestamp

const plugin = timestampsPlugin({
  dateFormat: 'unix'
})

// Generates: 1705318200 (seconds since epoch)
// Best for: INTEGER columns, time-series data

Date Object

const plugin = timestampsPlugin({
  dateFormat: 'date'
})

// Generates: new Date()
// Best for: Native DATE columns, database compatibility

Custom Timestamp Generator

Full control over timestamp generation:

// Example: Use a custom time source
const plugin = timestampsPlugin({
  getTimestamp: () => {
    return customTimeService.getCurrentTime()
  }
})

// Example: Always use UTC midnight for created dates
const plugin = timestampsPlugin({
  getTimestamp: () => {
    const now = new Date()
    now.setUTCHours(0, 0, 0, 0)
    return now.toISOString()
  }
})

// Example: Use millisecond precision Unix timestamp
const plugin = timestampsPlugin({
  getTimestamp: () => Date.now()
})

Set updated_at on Insert

Some applications want updated_at to equal created_at initially:

const plugin = timestampsPlugin({
  setUpdatedAtOnInsert: true
})

const user = await userRepo.create({
  email: '[email protected]',
  name: 'Alice'
})

console.log(user.created_at) // 2024-01-15T10:30:00.000Z
console.log(user.updated_at) // 2024-01-15T10:30:00.000Z (same!)

// On update, updated_at changes
const updated = await userRepo.update(user.id, { name: 'Alice Smith' })
console.log(updated.updated_at) // 2024-01-15T10:35:00.000Z (different)

Custom Primary Key Column

If your tables use a different primary key column name (e.g., uuid, user_id, pk), you can configure it:

// Example: Using 'uuid' as primary key
const plugin = timestampsPlugin({
  primaryKeyColumn: 'uuid'
})

// Now touch() will use the configured primary key
await userRepo.touch(userUuid)

// Example: Using 'user_id' as primary key
const plugin = timestampsPlugin({
  primaryKeyColumn: 'user_id'
})

// Database schema
interface Database {
  users: {
    user_id: Generated<number> // Custom primary key
    email: string
    name: string
    created_at: Generated<Date>
    updated_at: Date | null
  }
}

// Touch uses user_id instead of id
await userRepo.touch(userId)

Note: This option affects the following methods:

• touch() - Uses primaryKeyColumn in WHERE clause
• createMany() - Uses primaryKeyColumn for MySQL/MSSQL fallback ORDER BY
• updateMany() - Uses primaryKeyColumn in WHERE ... IN clause
• touchMany() - Uses primaryKeyColumn in WHERE ... IN clause

The base update() method uses the repository's own primary key logic.

🤖 Automatic Behavior

The timestamps plugin works through @kysera/executor's Unified Execution Layer to automatically manage timestamps on all operations.

On Create

When you call repository.create(), the plugin automatically adds created_at:

const user = await userRepo.create({
  email: '[email protected]',
  name: 'Bob'
})

// Equivalent SQL:
// INSERT INTO users (email, name, created_at)
// VALUES ('[email protected]', 'Bob', '2024-01-15T10:30:00.000Z')

console.log(user.created_at) // ✅ 2024-01-15T10:30:00.000Z
console.log(user.updated_at) // null (default behavior)

Manual Override:

// Provide your own created_at (e.g., migrating data)
const user = await userRepo.create({
  email: '[email protected]',
  name: 'Charlie',
  created_at: '2020-01-01T00:00:00.000Z' // ✅ Uses this instead
})

console.log(user.created_at) // 2020-01-01T00:00:00.000Z

On Update

When you call repository.update(), the plugin automatically sets updated_at:

const updated = await userRepo.update(userId, {
  name: 'New Name'
})

// Equivalent SQL:
// UPDATE users
// SET name = 'New Name', updated_at = '2024-01-15T10:35:00.000Z'
// WHERE id = 1

console.log(updated.updated_at) // ✅ 2024-01-15T10:35:00.000Z

Manual Override:

// Provide your own updated_at
const updated = await userRepo.update(userId, {
  name: 'New Name',
  updated_at: '2024-01-01T00:00:00.000Z' // ✅ Uses this instead
})

Bypass Timestamps

Sometimes you need to skip automatic timestamps:

// Create without timestamps
const user = await userRepo.createWithoutTimestamps({
  email: '[email protected]',
  name: 'System User'
})

console.log(user.created_at) // null (not set)
console.log(user.updated_at) // null (not set)

// Update without modifying updated_at
const updated = await userRepo.updateWithoutTimestamp(userId, {
  name: 'Silent Update'
})

console.log(updated.updated_at) // null (unchanged)

Use Cases:

• Migrating historical data with specific timestamps
• System operations that shouldn't update timestamps
• Preserving original timestamps when copying records
• Testing with fixed timestamps

🔍 Query Helpers

The plugin adds 10 powerful query methods to your repositories.

Find by Creation Date

findCreatedAfter

Find records created after a specific date:

// Find users created after Jan 1, 2024
const users = await userRepo.findCreatedAfter('2024-01-01T00:00:00.000Z')

// Find users created in the last 7 days
const weekAgo = new Date()
weekAgo.setDate(weekAgo.getDate() - 7)
const recentUsers = await userRepo.findCreatedAfter(weekAgo)

// With Unix timestamp
const users = await userRepo.findCreatedAfter(1704067200)

findCreatedBefore

Find records created before a specific date:

// Find users created before Dec 31, 2023
const users = await userRepo.findCreatedBefore('2023-12-31T23:59:59.999Z')

// Find users created more than 30 days ago
const monthAgo = new Date()
monthAgo.setDate(monthAgo.getDate() - 30)
const oldUsers = await userRepo.findCreatedBefore(monthAgo)

findCreatedBetween

Find records created within a date range:

// Find users created in January 2024
const users = await userRepo.findCreatedBetween(
  '2024-01-01T00:00:00.000Z',
  '2024-01-31T23:59:59.999Z'
)

// Find users created this week
const weekStart = new Date()
weekStart.setDate(weekStart.getDate() - weekStart.getDay())
const weekEnd = new Date()
const thisWeek = await userRepo.findCreatedBetween(weekStart, weekEnd)

// Find users created between two Unix timestamps
const users = await userRepo.findCreatedBetween(1704067200, 1706745599)

Find by Update Date

findUpdatedAfter

Find records updated after a specific date:

// Find users updated today
const today = new Date()
today.setHours(0, 0, 0, 0)
const updatedToday = await userRepo.findUpdatedAfter(today)

// Find users updated in last hour
const hourAgo = new Date(Date.now() - 60 * 60 * 1000)
const recentlyModified = await userRepo.findUpdatedAfter(hourAgo)

Recently Created/Updated

findRecentlyCreated

Get the most recently created records:

// Get 10 most recent users (default)
const latest = await userRepo.findRecentlyCreated()

// Get 50 most recent users
const latest50 = await userRepo.findRecentlyCreated(50)

// Get latest user
const latestUser = await userRepo.findRecentlyCreated(1)
console.log(latestUser[0]) // Most recent user

findRecentlyUpdated

Get the most recently updated records:

// Get 10 most recently updated users (default)
const recent = await userRepo.findRecentlyUpdated()

// Get 25 most recently updated
const recent25 = await userRepo.findRecentlyUpdated(25)

// Monitor active records
setInterval(async () => {
  const active = await userRepo.findRecentlyUpdated(5)
  console.log('Recently active users:', active)
}, 5000)

Touch Records

touch

Update a record's updated_at without changing any other fields:

// Touch a user (update their updated_at timestamp)
await userRepo.touch(userId)

// Useful for:
// - "Last seen" tracking
// - Refresh TTL for cached data
// - Activity tracking
// - Session management

// Example: Track user activity
await userRepo.touch(userId)
const user = await userRepo.findById(userId)
console.log(`User last active: ${user.updated_at}`)

🚀 Batch Operations

The plugin provides efficient batch operations for working with multiple records at once.

createMany

Create multiple records with timestamps in a single efficient bulk INSERT operation.

// Create multiple users at once
const users = await userRepo.createMany([
  { name: 'Alice', email: '[email protected]' },
  { name: 'Bob', email: '[email protected]' },
  { name: 'Charlie', email: '[email protected]' }
])

// All records created with the same timestamp
users.forEach(user => {
  console.log(user.created_at) // Same timestamp for all
})

// Empty arrays are handled gracefully
const empty = await userRepo.createMany([])
console.log(empty.length) // 0

Performance Benefits:

• Single database roundtrip instead of N queries
• All records get the same timestamp (consistent)
• ~10-100x faster than individual creates for large batches

Example: Bulk Import

// Import 1000 users from CSV
const csvData = parseCsv('users.csv')
const inputs = csvData.map(row => ({
  name: row.name,
  email: row.email
}))

// Efficient bulk insert with timestamps
const imported = await userRepo.createMany(inputs)
console.log(`Imported ${imported.length} users`)

updateMany

Update multiple records with the same data, automatically setting updated_at for all.

// Update multiple users
const userIds = [1, 2, 3, 4, 5]
const updated = await userRepo.updateMany(userIds, {
  status: 'active'
})

// All records updated with same timestamp
updated.forEach(user => {
  console.log(user.status) // 'active'
  console.log(user.updated_at) // Same timestamp for all
})

// Works with string IDs too
const stringIds = ['user-1', 'user-2', 'user-3']
await userRepo.updateMany(stringIds, { verified: true })

// Empty arrays are handled gracefully
const empty = await userRepo.updateMany([], { status: 'inactive' })
console.log(empty.length) // 0

Performance Benefits:

• Single UPDATE query with WHERE id IN clause
• Single SELECT to fetch updated records
• Much faster than individual updates

Example: Bulk Status Change

// Find users that haven't been updated recently
const thirtyDaysAgo = new Date()
thirtyDaysAgo.setDate(thirtyDaysAgo.getDate() - 30)
const active = await userRepo.findUpdatedAfter(thirtyDaysAgo)
const allUsers = await userRepo.findAll()
const inactive = allUsers.filter(u => !active.some(a => a.id === u.id))
const ids = inactive.map(u => u.id)

// Mark all as inactive in one operation
await userRepo.updateMany(ids, {
  status: 'inactive',
  reason: 'No activity for 30 days'
})

touchMany

Update updated_at for multiple records without changing any other data.

// Touch multiple users (update their timestamps)
const userIds = [1, 2, 3, 4, 5]
await userRepo.touchMany(userIds)

// Verify all were touched
const touched = await db.selectFrom('users').selectAll().where('id', 'in', userIds).execute()

touched.forEach(user => {
  console.log(user.updated_at) // All have same new timestamp
})

// Empty arrays are handled gracefully
await userRepo.touchMany([]) // No-op

Performance Benefits:

• Single UPDATE query setting only timestamp column
• No data fetched or returned
• Extremely fast even for large batches

Example: Track Session Activity

// Track activity for all users in a session
const activeUserIds = await getActiveUserIds()

// Update their last_seen timestamp
await userRepo.touchMany(activeUserIds)

// Find recently active users
const active = await userRepo.findRecentlyUpdated(100)
console.log(`${active.length} users active recently`)

Example: Refresh Cache TTL

// Keep cache fresh for popular items
const popularItems = await itemRepo.findPopular()
const ids = popularItems.map(i => i.id)

// Touch to refresh TTL
await itemRepo.touchMany(ids)

Combining Batch Operations

All batch operations work seamlessly together:

// 1. Create batch of users
const created = await userRepo.createMany([
  { name: 'User 1', email: '[email protected]' },
  { name: 'User 2', email: '[email protected]' },
  { name: 'User 3', email: '[email protected]' },
  { name: 'User 4', email: '[email protected]' }
])

// 2. Update some users
const idsToUpdate = created.slice(0, 2).map(u => u.id)
await userRepo.updateMany(idsToUpdate, { verified: true })

// 3. Touch others to mark as active
const idsToTouch = created.slice(2).map(u => u.id)
await userRepo.touchMany(idsToTouch)

🎯 Extended Methods

All repositories extended by the timestamps plugin gain these methods:

interface TimestampMethods<T> {
  // Date range queries
  findCreatedAfter(date: Date | string | number): Promise<T[]>
  findCreatedBefore(date: Date | string | number): Promise<T[]>
  findCreatedBetween(start: Date | string | number, end: Date | string | number): Promise<T[]>
  findUpdatedAfter(date: Date | string | number): Promise<T[]>

  // Recent records
  findRecentlyCreated(limit?: number): Promise<T[]>
  findRecentlyUpdated(limit?: number): Promise<T[]>

  // Batch operations
  createMany(inputs: unknown[]): Promise<T[]>
  updateMany(ids: (number | string)[], input: unknown): Promise<T[]>
  touchMany(ids: (number | string)[]): Promise<void>

  // Bypass timestamps
  createWithoutTimestamps(input: unknown): Promise<T>
  updateWithoutTimestamp(id: number, input: unknown): Promise<T>

  // Utilities
  touch(id: number): Promise<void>
  getTimestampColumns(): { createdAt: string; updatedAt: string }
}

getTimestampColumns

Get the configured column names:

const plugin = timestampsPlugin({
  createdAtColumn: 'created',
  updatedAtColumn: 'modified'
})

const orm = await createORM(db, [plugin])
const postRepo = orm.createRepository(/* ... */)

const columns = postRepo.getTimestampColumns()
console.log(columns)
// { createdAt: 'created', updatedAt: 'modified' }

// Useful for:
// - Dynamic query building
// - Migration scripts
// - Documentation generation
// - Runtime introspection

🔧 Advanced Usage

Multiple Plugins

Combine timestamps with other plugins via @kysera/executor's Unified Execution Layer:

import { createExecutor } from '@kysera/executor'
import { timestampsPlugin } from '@kysera/timestamps'
import { softDeletePlugin } from '@kysera/soft-delete'
import { auditPlugin } from '@kysera/audit'

// Register all plugins with Unified Execution Layer
const executor = await createExecutor(db, [
  timestampsPlugin(),
  softDeletePlugin(),
  auditPlugin({ getUserId: () => currentUserId })
])

const orm = await createORM(executor, [])

// All plugins work together seamlessly:
const user = await userRepo.create({ email: '[email protected]', name: 'Test' })
// ✅ created_at added by timestamps plugin
// ✅ deleted_at set to null by soft-delete plugin
// ✅ Audit log created by audit plugin

Transaction Support

Timestamps work seamlessly with transactions:

await db.transaction().execute(async trx => {
  const txRepo = userRepo.withTransaction(trx)

  const user = await txRepo.create({
    email: '[email protected]',
    name: 'Alice'
  })
  // ✅ created_at added

  await txRepo.update(user.id, {
    name: 'Alice Smith'
  })
  // ✅ updated_at added

  // If transaction fails, all changes (including timestamps) are rolled back
})

Conditional Timestamps

Different timestamp strategies per environment:

const plugin = timestampsPlugin({
  getTimestamp: () => {
    if (process.env.NODE_ENV === 'test') {
      // Fixed timestamp for tests
      return '2024-01-01T00:00:00.000Z'
    }

    if (process.env.USE_UTC_MIDNIGHT === 'true') {
      // UTC midnight for batch processing
      const now = new Date()
      now.setUTCHours(0, 0, 0, 0)
      return now.toISOString()
    }

    // Default: current timestamp
    return new Date().toISOString()
  }
})

Repository Pattern with Timestamps

// Define repository factory
function createUserRepository(executor: Executor<Database>) {
  const factory = createRepositoryFactory(executor)
  return factory.create<'users', User>({
    tableName: 'users',
    mapRow: row => ({
      id: row.id,
      email: row.email,
      name: row.name,
      createdAt: new Date(row.created_at), // Convert to Date
      updatedAt: row.updated_at ? new Date(row.updated_at) : null
    }),
    schemas: {
      create: CreateUserSchema,
      update: UpdateUserSchema
    }
  })
}

// Create plugin container with timestamps
const orm = await createORM(db, [timestampsPlugin()])

// Create repository (automatically extended)
const userRepo = orm.createRepository(createUserRepository)

// Use extended methods
const recent = await userRepo.findRecentlyCreated(10)

🗄️ Multi-Database Support

The plugin automatically adapts to your database.

PostgreSQL

import { Kysely, PostgresDialect } from 'kysely'
import { Pool } from 'pg'

const db = new Kysely<Database>({
  dialect: new PostgresDialect({
    pool: new Pool({ /* config */ })
  })
})

// Recommended schema
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) NOT NULL,
  name VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP
);

// Timestamp format: ISO string
const plugin = timestampsPlugin({
  dateFormat: 'iso'  // "2024-01-15T10:30:00.000Z"
})

MySQL

import { Kysely, MysqlDialect } from 'kysely'
import { createPool } from 'mysql2'

const db = new Kysely<Database>({
  dialect: new MysqlDialect({
    pool: createPool({ /* config */ })
  })
})

// Recommended schema
CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255) NOT NULL,
  name VARCHAR(255) NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME ON UPDATE CURRENT_TIMESTAMP
);

// Timestamp format: ISO string or DATETIME
const plugin = timestampsPlugin({
  dateFormat: 'iso'  // Works with DATETIME columns
})

SQLite

import { Kysely, SqliteDialect } from 'kysely'
import Database from 'better-sqlite3'

const db = new Kysely<Database>({
  dialect: new SqliteDialect({
    database: new Database('app.db')
  })
})

// Recommended schema (use TEXT for timestamps)
CREATE TABLE users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  email TEXT NOT NULL,
  name TEXT NOT NULL,
  created_at TEXT,
  updated_at TEXT
);

// Timestamp format: ISO string or Unix
const plugin = timestampsPlugin({
  dateFormat: 'iso'  // Stored as TEXT
})

// Or use Unix timestamps with INTEGER columns
const plugin = timestampsPlugin({
  dateFormat: 'unix'  // Stored as INTEGER
})

🎨 Type Safety

The plugin is fully type-safe with TypeScript.

Type-Safe Repository Extensions

import type { TimestampMethods } from '@kysera/timestamps'

// Repository automatically includes timestamp methods
type UserRepository = Repository<User, Database> & TimestampMethods<User>

const userRepo: UserRepository = orm.createRepository(/* ... */)

// ✅ Type-safe method calls
const recent: User[] = await userRepo.findRecentlyCreated(10)
const after: User[] = await userRepo.findCreatedAfter('2024-01-01')
const between: User[] = await userRepo.findCreatedBetween('2024-01-01', '2024-12-31')

// ❌ Type error: wrong argument type
await userRepo.findCreatedAfter(12345) // Error: number not assignable

// ❌ Type error: method doesn't exist
await userRepo.nonExistentMethod() // Error: method doesn't exist

Database Schema Types

import type { Generated } from 'kysely'

interface Database {
  users: {
    id: Generated<number>
    email: string
    name: string
    created_at: Generated<Date> // Auto-generated
    updated_at: Date | null // Nullable
  }
}

// TypeScript ensures correct column types
const plugin = timestampsPlugin({
  createdAtColumn: 'created_at', // ✅ Must match schema
  updatedAtColumn: 'updated_at' // ✅ Must match schema
})

🔒 Schema Validation (Optional)

The timestamps plugin provides optional Zod schemas for configuration validation. These are exported from a separate subpath to keep Zod as an optional dependency.

Installation

# Zod is optional - only needed if you use schema validation
npm install zod

Usage

import { TimestampsOptionsSchema } from '@kysera/timestamps/schema'

// Validate configuration
const result = TimestampsOptionsSchema.safeParse({
  createdAtColumn: 'created_at',
  updatedAtColumn: 'updated_at',
  setUpdatedAtOnInsert: true,
  dateFormat: 'iso'
})

if (result.success) {
  console.log('Valid options:', result.data)
  const plugin = timestampsPlugin(result.data)
} else {
  console.error('Invalid configuration:', result.error.issues)
}

Schema Fields

The TimestampsOptionsSchema validates:

| Field | Type | Description | |-------|------|-------------| | createdAtColumn | string? | Column name for creation timestamp | | updatedAtColumn | string? | Column name for update timestamp | | setUpdatedAtOnInsert | boolean? | Set updated_at on insert | | tables | string[]? | Whitelist of tables | | excludeTables | string[]? | Blacklist of tables | | getTimestamp | function? | Custom timestamp generator | | dateFormat | 'iso' \| 'unix' \| 'date'? | Timestamp format | | primaryKeyColumn | string? | Primary key column name |

Type Inference

import { TimestampsOptionsSchema, type TimestampsOptionsSchemaType } from '@kysera/timestamps/schema'

// Type is inferred from the schema
const options: TimestampsOptionsSchemaType = {
  createdAtColumn: 'created',
  updatedAtColumn: 'modified',
  dateFormat: 'unix'
}

CLI Integration

The schema is particularly useful for CLI tools and configuration files:

import { TimestampsOptionsSchema } from '@kysera/timestamps/schema'
import { readFileSync } from 'fs'

// Validate config file
const config = JSON.parse(readFileSync('timestamps.config.json', 'utf-8'))
const validated = TimestampsOptionsSchema.parse(config)

📖 API Reference

timestampsPlugin(options?)

Creates a timestamps plugin instance.

Parameters:

interface TimestampsOptions {
  createdAtColumn?: string // Default: 'created_at'
  updatedAtColumn?: string // Default: 'updated_at'
  setUpdatedAtOnInsert?: boolean // Default: false
  tables?: string[] // Default: undefined (all tables)
  excludeTables?: string[] // Default: undefined
  getTimestamp?: () => Date | string | number // Custom generator
  dateFormat?: 'iso' | 'unix' | 'date' // Default: 'iso'
  primaryKeyColumn?: string // Default: 'id'
}

Returns: Plugin instance

Example:

const plugin = timestampsPlugin({
  createdAtColumn: 'created',
  updatedAtColumn: 'modified',
  dateFormat: 'unix'
})

Repository Methods

findCreatedAfter(date)

Find records created after a date.

Parameters:

• date: Date | string | number - Date to compare against

Returns: Promise<T[]>

findCreatedBefore(date)

Find records created before a date.

Parameters:

• date: Date | string | number - Date to compare against

Returns: Promise<T[]>

findCreatedBetween(startDate, endDate)

Find records created between two dates (inclusive).

Parameters:

• startDate: Date | string | number - Start date
• endDate: Date | string | number - End date

Returns: Promise<T[]>

findUpdatedAfter(date)

Find records updated after a date.

Parameters:

• date: Date | string | number - Date to compare against

Returns: Promise<T[]>

findRecentlyCreated(limit?)

Get most recently created records.

Parameters:

• limit?: number - Number of records (default: 10)

Returns: Promise<T[]>

findRecentlyUpdated(limit?)

Get most recently updated records.

Parameters:

• limit?: number - Number of records (default: 10)

Returns: Promise<T[]>

createWithoutTimestamps(input)

Create a record without adding timestamps.

Parameters:

• input: unknown - Create data

Returns: Promise<T>

updateWithoutTimestamp(id, input)

Update a record without modifying updated_at.

Parameters:

• id: number - Record ID
• input: unknown - Update data

Returns: Promise<T>

touch(id)

Update only the updated_at timestamp.

Parameters:

• id: number - Record ID

Returns: Promise<void>

getTimestampColumns()

Get configured column names.

Returns: { createdAt: string; updatedAt: string }

createMany(inputs)

Create multiple records with timestamps in a single bulk INSERT operation.

Parameters:

• inputs: unknown[] - Array of create data objects

Returns: Promise<T[]> - Array of created records

Example:

const users = await userRepo.createMany([
  { name: 'Alice', email: '[email protected]' },
  { name: 'Bob', email: '[email protected]' }
])

updateMany(ids, input)

Update multiple records with the same data, automatically setting updated_at.

Parameters:

• ids: (number | string)[] - Array of record IDs to update
• input: unknown - Update data (applied to all records)

Returns: Promise<T[]> - Array of updated records

Example:

const updated = await userRepo.updateMany([1, 2, 3], {
  status: 'active'
})

touchMany(ids)

Update only the updated_at timestamp for multiple records.

Parameters:

• ids: (number | string)[] - Array of record IDs to touch

Returns: Promise<void>

Example:

await userRepo.touchMany([1, 2, 3, 4, 5])

✨ Best Practices

1. Use Nullable updated_at

// ✅ Good: updated_at is nullable
interface Database {
  users: {
    id: Generated<number>
    created_at: Generated<Date>
    updated_at: Date | null // ✅ Null until first update
  }
}

// ❌ Bad: updated_at not nullable
interface Database {
  users: {
    updated_at: Date // ❌ What value on insert?
  }
}

2. Use Generated for created_at

// ✅ Good: created_at is generated
interface Database {
  users: {
    created_at: Generated<Date> // ✅ Auto-generated
  }
}

// ⚠️ OK but verbose: created_at is optional
interface Database {
  users: {
    created_at: Date // Must be provided or plugin adds it
  }
}

3. Consistent Column Naming

// ✅ Good: Consistent across all tables
const plugin = timestampsPlugin({
  createdAtColumn: 'created_at',
  updatedAtColumn: 'updated_at'
})

// ❌ Bad: Different names per table
// (Use multiple plugin instances instead)

4. Whitelist System Tables

// ✅ Good: Exclude system/config tables
const plugin = timestampsPlugin({
  excludeTables: ['migrations', 'config', 'sessions', 'cache']
})

// ✅ Also Good: Explicit whitelist
const plugin = timestampsPlugin({
  tables: ['users', 'posts', 'comments', 'likes']
})

5. Use ISO Format for Portability

// ✅ Good: ISO strings work everywhere
const plugin = timestampsPlugin({
  dateFormat: 'iso' // Portable across databases
})

// ⚠️ OK: Unix timestamps for performance
const plugin = timestampsPlugin({
  dateFormat: 'unix' // Good for INTEGER columns
})

6. Index Timestamp Columns

-- ✅ Good: Index for timestamp queries
CREATE INDEX idx_users_created_at ON users(created_at);
CREATE INDEX idx_users_updated_at ON users(updated_at);

-- Enables fast queries:
-- - findCreatedAfter
-- - findRecentlyCreated
-- - findUpdatedAfter

7. Use touch() for Activity Tracking

// ✅ Good: Lightweight activity tracking
await userRepo.touch(userId) // Only updates timestamp

// ❌ Bad: Unnecessary data transfer
const user = await userRepo.findById(userId)
await userRepo.update(userId, user) // Fetches + updates all fields

8. Use Batch Operations for Multiple Records

// ✅ Good: Batch operations (single query)
const users = await userRepo.createMany([
  { name: 'User 1', email: '[email protected]' },
  { name: 'User 2', email: '[email protected]' },
  { name: 'User 3', email: '[email protected]' }
])
// 1 INSERT query

// ❌ Bad: Individual creates (N queries)
for (const input of inputs) {
  await userRepo.create(input)
}
// 3 INSERT queries (slow!)

// ✅ Good: Batch update
await userRepo.updateMany([1, 2, 3], { status: 'active' })
// 1 UPDATE + 1 SELECT

// ❌ Bad: Individual updates
for (const id of [1, 2, 3]) {
  await userRepo.update(id, { status: 'active' })
}
// 3 UPDATE queries (slow!)

// ✅ Good: Batch touch
await userRepo.touchMany([1, 2, 3, 4, 5])
// 1 UPDATE query

// ❌ Bad: Individual touches
for (const id of [1, 2, 3, 4, 5]) {
  await userRepo.touch(id)
}
// 5 UPDATE queries (slow!)

⚡ Performance

Plugin Overhead

| Operation | Base | With Timestamps | Overhead | | ----------------------- | ---- | --------------- | -------- | | create | 2ms | 2.05ms | +0.05ms | | update | 2ms | 2.05ms | +0.05ms | | findById | 1ms | 1ms | 0ms | | findRecentlyCreated | - | 5ms | N/A |

Timestamp Format Performance

| Format | Generation Time | Storage Size | Query Performance | | -------- | --------------- | ------------ | ----------------- | | ISO | ~0.001ms | 24-27 bytes | Medium | | Unix | ~0.0005ms | 4-8 bytes | Fast | | Date | ~0.001ms | Varies | Medium |

Recommendation: Use unix for high-performance time-series data, iso for general use.

Query Performance

// ✅ Fast: Uses index
const recent = await userRepo.findRecentlyCreated(10)
// SELECT * FROM users ORDER BY created_at DESC LIMIT 10
// Index: idx_users_created_at

// ✅ Fast: Range query with index
const range = await userRepo.findCreatedBetween(start, end)
// SELECT * FROM users WHERE created_at >= $1 AND created_at <= $2
// Index: idx_users_created_at

// ❌ Slow: No timestamp index
const all = await userRepo.findAll()
const sorted = all.sort((a, b) => b.createdAt - a.createdAt)

Bundle Size

@kysera/timestamps: 2.89 KB (minified)
├── timestampsPlugin: 1.5 KB
├── Query helpers: 1.0 KB
└── Type definitions: 0.39 KB

🔧 Troubleshooting

Timestamps Not Being Set

Problem: created_at is null after creating a record.

Solutions:

1. Check plugin is registered:

// ❌ Plugin not registered
const orm = await createORM(db, [])

// ✅ Plugin registered
const orm = await createORM(db, [timestampsPlugin()])

2. Check table is not excluded:

// Check configuration
const plugin = timestampsPlugin({
  excludeTables: ['users'] // ❌ Users excluded!
})

// Fix: Remove from exclusions
const plugin = timestampsPlugin({
  excludeTables: [] // ✅ No exclusions
})

3. Check column exists in database:

-- Check schema
DESCRIBE users;
-- OR
SELECT column_name FROM information_schema.columns
WHERE table_name = 'users';

Wrong Date Format

Problem: Dates stored as strings instead of timestamps.

Solution: Match dateFormat to column type:

// For TIMESTAMP/DATETIME columns
const plugin = timestampsPlugin({
  dateFormat: 'iso' // ✅ ISO string
})

// For INTEGER columns
const plugin = timestampsPlugin({
  dateFormat: 'unix' // ✅ Unix timestamp
})

// For DATE columns
const plugin = timestampsPlugin({
  dateFormat: 'date' // ✅ Date object
})

Query Methods Not Available

Problem: userRepo.findRecentlyCreated is undefined.

Solution: Ensure you're using the plugin container's createRepository method:

// ❌ Wrong: Direct factory (no plugin extensions)
const factory = createRepositoryFactory(db)
const userRepo = factory.create(/* ... */)
await userRepo.findRecentlyCreated() // ❌ Undefined

// ✅ Correct: Plugin container with plugins
const orm = await createORM(db, [timestampsPlugin()])
const userRepo = orm.createRepository(executor => {
  const factory = createRepositoryFactory(executor)
  return factory.create(/* ... */)
})
await userRepo.findRecentlyCreated() // ✅ Works!

Timestamps in Transactions

Problem: Timestamps not added in transactions.

Solution: Use withTransaction:

await db.transaction().execute(async trx => {
  // ❌ Wrong: Using original repo
  await userRepo.create({
    /* ... */
  })

  // ✅ Correct: Use transaction repo
  const txRepo = userRepo.withTransaction(trx)
  await txRepo.create({
    /* ... */
  }) // ✅ Timestamps added
})

📄 License

MIT © Kysera

🔗 Links

• GitHub Repository
• @kysera/repository Documentation
• @kysera/core Documentation
• Kysely Documentation
• Issue Tracker

Built with ❤️ for effortless timestamp management