Server-Side Operations With GraphQL

Today we will create an application that stores products in a database and visualize them with Smart.Grid. It will show us how to integrate a GraphQL endpoint with Smart.Grid and perform server-side paging, sorting, filtering and all of the CRUD operations.

What is GraphQL?

In the last years, GraphQL gained popularity as an alternative to REST when fetching data for clients. It provides a more efficient and flexible way to request and manipulate data from a server compared to traditional RESTful APIs.

The core of the GraphQL is the schema, this defines the structure and capabilities of the API. The schema describes the available data types, relationships between them, and the operations that clients can perform.

Read more about GraphQL here in this Introduction to GraphQL

Our GraphQL endpoint will be hosted on a Node.js (ApolloServer) server and it will communicate with the MySQL database.

Here is a visualization of the connections in our application:

Setup Database & NodeJS Server

If you don't have MySQL installed, you can do it from here

Download the following SQL script to create and populate our database: SQL database script

After executing the script, we are ready to use our MySQL database

The next step is to download this template server: download

After downloading it, install the dependencies via

npm install

Next, run the application

npm run nodemon

Defining GraphQL schema

It is time to define our GraphQL schema. The schema will be very basic with only one entity.

Open schema.js and paste the following:

exports.typeDefs = `#graphql
    type Query {
        products(productFetchDetails: ProductFetchDetails): ProductFetchDetailsOutput,
    }

    type Product {
        id: Int,
        name: String,
        type: String,
        calories: Int,
        fat: Int,
        protein: Int
    }

    type ProductFetchDetailsOutput {
        totalCount: Int!,
        products: [Product!]!
    }

    input ProductFetchDetails {
        limit: String,
        orderBy: String,
        where: String,
        first: Int,
        last: Int
    }
`

As you can see, it contains our product entity, an output type and an input. Our products query may accept an object with the details about the required data. That allows us to make server-side pagination, sorting and filtering.

Our schema is ready, now we have to resolve our query. To do so navigate in resolvers/Query.js and paste the following:

exports.Query = {
    products: async (parent, { productFetchDetails }, context) => {
        try {
            
            const products = await context.query(`
                SELECT * FROM products
                ${productFetchDetails?.where || ''}
                ${productFetchDetails?.orderBy || ''}
                ${productFetchDetails?.limit || ''}
            `)

            const totalCount = await context.query(`
                SELECT COUNT(*) AS c FROM products
                ${productFetchDetails?.where || ''}
            `)

            return { products, totalCount: totalCount[0].c }
        } catch (err) {
            console.log(err)
            return []
        }
    }
}

In the resolver of the products query, we have to fetch the data from MySQL. For the task, we will use the pre-created, from the template, query function.

The query function is accessible, thanks to the context. You may see it in server.js, where the startStandaloneServer is invoked.

With the above steps, our server is ready to accept queries and make server-side pagination, sorting and filtering.

You should start it with npm run nodemon

Setup React app

The first thing to do is to open a terminal in a client folder and run npx create-react-app smart-app

After creating the React project run cd smart-app to navigate to the project

Install SmartUI with the following command: npm install smart-webcomponents-react

Run npm start and open the application

The first thing to do is to remove the CSS from App.css

Also, remove the App-header from App.js

In the App.js you have to import SmartUI's CSS with the following import:

import 'smart-webcomponents-react/source/styles/smart.default.css';

To make queries to our GraphQL server, we should install ApolloClient and GraphQL in our React client

To do the following run: npm install @apollo/client graphql

After installing, we have to connect our ApolloClient to React. This is done the following way:

import React from 'react';
import ReactDOM from 'react-dom/client';

import './index.css';

import App from './App';

import { ApolloClient, InMemoryCache, ApolloProvider } from '@apollo/client';

const client = new ApolloClient({
  uri: 'http://localhost:3001/',
  cache: new InMemoryCache(),
});

const root = ReactDOM.createRoot(document.getElementById('root'));
root.render(
  <ApolloProvider client={client}>
    <App />
  </ApolloProvider>
);

Now we are ready to make queries to our ApolloServer using the useQuery hook.

Connect React with ApolloServer

In the previous section, we have connected ApolloClient to our React application, now we will create our Smart.Grid along with the necessary query to our GraphQL server.

Open App.js and create the query above the component:

const GET_PRODUCTS = gql`
  query Products($productFetchDetails: ProductFetchDetails){
    products(productFetchDetails: $productFetchDetails) {
      totalCount,
      products {
        id,
        name,
        type,
        calories,
        fat,
        protein
      }
    }
  }
`

The query can now be used with the useQuery hook. Initially, it will look like this:

