> ## Documentation Index
> Fetch the complete documentation index at: https://docs.cube.dev/llms.txt
> Use this file to discover all available pages before exploring further.

# Joining data from multiple data sources

> Cross-database rollup joins that relate entities living in different warehouses while keeping aggregation performant.

## Use case

Let's imagine we store information about products and their suppliers in
separate databases. We want to aggregate data from these data sources while
having decent performance. In the recipe below, we'll learn how to create a
[rollup join](/reference/data-modeling/pre-aggregations#rollup_join)
between two databases to achieve our goal.

## Configuration

First of all, we should define our database connections with the `dataSource`
option in the `cube.js` configuration file:

```javascript theme={"dark"}
module.exports = {
  driverFactory: ({ dataSource }) => {
    if (dataSource === "suppliers") {
      return {
        type: "postgres",
        database: "recipes",
        host: "demo-db-recipes.cube.dev",
        user: "cube",
        password: "12345",
        port: "5432"
      }
    }

    if (dataSource === "products") {
      return {
        type: "postgres",
        database: "ecom",
        host: "demo-db-recipes.cube.dev",
        user: "cube",
        password: "12345",
        port: "5432"
      }
    }

    throw new Error("dataSource is undefined")
  }
}
```

## Data modeling

First, we'll define
[rollup](/reference/data-modeling/pre-aggregations#rollup)
pre-aggregations for `products` and `suppliers`. Note that these
pre-aggregations should contain the dimension on which they're joined. In this
case, it's the `supplier_id` dimension in the `products` cube, and the `id`
dimension in the `suppliers` cube:

<CodeGroup>
  ```yaml title="YAML" theme={"dark"}
  cubes:
    - name: products
      # ...

      pre_aggregations:
        - name: products_rollup
          type: rollup
          dimensions:
            - name
            - supplier_id
          indexes:
            - name: category_index
              columns:
                - supplier_id

      joins:
        suppliers:
          sql: "{supplier_id} = {suppliers.id}"
          relationship: many_to_one
  ```

  ```javascript title="JavaScript" theme={"dark"}
  cube("products", {
    // ...

    pre_aggregations: {
      products_rollup: {
        type: `rollup`,
        dimensions: [name, supplier_id],
        indexes: {
          category_index: {
            columns: [supplier_id]
          }
        }
      }
    },

    joins: {
      suppliers: {
        sql: `${supplier_id} = ${suppliers.id}`,
        relationship: `many_to_one`
      }
    },

    // ...
  })
  ```
</CodeGroup>

<CodeGroup>
  ```yaml title="YAML" theme={"dark"}
  cubes:
    - name: suppliers
      # ...

      pre_aggregations:
        - name: suppliers_rollup
          type: rollup
          dimensions:
            - id
            - company
            - email
          indexes:
            - name: category_index
              columns:
                - id
  ```

  ```javascript title="JavaScript" theme={"dark"}
  cube("suppliers", {
    // ...

    pre_aggregations: {
      suppliers_rollup: {
        type: `rollup`,
        dimensions: [id, company, email],
        indexes: {
          category_index: {
            columns: [id]
          }
        }
      }
    }
  })
  ```
</CodeGroup>

Then, we'll also define a `rollup_join` pre-aggregation in the `products` cube,
which will enable aggregating data from multiple data sources:

<CodeGroup>
  ```yaml title="YAML" theme={"dark"}
  cubes:
    - name: products
      # ...

      pre_aggregations:
        - name: combined_rollup
          type: rollup_join
          dimensions:
            - suppliers.email
            - suppliers.company
            - name
          rollups:
            - suppliers.suppliers_rollup
            - products_rollup
  ```

  ```javascript title="JavaScript" theme={"dark"}
  cube("products", {
    // ...

    pre_aggregations: {
      combined_rollup: {
        type: `rollup_join`,
        dimensions: [suppliers.email, suppliers.company, name],
        rollups: [suppliers.suppliers_rollup, products_rollup]
      }
    }
  })
  ```
</CodeGroup>

## Query

Let's get the product names and their suppliers' info, such as company name and
email, with the following query:

```json theme={"dark"}
{
  "order": {
    "products.name": "asc"
  },
  "dimensions": ["products.name", "suppliers.company", "suppliers.email"],
  "limit": 3
}
```

## Result

We'll get the data from two pre-aggregations joined into one `rollup_join`:

```json theme={"dark"}
[
  {
    "products.name": "Awesome Cotton Sausages",
    "suppliers.company": "Justo Eu Arcu Inc.",
    "suppliers.email": "id.risus@luctuslobortisClass.net"
  },
  {
    "products.name": "Awesome Fresh Keyboard",
    "suppliers.company": "Quisque Purus Sapien Limited",
    "suppliers.email": "Cras@consectetuercursuset.co.uk"
  },
  {
    "products.name": "Awesome Rubber Soap",
    "suppliers.company": "Tortor Inc.",
    "suppliers.email": "Mauris@ac.com"
  }
]
```

## Source code

Please feel free to check out the
[full source code](https://github.com/cube-js/cube/tree/master/examples/recipes/joining-multiple-datasources-data)
or run it with the `docker-compose up` command. You'll see the result, including
queried data, in the console.