AlloyDB Dialect

$ 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: 'alloydb',
  host: '10.0.0.5',
  database: 'app',
  username: 'app',
  password: process.env.DB_PASSWORD,
});

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

export const db = new Prorm({
  dialect: 'alloydb',
  url: 'alloydb://app:secret@10.0.0.5:5432/app',
});

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) },
  meta: { type: DataTypes.JSONB() },
});

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

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

export const db = new Prorm({
  dialect: 'alloydb',
  host: '10.0.0.5',
  database: 'app',
  username: 'app',
  password: process.env.DB_PASSWORD,
});

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

export const db = new Prorm({
  dialect: 'alloydb',
  host: '10.0.0.5',
  database: 'app',
  username: 'svc@my-project.iam.gserviceaccount.com', // suffix stripped for you
  dialectOptions: {
    iam: true, // Application Default Credentials
  },
});

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

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

await User.findAll({ where: { meta: { [Op.contains]: { plan: 'pro' } } } });

await db.transaction(async (t) => {
  await User.create({ email: 'grace@example.com', name: 'Grace' }, { transaction: t });
  // A nested transaction is a SAVEPOINT; its failure undoes only its own work.
  await db
    .transaction(async (sp) => {
      await User.destroy({ where: { id: 1 }, transaction: sp });
      throw new Error('keep the row');
    }, { transaction: t })
    .catch(() => {});
});

await db.transaction(async (t) => {
  await User.findAll({ where: { name: 'Ada' }, lock: { level: 'UPDATE', skipLocked: true }, transaction: t });
});

import { Prorm } from 'ts-prorm-orm';
// or, constructed directly:

const orm = new Prorm({
  dialect: 'alloydb',
  host: 'localhost',
  // port defaults to 5432
  username: 'postgres',
  password: process.env.PG_PASSWORD,
  database: 'app',
});

// equivalently:
const d = new Prorm({
  dialect: 'alloydb',
  host: 'localhost',
  database: 'app',
});

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

const orm = new Prorm({
  dialect: 'alloydb',
  host: '10.0.0.5', // the instance's IP (direct connection)
  database: 'app',
  username: 'svc@my-project.iam.gserviceaccount.com', // suffix stripped for you
  dialectOptions: {
    iam: true, // Application Default Credentials
    // iam: { keyFilename: '/secrets/sa.json' } | { credentials } | { authClient }
    // iam: { tokenProvider }   // any TokenProvider; google-auth-library is not loaded
  },
});

[prorm alloydb] Connection is encrypted but the server certificate is not verified
(ssl.rejectUnauthorized is false): AlloyDB direct connections don't support verify-ca.
To verify the server, connect through the AlloyDB Auth Proxy (mTLS) or pass your own
`ssl.ca`. To require verification instead of falling back, set tls: 'verify'.

new Prorm({
  dialect: 'alloydb',
  username: 'svc@proj.iam.gserviceaccount.com',
  dialectOptions: {
    iam: true,
    tls: 'verify', // throws unless you also pass `ssl` yourself
  },
});

d.createExtensionSQL('google_ml_integration');
// CREATE EXTENSION IF NOT EXISTS "google_ml_integration" CASCADE
d.createExtensionSQL('alloydb_scann'); // pulls in "vector"
// CREATE EXTENSION IF NOT EXISTS "alloydb_scann" CASCADE

await d.createExtension('plpython3u');
// throws: "AlloyDB cannot install 'plpython3u': untrusted procedural
// languages require a PostgreSQL superuser, and AlloyDB is managed — the
// highest role is alloydbsuperuser. Use a trusted language such as plpgsql..."

await d.createExtension('vector', { ifNotExists: true }); // delegates to Postgres
// CREATE EXTENSION IF NOT EXISTS "vector"

d.columnarEngineAdd('public.orders', ['total', 'placed_at']);
// SELECT google_columnar_engine_add(relation => 'public.orders', columns => 'total,placed_at')
d.columnarEngineAdd('orders');
// SELECT google_columnar_engine_add(relation => 'orders')

d.columnarEngineDrop('orders', ['total']);
// SELECT google_columnar_engine_drop(relation => 'orders', columns => 'total')
d.columnarEngineEstimate('orders');
// SELECT google_columnar_engine_estimate(relation => 'orders')
d.columnarEngineRefresh('orders');
// SELECT google_columnar_engine_refresh(relation => 'orders')

