Timestamps Plugin

Automatically manage created_at and updated_at timestamps on your entities. Works through @kysera/executor's Unified Execution Layer for consistent behavior across both Repository and DAL patterns.

Installation

npm install @kysera/timestamps

Basic Usage

With Repository Pattern

import { createORM } from '@kysera/repository'
import { timestampsPlugin } from '@kysera/timestamps'

const orm = await createORM(db, [
  timestampsPlugin() // Zero config!
])

const postRepo = orm.createRepository(executor => {
  const factory = createRepositoryFactory(executor)
  return factory.create({ tableName: 'posts' /* ... */ })
})

// created_at is set automatically
const post = await postRepo.create({
  title: 'Hello World',
  content: 'My first post'
})
console.log(post.created_at) // 2024-01-15T10:30:00.000Z

// updated_at is set automatically on update
await postRepo.update(post.id, { title: 'Updated Title' })

With Executor Directly

import { createExecutor } from '@kysera/executor'
import { timestampsPlugin } from '@kysera/timestamps'

const executor = await createExecutor(db, [timestampsPlugin()])

// Timestamps work with direct executor usage
const post = await executor
  .insertInto('posts')
  .values({ title: 'Hello World', content: 'My first post' })
  .returningAll()
  .executeTakeFirst()
// created_at and updated_at set automatically

Configuration

interface TimestampsOptions {
  createdAtColumn?: string // Default: 'created_at'
  updatedAtColumn?: string // Default: 'updated_at'
  setUpdatedAtOnInsert?: boolean // Default: false
  tables?: string[] // Whitelist tables
  excludeTables?: string[] // Blacklist tables
  getTimestamp?: () => Date | string | number
  dateFormat?: 'iso' | 'unix' | 'date' // Default: 'iso'
  primaryKeyColumn?: string // Default: 'id' (only affects touch() method)
  logger?: KyseraLogger
}

Primary Key Column Support

All methods that use ID-based filtering respect the primaryKeyColumn configuration option:

Method	Uses primaryKeyColumn?	Notes
create()	N/A	No ID-based filtering
update()	N/A	No ID-based filtering
touch(id)	✅ Yes	Uses configured primary key
updateMany(ids)	✅ Yes	Uses configured primary key
touchMany(ids)	✅ Yes	Uses configured primary key
createMany()	N/A	No ID-based filtering

UUID Primary Keys

For tables with UUID or custom primary keys, configure primaryKeyColumn:

timestampsPlugin({
  primaryKeyColumn: 'uuid' // All methods will use this column for WHERE clauses
})

Configuration Examples

// Custom column names
timestampsPlugin({
  createdAtColumn: 'created',
  updatedAtColumn: 'modified'
})

// Unix timestamps
timestampsPlugin({
  dateFormat: 'unix',
  getTimestamp: () => Date.now()
})

// Only specific tables
timestampsPlugin({
  tables: ['users', 'posts', 'comments']
})

// Custom timestamp source
timestampsPlugin({
  getTimestamp: () => new Date().toISOString()
})

// Custom primary key (affects all ID-based operations)
timestampsPlugin({
  primaryKeyColumn: 'user_id' // touch(), updateMany(), touchMany() will all use user_id
})

Added Methods

Date Range Queries

Method	Description
findCreatedAfter(date)	Records created after date
findCreatedBefore(date)	Records created before date
findCreatedBetween(start, end)	Records created in range
findUpdatedAfter(date)	Records updated after date

Recent Records

Method	Description
findRecentlyCreated(limit?)	Most recently created (default: 10)
findRecentlyUpdated(limit?)	Most recently updated (default: 10)

Batch Operations

Method	Description
createMany(inputs)	Create with automatic timestamps
updateMany(ids, input)	Update with automatic timestamps
touchMany(ids)	Update only timestamps

Utilities

Method	Description
touch(id)	Update only updated_at
createWithoutTimestamps(input)	Create bypassing plugin
updateWithoutTimestamp(id, input)	Update bypassing plugin
getTimestampColumns()	Get column names

Usage Examples

Recent Records

// Get 10 most recently created posts
const latestPosts = await postRepo.findRecentlyCreated()

// Get 50 most recently created
const latestPosts = await postRepo.findRecentlyCreated(50)

