Relations

When Platformatic DB is reading your database schema, it identifies relationships between tables and stores metadata on them in the entity object's relations field. This is achieved by querying the database's internal metadata.

Example

Given this PostgreSQL schema:

CREATE SEQUENCE IF NOT EXISTS categories_id_seq;

CREATE TABLE "categories" (
    "id" int4 NOT NULL DEFAULT nextval('categories_id_seq'::regclass),
    "name" varchar(255) NOT NULL,
    PRIMARY KEY ("id")
);

CREATE SEQUENCE IF NOT EXISTS pages_id_seq;

CREATE TABLE "pages" (
    "id" int4 NOT NULL DEFAULT nextval('pages_id_seq'::regclass),
    "title" varchar(255) NOT NULL,
    "body_content" text,
    "category_id" int4,
    PRIMARY KEY ("id")
);

ALTER TABLE "pages" ADD FOREIGN KEY ("category_id") REFERENCES "categories"("id");

When this code is run:

'use strict'
const { connect } = require('@platformatic/sql-mapper')
const { pino } = require('pino')
const pretty = require('pino-pretty')
const logger = pino(pretty())

async function main() {
  const pgConnectionString = 'postgres://postgres:[email protected]/postgres'
  const mapper = await connect({
    connectionString: pgConnectionString,
    log: logger,
  })
  const pageEntity = mapper.entities.page
  console.log(pageEntity.relations)
  await mapper.db.dispose()
}
main()

The output will be:

[
  {
    constraint_catalog: 'postgres',
    constraint_schema: 'public',
    constraint_name: 'pages_category_id_fkey',
    table_catalog: 'postgres',
    table_schema: 'public',
    table_name: 'pages',
    constraint_type: 'FOREIGN KEY',
    is_deferrable: 'NO',
    initially_deferred: 'NO',
    enforced: 'YES',
    column_name: 'category_id',
    ordinal_position: 1,
    position_in_unique_constraint: 1,
    foreign_table_name: 'categories',
    foreign_column_name: 'id'
  }
]

As Platformatic DB supports multiple database engines, the contents of the relations object will vary depending on the database being used.

The following relations fields are common to all database engines:

• column_name — the column that stores the foreign key
• foreign_table_name — the table hosting the related row
• foreign_column_name — the column in foreign table that identifies the row