Sample CRM app with Fauna and Appsmith

Application development with Fauna and Appsmith is easy with the Fauna GraphQL API. In this tutorial, you’ll build a sample Customer Relationship Management (CRM) app using Fauna for data storage, a front end built with Appsmith, and GraphQL for querying and data manipulation.

Prerequisites

Set up a Fauna database

  1. Create a new database

    1. In the Fauna Dashboard, click the CREATE DATABASE link in the upper left corner.

    2. Name your database FaunaCRM (or another name of your choice).

    3. In the Region Group dropdown menu, select Classic.

    4. Leave the Use demo data box unchecked.

    5. Click the CREATE button.

  2. Import a GraphQL schema for your database

    1. Using your preferred text editor, create a file named schema.gql with the following content:

      type Customer {
  Customer_ID: String!
  Customer_name: String!
  Customer_email: String!
  Customer_Stage: String!
  Products: String!
}

type Product {
  ProductID: String!
  ProductName: String!
}

type Query {
  allCustomers: [Customer!]
  allProducts: [Product!]
}

    2. In the Fauna Dashboard, click GRAPHQL in the left-side navigation.

    3. Click IMPORT SCHEMA, which opens your browser’s file selector. Select the schema.gql file, and click the file selector’s Open button.

      The schema creates collections named Customer and Product, as well as indexes to support querying them.

  3. Add some documents

    The new collections were created when you imported the schema, but there are no documents in them yet. Click SHELL in the left-side navigation to access the Fauna Shell, where you can run shell commands to access and manipulate your data.

    Run the following command in the shell to add documents to the Customer collection:

    Map(
  [
      {
        Customer_ID: "c1001",
        Customer_name: "Confidence Okoghenun",
        Customer_email: "confidence@appsmith.com",
        Customer_Stage: "Opportunity",
        Products: "p1111, p1112, p1113, p1114, p1115"
      },
      {
        Customer_ID: "c1002",
        Customer_name: "Courtney Simpson",
        Customer_email: "courtney.simpson@email.com",
        Customer_Stage: "Opportunity",
        Products: "p1114, p1113, p1112"
      },
      {
        Customer_ID: "c1003",
        Customer_name: "Daniel Carlson",
        Customer_email: "daniel.carlson@email.com",
        Customer_Stage: "Customer",
        Products: "p1113, p1112, p1114, p1115"
      },
      {
        Customer_ID: "c1004",
        Customer_name: "Paul Pruitt",
        Customer_email: "paul.pruitt@email.com",
        Customer_Stage: "Opportunity",
        Products: "p1115, p1112"
      },
     {
        Customer_ID: "c1005",
        Customer_name: "Erica Dawson",
        Customer_email: "Erica.Dawson@email.com",
        Customer_Stage: "Lead",
        Products: "p1116, p1113, p1111"
      },
      {
        Customer_ID: "c1006",
        Customer_name: "Alyssa Smith",
        Customer_email: "Alyssa.Smith@email.com",
        Customer_Stage: "Opportunity",
        Products: "p1117, p1114, p1112, p1113"
      },
      {
        Customer_ID: "c1007",
        Customer_name: "Richard Miller",
        Customer_email: "Richard.Miller@email.com",
        Customer_Stage: "Lead",
        Products: "p1118, p1115, p1113"
      },
      {
        Customer_ID: "c1008",
        Customer_name: "Emily Gonzalez",
        Customer_email: "Emily.Gonzalez@email.com",
        Customer_Stage: "Opportunity",
        Products: "p1119, p1116, p1114"
      },
      {
        Customer_ID: "c1009",
        Customer_name: "Mark Hinton",
        Customer_email: "Mark.Hinton@email.com",
        Customer_Stage: "Customer",
        Products: "p1120, p1117, p1115"
      },
      {
        Customer_ID: "c1010",
        Customer_name: "Jason Todd",
        Customer_email: "Jason.Todd@email.com",
        Customer_Stage: "Customer",
        Products: "p1121, p1118, p1111, p1116"
      }
  ],

  Lambda(
    "data",
    Create(Collection("Customer"), { data: Var("data") })
  )
)

    Next, add some documents to the Product collection:

    Map(
  [
    { ProductID: "p1111", ProductName: "Face Wash" },
    { ProductID: "p1112", ProductName: "Hair shampoo" },
    { ProductID: "p1113", ProductName: "Pet food" },
    { ProductID: "p1114", ProductName: "Juice" },
    { ProductID: "p1115", ProductName: "Socks" },
    { ProductID: "p1116", ProductName: "Shoes" },
    { ProductID: "p1117", ProductName: "Slippers" },
    { ProductID: "p1118", ProductName: "Wallet" },
    { ProductID: "p1119", ProductName: "Headphones" },
    { ProductID: "p1120", ProductName: "Television" },
    { ProductID: "p1121", ProductName: "Phone charger" },
    { ProductID: "p1122", ProductName: "Battery Bank" },
    { ProductID: "p1123", ProductName: "Trash Can" },
    { ProductID: "p1124", ProductName: "Table" },
    { ProductID: "p1125", ProductName: "Chair" },
    { ProductID: "p1126", ProductName: "Speakers" },
    { ProductID: "p1127", ProductName: "Laptop" }
  ],
  Lambda("data", Create(Collection("Product"), { data: Var("data") }))
)

