@l10nmonster/helpers-pgsql

PostgreSQL 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

Installation

``bash
npm install @l10nmonster/helpers-pgsql
`

Usage

$3

`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(/ ... /);
`

$3

`javascript
new PostgresDALManager({
connectionString: 'postgres://user:password@localhost:5432/l10nmonster'
})
`

$3

`javascript
new PostgresDALManager({
connectionString: process.env.DATABASE_URL,
ssl: {
rejectUnauthorized: false
}
})
`

$3

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 }))
// ...
`

---

PgSuperStore

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)

$3

`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)
// ...
`

$3

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.

$3

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.

$3

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

$3

`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 });
`

$3

| 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 |

$3

| 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) |

$3

| Option | Type | Description |
|--------|------|-------------|
| id | string | Required. Logical store ID |
| snapStoreId | string | Data segregation key (defaults to id) |

---

PostgresDALManager

Complete PostgreSQL replacement for SQLite storage.

Configuration Options

$3

| 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 |

Migration from SQLite

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
`

Database Schema

The package automatically creates the following tables:

$3

- 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)

$3

- channel_toc - Channel metadata
- resources_{channelId} - Resources per channel
- segments_{channelId} - Segments per channel

$3

- snap_resources - Resources with temporal tracking (valid_from/valid_to)
- snap_segments - Segments with temporal tracking
- snap_toc - Snapshot timestamps and counts

Requirements

- Node.js >= 22.11.0
- PostgreSQL 12+
- @l10nmonster/core 3.1.1+

Setup

Before using the PostgreSQL DAL, create the database:

`bash


Using createdb command

createdb l10nmonster

Or via psql

psql -c "CREATE DATABASE l10nmonster;"
``

The required tables will be created automatically on first run.

License

MIT