New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Support existing MySQL Databases #1230

Closed
marktani opened this Issue Nov 9, 2017 · 15 comments

Comments

Projects
None yet
10 participants
@marktani
Contributor

marktani commented Nov 9, 2017

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

This comment has been minimized.

sedubois commented Nov 9, 2017

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

This comment has been minimized.

Contributor

kbrandwijk commented Nov 9, 2017

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

This comment has been minimized.

mingzhou commented Nov 10, 2017

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

This comment has been minimized.

Member

sorenbs commented Nov 10, 2017

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

@marktani marktani referenced this issue Nov 10, 2017

Closed

Update Database FAQs #1245

2 of 4 tasks complete
@msand

This comment has been minimized.

msand commented Nov 18, 2017

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

This comment has been minimized.

msand commented Nov 18, 2017

@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

This comment has been minimized.

abeagley commented Nov 19, 2017

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

This comment has been minimized.

abeagley commented Nov 24, 2017

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

Kubernetes and a GraphCool gateway running locally

@sorenbs

This comment has been minimized.

Member

sorenbs commented Nov 24, 2017

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

This comment has been minimized.

abeagley commented Nov 25, 2017

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

#1324

@marktani marktani changed the title from Support existing MySQL/Postgres DBs to Support existing MySQL Jan 18, 2018

@marktani marktani changed the title from Support existing MySQL to Support existing MySQL Databases Jan 18, 2018

@rohit-ravikoti

This comment has been minimized.

rohit-ravikoti commented Jan 18, 2018

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

This comment has been minimized.

johnkpaul commented Jan 18, 2018

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.

@LFP6

This comment has been minimized.

LFP6 commented Jan 24, 2018

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

This comment has been minimized.

Member

sorenbs commented Jan 28, 2018

The beginnings of a spec for supporting existing db schemas is being worked on in #1749 Please contribute your thoughts and requirements there :-)

@marktani

This comment has been minimized.

Contributor

marktani commented May 24, 2018

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

@marktani marktani closed this May 24, 2018

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment