Proposal: Expose a Database as a GraphQL API

[ThisaruGuruge]

Summary

Exposing a database as a GraphQL API is a common pattern in modern applications. This proposal aims to simplify this process using Ballerina's bal persist tool and the bal graphql tool, enabling developers to quickly create GraphQL APIs that interact directly with databases without needing extensive database management knowledge.

Goals

• Improved User Experience: Automatically generate Ballerina data types and service code from database schemas, reducing manual coding and potential errors.
• Database-Agnostic GraphQL Services: Enable developers to switch between databases without altering the GraphQL service code by simply changing the persist client.

Non-Goals

• Adding Support for New Databases: This feature will support only existing databases compatible with the bal persist tool. As the bal persist adds support for more databases, the GraphQL service generation will automatically support them.
• Dataloader Support: The generated GraphQL service will not implement the dataloader pattern for performance optimization, though users can add this manually if required.
• Interceptor Support: The generated GraphQL service will not include interceptors, but users can implement these manually if needed.

Motivation

Exposing databases as GraphQL APIs is becoming a common pattern in modern software development. There are some user queries about using Ballerina for this purpose. Existing solutions like Hasura and Prisma have addressed this need to some extent. The bal persist tool can enhance this capability within the Ballerina ecosystem by providing a streamlined method to expose a database as a GraphQL API, enriching the development experience.

Description

The main task of this proposal is to generate a GraphQL service from a given data model. The existing bal persist tool will be utilized to generate the persist client and Ballerina data types first. Then the Ballerina GraphQL tool will use these types to generate the corresponding GraphQL types and the service code with minimal manual coding. This integration will handle various CRUD operations and ensure the GraphQL API remains consistent with the underlying database structure.

Proposed Solution

This proposal suggests enhancements to the bal graphql tool to generate GraphQL services from given data models. The expected user workflow is as follows:

1. Initialize the persist client:

   bal persist init

2. Define the data model and execute the command to generate the persist client:

   bal persist generate --datasource <datasource> --module <module>

3. Generate the GraphQL service:

   bal graphql generate

Implementation

The feature implementation will proceed through the following steps:

1. Use bal persist tool APIs to retrieve the generated persist client and types as syntax trees.
2. Separate persist client methods into GraphQL Query and Mutation operations.
3. Map persist types to GraphQL types appropriately:
   • Input parameters of persist methods will become record types.
   • Return types of persist methods will become service types.
   • Key fields in persist types will be mapped to ID types with the @graphql:ID annotation.
   • Relationships between entities will be handled by generating additional record types to retrieve related entities.
4. Generate GraphQL resolver (remote and resource) methods for Query and Mutation operations, incorporating the generated persist client.
5. Insert persist client method calls in resolver methods to manage entity relationships.
6. Generate a complete syntax tree for the GraphQL service and save it to a file.

Type Generation

We propose an opinionated approach for type generation:

• Use Ballerina service types for all GraphQL OBJECT types.
• Use Ballerina record types for all GraphQL INPUT_OBJECT types.
• Use the graphql:ID annotation for key fields in the GraphQL schema.
• Each object field will have nullable return types as a best practice.

Schema Generation

To generate the GraphQL schema, the following steps will be followed:

1. Generate the Query type with all the get methods of the persist client.
2. Generate the Mutation type with all the post, update, and delete methods of the persist client.
3. Generate the record types for handling relationships.
4. Generate the service types for each entity in the data model to represent the OBJECT types in the GraphQL schema.
5. Generate the record types for each input of the mutation operations to represent the INPUT_OBJECT types in the GraphQL schema.

Future Work

• The solution proposed in this proposal expects three command executions from the user to generate the GraphQL service. This can be simplified by generating the persist client and the GraphQL service in a single command execution.
• As the bal persist is now integrated into bal build command, the bal graphql generate should also be integrated into the bal build command.

Example

Data Model

Following data model will be used for an example.

public type Movie record {|
    readonly string id;
    string title;
    Director director;
    SoundTrack? soundTrack;
|};

public type Director record {|
    readonly string id;
    string name;
    Movie[] movies;
|};

public type SoundTrack record {|
    readonly string id;
    string name;
    Movie movie;
|};

