Connect a Second Data Source

This tutorial outlines the steps for connecting a MySQL database to a Graphweaver instance with an existing Postgres database connection. In addition, we will connect entities across data sources. Users in the Postgres database will have Todos in the MySQL database. We can accomplish this in three steps:

1. Create a MySQL Database
2. Update our connections in Graphweaver
3. Create new data and schema entities representing the MySQL data

Prerequisites for adding a second datasource

In this guide we are assuming you already have a Graphweaver instance running and connected to a Postgres data source. If you don’t have this already set up, you can follow our Connect To Postgres tutorial. This tutorials starts from the end of that one.

We also want to ensure you have MySQL, Node.js version 18 or greater, pnpm version 8 or greater, and the mysql shell client installed installed on your local machine before continuing.

Step 1: Installing and Configuring MySQL

The first step in to make sure that we have the configuration settings correct in MySQL. For this tutorial, we are going to assume that you have MySQL running locally on port 3306.

If you have it running on another port then remember to change this in the Graphweaver configuration below.

Next, you are going to need a user in MySQL that we will use when connecting from Graphweaver. For this tutorial, we are going to assume that you have a user root and an empty password.

Here are the settings that we will need:

• user: root
• password: ''
• port: 3306

Next, let’s setup a database to use as an example.

Step 2: Creating the MySQL Database

Let’s seed that MySQL database with a simple table and some sample “todo” data.

Let’s start by creating the database:

mysql -h 127.0.0.1 -P 3306 -u root -p -e "create database gw";

Next let’s create the todo table:

mysql -h 127.0.0.1 -P 3306 -u root -p gw -e "CREATE TABLE todo (  id INT AUTO_INCREMENT PRIMARY KEY,  description VARCHAR(250) NOT NULL,  is_completed BOOLEAN NOT NULL DEFAULT false,  user_id INT NOT NULL);";

Lastly, let’s create some dummy todos in the table:

mysql -h 127.0.0.1 -P 3306 -u root -p gw -e "INSERT INTO todo (description, is_completed, user_id) VALUES    ('Get Groceries', true, 1),    ('Do Laundry', false, 2),    ('Go To Gym', false, 2),    ('Study', false, 3);"

Great! Now let’s update our connections in Graphweaver to include that data source

Step 3: Updating the connections in Graphweaver

Since we’re adding a new MySQL data source to Graphweaver, we need to ensure that @mikro-orm/mysql is installed. Please add this to your package.json dependencies and run pnpm install.

    "@mikro-orm/core": "5.4.2",
    "@mikro-orm/mysql": "5.4.2",
    "@mikro-orm/postgresql": "5.4.2",

Next, let’s update the database.ts file to connect to our MySQL server.

1. First, import the MySQL driver we just installed:

import { MySqlDriver } from "@mikro-orm/mysql";

2. Create the new connection object, matching the configuration created in Step 1 Creating the MySQL Database. Note that we have not created the Todo entity yet.
Copy

export const mySQLConnection = {
    connectionManagerId: "mysql",
    mikroOrmConfig: {
        entities: [Todo], // this will be implemented in the next step    
        driver: MySqlDriver,
        dbName: "gw",
        user: "root",
        password: "",
        port: 3306,
    },
};

3. (Optional) Rename the existing Postgres Connection to be more specific

export const postgresConnection = {
    connectionManagerId: "postgresql",
    mikroOrmConfig: {
        entities: [User], // This has not been properly exported yet, will handle in next step    
        driver: PostgreSqlDriver,
        dbName: "gw",
        user: "postgres",
        password: "",
        port: 5432,
    },
};

Finally, we need to rework our folder structure so that we can import from multiple data sources.

If you’ve followed our Connect To Postgres tutorial or used the import command to build your schema, your folder structure will look something like this:

Let’s create a new directory in backend/entities for mysql and create a new data entity in that folder for the Todo.

src/backend/entities/mysql/todo.ts

import { Entity, PrimaryKey, Property, Unique } from "@mikro-orm/core";
import { BaseEntity } from "@exogee/graphweaver-mikroorm";

@Entity({ tableName: "todo" })
export class Todo extends BaseEntity {
  @PrimaryKey({ type: "int8" })
  id!: string;

  @Property({ type: "string", length: 250 })
  description!: string;

  @Property({ type: "boolean", default: false })
  isCompleted!: boolean;

	@ExternalIdField({ from: "user" })
  @Property({ type: "int8", nullable: false })
  userId!: string;
}

You can read more about data entities on our Data Entities page.

Export that entity with an index.ts

export * from "./todo";

Similarly export the existing Postgres entities into a parent level index.ts, and export everything from both of those directories.

src/backend/entities/index.ts

export * from "./postgresql";
export * from "./mysql";

The folder structure will look like this:

Lastly import the data entities and provide them to their appropriate data source connection in database.ts:

import { PostgreSqlDriver } from "@mikro-orm/postgresql";
import { MySqlDriver } from "@mikro-orm/mysql";
import { User, Todo } from "./entities";

export const postgresConnection = {
    connectionManagerId: "postgresql",
    mikroOrmConfig: {
        entities: [User],
        driver: PostgreSqlDriver,
        dbName: "gw",
        user: "postgres",
        password: "",
        port: 5432,
    },
};

export const mySQLConnection = {
    connectionManagerId: "mysql",
    mikroOrmConfig: {
        entities: [Todo],
        driver: MySqlDriver,
        dbName: "gw",
        user: "root",
        password: "",
        port: 3306,
    },
};

Step 4: Create the Schema Entity

Now that we have the MySQL database connected to Graphweaver and the data entity for Todo created, the last step is to create the schema entity for that Todo .

In /schema, create a Todo schema entity. Note the user relationship.

import {
  Field,
  GraphQLEntity,
  ID,
  ObjectType,
  RelationshipField,
} from "@exogee/graphweaver";
import { Todo as OrmTodo } from "../../entities";
import { User } from "../user";
import { mySQLConnection } from '../database';

@Entity("Todo", {
	provider: new MikroBackendProvider(OrmTodo, myConnection),
})
export class Todo extends GraphQLEntity<OrmTodo> {
  public dataEntity!: OrmTodo;

  @Field(() => ID)
  id!: string;

  @Field(() => String)
  description!: string;

  @Field(() => String)
  isCompleted!: string;

  @RelationshipField<Todo>(() => User, { id: "userId" })
  user!: User;
}

As one last update let’s set the User’s username field to be a SummaryField. This allows us to see the username instead of the id, when viewing the users from the Todo table

  @Field(() => String, { nullable: true, { adminUIOptions: { summaryField: true } })
  username?: string;

Congratulations! 🎊

You can now start Graphweaver again with pnpm start and view the two data sources in the Admin UI or make a query for them in the GraphQL playground. You have successfully connected data from across two different data sources.