d.columnarEngineRecommend();
// SELECT google_columnar_engine_recommend()
d.columnarEngineRecommend('RECOMMEND_SIZE');
// SELECT * FROM google_columnar_engine_recommend(mode => 'RECOMMEND_SIZE')
d.columnarEngineResetRecommendation(true);
// SELECT google_columnar_engine_reset_recommendation(drop_columns => true)

d.columnarEngineAddIndex('docs_emb');
// SELECT google_columnar_engine_add_index(index => 'docs_emb')
d.columnarEngineDropIndex('docs_emb');
// SELECT google_columnar_engine_drop_index(index => 'docs_emb')

d.buildEmbeddingQuery('text-embedding-005', 'waterproof boots');
// { sql: 'SELECT embedding($1, $2)::vector AS "embedding"', values: [...] }

d.buildEmbedColumnUpdate({
  tableName: 'docs',
  sourceColumn: 'body',
  targetColumn: 'emb',
  model: 'text-embedding-005',
  where: { lang: 'en' },
  onlyMissing: true,
});
// UPDATE "docs" SET "emb" = embedding($1, "body")::vector
//   WHERE "lang" = $2 AND "emb" IS NULL
// values: ['text-embedding-005', 'en']

d.buildPredictRowQuery('publishers/google/models/gemini-2.0-flash', { x: 1 });
// SELECT ml_predict_row($1, $2::json) AS "prediction"

d.buildPredictRowByModelIdQuery('my-model', { x: 1 });
// SELECT google_ml.predict_row(model_id => $1, request_body => $2::json) AS "prediction"

d.buildCreateModelCall({
  modelId: 'emb',
  provider: 'google',
  type: 'text_embedding',
  qualifiedName: 'text-embedding-005',
  authType: 'alloydb_service_agent_iam',
});
// CALL google_ml.create_model(model_id => $1, model_provider => $2,
//   model_type => $3, model_qualified_name => $4, model_auth_type => $5)

d.buildDropModelCall('emb');
// CALL google_ml.drop_model($1)

d.buildSimilaritySearchQuery({
  tableName: 'docs',
  column: 'emb',
  metric: 'cosine',
  text: 'waterproof boots',
  model: 'text-embedding-005',
  where: { lang: 'en' },
  attributes: ['id', 'title'],
  distanceAs: 'distance',
  limit: 5,
});
// SELECT "id", "title", "emb" <=> embedding($2, $3)::vector AS "distance"
//   FROM "docs" WHERE "lang" = $1
//   ORDER BY "emb" <=> embedding($2, $3)::vector LIMIT 5
// values: ['en', 'text-embedding-005', 'waterproof boots']

d.buildSimilaritySearchQuery({
  tableName: 'docs',
  column: 'emb',
  metric: 'l2',
  vector: [0.1, -2, 3e-4],
  limit: 10,
});
// SELECT * FROM "docs" ORDER BY "emb" <-> $1::vector LIMIT 10
// values: ['[0.1,-2,0.0003]']

d.buildCreateScannIndexSql('docs', { name: 'docs_emb', column: 'emb', distance: 'cosine' });
// CREATE INDEX "docs_emb" ON "docs" USING scann ("emb" cosine)

d.buildCreateScannIndexSql('docs', {
  name: 'docs_emb',
  column: 'emb',
  distance: 'dot_product',
  numLeaves: 1000,
  quantizer: 'SQ8',
  maxNumLevels: 2,
  autoMaintenance: true,
});
// CREATE INDEX "docs_emb" ON "docs" USING scann ("emb" dot_product)
//   WITH (mode = 'MANUAL', num_leaves = 1000, quantizer = 'SQ8',
//         max_num_levels = 2, auto_maintenance = true)

d.buildScannSettingSql('scann.num_leaves_to_search', 20);
// SET scann.num_leaves_to_search = 20

await d.addIndex('t', 'i', ['a'], { where: { b: { [Op.gte]: 5 } } });
// CREATE INDEX "i" ON "t" ("a") WHERE "b" >= 5
await d.addIndex('t', 'j', ['a']); // no predicate: delegates straight to PostgresDialect.addIndex
// CREATE INDEX "j" ON "t" ("a")