Create a custom role and an API key

The safest way to access Fauna from a remote service such as Appsmith is to create a custom role which can only access the collections you specify, and then create an API key for that role.

  1. Create a new custom role

    Run the following command in the shell:

    CreateRole({
  name: "CRMrole",
  privileges: [
    {
      resource: Collection("Customer"),
      actions: { read: true, write: true }
    },
    {
      resource: Collection("Product"),
      actions: { read: true }
    },
    {
      resource: Index("allCustomers"),
      actions: { read: true, write: true }
    },
    {
      resource: Index("allProducts"),
      actions: { read: true }
    }
  ]
})

    The above command creates a role named CRMrole with read and write permissions on the Customer collection and the allCustomers index, and read permission on the Product collection and the allProducts index.

  2. Create an API key

    Next, run the following command in the shell:

    CreateKey({
  role: Role("CRMrole")
})

    Copy the string contained in the secret field and store it carefully. If you lose it, you must create a new key. There’s no way to display the secret for a key after it’s been created.

Create a new data source in Appsmith

Now that your database backend is set up, you’re ready to connect it with Appsmith.

  1. Log in to the Appsmith console

    Navigate to https://app.appsmith.com/user/login and log in with your Appsmith credentials.

  2. Create a new app

    On the APPS tab, click the NEW button to create a new app. Rename it to Fauna CRM in the upper left corner.

  3. Add a data source

    1. Click DATASOURCES in the left-side navigation.

    2. Click AUTHENTICATED API from the list of data sources.

    3. Rename the new data source FaunaCRM_data.

    4. In the URL text box, enter https://graphql.fauna.com/graphql.

    5. In the HEADERS text boxes, enter Authorization in the left box and Bearer <your-api-secret> in the right box. Replace <your-api-secret> with the Fauna API secret key you created earlier.

    6. Leave the other fields unchanged and click SAVE.

    The new data source is now ready to use.

Create queries

Next, create some GraphQL queries for the new data source to use in querying your Fauna database.

  1. Create a new API

    1. In the Appsmith console, click the NEW API button to the right of your new data source.

    2. Rename the new API to CustomerAPI.

    3. Select POST from the dropdown menu of HTTP methods.

    4. Click the BODY tab immediately below the URL.

    5. Enter the following query in the text box:

      {
    query: ' query {
        allCustomers {
             data {
                    _id
                    Customer_ID
                    Customer_name
                    Customer_email
                    Customer_Stage
                    Products
	    	      }
        }
    }'
}

      You can test your query by clicking the RUN button in the upper right corner. If the query is successful, the contents of your Customer collection appear in the pane below the query. If the query fails, you can find error information under the ERRORS tab.

      Now, you need another query to get data from the Product collection.

    6. In the left-side navigation, click on QUERIES/JS to expand the selection.

      You should see your existing query CustomerAPI there.

    7. Click ADD QUERY/JS.

    8. In the modal window, click New FaunaCRM_data Query.

    9. Rename the new API to ProductAPI.

    10. Select POST from the dropdown menu of HTTP methods.

    11. Click the BODY tab immediately below the URL.

    12. Enter the following query in the text box:

      {
    query: ' query {
        allProducts {
             data {
                    _id
                    ProductID
                    ProductName
	    	      }
        }
    }'
}

      Again, you can test your query with the RUN button. You should see the contents of your Product collection in the results pane.

      Next, create a query to update a customer’s information. This one is a bit more complex and it involves some UI elements which don’t exist yet, so you can’t test it right away, but it will be useful after you create the app UI.

      Repeat the above steps to create a new API. Name the new API updateCustomerAPI and give it the following query:

      {
        query: '
          mutation ($id: ID!, $object: CustomerInput!) {
            updateCustomer(id: $id, data: $object)
            {_id }
        }',
        variables: {
          "object": {
            "Customer_ID": {{Table1.selectedRow.Customer_ID}},
            "Customer_name": {{Input1.text}},
            "Customer_email": {{Input2.text}},
            "Customer_Stage": {{Select1.selectedOptionValue}},
            "Products": {{MultiSelect1.selectedOptionValues.join()}}
          },
          id: {{Table1.selectedRow._id}}
        }
}

      You may see some error messages in the Appsmith console, but don’t worry. The query will work after you’ve created the necessary UI elements for your app.