GraphQL Service Generation

The GraphQL service will be generated using the generated persist client methods. Following is the generated code for the given data model.

Note: Per each mutation operation, a separate input object type will be generated.

configurable int port = 9090;
final datasource:Client datasource = check new;

service on new graphql:Listener(port) {
    resource function get movies() returns Movie[]|error? {
        stream<datasource:Movie, error?> movies = datasource->/movies;
        return from datasource:Movie movie in movies
            select new (movie);
    }

    resource function get movie(string id) returns Movie|error? {
        datasource:Movie movie = check datasource->/movies/[id];
        return new (movie);
    }

    resource function get directors() returns Director[]|error? {
        stream<datasource:Director, error?> directors = datasource->/directors;
        return from datasource:Director director in directors
            select new (director);
    }

    resource function get director(string id) returns Director|error? {
        datasource:Director director = check datasource->/directors/[id];
        return new (director);
    }

    resource function get soundTracks() returns SoundTrack[]|error? {
        stream<datasource:SoundTrack, error?> soundTracks = datasource->/soundtracks;
        return from datasource:SoundTrack soundTrack in soundTracks
            select new (soundTrack);
    }

    resource function get soundTrack(string id) returns SoundTrack|error? {
        datasource:SoundTrack soundTrack = check datasource->/soundtracks/[id];
        return new (soundTrack);
    }

    remote function addMovies(AddMovieInput[] input) returns string[]|error? {
        return datasource->/movies.post(input);
    }

    remote function addDirectors(AddDirectorInput[] input) returns string[]|error? {
        return datasource->/directors.post(input);
    }

    remote function addSoundTracks(AddSoundTrackInput[] input) returns string[]|error? {
        return datasource->/soundtracks.post(input);
    }

    remote function updateMovie(UpdateMovieInput input) returns Movie|error? {
        datasource:Movie movie = check datasource->/movies/[input.id].put({
            title: input.title,
            directorId: input.directorId
        });
        return new (movie);
    }

    remote function updateDirector(UpdateDirectorInput input) returns Director|error? {
        datasource:Director director = check datasource->/directors/[input.id].put({
            name: input.name
        });
        return new (director);
    }

    remote function updateSoundTrack(UpdateSoundTrackInput input) returns SoundTrack|error? {
        datasource:SoundTrack soundTrack = check datasource->/soundtracks/[input.id].put({
            name: input.name,
            movieId: input.movieId
        });
        return new (soundTrack);
    }

    remote function deleteMovie(string id) returns Movie|error? {
        datasource:Movie movie = check datasource->/movies/[id].delete();
        return new (movie);
    }

    remote function deleteDirector(string id) returns Director|error? {
        datasource:Director director = check datasource->/directors/[id].delete();
        return new (director);
    }

    remote function deleteSoundTrack(string id) returns SoundTrack|error? {
        datasource:SoundTrack soundTrack = check datasource->/soundtracks/[id].delete();
        return new (soundTrack);
    }
}

GraphQL Output Object Types

For each entity type, a Ballerina service type will be generated. The init method of each generated service type will require the corresponding generated persist type as an input.

Generating Service Types

public isolated service class Movie {
    private final readonly & datasource:Movie movie;

    public isolated function init(datasource:Movie movie) {
        self.movie = movie.cloneReadOnly();
    }
}

public isolated service class Director {
    private final readonly & datasource:Director director;

    public isolated function init(datasource:Director director) {
        self.director = director.cloneReadOnly();
    }
}

public isolated service class SoundTrack {
    private final readonly & datasource:SoundTrack soundTrack;

    public isolated function init(datasource:SoundTrack soundTrack) {
        self.soundTrack = soundTrack.cloneReadOnly();
    }
}

A resource method will be generated for each non-related field of the entity to just return the value.

// Movie Type
isolated resource function get id() returns @graphql:ID string? => self.movie.id;
isolated resource function get title() returns string? => self.movie.title;

Note: The @graphql:ID annotation is used to indicate that the field is an ID field in the GraphQL schema.

For related fields, the following conversion will be used:

Handling Relationships

To handle relationships, additional record types will be generated to retrieve related entities. This record type will include the corresponding relationship as a record so that the persist client will handle the join query internally.

