# Database

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

## Database Configuration

### SQLite

This is the default database type. The DB file is located in a `db` folder under the current working directory (`process.cwd()`) by default. To override this, please define a custom path:

```js title="index.js"
configuration.dbFolder = import.meta.dirname + '/custom_folder';
```

### PostgreSQL

```js title="index.js"
configuration.postgreConfig = {
  host: 'localhost',
  user: 'postgres',
  password: 'password',
  database: 'test'
};
```

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

```js title="index.js"
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`

### Cloudflare D1

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

```js title="index.js"
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.

```js title="index.js"
import crowdinModule from '@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 Tables

The module also allows you to define custom tables, manage migrations, and use Drizzle ORM for CRUD operations.

Under the hood, the module uses [Drizzle ORM](https://orm.drizzle.team/), giving you access to its full capabilities.

As a prerequisite, install these dependencies:

```bash
npm install drizzle-orm drizzle-kit
```

### Schema

Create a file `schema.js`:

```js
    import { sqliteTable, text, integer } from 'drizzle-orm/sqlite-core';

    export const customTable = sqliteTable('custom_tbl', {
      id: text('id').primaryKey(),
      text: text('custom_text'),
      number: integer('custom_number'),
    });
    ```
  ```js
    import { pgTable, text, integer, serial } from 'drizzle-orm/pg-core';

    export const customTable = pgTable('custom_tbl', {
      id: serial('id').primaryKey(),
      text: text('custom_text'),
      number: integer('custom_number'),
    });
    ```
  ```js
    import { sqliteTable, text, integer } from 'drizzle-orm/sqlite-core';

    export const customTable = sqliteTable('custom_tbl', {
      id: text('id').primaryKey(),
      text: text('custom_text'),
      number: integer('custom_number'),
    });
    ```
  ### Migrations (SQLite and PostgreSQL)

Create a config file `config.js`:

```js
    export default defineConfig({
      schema: './schema.js',
      out: './migrations',
      dialect: 'sqlite',
    });
    ```
  ```js
    export default defineConfig({
      schema: './schema.js',
      out: './migrations',
      dialect: 'postgresql',
    });
    ```
  Then run:

```bash
npx drizzle-kit generate --config ./config.js
```

This command syncs your schema with migrations and creates any missing migration files.

### Migrations (D1)

For Cloudflare D1, declare migrations manually:

```js
const migrations = [
  {
    name: 'create_custom_table',
    run: async (db) => {
      const sql = 'CREATE TABLE custom_tbl (id text PRIMARY KEY NOT NULL, custom_text text, custom_number integer)';
      await db.$client.prepare(sql).run();
    },
  },
];
```

### Defining custom tables in the module

```js title="index.js"
import * as schema from './schema.js';

configuration.customTables = {
  schema,
  // for SQLite or PostgreSQL
  migrationFolder: './migrations',
  // for D1
  d1Migrations: migrations,
};
```

### Type declaration

For type safety, you also need a TypeScript file `types.ts` with the type declaration:

```ts
    import * as schema from './schema.js';

    export type DB = import('drizzle-orm/better-sqlite3').BetterSQLite3Database<typeof schema>;
    ```
  ```ts
    import * as schema from './schema.js';

    export type DB = import('drizzle-orm/node-postgres').NodePgDatabase<typeof schema>;
    ```
  ```ts
    import * as schema from './schema.js';

    export type DB = import('drizzle-orm/d1').DrizzleD1Database<typeof schema>;
    ```
  ### Example of usage

```js title="index.js"
import { eq } from 'drizzle-orm';

/** @type {import('./types').DB} */
// @ts-ignore
const db = crowdinApp.storage.db;

const result = await db.query.customTable.findMany({
  where: eq(schema.customTable.text, 'some value'),
});

await db.insert(schema.customTable).values({
  text: 'text',
  number: 123,
});

await db.update(schema.customTable)
  .set({ text: 'text' })
  .where(eq(schema.customTable.number, 123));

await db.delete(schema.customTable).where(eq(schema.customTable.number, 123));
```

For more examples please check [Drizzle documentation](https://orm.drizzle.team/docs/overview)