Build a user interface

The next step is to create a UI for your app with Appsmith.

  1. Start with a blank canvas

    Click the Page1 link in the upper left corner of the Appsmith console.

  2. Add UI elements

    1. Select the WIDGETS tab at the top of the left-side navigation.

    2. Find the Table widget and drag it onto the canvas. Expand the table to fill most of the canvas.

    3. In the right-side panel, delete the placeholder code in the TABLE DATA box and replace it with the following line:

      {{CustomerAPI.data.data.allCustomers.data}}

      The table should now be populated with data from your Customer collection.

    4. Turn off the visibility for some unnecessary columns.

      In the right-side panel, locate the COLUMNS pane and click the eye icon for the _id column and the Products column.

    5. Drag a Modal widget onto your canvas.

    6. Rename the widget to detailsModal. Close the modal window with the X icon in the upper right corner.

    7. Mouse over the table widget and click the Table1 link to open its properties.

    8. Click the ADD A NEW COLUMN button in the right-side panel.

    9. Rename the column Details.

    10. Click the gear icon on the Details column to see its properties. Change the column type from Plain Text to Button.

    11. Change the button’s label from Action to View.

    12. Select Open modal from the onClick dropdown menu.

    13. Select detailsModal from the Select Modal dropdown menu.

    14. Click a VIEW button to open the modal window.

    15. Drag four Text widgets onto the modal, stacking them vertically. Relabel them Name, Email, Stage, and Products.

      At this point the modal window should look similar to this:

      Appsmith modal window for customer details

    16. Drag Input widgets next to the Name and Email text boxes. Set the default text for the input box next the Name field to the following line:

      {{Table1.selectedRow.Customer_name}}

      Set the default text for the input box next the Email field to the following line:

      {{Table1.selectedRow.Customer_email}}

    17. Drag a Select widget next to the Stage text box. Replace the code in its Options panel with the following code:

      [
  {
    "label": "Customer",
    "value": "Customer"
  },
  {
    "label": "Opportunity",
    "value": "Opportunity"
  },
  {
    "label": "Lead",
    "value": "Lead"
  }
]

      Set the default value for the Select widget to the following line:

      {{Table1.selectedRow.Customer_Stage}}

    18. Drag a Multiselect widget next the Products text box. Set its Options panel to the following:

      {{ProductAPI.data.data.allProducts.data.map( (obj) =>{ return  {'label': obj.ProductName, 'value': obj.ProductID} })}}

      Set its default value to the following:

      {{Table1.selectedRow.Products}}

      The Details window should now look similar to the following:

      Appsmith modal window for customer details

    19. Edit the properties for the CONFIRM button. In the Events section of the properties panel, click the JS button in the onClick property. Enter the following code:

      {{updateCustomerAPI.run(() => {CustomerAPI.run(), closeModal('detailsModal')}, () => {})}}

      This sets an onClick event for the button which refreshes the table and closes the window.

Test the app

Your app is now complete and ready to try out. Click the DEPLOY button in the top-right corner of the console to view the app in a web page. You can view and update customer details with the modal window.

Is this article helpful? 

Tell Fauna how the article can be improved:
 Visit Fauna's forums or email docs@fauna.com

Thank you for your feedback!