Skip to Content

Polyglot persistence for PostgreSQL & MySQL using GraphQL & TypeScript

Published: September 09, 2021
Jens Neuse

Jens Neuse

Should you use PostgreSQL or MySQL for your next project? Are you moving cloud providers and don't have both options available? Maybe you'd like to start with one option and be able to make the switch later on. Why decide now when you can have both options without any tradeoffs?

Adding a layer of abstraction, a GraphQL API, allows you to swap database engines without having to change any UI or business logic related code.

Let's say we'd like to build a realtime chat app using Next.JS and Typescript.

There's a few things we want to achieve:

  • the API should be typesafe end-to-end, meaning: typescript models for a good developer experience
  • switching the database engine shouldn't change the API contract
  • perfect integration with NextJS using generated hooks
  • realtime updates as the data changes

Not into reading?#

Skip the blog post and try it yourself!

Creating the API#

WunderGraph allows you to introspect different DataSources and turn them into a single GraphQL API. We've started out with support for GraphQL & REST (OpenAPI), then added Apollo Federation (including subscriptions). Recently, we've added support for PostgreSQL. Today we're adding MySQL to the list, allowing for a polyglot persistence layer. There's more to come but let's focus on the two database engines for now.

How do we turn a database into an executable GraphQL schema? Introspection!

Here's an example for PostgreSQL:

const db = introspect.postgresql({
database_querystring: "postgresql://admin:admin@localhost:54322/example?schema=public",
});

Using this single command turns your database into a GraphQL API. The complete configuration file can be found here.

The database schema looks like this:

create table if not exists users
(
id serial primary key not null,
email varchar(255) not null,
name text not null,
unique (email)
);
create table if not exists messages
(
id serial primary key not null,
users_id int not null,
message text not null,
constraint user_id
foreign key (users_id) references users (id)
on delete CASCADE
on update RESTRICT
);

This is the equivalent for MySQL:

const db = introspect.mysql({
database_querystring: "mysql://admin:admin@localhost:54333/example",
});

The MySQL database schema looks like this:

create table if not exists users
(
id serial primary key not null,
email varchar(255) not null,
name text not null,
unique (email)
);
create table if not exists messages
(
id serial primary key not null,
users_id BIGINT UNSIGNED not null,
message text not null,
constraint user_id
foreign key (users_id) references users (id)
on delete CASCADE
on update RESTRICT
);

The resulting db object can be used to create a WunderGraph application.

What's important to note is that the APIs will look exactly the same if the database schemas match!

If you look closely, you'll see that there are minor differences in the database schema due to the way each database works, but in general all tables and fields match another to make them compatible.

Defining the API contract#

Let's now implement the core functionality of a realtime chat application. Users want to add a message to the chat as well as get the most recent messages as a stream.

Once we enter the chat, we'd like to load the most recent 20 messages and keep the UI updated. With WunderGraph, you're able to turn any Query into a "Live-Query" automatically, we'll look into that later.

query Messages {
findManymessages(take: 20 orderBy: [{id: desc}]){
id
message
users {
id
name
}
}
}

Next, we'd like to define a method to add messages to the Chat. You can see a special feature of WunderGraph in this example. We allow you to inject claims (name value pairs of information about the authenticated user) into a Query.

Simply put, using the "AddMessage" mutation requires the user to be authenticated. Authentication can be delegated to any OpenID Connect provider, e.g. Google.

Once the user is authenticated, we store some of their claims in a cookie so that you're able to inject them easily into your GraphQL Operations.

If you thought this use case needs custom business logic or a backend, nope. =)

Finally, the $message variable is not annotated, the user is allowed to provide this value.

Keep in mind that we're also generating a JSON-Schema for the inputs that only allows the field message which must be of type String. So, in terms of security, nothing to worry about.

mutation AddMessage (
$email: String! @fromClaim(name: EMAIL)
$name: String! @fromClaim(name: NAME)
$message: String!
) {
createOnemessages(data: {message: $message users: {connectOrCreate: {create: {name: $name email: $email} where: {email: $email}}}}){
id
message
}
}

Using the API contract#

Ok, we've fined our API, now let's make use of it.

WunderGraph takes the information from the database and generates a GraphQL schema from it. Next, we'll parse all Queries, Mutations & Subscriptions and generate a typesafe client from it. Additionally, we generate all the required backend middleware so that the generated client has a counterpart which understands exactly what the client wants.

You don't have to install any extra dependencies. Define your DataSources, write your Queries and Mutations, then wunderctl up and magic happens.

Finally, we can put everything together to build our realtime chat application.

// generated Hook to add messages
const {mutate: addMessage, response: messageAdded} = useMutation.AddMessage();
// generated Hook to live-update the UI when new messages are added
const {response: loadMessages} = useLiveQuery.Messages();

That's it, all you have to do is glue together your UI with the generated hooks. We want developers to focus on their application, not writing glue code or trying to figure out how to generate typescript Types from GraphQL schemas.

Guess what happens when you swap PostgreSQL for MySQL or vice versa? Nothing! The GraphQL Schema doesn't change, queries and mutations won't change either. You might want to migrate over your data but that's another concern which WunderGraph is not trying to solve.

Outlook#

Building the foundation of all this was the hard part, like building the engine, the middleware, the code generators etc...

Adding more languages will be the easy part. It's a matter of time until we expand generating clients to other languages, making easy database (and API) access available to many other languages.

Conclusion#

We've shown you a way how you can leverage a GraphQL abstraction layer to decouple your application from a specific database engine.

You could start a project with PostgreSQL and later on decide to move to Planetscale.

WunderGraph gives you the flexibility to select the best possible database for your next Project.

If both PostgreSQL and MySQL do not satisfy your needs, how about faunaDB or dgraph? Both offer a GraphQL API out of the box, making them excellent candidates to include in your WunderGraph project.

Additionally, you learned that you can save a lot of time because you don't always need a custom backend. WunderGraph happily accepts GraphQL and REST APIs as upstream, so you're free to implement your own custom backend and use this together with the WunderGraph layer.

Want to see it in action?#

Video coming soon!

Try it out yourself!#

Here's a link to the repo so you can play with the application yourself.

More info on how to use databases with WunderGraph,

What next?#

What's your opinion on this approach? What could you build with this? Join us on discord and discuss. Otherwise, you can also book a Meeting and we'll discuss options in person.

About the Author
Jens Neuse

Jens Neuse

Jens has experience in building native apps for iOS and Android, built hybrid apps with Xamarin, React Native and Flutter, worked on backends using PHP, Java and Go. He's been in roles ranging from development to architecture and led smaller and larger engineering teams.

Throughout his whole career he realized that working with APIs is way too complicated, repetitive and needs a lot more standardization and automation. That's why he started WunderGraph, to make usage of APIs and collaboration through APIs easier.

He believes that businesses of the future will be built on top of collaborative systems that are connected through APIs. Making usage, exploration, sharing and collaboration with and through APIs easier is key to achieve this goal.

Follow and connect with Jens to exchange ideas or simply participate in his feed of thoughts.

Comments

Product

Subscribe to our newsletter!

Stay informed when great things happen! Get the latest news about APIs, GraphQL and more straight into your mailbox.

© 2021 WunderGraph