type MovieWithDirector record {|
    datasource:Director director;
|};

type MovieWithSoundTrack record {|
    datasource:SoundTrack soundTrack;
|};

public isolated service class Movie {
    // ...

    isolated resource function get director() returns Director|error? {
        MovieWithDirector movieWithDirector = check datasource->/movies/[self.movie.id];
        return new (movieWithDirector.director);
    }

    isolated resource function get soundTracks() returns SoundTrack|error? {
        MovieWithSoundTrack movieWithSoundTrack = check datasource->/movies/[self.movie.id];
        return new (movieWithSoundTrack.soundTrack);
    }
}

type DirectorWithMovie record {|
    datasource:Movie[] movies;
|};

public isolated service class Director {
    // ...

    isolated resource function get movies() returns Movie[]|error? {
        DirectorWithMovie directorWithMovie = check datasource->/directors/[self.director.id];
        return from datasource:Movie movie in directorWithMovie.movies
            select new (movie);
    }

    // ...
}

type SoundTrackWithMovie record {|
    datasource:Movie movie;
|};

public isolated service class Soundtrack {
    // ...

    isolated resource function get movie() returns Movie|error? {
        SoundTrackWithMovie soundTrackWithMovie = check datasource->/soundtracks/[self.soundTrack.id];
        return new (soundTrackWithMovie.movie);
    }

    // ...
}

GraphQL Input Object Types

Input objects are generated per each mutation operation. The generated input object types will be as follows:

public type AddMovieInput record {|
    @graphql:ID
    readonly string id;
    string title;
    string directorId;
|};

public type AddDirectorInput record {|
    @graphql:ID
    readonly string id;
    string name;
|};

public type AddSoundTrackInput record {|
    @graphql:ID
    readonly string id;
    string name;
    string movieId;
|};

public type UpdateMovieInput record {|
    @graphql:ID
    readonly string id;
    string title?;
    string directorId?;
|};

public type UpdateDirectorInput record {|
    @graphql:ID
    readonly string id;
    string name?;
|};

public type UpdateSoundTrackInput record {|
    @graphql:ID
    readonly string id;
    string name?;
    string movieId?;
|};

Note: The graphql:ID annotation is used to indicate that the field is an ID field in the GraphQL schema.

The generated GraphQL schema will be the following:

type Query {
    movies: [Movie!]
    movie(id: String!): Movie
    directors: [Director!]
    director(id: String!): Director
    soundTracks: [SoundTrack!]
    soundTrack(id: String!): SoundTrack
}

type Movie {
    id: ID
    title: String
    director: Director
}

type Director {
    id: ID
    name: String
    movies: [Movie!]
}

type SoundTrack {
    id: ID
    name: String
    movie: Movie
}

type Mutation {
    addMovies(input: [AddMovieInput!]!): [String!]
    addDirectors(input: [AddDirectorInput!]!): [String!]
    addSoundTracks(input: [AddSoundTrackInput!]!): [String!]
    updateMovie(input: UpdateMovieInput!): Movie
    updateDirector(input: UpdateDirectorInput!): Director
    updateSoundTrack(input: UpdateSoundTrackInput!): SoundTrack
    deleteMovie(id: String!): Movie
    deleteDirector(id: String!): Director
    deleteSoundTrack(id: String!): SoundTrack
}

input AddMovieInput {
    id: ID!
    title: String!
    directorId: String!
}

input AddDirectorInput {
    id: ID!
    name: String!
}

input AddSoundTrackInput {
    id: ID!
    name: String!
    movieId: String!
}

input UpdateMovieInput {
    id: ID!
    title: String
    directorId: String
}

input UpdateDirectorInput {
    id: ID!
    name: String
}

input UpdateSoundTrackInput {
    id: ID!
    name: String
    movieId: String
}

Following will be the complete generated code:

import persist_graphql.datasource; // Import the generated datasource, if it is in a separate module.

import ballerina/graphql;

configurable int port = 9090;
final datasource:Client datasource = check new;

