How to use MUI Autocomplete with API Calls to a SQL Database in Next.js.

Overview

In this article, we will once again explore the awesome combination of Next.js and Material UI to create a dynamic autocomplete with data fetched from an external API. Since Next.js is a full stack framework, we will implement an autocomplete component that leverages API calls to retrieve real-time data. This would would be useful for title or name autocomplete field from a table with a large number of records. So, let’s dive in and learn how to build an efficient and beautiful autocomplete component using Next.js and Material UI.

Download Source from GitHub https://github.com/fullstacksoup

GitHub

Getting Started

Get the MUI Next.js Template – Recommended

If you don’t want to download the example above from GitHub then you can get the Next.js with MUI template below.

Note: Version 5 was released on Sep 16, 2021.

Next.js with MUI 5.x JavaScript Template

https://github.com/mui/material-ui/tree/master/examples/material-next

Next.js with MUI 5.x TypeScript Template

https://github.com/mui/material-ui/tree/master/examples/material-next-ts

Install SQLite

SQLite is a SQL database that runs locally and is used to develop embedded software for devices like televisions, cell phones, etc. For the example below, it is used for convenience, so there is no need to signup for a hosted database.

Install SQlite.

npm install sqlite sqlite3

Install Axios as an alternative to Fetch (Optional)

npm install axios

Database Connector File – (Optional)

Building an API handler under ‘libs/SiteConn.js to get

export default {
  get_config: function(){
    return {           
      ChinookDbFileName: "./data/Chinook.db",
      NorthwindDbFileName: "./data/northwind.db",
      CommentDbFileName: "./data/comments.db",
    }
  }
}

Next.js API

Building an API handler under ‘pages/api/[pid].js to get

const sqlite = require('sqlite');
const sqlite3= require('sqlite3');
import {open} from 'sqlite';
import moment from 'moment';
import LibConst from "@/libs/SiteConn";

export default async function (req, res){

  if (req.method === 'GET') {
    try{
      
      const { pid } = req.query
      console.log('pid', pid)
      var dbFile = LibConst.get_config().ChinookDbFileName
      const db = await open(
        {filename: dbFile , driver: sqlite3.Database}
      );
    
      const results = await db.all(
        `SELECT AlbumId, ArtistId, Title FROM albums WHERE Title LIKE '${pid}%'`,      
        
      )    
               
      res.json(results);

    } catch (err) {
        console.log(err);
        res.status(500).send();    
    }   
  }
};

Autocomplete Component

Building a component labeled AutocompleteNames.js

import React from "react";
import TextField from '@mui/material/TextField';
import Stack from '@mui/material/Stack';
import Box from '@mui/material/Box';
import Autocomplete from '@mui/material/Autocomplete';
import CircularProgress from "@mui/material/CircularProgress";
import axios from "axios";


function sleep(delay = 0) {
  return new Promise((resolve) => {
    setTimeout(resolve, delay);
  });
}

export default function AutocompleteTitles(props) {
  const [open, setOpen] = React.useState(false);
  const [options, setOptions] = React.useState([]);
  const loading = open && options.length === 0;

  const onChangeHandle = async (value) => {
// this default api does not support searching but if you use google maps or some other use the value and post to get back you reslut and then set it using setOptions 
    console.log(value);
    
    const url = `http://localhost:3000/api/albums/${value}`;
    console.log('AutocompleteTitles url',url)
    if(value.length >= 1) {
      axios.get(url)
      .then(resp => {      
         console.log('albums',resp)
         setOptions(resp.data);   
      })
      .catch(err => {
        console.log('Error', err)
      })        
  
    }
  };

  React.useEffect(() => {
    let active = true;

    if (!loading) {
      return undefined;
    }

    (async () => {
      await sleep(1e3); // For demo purposes.

      if (active) {
        setOptions([]);
      }
    })();

    return () => {
      active = false;
    };
  }, [loading]);

  return (
    <Autocomplete
      id="async-autocomplete"
      style={{ width: 300 }}
      open={open}
      onOpen={() => {
        setOpen(true);
      }}
      onClose={() => {
        setOpen(false);
      }}
      
      getOptionSelected={(option, value) => 
        option === value
      }
      getOptionLabel={option => option.Title}
      renderOption={(props, option) => (        
        <Box component="li" sx={{ '& > img': { mr: 2, flexShrink: 0 } }} {...props}>           
          {option.Title} 
        </Box>
      )}
      options={options}
      loading={loading}      
      renderInput={params => (
        <TextField
          {...params}
          label="Select Page"
          variant="standard"
          onChange={ev => {
            // dont fire API if the user delete or not entered anything
            if (ev.target.value !== "" || ev.target.value !== null) {
              onChangeHandle(ev.target.value);
            }
          }}
          InputProps={{
            ...params.InputProps,
            endAdornment: (
              <React.Fragment>
                {loading ? (
                  <CircularProgress color="inherit" size={20} />
                ) : null}
                {params.InputProps.endAdornment}
              </React.Fragment>
            )
          }}
        />
      )}
    />
  );
}

Custom Select Options – (getOptionalLabel)

In cases where you want to display multiple columns when searching, getOptionLabel can accept custom input string.

getOptionLabel={(option) => `${option.FirstName} ${option.LastName}`}

Custom Render Options – (renderOption)

Use the renderOption property to render an image and/or multiple columns.

renderOption={(props, option) => (
  <Box component="li" sx={{ '& > img': { mr: 2, flexShrink: 0 } }} {...props}>
   <img
      loading="lazy"
      width="20"
      src={option.ImageURL}
      
      alt=""
    />
    {option.FirstName} {option.LastName}
  </Box>
)}

Page Component

Building a component labeled AutocompleteNames.js

import * as React from 'react';
import Container from '@mui/material/Container';
import Box from '@mui/material/Box';
import AutocompleteTitles from '@/components/AutocompleteTitles'

export default function Index() {

  return (
    <Container maxWidth="md" align="center">
      <Box sx={{ my: 4 }} >         
        
       <AutocompleteTitles/>
      </Box>
    </Container>
  );
}

Conclusion

Integrating Next.js with Material UI Autocomplete and API calls opens possibilities for working with more extensive tables. This how-to guide has the steps to seamlessly combine these technologies, allowing you to fetch data from APIs and display it efficiently in your Autocomplete component. By following these instructions, you can enhance the user experience of your web applications by providing instant suggestions and real-time data updates. Remember, the key to success is carefully managing API requests, optimizing performance, and crafting a user-friendly design. With this newfound knowledge, you are well-equipped to leverage the full potential of Next.js and Material UI Autocomplete, elevating your projects to new heights.