CrateDB Dialect

dialect.name;    // 'cratedb'
dialect.library; // 'pg'  (inherited from PostgresDialect)

$ npm install ts-prorm-orm pg

$ pnpm add ts-prorm-orm pg

$ yarn add ts-prorm-orm pg

$ bun add ts-prorm-orm pg

import { Prorm } from 'ts-prorm-orm';

export const db = new Prorm({
  dialect: 'cratedb',
  host: 'localhost',
  database: 'doc',
  username: 'crate',
});

import { Prorm } from 'ts-prorm-orm';

export const db = new Prorm({
  dialect: 'cratedb',
  url: 'cratedb://crate@localhost:5432/doc',
});

import { DataTypes } from 'ts-prorm-orm';

const User = db.define('User', {
  id: { type: DataTypes.INTEGER(), primaryKey: true, autoIncrement: true },
  email: { type: DataTypes.STRING(255), allowNull: false, unique: true },
  name: { type: DataTypes.STRING(100) },
});

await db.sync(); // CREATE TABLE — see Generated SQL below

import { Op } from 'ts-prorm-orm';

await User.create({ email: 'ada@example.com', name: 'Ada' });

const page = await User.findAll({
  where: { name: { [Op.like]: 'A%' } },
  order: [['id', 'ASC']],
  limit: 20,
  offset: 40,
});

await User.create({ email: 'ada@example.com', name: 'Ada' });
await User.update({ name: 'Grace' }, { where: { email: 'ada@example.com' } });
await User.destroy({ where: { id: 1 } });

await User.upsert({ id: 1, email: 'ada@example.com', name: 'Ada Lovelace' });

await User.findAll({
  where: { name: { [Op.like]: 'A%' } },
  order: [['id', 'ASC']],
  limit: 20,
  offset: 40,
});

await db.transaction(async (t) => {
  await User.create({ email: 'grace@example.com', name: 'Grace' }, { transaction: t });
  await User.update({ name: 'Grace Hopper' }, { where: { email: 'grace@example.com' }, transaction: t });
});

import { Prorm } from 'ts-prorm-orm';

const prorm = new Prorm({
  dialect: 'cratedb',
  host: 'localhost',
  port: 5432, // default; PG-wire compatible
  username: 'crate',
  password: '',
  database: 'doc',
});

CrateDB has no row-level locking, so `lock` cannot be honoured. Remove the `lock` option; a lock the caller believes it holds but does not is worse than an error.

Cannot render WHERE condition at tags.$contains for cratedb: the operator '$contains' is not implemented by its WHERE builder. The statement was not sent, because running it without the condition would match rows it should not.

interface CrateDBTableOptions {
  clusteredInto?: number;      // CLUSTERED INTO n SHARDS
  shards?: number;             // alias for clusteredInto
  clusteredBy?: string;        // CLUSTERED BY (col)
  partitionedBy?: string | string[]; // PARTITIONED BY (col, ...)
  with?: Record<string, string | number | boolean>; // WITH ( key = value, ... )
  fulltextIndexes?: Array<{ name: string; fields: string[]; analyzer?: string }>;
}

const columns = {
  id:      { type: { key: 'BIGINT' }, primaryKey: true },
  day:     { type: { key: 'STRING' } },
  payload: {
    type: {
      key: 'OBJECT',
      columnPolicy: 'DYNAMIC',
      fields: { tags: { key: 'ARRAY', type: { key: 'STRING' } } },
    },
  },
};

dialect.buildCreateTableSQL('events', columns, {
  partitionedBy: 'day',
  clusteredBy: 'id',
  clusteredInto: 6,
  with: { number_of_replicas: '1', '"refresh_interval"': 1000 },
});

dialect.getDataTypeSql({
  key: 'OBJECT',
  columnPolicy: 'DYNAMIC',
  fields: { name: { key: 'STRING' }, age: { key: 'INTEGER' } },
});
// OBJECT(DYNAMIC) AS ("name" TEXT, "age" INTEGER)

dialect.getDataTypeSql({ key: 'ARRAY', type: { key: 'INTEGER' } }); // ARRAY(INTEGER)
dialect.getDataTypeSql({ key: 'ARRAY', type: { key: 'STRING' } });  // ARRAY(TEXT)

dialect.buildCreateTableSQL('docs', columns, {
  clusteredInto: 4,
  fulltextIndexes: [{ name: 'ft_body', fields: ['title', 'body'], analyzer: 'english' }],
});

dialect.buildInsertQuery('users', { id: 1, name: 'Ada' });
// INSERT INTO "users" ("id", "name") VALUES ($1, $2)   values: [1, 'Ada']

dialect.buildSelectQuery({ tableName: 'users', attributes: ['id', 'name'] });
// SELECT "id", "name" FROM "users"

dialect.buildUpdateQuery('users', { name: 'Bob' }, { id: 1 });
// UPDATE "users" SET "name" = $1 WHERE ...

dialect.buildDeleteQuery('users', { id: 1 });
// DELETE FROM "users" WHERE ...

dialect.buildUpsertQuery(
  'users',
  { id: 1, name: 'Ada', email: 'a@x.io' },
  { conflictFields: ['id'], updateOnDuplicate: ['name', 'email'] },
);
// INSERT INTO "users" ("id", "name", "email") VALUES (...)
//   ON CONFLICT ("id") DO UPDATE SET "name" = excluded."name", "email" = excluded."email"

// With a conflict target:
dialect.buildInsertQuery('users', { id: 1, name: 'Ada' },
  { onConflictDoNothing: true, conflictFields: ['id'] });
// INSERT INTO "users" ("id", "name") VALUES ($1, $2) ON CONFLICT ("id") DO NOTHING

// Without a conflict target:
dialect.buildInsertQuery('users', { id: 1 }, { onConflictDoNothing: true });
// INSERT INTO "users" ("id") VALUES ($1) ON CONFLICT DO NOTHING

// With RETURNING (placed after DO NOTHING):
dialect.buildInsertQuery('users', { id: 1 },
  { onConflictDoNothing: true, conflictFields: ['id'], returning: ['id'] });
// INSERT INTO "users" ("id") VALUES ($1) ON CONFLICT ("id") DO NOTHING RETURNING "id"

dialect.buildCreateTableSQL('events', {
  id:       { type: { key: 'BIGINT' }, primaryKey: true },
  owner_id: { type: { key: 'BIGINT' }, references: { table: 'users', field: 'id' } },
}, {
  clusteredInto: 6,
  constraints: [{ type: 'FOREIGN KEY', fields: ['owner_id'], references: { table: 'users', field: 'id' } }],
});
// The output contains "owner_id" BIGINT but NO "FOREIGN KEY" / "REFERENCES".