Migrate your MySQL database to Postgres with Supabase, Hasura & pgloader
Background
Recently, we decided to rearchitect our backend & migrate to a new stack. We realised our development velocity & ability to ship out different features was not as per the standards we desired.
After a month of analysing the business requirements, future product plans & spiking we arrived at the stack described in the picture below. All the team members seemed pretty satisfied & we envision ourselves to commit to it for atleast the next 2 years.
Self-hosting
Due to the nature of the task at hand, it was imperative for us to self-host our database. Benefits of self-hosting include -
- Pricing: Self-hosting is effectively free forever
- Portability: No vendor lock-in. Letting a software vendor handle your business-critical data wasn’t an option.
- Full Control over data: When you are self-hosting your software, you have full control over your data.
- Customisation: On-premises software that is open source gives you the flexibility for customisation. You can add or customise code to make it meet your organisation’s needs.
- Integrations: You can easily integrate with other services & extend its functionality
Hosting your own database
For our self-hosting requirements, we needed something that was open-source & can easily be self-hosted. Supabase positions itself as “The Open SourceFirebase Alternative”. It’s core offering includes -
- Postgres Database
- PostgREST for generating APIs
- GoTrue for handling user registration and authentication
- Storage for managing files in S3 buckets or other S3-compatible systems
- Kong as api gateway
- Supabase dashboard
Clone the repo
gh repo clone supabase/supabase
Modify docker compose file
docker/docker-compose.yml
contains all the services that supabase provides. Since, we didn’t need PostgREST & Storage services, we removed them
Alternatively, you can directly use their postgres docker image
Modify the environment variables
Copy the contents of .env.example
to .env
file in the same directory & update the secrets/keys/password.
I use vscode-random extension to generate random secrets
Add Hasura GraphQL Engine
The Hasura GraphQL engine makes your data instantly accessible over a real-time GraphQL API. Just point to it to your database & it will instantly provide GraphQL APIs 🚀
Update the docker-compose file in previous step to include Hasura’s GraphQL Engine.
Note that
PG_DATABASE_URL
points to the postgres database from previous step
Running them all
Run docker-compose up -d
to run all the services. If successful, you should all the services running properly
Postgres Database:
Supabase studio: http://localhost:3000
Hasura Console: http://localhost:8081/console
Migrating MySQL to Postgres
pgLoader is an open-source database migration tool that aims to simplify the process of migrating to PostgreSQL. It supports migrations from several file types and RBDMSs — including MySQL and SQLite — to PostgreSQL.
Install pgloader
Create database
Inside postgres, create a new database with desired name {PG_DBNAME}
Migration
Migrate your local MySQL Database to PostgreSQL locally
Migrate CRUD APIs
Our backend had 400+ APIs; about 300 of which were CRUD APIs. Hasura provides queries & mutations for CRUD operations on top of your database schema & allows to convert them to REST APIs.
As a result, we decided to move the existing apis to the graphql layer
Track all tables & relationships
- Connect to database from the GraphQL console’s data tab
- For the database created in previous tab, click on
Track All tables
- Do the same for all relationships as well
- Implement the backend logic as queries & convert them to GET apis
- Similarly, you can convert other operations into mutations & host them as POST apis
Refer below video for more details -
This way we were able to move our 60% of the apis to graphql
(Additional) Routing graphql queries via Kong
As per the architecture diagram in the first section, all the graphql queries must be routed through Kong Gateway.
In order to do that, we need to add GraphQL service to the list of Kong services. Read this excellent docs by kong to understand in detail the concept of services & routes in Kong
Edit the kong configuration file located at — docker/volumes/api/kong.yml
URL
— URL at which the GraphQL service is running. Remember, we named our Hasura containergraphql
& by default it runs on port 8080. As a result, Kong can communicate with Hasura at - http://graphql:8080/v1/graphqlROUTES
— Routes determine how requests are sent to their Services after they reach Kong Gateway. Here, we are saying any request that Kong receives at/v1/graphql
should be redirected to Hasura service running at URL described abovePLUGINS
— There are couple of plugins namely for CORS & Key Authentication. There are many community plugins available here
Now any request to graphql service is routed via Kong. Since we added Key Auth plugin it needs an apikey
in the header, use ANON_KEY
from environment file as its value.
Conclusion
Running postgres with hasura in a dockerised environment is a great solution.
Combining this with Gotrue’s authentication, Hasura’s authorisation & Kong is a powerful combination. It can act as a backend stack for most common (& advanced) use cases.