Pure PL/pgSQL implementation of RFC 5545 RRULE for PostgreSQL - No C extensions, works everywhere
npm install rrule-plpgsqlPure PL/pgSQL implementation of iCalendar RRULE (RFC 5545) for PostgreSQL. No C extensions, no compilation, works everywhere.
---
This library provides complete RRULE recurrence calculation functionality using pure PL/pgSQL. Use this implementation for all environments (local, development, staging, production) to ensure consistency.
Key Features:
- ✅ No C extensions required - Pure PL/pgSQL, works on any PostgreSQL
- ✅ Full timezone support with DST handling - Wall-clock time preserved ("10 AM stays 10 AM" across DST transitions)
- ✅ Consistent everywhere - Same implementation across all environments
- ✅ Production-ready - Comprehensive test suite with 187 tests (including table operation integration tests)
- ✅ RFC 5545 & RFC 7529 compliant - Supports standard RRULE patterns plus SKIP/RSCALE
- ✅ 50-75x faster than Node.js - Excellent performance without compilation
- ✅ Works on managed services - AlloyDB, RDS, Azure Database for PostgreSQL
---
Computation where your data lives delivers performance impossible with external processing:
- Set-based operations: JOIN recurrence rules against events, bookings, or any table without data transfer
- Native WHERE/aggregation: Filter by occurrence dates, COUNT occurrences, GROUP BY - all in SQL
- Batch processing: Process 100+ schedules in a single query without round trips
- Memory-efficient streaming: SETOF returns results incrementally, not all at once
- 50-75x faster than Node.js for single-schedule operations, infinitely faster for multi-schedule batch queries
See Example Usage for practical patterns including subscription billing, batch updates, and conflict detection.
Pure PL/pgSQL means:
- ✅ No C compiler or build tools required
- ✅ Install with a single SQL file - no configuration
- ✅ Consistent behavior across all environments (dev, staging, production)
Universal compatibility:
- ✅ Google AlloyDB - High-performance PostgreSQL with AI integration
- ✅ Google Cloud SQL - Fully managed PostgreSQL service
- ✅ Amazon Aurora PostgreSQL - Serverless PostgreSQL with automatic scaling
- ✅ Amazon RDS PostgreSQL - Managed PostgreSQL without custom extensions
- ✅ Azure Database for PostgreSQL - Fully supported managed service
- ✅ Self-hosted PostgreSQL - No special configuration required
- ✅ Docker PostgreSQL - Standard postgres image compatibility
---
``bash`
npm install rrule-plpgsql
Then install in your database using your ORM/client. See Installation Guide for TypeScript/ORM integration.
`bashInstall via psql
psql -d your_database -f src/install.sql
Next Steps: See Example Usage for practical patterns.
---
Documentation
$3
- Installation Guide - TypeScript ORMs (node-postgres, TypeORM, Prisma, Knex, Sequelize, Drizzle)
- Example Usage - Real-world patterns: subscription billing, batch operations, conflict detection
- API Reference - Complete function reference with parameters and examples
- RFC Spec Compliance - Supported RFC 5545/7529 features and limitations
- Validation Rules - RRULE constraint validation and error messages
- Development Guide - Testing, architecture, and contribution guidelines
$3
- Security Guide - Best practices and vulnerability reporting
- Performance Optimization - Indexes, query patterns, and scaling recommendations
- Sub-Day Operations - HOURLY/MINUTELY/SECONDLY guide (disabled by default)
- Migration Guide - Upgrading with dependent database objects
---
Key Features
$3
✅ Production-Ready (Always Enabled):
-
- Daily recurrence with date/time filters
- FREQ=WEEKLY - Weekly recurrence with day-of-week selection
- FREQ=MONTHLY - Monthly recurrence with day/week-of-month selection
- FREQ=YEARLY - Yearly recurrence with month/day/week selection⚠️ Advanced (Optional, Disabled by Default):
-
FREQ=HOURLY - Hourly recurrence (8,760/year max)
- FREQ=MINUTELY - Minutely recurrence (525,600/year max)
- FREQ=SECONDLY - Secondly recurrence (31M/year max)See Sub-Day Operations Guide for enabling these safely.
$3
-
COUNT - Limit number of occurrences
- UNTIL - End date for recurrence
- INTERVAL - Frequency multiplier (every N days/weeks/months)
- BYDAY - Filter by day of week (MO, TU, WE, TH, FR, SA, SU)
- BYDAY with ordinals - Positioned weekdays (2MO = 2nd Monday, -1FR = last Friday)
- BYMONTHDAY - Filter by day of month (1-31, -1 = last day)
- BYMONTH - Filter by month (1-12)
- BYYEARDAY - Filter by day of year (1-366, negative supported)
- BYWEEKNO - Filter by ISO week number (1-53, YEARLY only)
- BYSETPOS - Select specific positions from generated set
- WKST - Week start day (SU, MO, TU, WE, TH, FR, SA)
- SKIP - Invalid date handling (OMIT, BACKWARD, FORWARD)
- TZID - Timezone specification with automatic DST handling
- RSCALE - Calendar system (GREGORIAN supported)See RFC Spec Compliance for complete feature support matrix and examples.
$3
Full RFC 5545 timezone support with automatic DST handling:
`sql
-- Meeting stays at 10 AM wall-clock time across DST boundary
SELECT * FROM rrule.all(
'FREQ=DAILY;COUNT=3;TZID=America/New_York',
'2025-03-08 10:00:00'::TIMESTAMP
);
-- Returns: March 8 (EST), March 9 (EDT), March 10 (EDT)
-- All at 10:00 AM wall-clock time
`See API Reference for TIMESTAMPTZ API details.
---
Installation Options
$3
bash
psql -d your_database -f src/install.sql
`Includes:
- DAILY, WEEKLY, MONTHLY, YEARLY frequencies
- All standard modifiers and filters
- Timezone support with DST handling
- Secure by default (no DoS vectors)
$3
bash
psql -d your_database -f src/install_with_subday.sql
`Includes standard PLUS:
- HOURLY, MINUTELY, SECONDLY frequencies
- ⚠️ Security warnings during installation
- Requires application-level validation
See Sub-Day Operations Guide before using this installation.
---
Troubleshooting
$3
Error: "Invalid RRULE: FREQ parameter is required"
- Every RRULE must start with FREQ
- Fix: Add
(or WEEKLY/MONTHLY/YEARLY)Error: "COUNT and UNTIL are mutually exclusive"
- Cannot use both COUNT and UNTIL in same RRULE
- Fix: Use either
COUNT=10 OR UNTIL=20251231T235959Error: "BYWEEKNO can only be used with FREQ=YEARLY"
- BYWEEKNO requires YEARLY frequency
- Fix: Change to
See Validation Rules for complete error reference.
---
API Overview
$3
All functions support both
and TIMESTAMPTZ with automatic timezone handling:`sql
-- Generate occurrences
rrule.all(rrule, dtstart) → SETOF TIMESTAMP
rrule.between(rrule, dtstart, start, end) → SETOF TIMESTAMP-- Query occurrences
rrule.after(rrule, dtstart, after_date) → TIMESTAMP
rrule.before(rrule, dtstart, before_date) → TIMESTAMP
rrule.next(rrule, dtstart) → TIMESTAMP
rrule.most_recent(rrule, dtstart) → TIMESTAMP
-- Utilities
rrule.count(rrule, dtstart) → INTEGER
rrule.overlaps(dtstart, dtend, rrule, mindate, maxdate) → BOOLEAN
`$3
sql
-- Every Monday for 4 weeks
SELECT * FROM rrule.all(
'FREQ=WEEKLY;BYDAY=MO;COUNT=4',
'2025-01-06 10:00:00'::TIMESTAMP
);-- With timezone support
SELECT * FROM rrule.all(
'FREQ=DAILY;COUNT=5;TZID=America/New_York',
'2025-03-08 10:00:00'::TIMESTAMP -- DST handled automatically
);
``See API Reference for complete function signatures, timezone handling details, and advanced examples.
---
Contributions welcome! Please:
1. Run all tests (all 10 test suites must pass)
2. Add test coverage for new features
3. Update documentation
4. Follow RFC 5545/7529 specifications
5. Submit pull request
See Development Guide for contribution guidelines and test suite details.
---
Single schedule: 50-75x faster than Node.js rrule.js
Multi-schedule queries: Eliminates application-database round trips entirely
Scalability advantages:
- Set-based batch operations: Query 100+ schedules in parallel without loops
- Streaming results: SETOF returns incrementally, not all at once (constant memory)
- Query planner optimization: PostgreSQL optimizes JOINs and filters with occurrence expansion
- Early-exit optimizations: Stops computation when COUNT/UNTIL limits reached
See Performance Guide for optimization strategies and Development Guide for benchmarking details.
---
- PostgreSQL 12 or higher
- No C extensions required
- No external dependencies
- Works on all PostgreSQL-compatible platforms
See Development Guide for development setup instructions.
---
- RFC 5545 (iCalendar) - RRULE specification
- RFC 7529 (Non-Gregorian Recurrence Rules) - SKIP and RSCALE parameters
---
- Issues: https://github.com/sirrodgepodge/rrule_plpgsql/issues
- Security: See SECURITY.md
- Documentation: See API Reference
- License: MIT (see LICENSE)