tessl/npm-payloadcms--db-sqlite
The officially supported SQLite database adapter for PayloadCMS with Drizzle ORM integration.
—
Pending
Quality
Pending
Does it follow best practices?
Impact
Pending
No eval scenarios have been run
migration-system.mddocs/
Migration System
Comprehensive database migration utilities for schema management, data transformations, and production deployments. The migration system provides both automated schema migrations and custom migration support.
Capabilities
Migration Function Arguments
Standard arguments provided to all migration functions for database and API access.
interface MigrateUpArgs {
/**
* The SQLite Drizzle instance for direct SQL execution within current transaction
* Provides access to raw SQL execution and Drizzle query builder
*/
db: Drizzle;
/**
* The Payload instance for Local API method execution
* Use with 'req' parameter to maintain transaction context
*/
payload: Payload;
/**
* The PayloadRequest object containing the current transaction
* Required for transactional Local API operations
*/
req: PayloadRequest;
}
interface MigrateDownArgs {
/**
* The SQLite Drizzle instance for direct SQL execution within current transaction
* Provides access to raw SQL execution and Drizzle query builder
*/
db: Drizzle;
/**
* The Payload instance for Local API method execution
* Use with 'req' parameter to maintain transaction context
*/
payload: Payload;
/**
* The PayloadRequest object containing the current transaction
* Required for transactional Local API operations
*/
req: PayloadRequest;
}Production Migrations
Define custom migration functions for production deployments.
interface ProductionMigration {
/** Migration name for tracking and identification */
name: string;
/** Forward migration function */
up: (args: MigrateUpArgs) => Promise<void>;
/** Rollback migration function */
down: (args: MigrateDownArgs) => Promise<void>;
}
interface ProductionMigrationConfiguration {
/** Array of production migration definitions */
prodMigrations?: ProductionMigration[];
}Usage Examples:
import { sqliteAdapter, sql } from '@payloadcms/db-sqlite';
import type { MigrateUpArgs, MigrateDownArgs } from '@payloadcms/db-sqlite';
const adapter = sqliteAdapter({
client: { url: './payload.db' },
prodMigrations: [
{
name: '20240101_add_user_preferences',
async up({ db, payload, req }: MigrateUpArgs) {
// Direct SQL execution
await db.run(sql`
ALTER TABLE users
ADD COLUMN preferences TEXT DEFAULT '{}'
`);
// Use Payload Local API within transaction
const users = await payload.find({
collection: 'users',
req, // Pass req to maintain transaction context
});
for (const user of users.docs) {
await payload.update({
collection: 'users',
id: user.id,
data: {
preferences: JSON.stringify({ theme: 'light' }),
},
req,
});
}
},
async down({ db, payload, req }: MigrateDownArgs) {
// Rollback changes
await db.run(sql`
ALTER TABLE users
DROP COLUMN preferences
`);
},
},
],
});Migration Directory Configuration
Configure custom migration directory paths for automated migrations.
interface MigrationDirectoryConfiguration {
/** Custom path to migration files directory */
migrationDir?: string;
}Usage Examples:
// Default migrations directory (./migrations)
const defaultMigrations = sqliteAdapter({
client: { url: './payload.db' },
// migrationDir not specified - uses default
});
// Custom migrations directory
const customMigrations = sqliteAdapter({
client: { url: './payload.db' },
migrationDir: './database/migrations',
});SQL Template Literal
Utility for constructing raw SQL queries with proper escaping and parameter binding.
/**
* SQL template literal function for safe query construction
* Imported from drizzle-orm with proper SQLite dialect support
*/
declare const sql: <T = unknown>(
strings: TemplateStringsArray,
...values: any[]
) => SQL<T>;Usage Examples:
import { sql } from '@payloadcms/db-sqlite';
// Basic migration with raw SQL
export async function up({ db }: MigrateUpArgs) {
// Create index
await db.run(sql`
CREATE INDEX idx_users_email
ON users(email)
`);
// Insert data with parameters
const userData = { name: 'Admin', email: 'admin@example.com' };
await db.run(sql`
INSERT INTO users (name, email)
VALUES (${userData.name}, ${userData.email})
`);
// Complex query with multiple parameters
const threshold = 100;
const status = 'active';
const results = await db.run(sql`
UPDATE posts
SET status = ${status}
WHERE view_count > ${threshold}
`);
}Migration File Structure
Standard structure for migration files in the migrations directory.
/**
* Standard migration file exports
* Each migration file should export up and down functions
*/
interface MigrationFile {
/** Forward migration function */
up: (args: MigrateUpArgs) => Promise<void>;
/** Rollback migration function */
down: (args: MigrateDownArgs) => Promise<void>;
}Migration File Example:
// migrations/20240101_001_add_user_roles.ts
import type { MigrateUpArgs, MigrateDownArgs } from '@payloadcms/db-sqlite';
import { sql } from '@payloadcms/db-sqlite';
export async function up({ db, payload, req }: MigrateUpArgs): Promise<void> {
// Create roles table
await db.run(sql`
CREATE TABLE user_roles (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER NOT NULL,
role TEXT NOT NULL,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
)
`);
// Migrate existing data using Payload API
const users = await payload.find({
collection: 'users',
req,
});
for (const user of users.docs) {
await db.run(sql`
INSERT INTO user_roles (user_id, role)
VALUES (${user.id}, 'user')
`);
}
}
export async function down({ db }: MigrateDownArgs): Promise<void> {
await db.run(sql`DROP TABLE user_roles`);
}Transaction Context
All migrations run within database transactions, ensuring data consistency.
/**
* Migration execution context
* All migration functions run within a database transaction
* Transaction is automatically committed on success or rolled back on error
*/
interface MigrationContext {
/** Automatic transaction management */
autoTransaction: true;
/** Transaction isolation level */
isolation: 'READ_COMMITTED';
/** Rollback on error */
rollbackOnError: true;
}Best Practices:
export async function up({ db, payload, req }: MigrateUpArgs) {
try {
// All operations are automatically wrapped in a transaction
await db.run(sql`ALTER TABLE posts ADD COLUMN featured BOOLEAN DEFAULT FALSE`);
// Update existing records
await db.run(sql`UPDATE posts SET featured = TRUE WHERE view_count > 1000`);
// Use Payload API (will use same transaction via req)
const featuredPosts = await payload.find({
collection: 'posts',
where: { featured: { equals: true } },
req, // Critical: pass req to maintain transaction context
});
console.log(`Updated ${featuredPosts.totalDocs} featured posts`);
// Transaction commits automatically on success
} catch (error) {
// Transaction rolls back automatically on error
console.error('Migration failed:', error);
throw error; // Re-throw to trigger rollback
}
}Error Handling
Proper error handling patterns for migration functions.
/**
* Migration error handling
* Errors in migration functions trigger automatic transaction rollback
*/
interface MigrationErrorHandling {
/** Automatic rollback on uncaught errors */
autoRollback: true;
/** Error logging and reporting */
errorReporting: boolean;
/** Migration status tracking */
statusTracking: boolean;
}Error Handling Examples:
export async function up({ db, payload, req }: MigrateUpArgs) {
// Validate preconditions
const tableExists = await db.get(sql`
SELECT name FROM sqlite_master
WHERE type='table' AND name='users'
`);
if (!tableExists) {
throw new Error('Users table does not exist - cannot proceed with migration');
}
try {
// Perform migration operations
await db.run(sql`ALTER TABLE users ADD COLUMN last_login DATETIME`);
// Validate migration success
const columnExists = await db.get(sql`
PRAGMA table_info(users)
`).then(info => info.some(col => col.name === 'last_login'));
if (!columnExists) {
throw new Error('Failed to add last_login column');
}
} catch (error) {
// Log detailed error information
console.error('Migration failed at step:', error.message);
console.error('Stack trace:', error.stack);
// Re-throw to trigger rollback
throw error;
}
}Install with Tessl CLI
npx tessl i tessl/npm-payloadcms--db-sqlite