How to use REFERENCES?

[Zer0xxxx]

Hello!
First of all, thank you a lot for this fp ORM.

I would like to know how to map using References with Orange ORM?
Is it okay to use the by function to reproduce it?

  .map((x) => ({
    my_service: x.my_service.map(({ hasOne }) => ({
      my_module: hasOne(x.my_module).by('supplier_id')
    }))
  }));

Something like:

CREATE TYPE LOCALES_ENUM AS ENUM ('en-US', 'fr', 'it');
CREATE TYPE SUPPLIER_ID AS BIGINT;
CREATE TYPE SUPPLIER_CHANEL_ID AS BIGINT;

CREATE TABLE my_service (
    id BIGSERIAL PRIMARY KEY,
    lang LOCALES_ENUM DEFAULT 'en-US' NOT NULL,
    has_premium BOOLEAN DEFAULT FALSE,

    supplier_id SUPPLIER_ID UNIQUE NOT NULL,
);

CREATE TABLE my_module (
    id BIGSERIAL PRIMARY KEY,

    welcome_message VARCHAR(1024) NOT NULL,
    supplier_chanel_id SUPPLIER_CHANEL_ID UNIQUE NOT NULL,

    supplier_id SUPPLIER_ID UNIQUE NOT NULL,
    FOREIGN KEY (supplier_id) REFERENCES my_service(supplier_id)
);

[Zer0xxxx]

Oops, it is documented in the 📄 map.ts example.
Sorry!

[lroal]

Yes, but your example table seems to have a mix of reference and hasOne: as supplier_id is both on the my_service and my_module.
Also, if you are using references, the left side column needs to point to the primary key of the target table (id of my_module). So there is no point having supplier_id on the my_module.
On other hand, If you want to use hasOne, the supplier_id must be removed
from my_service and use my_module.supplier_id instead.

[Zer0xxxx]

In fact, I'm really new into Databases...

And Orange ORM seems a safe way for me to learn and have a safe approach to use a database with Node.js
I think it could be helpful to see a SQL example in addition to the diagram and 📄 map.ts example for beginners like me. ^^'

Thank you a lot for your answer, gonna think again about what I'm currently trying to implement!

[lroal]

You will find the sql if you expand the 'Mapping tables' section. Though it is Sqlite.

import map from './map';
const db = map.sqlite('demo.db');

const sql = `DROP TABLE IF EXISTS deliveryAddress; DROP TABLE IF EXISTS package; DROP TABLE IF EXISTS orderLine; DROP TABLE IF EXISTS _order; DROP TABLE IF EXISTS customer;
CREATE TABLE customer (
    id INTEGER PRIMARY KEY,
    name TEXT,
    balance NUMERIC,
    isActive INTEGER
);

CREATE TABLE _order (
    id INTEGER PRIMARY KEY,
    orderDate TEXT,
    customerId INTEGER REFERENCES customer
);

CREATE TABLE orderLine (
    id INTEGER PRIMARY KEY,
    orderId INTEGER REFERENCES _order,
    product TEXT,
    amount NUMERIC(10,2)
);

CREATE TABLE package (
    packageId INTEGER PRIMARY KEY,
    lineId INTEGER REFERENCES orderLine,
    sscc TEXT
);

CREATE TABLE deliveryAddress (
    id INTEGER PRIMARY KEY,
    orderId INTEGER REFERENCES _order,
    name TEXT, 
    street TEXT,
    postalCode TEXT,
    postalPlace TEXT,
    countryCode TEXT
)
`;


async function init() {
  const statements = sql.split(';');
  for (let i = 0; i < statements.length; i++) {
    await db.query(statements[i]);
  }
}
export default init;