SQL Querying Made Simple with GraphQL and Prisma !

Hola Amigos

In todays blog we will see how we can built a sql querying mechanism using prisma and graphQL , We will be using GraphQL using the Apollo Client and a graphQL server inference using express.

Tech stacks used here are :
GraphQL , Prisma PgSql dump , Express (inferencing the GraphQL backend Server).

If you want your graphQL queries, communicating with our backend interface we just might need to do something I am doing below in React this is just a configuration part which I would like to show technically, that how could we configure graphQL on the frontend part.

const client = new ApolloClient({
  uri: import.meta.env.VITE_APP_GRAPHQL_API,
  cache: new InMemoryCache(),
});

Then we will use it like this wrapping up the App Component as we will be using the client across the whole application.

 <ApolloProvider client={client}>
  <App />
 </ApolloProvider>

The above code have to be written inside the main.tsx file or inside the index.ts file if using cra , where the root is render inside the ReactDom. The above code creates the client , with the help of apollo-client library use to create a client for using GraphQL it takes two parameters the uri which is nothing but the backend inference of the GraphQL API and a cache parameter where we have defined InMemoryCache() where the apollo client responds immediately if have stored the queries inside the local cache storage without the network request.

Now we need to Focus towards the Express where we will be inferencing the GraphQL server for mutating and fetching the data from our post-gres data dump

Lets start with the Setup in backend for GraphQL followed by Prisma

Here we don't see much of the configuration to be set up but only the Apollo Server to serve the client request, our main focus here would be to make resolver function and schema files accurately and precisely as it may be quite messy sometimes when the product code base get's quite huge.

in my ./index.js

const server = new ApolloServer({
  schema,                        // you executable schema file
  context: ({ req }) => ({
    db,                         // your database connection
  }),
  plugins: [ApolloServerPluginDrainHttpServer({ httpServer })],
});

await server.start();

In my case I am using Postgres Databse Dump so I will be using pg-promise to make a database instance and make a connection string.Also I have attached how we will be using it from .env file as if some newbie reads the blog they might get a sense how a production / industry application needs to developed 🗿.

const pgp = pgPromise();
export const db = pgp({
  connectionString: `postgres://${process.env.DB_USER}:${process.env.DB_PASSWORD}@${process.env.DB_HOST}:${process.env.DB_PORT}/${process.env.DB_DATABASE}`,
});

Now Lets get towards the executable schema files and give it to apollo server

import makeExecutableSchema from "graphql-tools" // also add it into the file

const schema = makeExecutableSchema({ typeDefs, resolvers });

So what are typeDef and resolvers ?

TypeDefs are nothing but a schema file where the datatype of the field mentioned, something similar to the types in typescript , I will add some of it how do we mentioned in the file and also some scalar types how do we manage some scalar types and how do we create some custom typedef using GraphQLScalarTypes. Also linking a document from ApolloGraphQL for making schema more better and sound.

also in my ./schema.js

Let us assume I am making something like user_secrets having my DB entites in following manner

 type sus_users_talez{
        id: Int!,
        created_at: TimestampWithTimezone!,
        updated_at: TimestampWithTimezone,
        deleted_at: TimestampWithTimezone,
        name: String,
        sus_created_at: TimestampWithTimezone,
        sus_updated_at: TimestampWithTimezone,
        visibility: String,
        workflows: [String],

    }

Here you can see I have TimeStampWithTimeZone which is a custom typeDef, we will create this in a miniute

But before that , I want to show how will we make a Schema File more good the above was just an example of type