function App() {

    const { loading, error, data, refetch } = useQuery(GET_PRODUCTS);
    ...

After having the query, we will create our Smart.Grid with virtualDataSource. The virtualDataSource is a function, which is invoked on each grid's action that manipulates the data. In this function, we will refetch our query with different variables. Please see the full version of our App.js

import './App.css';
import 'smart-webcomponents-react/source/styles/smart.default.css';
import { useState, useRef } from 'react';
import { gql, useQuery } from '@apollo/client';

import Grid from 'smart-webcomponents-react/grid';

const GET_PRODUCTS = gql`
  query Products($productFetchDetails: ProductFetchDetails){
    products(productFetchDetails: $productFetchDetails) {
      totalCount,
      products {
        id,
        name,
        type,
        calories,
        fat,
        protein
      }
    }
  }
`

function App() {

  const { loading, error, data, refetch } = useQuery(GET_PRODUCTS);

  const gridRef = useRef(null);

  const [gridSettings] = useState({
    dataSource: new window.Smart.DataAdapter({
      virtualDataSource: async (resultCallback, details) => {
        try {
          const res = await refetch({
            productFetchDetails: {
              limit: details.query.limit,
              orderBy: details.query.orderBy,
              where: details.query.where,
              first: details.first,
              last: details.last
            }
          })

          resultCallback({
            dataSource: res.data?.products.products,
            virtualDataSourceLength: res.data?.products.totalCount
          })
        } catch (error) {
          console.log(error);
          resultCallback({
            dataSource: []
          })
        }
      },
      id: 'id',
      dataFields: [
        'id: number',
        'name: string',
        'type: string',
        'calories: number',
        'fat: number',
        'protein: number'
      ]
    }),
    columns: [
      {
        label: 'Name',
        dataField: 'name'
      },
      {
        label: 'Type',
        dataField: 'type'
      },
      {
        label: 'Calories',
        dataField: 'calories'
      },
      {
        label: 'Fat',
        dataField: 'fat'
      },
      {
        label: 'Protein',
        dataField: 'protein'
      }
    ],
    sorting: {
      enabled: true
    },
    filtering: {
      enabled: true
    },
    pager: {
      visible: true
    },
    paging: {
      enabled: true,
      pageSize: 2
    }
  })

  return (
    <div className="App">
      <Grid
        ref={gridRef}
        dataSource={gridSettings.dataSource}
        columns={gridSettings.columns}
        sorting={gridSettings.sorting}
        filtering={gridSettings.filtering}
        pager={gridSettings.pager}
        paging={gridSettings.paging}
      >
      </Grid>
    </div>
  );
}

export default App;

Now you can start the application and make server-side operations with Smart.Grid, thanks to the ApolloClient and ApolloServer.

Adding CRUD operation

To level-up the Grid, we can add CRUD support. To support such operations, the schema.js of the GraphQL should be updated to this:

exports.typeDefs = `#graphql
    type Query {
        products(productFetchDetails: ProductFetchDetails): ProductFetchDetailsOutput,
    }

    type Mutation {
        addProduct(data: ProductData): Product,
        removeProduct(id: Int): Boolean,
        updateProduct(id: Int, data: ProductData): Product
    }

    type Product {
        id: Int,
        name: String,
        type: String,
        calories: Int,
        fat: Int,
        protein: Int
    }

    type ProductFetchDetailsOutput {
        totalCount: Int!,
        products: [Product!]!
    }

    input ProductFetchDetails {
        limit: String,
        orderBy: String,
        where: String,
        first: Int,
        last: Int
    }

    input ProductData {
        name: String,
        type: String,
        calories: Int,
        fat: Int,
        protein: Int
    }
`

After defining the Mutations, we have to resolve them. The correct way is to create a new file in the resolvers, called Mutation.js. In that file all the mutations are going to be resolved, paste this into the file. As you can see, again we are using the query from the context:

exports.Mutation = {
    addProduct: async (parent, { data }, context) => {
        try {
            const res = await context.query(`
                INSERT INTO products(name, type, calories, fat, protein)
                VALUES ('${data.name}', '${data.type}', ${data.calories}, ${data.fat}, ${data.protein})
            `)
            
            const product = await context.query(`
                SELECT * FROM products
                WHERE id = ${res.insertId}
            `);

            return product[0]
        } catch (err) {
            console.log(err)
            return null
        }
    },
    removeProduct: async (parent, { id }, context) => {
        try {
            await context.query(`
                DELETE FROM products WHERE id = ${id};
            `)

            return true
        } catch (err) {
            console.log(err)
            return false
        }
    },
    updateProduct: async (parent, { id, data }, context) => {
        try {
            const res = await context.query(`
                UPDATE products
                SET name = '${data.name}', type = '${data.type}', calories = ${data.calories}, fat = ${data.fat}, protein = ${data.protein}
                WHERE id = ${id};
            `)
            
            const product = await context.query(`
                SELECT * FROM products
                WHERE id = ${id}
            `);

            return product[0]
        } catch (err) {
            console.log(err)
            return null
        }
    }
}

We have created the resolver, but now it should be registered inside the ApolloServer constructor. Open server.js and edit the server creation:

...
const { typeDefs } = require('./src/schema');
const { Query } = require('./src/resolvers/Query');
const { Mutation } = require('./src/resolvers/Mutation');

const server = new ApolloServer({
    typeDefs,
    resolvers: {
        Query,
        Mutation
    }
});
...

With these steps, the server is ready to receive requests, now we will transition to the React client. The settings of the Grid have to be changed to support CRUD operations. The virtualDataSource should also be changed and mutations should be created. Edit the App.js to this:

import './App.css';
import 'smart-webcomponents-react/source/styles/smart.default.css';
import { useState, useRef } from 'react';
import { gql, useQuery, useMutation } from '@apollo/client';

import Grid from 'smart-webcomponents-react/grid';

const GET_PRODUCTS = gql`
  query Products($productFetchDetails: ProductFetchDetails){
    products(productFetchDetails: $productFetchDetails) {
      totalCount,
      products {
        id,
        name,
        type,
        calories,
        fat,
        protein
      }
    }
  }
`

const ADD_PRODUCT = gql`
  mutation AddProduct($data: ProductData){
    addProduct(data: $data) {
      id
    }
  }
`

const REMOVE_PRODUCT = gql`
  mutation RemoveProduct($id: Int){
    removeProduct(id: $id)
  }
`

const UPDATE_PRODUCT = gql`
  mutation UpdateProduct($id: Int, $data: ProductData){
    updateProduct(id: $id, data: $data) {
      id
    }
  }
`

function App() {

  const getProductResults = useQuery(GET_PRODUCTS);
  const [addProduct] = useMutation(ADD_PRODUCT);
  const [removeProduct] = useMutation(REMOVE_PRODUCT);
  const [updateProduct] = useMutation(UPDATE_PRODUCT);

  const gridRef = useRef(null);

  const [gridSettings] = useState({
    dataSource: new window.Smart.DataAdapter({
      virtualDataSource: async (resultCallback, details) => {
        try {

          if (details.action === 'remove') {
            await removeProduct({ variables: { id: details.edit.row.id } })
          } else if (details.action === 'update' && details.edit?.row?.id) {
            await updateProduct({
              variables: {
                id: details.edit.row.id,
                data: {
                  name: details.edit.row.name,
                  type: details.edit.row.type,
                  calories: details.edit.row.calories,
                  fat: details.edit.row.fat,
                  protein: details.edit.row.protein
                }
              }
            })
          } else if (details.action === 'add') {
            await addProduct({
              variables: {
                data: {
                  name: details.edit.row.name,
                  type: details.edit.row.type,
                  calories: details.edit.row.calories,
                  fat: details.edit.row.fat,
                  protein: details.edit.row.protein
                }
              }
            })
          }

          const productFetchDetails = {
            limit: details.query.limit,
            orderBy: details.query.orderBy,
            where: details.query.where,
            first: details.first,
            last: details.last
          }

          const res = await getProductResults.refetch({ productFetchDetails })

          resultCallback({
            dataSource: res.data?.products.products,
            virtualDataSourceLength: res.data?.products.totalCount
          })
        }
        catch (error) {
          console.log(error);
          resultCallback({
            dataSource: []
          })
        }
      },
      dataFields: [
        'id: number',
        'name: string',
        'type: string',
        'calories: number',
        'fat: number',
        'protein: number'
      ]
    }),
    columns: [
      {
        label: 'Name',
        dataField: 'name'
      },
      {
        label: 'Type',
        dataField: 'type'
      },
      {
        label: 'Calories',
        dataField: 'calories'
      },
      {
        label: 'Fat',
        dataField: 'fat'
      },
      {
        label: 'Protein',
        dataField: 'protein'
      }
    ],
    sorting: {
      enabled: true
    },
    filtering: {
      enabled: true
    },
    pager: {
      visible: true
    },
    paging: {
      enabled: true,
      pageSize: 2
    },
    editing: {
      enabled: true,
      action: 'none',
      mode: 'row',
      addDialog: {
        enabled: true
      },
      addNewRow: {
        visible: true,
        position: 'far'
      },
      commandColumn: {
        visible: true
      }
    }
  })

  return (
    <div className="App">
      <Grid
        ref={gridRef}
        dataSource={gridSettings.dataSource}
        columns={gridSettings.columns}
        sorting={gridSettings.sorting}
        filtering={gridSettings.filtering}
        pager={gridSettings.pager}
        paging={gridSettings.paging}
        editing={gridSettings.editing}
      >
      </Grid>
    </div>
  );
}

export default App;

After starting the server and the client a Smart.Grid that supports Server-Side Sorting, Filtering, Pagination and CRUD operations should appear!