Building CRUD operations in AG Grid with Sequelize & Angular
Tags: howto
Published: 2022-01-20 14:22:10

---
title: Building CRUD operations in AG Grid with Sequelize & Angular
published: true
date: 2022-01-18 15:41:33 UTC
tags: HowTo
canonical_url: https://blog.ag-grid.com/building-crud-operations-with-sequelize-angular-ag-grid/
---

![Building CRUD operations in AG Grid with Sequelize & Angular](https://blog.ag-grid.com/content/images/2022/01/CrudWAngSeq.png)

Author Credit: [Bam Fard](https://blog.ag-grid.com/author/bam/)

In this post we'll show how to implement CRUD operations in AG Grid, Angular and [Sequelize](https://sequelize.org/). You'll see how easy it is to integrate AG Grid, a Node.js server with Express, Sequelize and a [PostgreSQL](https://www.postgresql.org/)database.‌

**What is Sequelize?**

According to the [Sequelize](https://sequelize.org/)website, it is a promise-based Node.js ORM for Postgres, MySQL, MariaDB, SQLite and Microsoft SQL Server. It features solid transaction support, relations, eager and lazy loading, read replication and more.

In this post, we'll go through the steps of configuring the database, the backend and the frontend containing AG Grid to display and create data. Use the table of contents to skip ahead to the relevant section if you need to.


- [Database Setup](#database-setup)
- [Server Setup](#server-setup)
- [Sequelize Setup](#sequelize-setup)
- [Angular & AG Grid Setup](#angular-and-ag-grid-setup)
- [Server-side Logic](#server-side-logic)
  - [Column Sorting](#column-sorting)
  - [Column Filtering](#column-filtering)
  - [Row Grouping](#row-grouping)
  - [Querying the Database](#querying-the-database)

## Database Setup

I'll be using [PostgreSQL](https://www.postgresql.org/)for this blog because it’s feature-rich, has extensive documentation, is free and compatible with [Sequelize](https://sequelize.org/). It also has an excellent GUI tool in the form of [pgAdmin](https://www.pgadmin.org/).

Because we’re going to be using Sequelize, the choice of database doesn’t really matter as long as the database is a recognised _dialect_ in Sequelize. I’d recommend you stick with me on Postgres, but if you prefer you can swap it out for another technology; check out the Sequelize docs [**here**](https://sequelize.org/v5/manual/dialects.html) for more information on compatible databases.

Please download the latest version of PostgreSQL, pgAdmin4 & Command Line Tools (optional but useful if you want to go further into Postgres outside of this blog). You can find these **[here](https://www.enterprisedb.com/downloads/postgres-postgresql-downloads)**, they're all part of the same installer.

![Building CRUD operations in AG Grid with Sequelize & Angular](https://blog.ag-grid.com/content/images/2021/10/Select-Components-1.png)
_Select these and follow the wizard_

‌Make sure to remember your password!

## Server Setup

Now that you have a database up and running, let's configure the server needed to host the application code.

If you haven’t built a server before, **[this](https://www.enterprisedb.com/postgres-tutorials/how-quickly-build-api-using-nodejs-postgresql)** short introduction is extremely useful and it’s what I used as a base for my server.

If you’re already confident with servers or have finished the above guide, then you’re ready for _my_ server code which you'll find **[here](https://github.com/icedjello/sequelize-postgres-olympic-data)**. Please clone or download the repo and run `yarn install`  OR `npm i`.

**Note** : At this point you won't be able to run the project as there are parts you'll need to fill in.

Before we can get to the Sequelize bit, I’m going to run you through a couple of additions I made to the EDB guide:

**[CORS](https://www.npmjs.com/package/cors)(essential):** We’ll use this tool to prevent security issues between the browser and server (in real applications, this is a major vector for attack). In our case, we’re setting it up as follows:

```
const cors = require("cors");
app.use(cors());
```

**[nodemon](https://www.npmjs.com/package/nodemon)(optional):** Programming a server can quickly become excruciating if you have to terminate and rerun your server each time you make a change (purists may disagree), if you want to have an easier life, install nodemon and augment your start script thusly:

```
"scripts": {
  "start": "nodemon ./server.js"
},

```

_Each time you save, the server automatically restarts_

## Sequelize Setup

‌[Sequelize](https://sequelize.org/)is a wonderful tool, especially for JavaScript developers, as it translates syntactically rich JavaScript into different _dialects_ of database.

For those who aren’t familiar with Sequelize, let me quickly explain: There are many databases out there, some quite simple “I need this from this table” affairs and others capable of handling massive datasets spread across multiple tables with granular security configurations. And although most SQL frameworks look syntactically similar to our human eyes—we humans can usually get by with learning one and jerry-rigging the rest with a little help from documentation and forums —computers can’t just fudge it…

Cue Sequelize: A neat translation tool that allows for relatively easy promise-based interaction with supported databases.

In a non-Sequelize CRUD, you would have to program your server to specifically make database calls in the language of the database; for example, if we had a table of cars and wanted to see all of its data, we would use the following query:

```
SELECT * FROM cars;
```

_One of the greatest hits_

Here’s the same query using Sequelize:

```
const allCars = await Cars.findAll();
```

_Looks like JavaScript, right?_

‌On the back-end, Sequelize is turning your JavaScript into `SELECT * FROM cars;` and asynchronously popping the result into a const called `allCars`.

Now that we have a database set up and ready, we need to connect our server to it. At the top of the `server.js` page, you’ll note the following:

```
const environment = require('./config/config.json').test;
```

If you look in the files however, you’ll note that there isn’t a config folder containing a config.json file. This is because this file is different for each user, and you’ll have to make it yourself. So add a config folder at the root directory,  call it “config” and inside it create a “config.json” file.

Within you’ll need to add code like this:

```
{
  "development": {
  },
  "test": {
    "username": "postgres",
    "password": “PASSWORD_FOR_YOUR_OWN_DATABASE”,
    "database": "olympic_data_test",
    "host": "127.0.0.1",
    "dialect": "postgres"
  },
  "production": {
  }
}
```

Note that these are **MY** details, remember the password you originally used to make your database? This is where it’ll come in handy.

Also note that for the sake of this blog, I’m only using the “test” profile.

Lastly, see the last line in the test object? We’re defining our dialect as “postgres”—more on this in a minute.

Once you’re done, your server should have access to all the information needed to access the database, and we can pass the information from our config file to Sequelize like so:

```
const sequelize = new Sequelize(environment.database, environment.username, environment.password, {
    host: environment.host,
    dialect: environment.dialect,
    additional: {timeStamps: false}
});
```

You can see I’m passing the database name, username, password and host address to Sequelize. I’m also telling Sequelize what database language to translate my JS to and lastly, I’m telling it I don’t want timestamps (optional).

The next couple lines of code are for authentication, establishing a stable connection to the database (not the table, as we haven’t even defined it yet), if you get an error here, chances are you misspelled something.

```
sequelize
    .authenticate()
    .then(() => {
        console.log('Connection has been established successfully.');
    })
    .catch(err => {
        console.error('Unable to connect to the database:', err);
    });
```

After this, we define our Athlete table:

```
const Athlete = sequelize.define('athlete', {
    athlete: {type: Sequelize.STRING, allowNull: false},
    age: {type: Sequelize.INTEGER, allowNull: false},
    country: {type: Sequelize.STRING, allowNull: false},
    sport: {type: Sequelize.STRING, allowNull: false},
    gold: {type: Sequelize.INTEGER, allowNull: false},
    silver: {type: Sequelize.INTEGER, allowNull: false},
    bronze: {type: Sequelize.INTEGER, allowNull: false}
});
```

This code defines a model for Sequelize, so it _understands_ what an “athlete” looks like. This is all _bread-and-butter_, each field has a type and I’ve coded it so no field can be null.

Next, I'm force-synchronising our Sequelize model with the database. This means Sequelize will check the database to see if a table with the name "athlete" exists, if it does it will be _dropped_ and recreated (the "force" part) and if it doesn’t it will just be created.

```
Athlete.sync({force: true})
    .then(() => {
        const rowData = require('./rowData/rowData');
        async function addDataToDB(rowData) {
            for (let row of rowData) {
                await Athlete.create(row);
            }
        }
        addDataToDB(rowData).then(() => {});
    });
```

Once this is done, the `then` clause comes into play, taking the data from `./rowData/rowData` and creating an entry in the database’s athlete table for each object.

**Note** : What I’ve just done above is the simplest database setup workflow. This is not best practice, it’s just the quickest way I can explain/show the process. This would be pretty useless for most real-world deployments because each time the server is instantiated, the table is dropped (all data lost) and a new one made (with fresh data). If you want to prevent this **for educational purposes,** you can change the value of the `force` parameter to `false`. This will persist your database.‌

## Angular and AG Grid Setup

Now that we've configured the backend, let's move to building the frontend. For the sake of this blog, I’ve decided to go with Angular 12, though any modern version should work. If you’re new here, I’d recommend following the Angular guide from AG Grid's **[documentation](https://www.ag-grid.com/angular-data-grid/getting-started/)** to get started. I’m going to be using an almost-identical setup for this blog, the only addition here is the material theme which allows me to have nice-looking buttons for the ‘create’ & ‘delete’ functions and a pretty modal interface for the former.

Once you're familiar with Angular & AG Grid, you're ready to download the front-end project **[here](https://github.com/icedjello/oympic-winners-fe)**.

If you’re already familiar with AG Grid or used the getting started guide from our docs, there are a few things to note:

- I’m [importing AG Grid using modules](https://www.ag-grid.com/javascript-data-grid/modules/), because I like the practice of keeping things tight and minimal, importing only what’s needed:

```
public readonly modules: Module[] = [ServerSideRowModelModule, ColumnsToolPanelModule, MenuModule, SetFilterModule];
```

- I’m using the [server-side row model](https://www.ag-grid.com/javascript-data-grid/server-side-model/) type to load the data from the server in pages, as data-manipulations are done server-side. The code below activates the use of the server-side row model:

```
public readonly rowModelType = 'serverSide';
```

- I’ve also decided to have both [partial & full store types](https://www.ag-grid.com/javascript-data-grid/server-side-model-row-stores/). This means that the grid will use the “full” store type when querying for grouped rows and the “partial” type when no grouping is active, as shown below:

```
public getServerSideStoreParams: (params: GetServerSideStoreParamsParams) => ServerSideStoreParams = params => {
  const groupingActive = !(params.rowGroupColumns.length === 0);
  if (groupingActive) {
    return {
      storeType: 'full',
    };
  } else {
    return {
      storeType: 'partial',
      cacheBlockSize: 20,
      maxBlocksInCache: 3,
    };
  }
}

```

**Reading records**

The AG Grid [server-side row model](https://www.ag-grid.com/javascript-data-grid/server-side-model/) loads data from the server using the `getRows` API method. You’ll find this data getting bound via `setServerSideDatasource` in the `onGridReady` event handler:

```
  this.gridApi.setServerSideDatasource({
    getRows(params: IServerSideGetRowsParams) {
      const {startRow, endRow, rowGroupCols, groupKeys, filterModel, sortModel} = params.request;
      const body = {startRow, endRow, rowGroupCols, groupKeys, filterModel, sortModel};
      that.http.post('http://localhost:3000/read', body)
        .subscribe((dataAndCount) => {
          const receivedData = dataAndCount as { rows: {}[], count: number }
          params.success({
            rowData: receivedData.rows,
            rowCount: receivedData.count
          });
        })
      return;
    }
  })
}
```

Here you can see how I'm making a `POST` request in each `getRows` call, the body of which is furnished with relevant information from the request object provided by AG Grid. This information is then used by server to generate the relevant query for database.

The route used here ends with `/read` . That’s because this is the ‘Read’ operation in CRUD.

**Creating records**

Let’s look at how to add records. In our sample click the **Create** button above the grid:

![Building CRUD operations in AG Grid with Sequelize & Angular](https://blog.ag-grid.com/content/images/2021/10/Pasted-Graphic-1.png)

This brings up a modal, and once all the fields are populated (remember from earlier, no nulls are allowed in any column of our table), the **Submit** button becomes clickable. Once a user clicks the **Submit** button, the following function is called:

```
private createServerSideData(newRowData: {}): void {
  this.http.post('http://localhost:3000/create', newRowData)
    .subscribe((resMsg) => {
        console.log(resMsg)
      }
    )
  if (this.isGroupingActive()) {
    const result: ServerSideTransactionResult = this.gridApi?.applyServerSideTransaction({
        add: [newRowData],
      },
    ) as ServerSideTransactionResult
    console.log(result)
  } else {
    this.noGroupingRefresh();
  }
}
```

Here you can see our route ends with "create", the “C” in “CRUD”, and the body is all the information captured by the modal.‌

## Server-side logic

Now that we've covered the front-end code, let’s work backwards to the server and see how this circle closes. In our server.js file, we have the following four lines of code:

```
app.post('/create', (request, response) => createHandler(Athlete, request, response));

app.post('/read', (request, response) => readHandler(Athlete, request, response));

app.post('/update', (request, response) => updateHandler(Athlete, request, response));

app.post('/delete', (request, response) => deleteHandler(Athlete, request, response));
```

**Note** : If you’re wondering where **`app.post`** is from, this is all handled by Express, a node.js routing library—explained in the linked guide from Part 2.

As you’ve probably noticed, each line above refers to one of the four letters/functionalities of CRUD. In keeping with the above chapter let’s focus on the READ path:

```
app.post('/read', (request, response) => readHandler(Athlete, request, response));
```

The request comes in, and along with the  table/model (‘Athlete’), is passed to a handler function. If you have a look at the ~/handlers/readHandler.js file, you'll note that it's quite large so let's break it down in sections and hopefully it'll make sense.

```
module.exports.readHandler = function (database, request, response) {
    const {startRow, endRow, rowGroupCols, groupKeys, filterModel, sortModel} = request.body;
    let options = {
        offset: startRow,
        limit: endRow,
        where: {
            [Op.and]: []
        }
    };
    const AND_ARRAY = options.where[Op.and];
```

In the code above, I've extracted all the variables that could affect our query into const objects read from the request object. Remember, this is information provided as params to `getRows()` from AG Grid.

Next I'm creating an object called options. This object is provided to Sequelize to help it build the eventual query for the database. The first two properties should be quite self-explanatory, using server-side data often means you don't want the whole dataset at once, offset and limit allow us to declare which slice of the total dataset we're after—for instance, rows 250 to 400.

I've also built a `where`  clause and within it used the Sequelize `Op.and` operator which takes an array of conditions—for instance "car = Volkswagen". This may look quite complicated but is one of Sequelize's main benefits; once you start building queries like this, you're able to quickly deploy RESTful APIs to do what you want. To find out more about Sequelize and it's wonderfully flexible operators check out the documentation **[here](https://sequelize.org/master/manual/model-querying-basics.html)**.

The last line in the code is there for utility, due to the way I've designed the grid and its filters, all conditions for READ requests can be stacked inside the `AND_ARRAY`. Therefore, this reference allows me to easily add conditions later on.

Now let's look at sorting, filtering and grouping.‌

### Column Sorting

To sort, we first need to find out if a `sortModel` exists. The request from AG Grid will always contain a `sortModel` array, and a sort is applied if the sort model has non-zero length:

```
    if (sortModel.length > 0) {
        options.order = getSortingOrder(sortModel);
    }
```

If a `sortModel` exists—the array is passed to a helper-function which turns AG Grid's `sortModel` into a Sequelize-consumable order which is set into the aforementioned options object as shown below:

```
module.exports.getSortingOrder = function (sortModel) {
    let order = [];
    for (let sort of sortModel) {
        const direction = sort.sort.toUpperCase();
        order.push([sort['colId'], direction]); // [column, direction]
    }
    return order;
}
```

### Column Filtering

As with sorting, we need to find out if a valid filterModel exists.  This is done using a similar approach:

```
    if (Object.keys(filterModel).length > 0) {
        AND_ARRAY.push(getFilteringAndArray(filterModel));
    }
```

If a valid model exists, a helper function is used to build the Sequelize-query. I'll spare you a copy & paste of the entire function from ~/helper\_functions/getFilteringAndArray.js and just explain the different parts.

AG Grid has different kinds of filters, [**Simple Column Filters**](https://www.ag-grid.com/javascript-data-grid/filter-provided-simple/), and **[Set Filters](https://www.ag-grid.com/javascript-data-grid/filter-set/)**; each type is represented slightly differently in the `filterModel` object and thus will have to be treated differently. We can do this by checking if certain column names exists:

```
    if (filterColumns.includes('athlete')) {
        andArray.push({
            athlete: {
                [Op.substring]: filterModel.athlete.filter
            }
        });
    }
```

The first condition checks for the athlete column, this will require a _like_ query—so when a user types "Yang" the query should work such that all rows with the substring "yang" in their "athlete" column should be returned. Here, the Sequelize `Op.substring` operator is used to get this result as shown below:

![Building CRUD operations in AG Grid with Sequelize & Angular](https://blog.ag-grid.com/content/images/2021/11/Nov-18-2021-10-21-21.gif)

The country and Sport column both have setFilters which need to be handled differently.

![Building CRUD operations in AG Grid with Sequelize & Angular](https://blog.ag-grid.com/content/images/2021/11/Nov-18-2021-10-24-13.gif)

Set filters in AG Grid allow users to select multiple filter values at once. This requires an OR operator between the different filter values:

```
    if (filterColumns.includes('country')) {
        andArray.push({
            country: {
                [Op.or]: filterModel.country.values
            }
        });
    }
```

Lastly, the simple number filters with greater-than/less-than operators:

```
Object.entries(filterModel).forEach(([columnName]) => {
        if (['age', 'gold', 'silver', 'bronze'].includes(columnName)) {
            const filterValue = filterModel[columnName].filter;
            const filterType = filterModel[columnName].type;
            const filterSign = filterType === "greaterThan" ? Op.gt : filterType === "lessThan" ? Op.lt : Op.eq;
            const query = {
                [columnName]: {
                    [filterSign]: filterValue
                }
            };
            andArray.push(query);
        }
    });
```

In the end, all of this returns an array which is added to our `AND_ARRAY`.

### Row Grouping

In order to demonstrate row grouping, we display the AG Grid [row group panel](https://www.ag-grid.com/javascript-data-grid/grouping-group-panel/), which allows users to simply drag and drop columns to group by, as shown below:

![Building CRUD operations in AG Grid with Sequelize & Angular](https://blog.ag-grid.com/content/images/2021/11/Nov-18-2021-10-29-43.gif)

The key here is knowing how to manage the 'attributes' you wish to show when grouping or when leaf-nodes are exposed.

For example, in the GIF above, when I initially group by "Country", the only attribute shown should be "Country". However, when I first open the "Italy" group, all the attributes should be provided.

If you look at the grouping section of **readHandler.js** , you'll note how I'm using `if()` statements to deduce what state of grouping is present in the grid. The comments in capitals in this file explain how this is implemented.

### Querying the Database

Lastly, we have to actually query the database and send the data back to the grid:

```
    database.findAndCountAll(options)
        .then(dataAndCount => {
            if (Array.isArray(dataAndCount.count)) {
                dataAndCount.count = dataAndCount.count.length;
            }
            response.json(dataAndCount);
        });
```

You'll find code like the above at the bottom of each handler, this is the part where the query is dispatched. The last check is in case of grouping, where the count value is returned as an array of objects rather than a number—each object holding the count for a given group– for example: `{Spain: 20}`. In cases where row grouping is active, just count the number of objects in the array.

Once this check is out of the way, the data is sent as a JSON array back to the grid where the response is consumed and via `params.success` is set as rowData:

```
    this.gridApi.setServerSideDatasource({
      getRows(params: IServerSideGetRowsParams) {
        const {startRow, endRow, rowGroupCols, groupKeys, filterModel, sortModel} = params.request;
        const body = {startRow, endRow, rowGroupCols, groupKeys, filterModel, sortModel};
        that.http.post('http://localhost:3000/read', body)
          .subscribe((dataAndCount) => {
            const receivedData = dataAndCount as { rows: {}[], count: number }
            params.success({
              rowData: receivedData.rows,
              rowCount: receivedData.count
            });
          })
        return;
      }
    })
```

## Summary

We hope you find this article helpful when using AG Grid with Angular, a Node.js server with Express, Sequelize and a [PostgreSQL](https://www.postgresql.org/)database. Feel free to fork the example from this blog and modify it according to your needs.

If you would like to try out AG Grid check out our getting started guides ([JS](https://www.ag-grid.com/javascript-getting-started/) / [React](https://www.ag-grid.com/react-getting-started/) / [Angular](https://www.ag-grid.com/angular-getting-started/) / [Vue](https://www.ag-grid.com/vue-getting-started/))