export const typeDefs = `#graphql
    scalar TimestampWithTimezone
    scalar JSON

 type sus_users_talez{
        id: Int!,
        created_at: TimestampWithTimezone!,
        updated_at: TimestampWithTimezone,
        deleted_at: TimestampWithTimezone,
        name: String,
        sus_created_at: TimestampWithTimezone,
        sus_updated_at: TimestampWithTimezone,
        visibility: String,
        workflows: [String],


    }


// this typedef will be responsible for Querying (Remember useQuery in ApolloGraphQL for fetching data from server)

// Note : I have also shown how it will take parameter for Pagination and a id for specifc user data. 

type Query {

 # Query to get all sus_users_talez
  getAllSusUsers(limit:Int, offset:Int): [sus_users_talez]

 # Query to get sus_users_talez by ID
  getSusUser(id: Int!): sus_users_talez

}

// Similarly we will create a Mutation typedef to accomplish Querying from frontend.

 type Mutation {
        # mutation endpoint for customSQL queries from frontend
        customSQLQuery(limit:Int, offset: Int, sql: String! , user_id: String!): JSON

}`

Also let me get back to the Custom Scalar type :) , And we will just add it to the above type we exported, in the same file or create and export it from a new file.

Also to note GraphQL supports JSON by itself, above in mutation we have used it as user from frontend will send a JSON formatted payload.

import { GraphQLScalarType } from "graphql";


export const TimestampWithTimezone = new GraphQLScalarType({
  name: "TimestampWithTimezone",
  description: "Timestamp with timezone offset",
  serialize: (value) => {
    // Serialize a timestamp value from your database
    return value.toISOString();
  },
  parseValue: (value) => {
    // Parse a timestamp value from a client input
    return new Date(value);
  },
  parseLiteral: (ast) => {
    if (ast.kind === Kind.STRING) {
      // Parse a timestamp from an AST literal
      return new Date(ast.value);
    }
    return null;
  },
});

Next up is Resolver Function, how we will create a Resolver Function which returns the data on the basis of User Request from Client.

Let us start building our resover file from scratch :)

export const resolvers = {
  Query: {
    getAllSusUsersTalez: async (parent, { limit, offset }) => {
      try {
        // Use Prisma to fetch all sus_users_talez
        const allUsers = await prisma.sus_users_talez.findMany({
          skip: offset,
          take: limit,
        });
        return allUsers;
      } catch (error) {
        console.error("Error fetching sus_users_talez:", error);
        throw error;
      }
    },
}

 Mutation: {
    customSQLQuery: async (parent, { limit, offset, sql, user_id }) => {
      try {
        // handling the recent queries
        // fetching the user_details for the metaData
        const user = await prisma.talez.findUnique({
          where: {
            user_id: user_id,
          },
        });

        // keeping a track for complete data count without offset
        const countQuery = `SELECT COUNT(*) FROM (${sql}) AS total`;
        const totalCount = await prisma.$queryRawUnsafe(countQuery);


        const paginatedSQL = `${sql} LIMIT ${limit} OFFSET ${offset}`;
        const result = await prisma.$queryRawUnsafe(paginatedSQL);

        // Check if there are more items
        const hasMoreItems = offset + limit < totalCount[0]?.count;

        return { result: result, hasMoreItems };
      } catch (error) {
        console.error("Error executing SQL query:", error);
        return { message: "Error Executing SQL Query, Try Different Query" };
      }
    },
  }

Here I have created a query function where I have tried fetching all the data related to the sus_users_talez also I have a added a mutation function where users can easily write a custom sql query if they want something by LIMIT and offset also they can easily get that.

For example Select * From sus_users_talez WHERE id="12345" and kaboom you get your result's.

PS: Building on top of prisma would make some extra efforts like prisma schema files and migrations if you alter any fields, if you have already a DB dump like me you will need to have a db pull with all the schema related stuff and you just need to hit a command called prisma db pull you can find it to the docs of prisma.

So now you might create many more reference from here using graphQL more adequately and also making a better use case of application you want to built on the top of GraphQL, Prisma and PG-sql using express and any Frontend framework.

Btw, I have never given my introduction on my past blogs , Here Find me out Who I am? :)

I am SHREY, SDE at early scale startup building a asset managment and compliance based application also helping people make MVP's of their startup application, I am also building Talez a product management tool where you can brainstorm the ideas of the application while building your product with many others working on the same.
You can follow me on Twitter

Share it for good karma & 🍧 ! , Have a Great Day Folks!