Connecting a Second Data Source To Graphweaver

This 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:

Create a MySQL Database
Update our connections in Graphweaver
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 installed on your local and that you have Node.js 18 or greater, pnpm version 8 or greater, and the mysql shell client installed on your local 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 “how to” 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 “how to” 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 pnpm install.

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

💡 Important! The version of @mikro-orm/mysql must match the version of @mikro-orm/core in your project.

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

First import the MySQL driver we just installed

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

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.

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

(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.

And 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 and Resolver

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 and resolver for that Todo .

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

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

@ObjectType("Todo")
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;
}

Create a resolver for the Todo ensuring to use the mySQLConnection:

import { createBaseResolver, Resolver } from "@exogee/graphweaver";
import { MikroBackendProvider } from "@exogee/graphweaver-mikroorm";
import { Todo } from "./entity";
import { Todo as OrmTodo } from "../../entities";
import { mySQLConnection } from "../../database";

@Resolver((of) => Todo)
export class TodoResolver extends createBaseResolver<Todo, OrmTodo>(
  Todo,
  new MikroBackendProvider(OrmTodo, mySQLConnection)
) {}

Finally, we need to update our resolver imports and exports so that Graphweaver has the resolver for the new entity:

src/backend/schema/index.ts

import { UserResolver } from "./user";
import { TodoResolver } from "./todo";

export const resolvers = [UserResolver, TodoResolver];

The final folder structure will look like this:

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 user’s from the Todo table

  @SummaryField()
  @Field(() => String, { nullable: 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.