Skip to Content

Instant Realtime APIs using PostgreSQL, MySQL, SQLite, SQLServer and Planetscale

Published: November 23, 2021
Jens Neuse

Jens Neuse

Our Vision is to build the best possible developer experience for working with APIs. One of the problems we've identified on our way is the complexity of building a modern web application on top of a relational database.

It's not that the problems are impossible to solve. It's more about the endless repetition. Developers keep re-inventing the wheel by finding their own "best way" of combining a web framework, an API style and an ORM or database client.

Avoiding vendor lock-in by using open standards#

Building on top of RDBMS like PostgreSQL, MySQL, SQLServer and SQLite means that you can choose from different providers, you're not locking yourself into a specific vendor, which leads us to our secondary goal.

We don't just want to create a great DX for working with APIs, we also want to build a tool that prevents vendor lock-in automatically.

Services like Cloudflare Workers for example offer an amazing functionality, but you have to rely on a single provider, you cannot easily eject their service or run it on your own.

I personally would much rather prefer a service like fly.io over Cloudflare Workers. Fly "simply" hosts a docker container for you on the edge. If they go broke or change their business model, I can run my container on one of their competitors. It's not easy what they do, but the "integration layer", in this case the docker container, is open and not proprietary.

It's counter-intuitive but Cloudflares moat is also their weakness. Nobody can easily copy their Workers implementation. Do you really want to build on top of a proprietary layer of software?

One part of our strategy is to open source our framework using a MIT license. If you'd like to get informed once we're releasing it, sign up using the following form.

WunderGraph - headless FullStack while being backend agnostic#

WunderGraph has the vision to become a vendor independent, headless, backend agnostic API Integration framework. Ok, that's a mouthful. Let's unpack it bit by bit.

Vendor independent means, you should be able to use WunderGraph anywhere you want, on your laptop, on any public or private cloud. Additionally, you should be able to connect DataSources of any kind, making your whole stack portable to different environments.

As you might know, WunderGraph generated clients if you want. Currently, we're supporting TypeScript, React and NextJS. We will extend support for other languages and frameworks soon. So, by "Headless" we mean, WunderGraph doesn't force you into a specific API consumer / frontend stack. You will be able to generate a Java client, Svelte, Vue, iOS Swift or anything else in the future.

Finally, what do we mean by backend agnostic? WunderGraph generates an API from all the DataSources you define. These could be GraphQL or REST APIs, or even Databases. In the future, we'd like to support Kafka and many others, but it's really up to you what you'd like to plug into WG.

Think of WunderGraph like a full stack framework that does all the heavy lifting of security, authentication, authorization, caching, etc., while leaving the "head" and the "tail" completely up to you. Bring your own APIs and databases, and put your own user interface on top, but everything in the middle, you shouldn't worry about.

You can build your backend using NestJS or just plug in your PostgreSQL DB and use it right away. Or, start with one approach and then slowly move towards the other. You can save time by going DB first and then replace the generated API step by step with a custom one once you've got more resources.

All DataSources available in WunderGraph today#

To move towards this goal, we've extended our support to cover the most used databases out there, including one exotic but exciting solution.

Here's a list of all the DataSources we support so far:

You're able to combine and stitch any number of DataSources into a single, unified, API.

WunderGraph also comes with a TypeScript SDK to configure your APIs. We believe that using TypeScript as a configuration language gives you the best possible developer experience. Define your config in code, open a PR, deploy on push. That's how any workflow nowadays should look like.

From Database to production-ready API in minutes, example using Planetscale#

Let's pick Planetscale, the Vitess-as-a-Service solution as an example.

First, introspect the DataBase Schema:

// wundergraph.config.ts
const api = introspect.planetscale({
databaseURL: "mysql://user:pass@dost.eu-west-3.psdb.cloud/db?sslaccept=strict",
})

Our data model is very simple, just a single table to store users:

CREATE TABLE `users` (
`id` int NOT NULL AUTO_INCREMENT,
`email` varchar(255) NOT NULL,
`first_name` varchar(255) DEFAULT NULL,
`last_name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
);

Running the introspection generates a GraphQL Schema which we can then use to define your first Operation.

# ./operations/AllUsers.graphql
{
findManyusers{
id
email
first_name
last_name
}
}

Once this Operation is defined, the WunderGraph code generator builds a secure backend to just expose this single Operations as a JSON-RPC Endpoint as well as a TypeScript + React client. (As said earlier, we can generate other clients as well)

Once generated, all you do is call useQuery.AllUsers() from your NextJS application, and the data layer of your application is ready.

const AdminPage: NextPage = () => {
const data = useQuery.AllUsers();
return (
<div>
{JSON.stringify(data)}
</div
)
}

Alternatively, you could "subscribe" to a "stream" of Live Updates. Replace useQuery with useLiveQuery and you're done. WunderGraph efficiently polls your database server-side in configurable intervals and automatically streams updates to the ui.

const AdminPage: NextPage = () => {
const data = useLiveQuery.AllUsers();
return (
<div>
{JSON.stringify(data)}
</div
)
}

Extensibility of your generated API#

If you decide at any point in time that the generated API is limiting you in terms of extensibility, you've got multiple options to move forward.

One way of extending your application is using Hooks. Again, we've optimized this path for the perfect developer experience. Hooks are not just "WebHooks" you have to implement yourself in some language and deploy them manually. Hooks can be written in TypeScript, and all the definition are generated from your Operations.

Imagine, you'd like to check for a specific user role before returning a response to the user, no problem with the generated Hooks skeleton:

// wundergraph.hooks.ts
const wunderGraphHooks = configureWunderGraphHooks({
queries: {
AllUsers: {
mutatingPostResolve: async (ctx, response) => {
if (ctx.user?.roles?.find(r => r !== "superadmin")){
return {
data: undefined,
errors: [
{message: "unauthorized"}
]
}
}
return {
...response
}
}
}
},
});

Keep in mind that the structure of the hooks, the Operations and the role "superadmin", all of them are generated from your configuration and TypeScript Intellisense tells you what to do.

How do you deploy the hooks? You don't! We've internally wrapped them with a fastify server which we start automatically. Define a hook, save, done.

You might be thinking that Hooks are great, but you want 100% control of your backend. We understand! That's why we also have a GraphQL DataSource. At any time, you can implement the used surface of the GraphQL API yourself and swap the generated API out. As the Operations are already defined, you'll immediately notice (before deployment), if your custom API covers all of them or if something is missing.

Conclusion#

As you can see, you're able to move at an extremely fast pace without depending on a specific vendor. Being able to get a Realtime API on top of any Database in a couple of minutes is just the tip of the iceberg.

For me, personally, generating a type-safe API for any combination of Databases and APIs is the real game changer here. It saves you a lot of time doing manual integration.

Additionally, you're able to use different Databases for dev and prod, if the schemas are the same. Use SQLite on your laptop and PostgreSQL for integration-testing and production. Alternatively, you can use hooks to mock the database completely during development.

All that wrapped nicely with essential features for security, authentication and authorization, developers can focus on the two core aspects that matter: User Experience and Business Logic.

Bring your Database, bring your APIs, own your user interface, we do the boring stuff in the middle.

Want to give it a try? Check out the Quickstart!

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

Comparisons

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