Prisma1: Support existing MySQL Databases

[marktani]

Connecting an existing MySQL to the Graphcool Framework would be a great feature.

A collection of scenarios and requirements is very helpful to get an overview of common use cases and the impact of different tradeoffs.

Here are a few interesting questions about existing DBs:

• What are the requirements for the DB schema?
• Are there additional DB-specific requirements such as special column types, specific ID methods (cuid, uuid, ...), etc?
• What tradeoffs exist with respect to the _migration_ of the DB schema?
• How are relations modelled in the DB?

[sedubois]

I would love this to have a migration path away from my existing Rails app with Postgres DB. Rails DB schemas being quite standard, it’s probably easy to answer the above questions for this case.

[kbrandwijk]

As long as you conform to the schema, I think it would be as simple as being able to define the connection somewhere. The issues begin when the schema doesn't match, and you need some kind of mapping layer.

[mingzhou]

I would like to map existing MSSQL Database Schema to Graphcool GraphQL Schema for three reasons:

1. The MSSQL database are provided and auto-sync data from data providers. We cannot migrate into Graphcool database.
2. Many existing applications directly access MSSQL with SQL statements. Migrating these applications are not in the working schedule currently.
3. I would like to separate Graphcool Server in docker environment and databases remain on direct OS environment.
   Thanks.

[sorenbs]

Thanks @mingzhou

Would you be able to contact me directly in slack (@sorenbs https://slack.graph.cool/) I would love to ask you a few questions :-)

[msand]

In a project I'm currently involved in, we're migrating from mysql to postgresql using pgloader, running https://www.graphile.org/postgraphile/ as an introspected graphql backend for the public schema part of the db, and https://github.com/AEB-labs/graphql-weaver/ to stitch it with other data sources (elasticsearch, wordpress etc.) and will probably take https://github.com/apollographql/apollo-engine-js into use in the setup.

There is a requirement of preserving the existing schema structure / sql compatiblity used against the mysql database to allow legacy code (mostly php) to keep working with it as well with minimal changes, extending it is fine, but not any major changes to the already used parts, until all client frontends & admin uis are migrated to the new graphql api endpoint.

Extending postgraphile to support mysql or knex or something similar, could be a path to get mysql, postgresql mssql etc, exposed as graphql, and then stitching it together with graph.cool would be great. Get reuse for existing setups and infra, exposed as graphql, and do any new development in graph.cool, postgres or whatever tool fits the specific query/mutation use case patterns and requirements the best or good enough somewhere in a pareto front. With Event Sourcing (ES) and Command Query Responsibility Segregation (CQRS) we can get the best of all worlds, for fast writes and fast reads in all queries and mutations, with good guarantees of normalization, redundancy, delivery and retries in queues etc.

[msand]

@sedubois you might want to checkout https://github.com/postgraphql/postgraphql/tree/postgraphile or the possibly more stable https://github.com/postgraphql/postgraphql/tree/master if you only want to expose your postgresql db schema as graphql.

[abeagley]

In my current project we're not looking to support any sort of legacy tables or anything, but would like to utilize GCP (Google Cloud Platform) in order to support our Graphcool framework instance - like you do with your hosted platform and AWS. This would include allowing Graphcool to support logging into and utilizing GCP instances of either MySQL or Postgres (eventually when the adapter is ready).

Digging around a bit I noticed some environment setup within the Graphcool framework that sets it to connect to the local containers currently running in parallel within Docker which is nice for an all in one deployment scenario, but makes it difficult to extend to a custom cloud based DB and drop that db container. I'd suggest changing that environment file to look for existing environment configuration (perhaps from the ejected docker-compose.yml file) to connect to another MySQL instance for now? Could be a quick change to support cloud based MySQL instances while developing other adapters and ways to support legacy systems and existing infrastructure without having to modify the container volume directly.

[abeagley]

Ok, so after a couple days of just messing around with the Kubernetes engine and your current dev containers I successfully managed to port your compose file over to individual deployments, services, and persisted volumes for development and deployment on GCP Container! I did have to do a little work with graphcool deploy but I did manage to get that working for Kubernetes as well. This will also support GCP Cloud Instances by changing the local mysql information in a Kubernetes Secret.

I can post up an example soon after I clean it up a bit, but it looks a little like this when it's running (Also running a custom gateway to keep some of the internal services of Kubernetes hidden from the public):

[sorenbs]

That's pretty cool @abeagley! Would love to see a writeup of how to set this up.

We plan to have more official documentation for this in the future, but until that happens, I think it could be useful for a lot of people to see how you did it :-)

[abeagley]

For those who want to know about getting this running in Kubernetes and some insight on how to deploy to GCP:

1324

[rohit-ravikoti]

We have an existing legacy MySQL RDS which the business relies on. Unfortunately, when it was built, relations were only stored as id's and not foreign keys. We have a very similar requirement to what @msand has:

There is a requirement of preserving the existing schema structure / sql compatiblity used against the mysql database to allow legacy code (mostly php) to keep working with it as well with minimal changes, extending it is fine, but not any major changes to the already used parts, until all client frontends & admin uis are migrated to the new graphql api endpoint.

[johnkpaul]

My use case is that I need to use existing company infrastructure for databases. It's not that I need to build on top of an existing schema, but rather that I need to be able to provide a connection string or equivalent to allow prisma to use an existing deployment of mysql or RDS.

For me, the bigger issue is not using the docker container local mysql and instead using regular external mysql.

Thanks.

[luxaritas]

FWIW, I think it would be interesting to look at to what degree this could be supported by simply creating a GraphQL schema to match the DB schema, as opposed to the other way around - for example, requiring a bring your own ID (#1278). Don't know if having some sort of gateway layer to perform basic transforms (kinda like graphql-yoga, but further back in the stack) before the database is hit would be viable.

[sorenbs]

The beginnings of a spec for supporting existing db schemas is being worked on in https://github.com/graphcool/prisma/issues/1749 Please contribute your thoughts and requirements there :-)

[marktani]

This is continued in #1749 (Postgres) and #2506 (MySQL).