PostgreSQL DALManager implementation for L10n Monster
npm install @l10nmonster/helpers-pgsqlPostgreSQL implementations for L10n Monster. This package provides:
- PostgresDALManager - Complete replacement for the default SQLite storage
- PgSuperStore - Factory for creating TM and Snap stores backed by PostgreSQL
``bash`
npm install @l10nmonster/helpers-pgsql
`javascript
// l10nmonster.config.mjs
import { config, adapters, providers } from '@l10nmonster/core';
import { PostgresDALManager } from '@l10nmonster/helpers-pgsql';
export default config.l10nMonster(import.meta.dirname)
.dalManager(new PostgresDALManager({
connection: {
host: process.env.PGHOST || 'localhost',
port: parseInt(process.env.PGPORT || '5432'),
database: process.env.PGDATABASE || 'l10nmonster',
user: process.env.PGUSER,
password: process.env.PGPASSWORD,
},
pool: {
min: 2,
max: 10,
}
}))
.channel(/ ... /)
.provider(/ ... /);
`
`javascript`
new PostgresDALManager({
connectionString: 'postgres://user:password@localhost:5432/l10nmonster'
})
`javascript`
new PostgresDALManager({
connectionString: process.env.DATABASE_URL,
ssl: {
rejectUnauthorized: false
}
})
For Google Cloud SQL integration, use createCloudSqlPool from @l10nmonster/helpers-googlecloud:
`javascript
import { PostgresDALManager } from '@l10nmonster/helpers-pgsql';
import { createCloudSqlPool } from '@l10nmonster/helpers-googlecloud';
// Create a Cloud SQL pool with IAM authentication
const pool = await createCloudSqlPool({
instanceConnectionName: 'project:region:instance',
database: 'l10nmonster',
user: 'my-service-account', // Without @project.iam.gserviceaccount.com
authType: 'IAM'
});
export default config.l10nMonster(import.meta.dirname)
.dalManager(new PostgresDALManager({ existingPool: pool }))
// ...
`
---
Factory for creating PostgreSQL-backed TM stores and Snap stores. All stores created from the same factory share a connection pool.
Unified Schema: TMStore uses the same tus_{sourceLang}_{targetLang} tables and jobs table as PostgresDALManager. Data is segregated using:store_id
- column in TU tables (NULL for local, store ID for TMStore)tm_store
- column in jobs table (NULL for local, store ID for TMStore)
`javascript
import { PgSuperStore } from '@l10nmonster/helpers-pgsql';
const superStore = new PgSuperStore({
connectionString: 'postgresql://user:pass@localhost/l10nmonster'
});
// Create stores
const tmStore = superStore.createTmStore({ id: 'my-tm' });
const snapStore = superStore.createSnapStore({ id: 'my-snaps' });
export default config.l10nMonster(import.meta.dirname)
.tmStore(tmStore)
.snapStore(snapStore)
// ...
`
Multiple stores can share the same database tables. Each store's data is tracked via provenance columns:
`javascript
// Production and staging TM stores using the same tables
const productionTm = superStore.createTmStore({
id: 'production-tm',
tmStoreId: 'production' // Stored in store_id column
});
const stagingTm = superStore.createTmStore({
id: 'staging-tm',
tmStoreId: 'staging',
access: 'readonly'
});
`
Key benefit: DAL queries see ALL TUs (regardless of store_id) with global ranking. The best translation wins, whether it came from local work or a synced TMStore.
Filter TM downloads to only include TUs that exist in current content:
`javascript`
const leveragedTm = superStore.createTmStore({
id: 'leveraged-tm',
tmStoreId: 'production',
onlyLeveraged: ['android', 'ios'], // Only TUs in these channels
snapStoreId: 'my-snaps' // Uses snap store for GUID lookup
});
This filters out TUs that aren't "leveraged" (don't exist in active content), reducing sync time and storage.
The snap store uses temporal tables (SCD Type 2) to store only changed data:
`javascript`
const snapStore = superStore.createSnapStore({
id: 'backup-snaps',
snapStoreId: 'backup'
});
Benefits:
- Only stores changed/new/deleted rows (not full copies)
- MD5 hash comparison for fast change detection
- Point-in-time snapshot reconstruction
- Full history preserved
`javascript
import { PgSuperStore } from '@l10nmonster/helpers-pgsql';
import { createCloudSqlPool } from '@l10nmonster/helpers-googlecloud';
const pool = await createCloudSqlPool({
instanceConnectionName: 'project:region:instance',
database: 'l10nmonster',
user: 'my-service-account',
authType: 'IAM'
});
const superStore = new PgSuperStore({ existingPool: pool });
`
| Option | Type | Description |
|--------|------|-------------|
| connectionString | string | PostgreSQL connection URL |connection.host
| | string | Database host (default: 'localhost') |connection.port
| | number | Database port (default: 5432) |connection.database
| | string | Database name (default: 'l10nmonster') |connection.user
| | string | Database user |connection.password
| | string | Database password |pool.min
| | number | Minimum pool size (default: 4) |pool.max
| | number | Maximum pool size (default: 32) |ssl
| | boolean \| object | SSL configuration |existingPool
| | pg.Pool | Pre-configured Pool instance |
| Option | Type | Description |
|--------|------|-------------|
| id | string | Required. Logical store ID |tmStoreId
| | string | Data segregation key (defaults to id) |access
| | 'readwrite' \| 'readonly' \| 'writeonly' | Access mode (default: 'readwrite') |partitioning
| | 'job' \| 'provider' \| 'language' | Partitioning strategy (default: 'language') |onlyLeveraged
| | string[] | Channel IDs to filter TUs by |snapStoreId
| | string | SnapStore ID for onlyLeveraged (required if onlyLeveraged is set) |
| Option | Type | Description |
|--------|------|-------------|
| id | string | Required. Logical store ID |snapStoreId
| | string | Data segregation key (defaults to id) |
---
Complete PostgreSQL replacement for SQLite storage.
| Option | Type | Description |
|--------|------|-------------|
| connectionString | string | PostgreSQL connection URL |connection.host
| | string | Database host (default: 'localhost') |connection.port
| | number | Database port (default: 5432) |connection.database
| | string | Database name (default: 'l10nmonster') |connection.user
| | string | Database user |connection.password
| | string | Database password |pool.min
| | number | Minimum pool size (default: 2) |pool.max
| | number | Maximum pool size (default: 10) |pool.idleTimeoutMillis
| | number | Idle connection timeout (default: 30000) |ssl
| | boolean \| object | SSL configuration |existingPool
| | pg.Pool | Pre-configured Pool instance |
To migrate existing data from SQLite to PostgreSQL:
1. Export your TM to a store:
`bash`
npx l10n tm syncup
2. Update your config to use PostgresDALManager
3. Import from the store:
`bash`
npx l10n tm bootstrap
The package automatically creates the following tables:
- jobs - Job metadata with tm_store column for provenance (NULL=local, string=TMStore)tus_{sourceLang}_{targetLang}
- - Translation units with store_id column for provenance (NULL=local, string=TMStore)
- channel_toc - Channel metadataresources_{channelId}
- - Resources per channelsegments_{channelId}
- - Segments per channel
- snap_resources - Resources with temporal tracking (valid_from/valid_to)snap_segments
- - Segments with temporal trackingsnap_toc
- - Snapshot timestamps and counts
- Node.js >= 22.11.0
- PostgreSQL 12+
- @l10nmonster/core 3.1.1+
Before using the PostgreSQL DAL, create the database:
`bashUsing createdb command
createdb l10nmonster
The required tables will be created automatically on first run.
MIT