// Get recently updated
const recentlyUpdated = await postRepo.findRecentlyUpdated(25)

Date Range Queries

// Posts from last week
const weekAgo = new Date()
weekAgo.setDate(weekAgo.getDate() - 7)
const recentPosts = await postRepo.findCreatedAfter(weekAgo)

// Posts in date range
const posts = await postRepo.findCreatedBetween('2024-01-01', '2024-01-31')

// Recently modified posts
const updatedPosts = await postRepo.findUpdatedAfter(yesterday)

Touch (Last Activity Tracking)

// Update user's last activity
await userRepo.touch(userId)

// User's updated_at now reflects last activity
const user = await userRepo.findById(userId)
console.log(`User last active: ${user.updated_at}`)

Batch Operations

// Create many with automatic timestamps
const posts = await postRepo.createMany([
  { title: 'Post 1', content: '...' },
  { title: 'Post 2', content: '...' },
  { title: 'Post 3', content: '...' }
])

// Update many - respects primaryKeyColumn configuration
await postRepo.updateMany([1, 2, 3], { status: 'published' })

// Touch many - respects primaryKeyColumn configuration
await postRepo.touchMany([1, 2, 3, 4, 5])

// For UUID primary keys, configure primaryKeyColumn:
// timestampsPlugin({ primaryKeyColumn: 'uuid' })
await userRepo.touchMany(['uuid-1', 'uuid-2', 'uuid-3'])

Bypassing Timestamps

// Create without automatic timestamps
const importedPost = await postRepo.createWithoutTimestamps({
  title: 'Imported Post',
  content: '...',
  created_at: originalCreatedAt // Preserve original date
})

// Update without changing updated_at
await postRepo.updateWithoutTimestamp(postId, {
  view_count: post.view_count + 1
})

Database Compatibility

The timestamps plugin handles database-specific differences in how records are returned after INSERT operations:

RETURNING Clause Support

Database	RETURNING Support	Fallback Behavior
PostgreSQL	✅ Full support	Uses RETURNING * for immediate data access
SQLite	✅ 3.35+	Uses RETURNING * for immediate data access
MySQL	❌ Not supported	Insert then fetch: requires extra SELECT query
MSSQL	⚠️ OUTPUT clause	Uses OUTPUT for single inserts, fallback for batch

How the Fallback Works

For MySQL and batch operations on MSSQL, the plugin uses an insert-then-fetch strategy:

// PostgreSQL/SQLite: Single query with RETURNING
const result = await db.insertInto('posts').values(data).returningAll().executeTakeFirst()

// MySQL/MSSQL fallback: Two queries
await db.insertInto('posts').values(data).execute()
const result = await db.selectFrom('posts').where('id', '=', insertId).executeTakeFirst()

Performance implications:

• MySQL/MSSQL require an extra SELECT query for each insert
• Batch inserts (createMany) use optimized single-query fetching
• Consider using database defaults for timestamps in high-throughput MySQL scenarios

Database Schema

-- PostgreSQL
ALTER TABLE posts ADD COLUMN created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE posts ADD COLUMN updated_at TIMESTAMP;
CREATE INDEX idx_posts_created_at ON posts(created_at DESC);
CREATE INDEX idx_posts_updated_at ON posts(updated_at DESC);

-- MySQL
ALTER TABLE posts ADD COLUMN created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE posts ADD COLUMN updated_at DATETIME;

-- SQLite
ALTER TABLE posts ADD COLUMN created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE posts ADD COLUMN updated_at TEXT;

Performance

The timestamps plugin adds minimal overhead:

Operation	Overhead
create	+0.1ms
update	+0.1ms
findRecentlyCreated	+0.2ms
createMany	Less than 1ms regardless of count

Best Practices

1. Index Timestamp Columns

CREATE INDEX idx_posts_created_at ON posts(created_at DESC);
CREATE INDEX idx_posts_updated_at ON posts(updated_at DESC);

2. Use for Activity Tracking

// Track user activity without explicit field
app.use(async (req, res, next) => {
  if (req.user) {
    await userRepo.touch(req.user.id)
  }
  next()
})

3. Combine with Other Plugins

const orm = await createORM(db, [
  timestampsPlugin(), // Handles timestamps
  softDeletePlugin(), // Handles deleted_at separately
  auditPlugin() // Full audit trail
])