Aurora serverless Postgresql not supported

[berenddeboer]

Running amplify api add-graphql-datasource will fetch my Aurora PostgreSQL cluster, then the secret, and then crashes:

? Select the secret used to access your Aurora Serverless cluster: RDSRootSecret
⠼ Fetching Aurora Serverless cluster...BadRequestException: FATAL: database "root" does not exist
    at Object.extractError (/usr/lib/node_modules/@aws-amplify/cli/node_modules/aws-sdk/lib/protocol/json.js:51:27)
    at Request.extractError (/usr/lib/node_modules/@aws-amplify/cli/node_modules/aws-sdk/lib/protocol/rest_json.js:55:8)
    at Request.callListeners (/usr/lib/node_modules/@aws-amplify/cli/node_modules/aws-sdk/lib/sequential_executor.js:106:20)
    at Request.emit (/usr/lib/node_modules/@aws-amplify/cli/node_modules/aws-sdk/lib/sequential_executor.js:78:10)
    at Request.emit (/usr/lib/node_modules/@aws-amplify/cli/node_modules/aws-sdk/lib/request.js:683:14)
    at Request.transition (/usr/lib/node_modules/@aws-amplify/cli/node_modules/aws-sdk/lib/request.js:22:10)
    at AcceptorStateMachine.runTo (/usr/lib/node_modules/@aws-amplify/cli/node_modules/aws-sdk/lib/state_machine.js:14:12)
    at /usr/lib/node_modules/@aws-amplify/cli/node_modules/aws-sdk/lib/state_machine.js:26:10
    at Request.<anonymous> (/usr/lib/node_modules/@aws-amplify/cli/node_modules/aws-sdk/lib/request.js:38:9)
    at Request.<anonymous> (/usr/lib/node_modules/@aws-amplify/cli/node_modules/aws-sdk/lib/request.js:685:12)
There was an error adding the datasource

The reason is my database user is called root and there is no database root, it just has a different name.

What I would expect is that it does not switch to the postgresql default database (which is the name of the user), and continues to show me all my databases.

Desktop

• Amplify cli: 4.7.0
• Ubuntu 19.10
• Node Version: 10.18.0

[Ricardo1980]

Are you sure your db is active? Can you use the Online Query Editor to send requests and works?
I had a simular issue and then I realised that amplify cannot wake up the database if it is inactive.

[berenddeboer]

Have changed the title, the issue is that Aurora Postgresql is not supported. Look at appSync-rds-walkthrough.js, method selectDatabase(), mysql is hard-coded.

[berenddeboer]

Had a bit of a look to see if I could support Postgres, but I would need to determine what kind of cluster this is, i.e. its engine. Couldn't see this easily. Anyway, for Postgres instead of show databases you want SELECT datname FROM pg_database.

[berenddeboer]

See aws-amplify/amplify-cli#1914 as well.

[UnleashedMind]

@berenddeboer I will mark this as a feature request, our PR will review it.

[Genkilabs]

+1 for feature

See more history on this issue/desire as well here: aws-amplify/amplify-cli#2423 (comment)

[azatoth]

Any progress in this feature?

[jameelgrand]

any new updates? a must needed feature for my application

[deanthemachine2]

How can I upvote this, I'd LOVE this ability in postgres, it could change the whole project I'm working on.

[Genkilabs]

Dear Amplify devs,
This issue (along with the one allowing the addition of multiple AWS resources arbitrarily) was one of the factors by which our company determined that Amplify was simply too immature (and opinionated) to be ready soon enough for our enterprise-scale apps...

We'd love to see this one day become production ready. Until then we will be consuming our AppSync graph with core/vanilla Apollo. ^_^

[curtismorte]

+1 for support

I'll acknowledge the following:

• Amplify is a toolkit to help automate as much of the full stack development process as possible.

Having used AppSync for managed GraphQL, we decided to use the Amplify CLI since it appears to be a great tool for managing / creating multiple environments. Since we're leveraging multiple types of data sources, you might think that the Amplify CLI should support data model management for those data sources (since Amplify is meant to manage everything for you, mostly...). However, we've actually used an Aurora Serverless Postgres database as a source of data (zipcodes, distance, etc.). We don't mind keeping the data model and resolvers for Queries/Mutations in sync ourselves, but functionality would be very nice to mark a data source as "self-managed" so when our resolvers are replicated for new environment creation they will work automatically (since the resolvers are stored to /amplify/backend/api/NAME/resolvers/).

I would propose something like amplify api add-graphql-datasource --managed=false to create the data source via the CLI which would allow environment creation for data sources managed outside of Amplify. You might need to have a decorator for certain schema types, but this seems like it could be a solution to help with onboarding of existing applications that might not leverage the Amplify CLI, and are looking to.

[Dizzzmas]

+1 for the feature.

The debate about using Amplify in our team largely revolves around having Postgres as an option.

[sar]

Just ran into this issue and certainly agree with many of the comments, Aurora Serverless Postgres + AppSync GraphQL would make for an awesome stack!

[Sam-NB]

There's actually another graphql api generator that already supports Aurora Postgres serverless called "Postgraphile". The graphql server layer can also be deployed as lambdas behind an API gateway. If there was an Amplify option to add another graphql endpoint as a datasource it could also achieve the same goal. Is that possible?

[t829702]

this is 2021 I'm still hit on the same issue when database name is not same as dbusername; the amplify cli seems assuming the database always same as dbusername? in this case when my dbadmin name is 'pgadmin'

what's worse is the cli seems trying fetching again and stuck there forever, Until Ctrol-C to Abort

$ amplify api add-graphql-datasource
⠹ Fetching Aurora Serverless cluster...BadRequestException: FATAL: database "pgadmin" does not exist
[...many lines of error stack]

There was an error adding the datasource
⠋ Fetching Aurora Serverless cluster...^Aborted!

also tried to give dbname on the cli and try to ask for --help, but seems this CLI is simply ignoring any extra args

$ amplify api add-graphql-datasource --dbname dbname            # or tried --help but all the same way error

I am following this guide trying add-graphql-datasource, but never reached the point of asking database name to use as datasource
https://docs.amplify.aws/cli/graphql-transformer/relational

Return to your command line and run amplify api add-graphql-datasource from the root of your amplify project.

[jesper-bylund]

Having the same issue as @t829702 . This is more than a bit bisarre.
Should we really need to have exactly the same database name and database user name?

[ThierDev]

+1 For Postgres Support

[georgeleeventures]

+1 for Postgres support. MySQL isn't ACID compliant. How come Amplify supports MySQL and not Postgresql?

[dilipv21]

+1 For Postgres support.

[skryshi]

+1 for PostregSQL support

[IllusiveMilkman]

+1 for PostregSQL support

[vinodvarma24]

+1 for Postgres Support

[NoahGaeta]

+1 for Postgres Support

[imjulianeral]

+1 for Postgres Support

[danjesus]

+1 for Postgres support

[nathan-mill]

+1 for natively supporting literally any kind of persistence layer other than DynamoDB.

[renebrandel]

Hi folks - wanted to cross-post this RFC here:

• RFC: Support GraphQL API for existing Relational Databases (SQL) #815

We are working on adding support for Postgres. The linked preview build allows you to import MySQL databases now. We plan on following the same pattern for Postgres. #815 (comment)