service on new graphql:Listener(port) {
    resource function get movies() returns Movie[]|error? {
        stream<datasource:Movie, error?> movies = datasource->/movies;
        return from datasource:Movie movie in movies
            select new (movie);
    }

    resource function get movie(string id) returns Movie|error? {
        datasource:Movie movie = check datasource->/movies/[id];
        return new (movie);
    }

    resource function get directors() returns Director[]|error? {
        stream<datasource:Director, error?> directors = datasource->/directors;
        return from datasource:Director director in directors
            select new (director);
    }

    resource function get director(string id) returns Director|error? {
        datasource:Director director = check datasource->/directors/[id];
        return new (director);
    }

    resource function get soundTracks() returns SoundTrack[]|error? {
        stream<datasource:SoundTrack, error?> soundTracks = datasource->/soundtracks;
        return from datasource:SoundTrack soundTrack in soundTracks
            select new (soundTrack);
    }

    resource function get soundTrack(string id) returns SoundTrack|error? {
        datasource:SoundTrack soundTrack = check datasource->/soundtracks/[id];
        return new (soundTrack);
    }

    remote function addMovies(AddMovieInput[] input) returns string[]|error? {
        return datasource->/movies.post(input);
    }

    remote function addDirectors(AddDirectorInput[] input) returns string[]|error? {
        return datasource->/directors.post(input);
    }

    remote function addSoundTracks(AddSoundTrackInput[] input) returns string[]|error? {
        return datasource->/soundtracks.post(input);
    }

    remote function updateMovie(UpdateMovieInput input) returns Movie|error? {
        datasource:Movie movie = check datasource->/movies/[input.id].put({
            title: input.title,
            directorId: input.directorId
        });
        return new (movie);
    }

    remote function updateDirector(UpdateDirectorInput input) returns Director|error? {
        datasource:Director director = check datasource->/directors/[input.id].put({
            name: input.name
        });
        return new (director);
    }

    remote function updateSoundTrack(UpdateSoundTrackInput input) returns SoundTrack|error? {
        datasource:SoundTrack soundTrack = check datasource->/soundtracks/[input.id].put({
            name: input.name,
            movieId: input.movieId
        });
        return new (soundTrack);
    }

    remote function deleteMovie(string id) returns Movie|error? {
        datasource:Movie movie = check datasource->/movies/[id].delete();
        return new (movie);
    }

    remote function deleteDirector(string id) returns Director|error? {
        datasource:Director director = check datasource->/directors/[id].delete();
        return new (director);
    }

    remote function deleteSoundTrack(string id) returns SoundTrack|error? {
        datasource:SoundTrack soundTrack = check datasource->/soundtracks/[id].delete();
        return new (soundTrack);
    }
}

type MovieWithDirector record {|
    datasource:Director director;
|};

type MovieWithSoundTrack record {|
    datasource:SoundTrack soundTrack;
|};

public isolated service class Movie {
    private final readonly & datasource:Movie movie;

    public isolated function init(datasource:Movie movie) {
        self.movie = movie.cloneReadOnly();
    }

    isolated resource function get id() returns @graphql:ID string? => self.movie.id;

    isolated resource function get title() returns string? => self.movie.title;

    isolated resource function get director() returns Director|error? {
        MovieWithDirector movieWithDirector = check datasource->/movies/[self.movie.id];
        return new (movieWithDirector.director);
    }

    isolated resource function get soundTracks() returns SoundTrack|error? {
        MovieWithSoundTrack movieWithSoundTrack = check datasource->/movies/[self.movie.id];
        return new (movieWithSoundTrack.soundTrack);
    }
}

type DirectorWithMovie record {|
    datasource:Movie[] movies;
|};

public isolated service class Director {
    private final readonly & datasource:Director director;

    public isolated function init(datasource:Director director) {
        self.director = director.cloneReadOnly();
    }

    isolated resource function get id() returns @graphql:ID string? => self.director.id;

    isolated resource function get name() returns string? => self.director.name;

    isolated resource function get movies() returns Movie[]|error? {
        DirectorWithMovie directorWithMovie = check datasource->/directors/[self.director.id];
        return from datasource:Movie movie in directorWithMovie.movies
            select new (movie);
    }
}

type SoundTrackWithMovie record {|
    datasource:Movie movie;
|};

