Database

By default, module uses SQLite database to store data. However, it can be configured to use PostgreSQL, MySQL, or Cloudflare D1.

Database Configuration

SQLite

This is the default database type. DB file is located in __dirname/db folder by default. To override this, please define a custom path:

configuration.dbFolder = __dirname + '/custom_folder';

PostgreSQL

configuration.postgreConfig = {
  host: 'localhost',
  user: 'postgres',
  password: 'password',
  database: 'test'
};

To migrate from SQLite to PostgreSQL, add the migrateToPostgreFromSQLite option to your configuration object:

configuration.migrateToPostgreFromSQLite = true;

If the migration fails, check your logs for error details. To roll back to SQLite:

Remove the postgreConfig option
Set migrateToPostgreFromSQLite option to false

MySQL

configuration.mysqlConfig = {
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'test'
};

Cloudflare D1

Cloudflare D1 is a serverless SQL database for Workers deployments. Database tables are created automatically on first request (lazy migrations).

configuration.d1Config = {
  database, // D1 database binding from Cloudflare Workers environment
};

Setup: Create database with npx wrangler d1 create my-app-db, add binding to wrangler.toml, and configure as shown above.

App Metadata

The SDK provides the metadataStore to store metadata. Metadata is a key-value pair that can be used to store any data that is not directly related to the main data model. For example, you can store settings, user preferences, etc.

const crowdinModule = require('@crowdin/app-project-module');

const configuration = {
  // ...
};

const crowdinApp = crowdinModule.createApp(configuration);

// ...

crowdinApp.saveMetadata({ id: 'key', metadata: 'metadata', crowdinId: 'crowdinId' });
crowdinApp.getMetadata('key');

`saveMetadata({ id, metadata, crowdinId })`

Used to save some metadata to be used in other parts of the application. It might be associated with an organization, project, etc.

id - string - The id of the metadata.
metadata - any - The metadata to be saved.
crowdinId - string - The organization ID or domain of the Crowdin project. This parameter is required and must be a valid Crowdin ID. If not provided, the function will throw an error. The ID can be obtained from the JWT payload jwtPayload.domain for Enterprise and jwtPayload.context.organization_id for Crowdin.

`getMetadata(id)`

Used to get metadata.

id - string - The id of the metadata.

`deleteMetadata(id)`

Used to delete metadata. Usually useful in the onUninstall hook.

id - string - The id of the metadata.

`getUserSettings(clientId)`

Used to get settings that users manage in the integration module.

clientId - string - The id of the user.

Custom Database Operations

The database operations methods provide a complete set of tools for working with custom database tables. These methods allow you to create tables, insert new records, select data from tables, update existing records, and delete records from your custom tables.

Database Type Differences

Depending on the database you’re using, the SQL syntax differs slightly:

PostgreSQL: Uses numbered parameters ($1, $2, $3) and requires double quotes for identifiers
SQLite: Uses question marks (?) as placeholders and doesn’t require quotes for identifiers
MySQL: Uses question marks (?) as placeholders and can use backticks for identifiers
Cloudflare D1: Uses question marks (?) as placeholders, similar to SQLite syntax

Example of WHERE clause in different database types:

WHERE "organizationId" = $1 AND "projectId" = $2

WHERE organizationId = ? AND projectId = ?

WHERE `organizationId` = ? AND `projectId` = ?

WHERE organizationId = ? AND projectId = ?

Column Data Types

Different databases use different syntax for common column types:

Type	PostgreSQL	SQLite	MySQL	Cloudflare D1
Auto-incrementing primary key	`SERIAL PRIMARY KEY`	`INTEGER PRIMARY KEY AUTOINCREMENT`	`INT AUTO_INCREMENT PRIMARY KEY`	`INTEGER PRIMARY KEY AUTOINCREMENT`
Integer	`INTEGER`	`INTEGER`	`INT`	`INTEGER`
Text	`TEXT`	`TEXT`	`TEXT`	`TEXT`
Timestamp	`TIMESTAMP`	`TIMESTAMP`	`DATETIME`	`TIMESTAMP`

When defining your table schema, use the appropriate syntax for your database type:

await crowdinApp.storage.registerCustomTable('translations', {
  id: 'SERIAL PRIMARY KEY',
  content: 'TEXT',
  created_at: 'TIMESTAMP',
});

await crowdinApp.storage.registerCustomTable('translations', {
  id: 'INTEGER PRIMARY KEY AUTOINCREMENT',
  content: 'TEXT',
  created_at: 'TIMESTAMP',
});

await crowdinApp.storage.registerCustomTable('translations', {
  id: 'INT AUTO_INCREMENT PRIMARY KEY',
  content: 'TEXT',
  created_at: 'DATETIME',
});

await crowdinApp.storage.registerCustomTable('translations', {
  id: 'INTEGER PRIMARY KEY AUTOINCREMENT',
  content: 'TEXT',
  created_at: 'TIMESTAMP',
});

`registerCustomTable`

Function to register a new custom table with a specified schema. It accepts the following parameters:

tableName - the name of the table to create
schema - an object defining the table structure, where keys are column names and values are SQL data types

await crowdinApp.storage.registerCustomTable('translations', {
  id: 'SERIAL PRIMARY KEY',
  organizationId: 'INTEGER',
  projectId: 'INTEGER',
  stringId: 'INTEGER',
  content: 'TEXT',
  targetLanguage: 'TEXT',
  created_at: 'TIMESTAMP',
});

`insertRecord`

Function to insert a new record into a specified table. It accepts the following parameters:

tableName - the name of the table to insert into
data - an object containing the record data, where keys are column names and values are the data to insert

await crowdinApp.storage.insertRecord('translations', {
  organizationId: 1,
  projectId: 2,
  stringId: 3,
  content: 'Hello World',
  targetLanguage: 'es',
  created_at: new Date(),
});

`selectRecords`

Function to query records from a specified table. It accepts the following parameters:

tableName - the name of the table to query
options - object with query parameters:
- columns - array of column names to select
- whereClause - SQL WHERE clause for filtering using $1, $2, etc. for parameters
- orderBy - SQL ORDER BY clause for sorting
- limit - maximum number of records to return
- offset - number of records to skip before starting to return results (used for pagination)
- distinct - whether to return distinct records
params - array of parameters for the WHERE clause

const records = await crowdinApp.storage.selectRecords('translations', {
  columns: ['id', 'content', 'created_at'],
  whereClause: 'WHERE "organizationId" = $1 AND "projectId" = $2 AND "stringId" = $3',
  orderBy: 'created_at DESC',
  limit: 10,
}, [orgId, projectId, stringId]);

`updateRecord`

Function to update existing records in a specified table. It accepts the following parameters:

tableName - the name of the table to update
data - an object containing the updated data
whereClause - SQL WHERE clause to identify records to update using $1, $2, etc. for parameters
params - optional array of parameters for the WHERE clause

await crowdinApp.storage.updateRecord('translations', {
  content: 'Updated translation',
}, 'WHERE "organizationId" = $1 AND "projectId" = $2 AND "stringId" = $3', [orgId, projectId, stringId]);

`deleteRecord`

Function to delete records from a specified table. It accepts the following parameters:

tableName - the name of the table to delete from
whereClause - SQL WHERE clause to identify records to delete using $1, $2, etc. for parameters
params - optional array of parameters for the WHERE clause

await crowdinApp.storage.deleteRecord('translations',
  '"WHERE organizationId" = $1 AND "projectId" = $2 AND "stringId" = $3 AND "targetLanguage" = $4',
  [orgId, projectId, stringId, targetLang],
);