public isolated service class SoundTrack {
    private final readonly & datasource:SoundTrack soundTrack;

    public isolated function init(datasource:SoundTrack soundTrack) {
        self.soundTrack = soundTrack.cloneReadOnly();
    }

    isolated resource function get id() returns @graphql:ID string? => self.soundTrack.id;

    isolated resource function get name() returns string? => self.soundTrack.name;

    isolated resource function get movie() returns Movie|error? {
        SoundTrackWithMovie soundTrackWithMovie = check datasource->/soundtracks/[self.soundTrack.id];
        return new (soundTrackWithMovie.movie);
    }
}

public type AddMovieInput record {|
    @graphql:ID
    readonly string id;
    string title;
    string directorId;
|};

public type AddDirectorInput record {|
    @graphql:ID
    readonly string id;
    string name;
|};

public type AddSoundTrackInput record {|
    @graphql:ID
    readonly string id;
    string name;
    string movieId;
|};

public type UpdateMovieInput record {|
    @graphql:ID
    readonly string id;
    string title?;
    string directorId?;
|};

public type UpdateDirectorInput record {|
    @graphql:ID
    readonly string id;
    string name?;
|};

public type UpdateSoundTrackInput record {|
    @graphql:ID
    readonly string id;
    string name?;
    string movieId?;
|};

Alternatives

An alternative would be to integrate GraphQL service generation directly into the bal persist command; similar to the following command.

bal persist generate --graphql

However, this approach could overly complicate the bal persist command and tightly couple persist client and GraphQL service generation. The proposed method keeps the commands focused and distinct in their functionality.

Dependencies

1. Ballerina syntax API.
2. Ballerina semantic API.
3. Ballerina persist tool APIs (Should be exported as Java module in the maven artifact).

[daneshk]

@ThisaruGuruge Do we have a handwritten sample to see how the generated service code looks like with bal persist?

[ThisaruGuruge]

@ThisaruGuruge Do we have a handwritten sample to see how the generated service code looks like with bal persist?

Updated with an example.

[daneshk]

How about generating Movie object type like below. We can do the same for the 1:n relationship. Here we are fetching the relations from the movies resource and bal persist handles the join query underneath.

type MovieWithDirector record {
   string id;
   datasource:Director director;
}

type MovieWithSoundTrack record {
   string id;
   datasource:SoundTrack soundTrack;
}

public isolated service class Movie {
    private final readonly & datasource:Movie movie;

    public isolated function init(datasource:Movie movie) {
        self.movie = movie.cloneReadOnly();
    }

   // primary key can't be null
    isolated resource function get id() returns @graphql:ID string => self.movie.id;

    isolated resource function get title() returns string? => self.movie.title;

    isolated resource function get director() returns datasource:Director|error? {
        MovieWithDirector movieWithDirector = check datasource->/movies/[self.movie.id];
        return new (movieWithDirector.director);
    }

    isolated resource function get soundTracks() returns SoundTrack|error? {
        MovieWithSoundTrack movieWithSoundTrack = check datasource->/movies/[self.movie.id];
        return new (movieWithSoundTrack.soundTrack);
}

[ThisaruGuruge]

How about generating Movie object type like below. We can do the same for the 1:n relationship. Here we are fetching the relations from the movies resource and bal persist handles the join query underneath.

type MovieWithDirector record {
   string id;
   datasource:Director director;
}

type MovieWithSoundTrack record {
   string id;
   datasource:SoundTrack soundTrack;
}

public isolated service class Movie {
    private final readonly & datasource:Movie movie;

    public isolated function init(datasource:Movie movie) {
        self.movie = movie.cloneReadOnly();
    }

    isolated resource function get id() returns @graphql:ID string? => self.movie.id;

    isolated resource function get title() returns string? => self.movie.title;

    isolated resource function get director() returns datasource:Director|error? {
        MovieWithDirector movieWithDirector = check datasource->/movies/[self.movie.id];
        return new (movieWithDirector.director);
    }

    isolated resource function get soundTracks() returns SoundTrack|error? {
        MovieWithSoundTrack movieWithSoundTrack = check datasource->/movies/[self.movie.id];
        return new (movieWithSoundTrack.soundTrack);
}

+1. This seems more elegant. I will update the proposal.