Step-by-Step Guide to building a custom database optimizer portal block for Gutenberg using Next.js headless configurations

Setting Up the Next.js Headless WordPress Environment

To build a custom Gutenberg block powered by a Next.js frontend, we first need a robust headless WordPress setup. This involves configuring WordPress to serve content via its REST API and setting up a Next.js application that can consume this API. We’ll leverage the WPGraphQL plugin for a more structured and efficient GraphQL API, which is ideal for complex data fetching in modern JavaScript applications.

Prerequisites:

• A local or remote WordPress installation (version 5.6+ recommended).
• Node.js and npm/yarn installed on your development machine.
• Basic understanding of React and Next.js.

1. Install and Activate WPGraphQL:

Navigate to your WordPress admin dashboard, go to Plugins > Add New, search for “WPGraphQL”, install, and activate it. This plugin exposes a GraphQL endpoint, typically at /graphql.

2. Create a New Next.js Application:

Use Create Next App to scaffold a new project. We’ll use TypeScript for better type safety.

npx create-next-app@latest my-db-optimizer-portal --typescript
cd my-db-optimizer-portal

3. Install GraphQL Client (Apollo Client):

Apollo Client is a powerful and flexible GraphQL client for React and JavaScript. Install it along with its necessary dependencies.

npm install @apollo/client graphql

4. Configure Apollo Client:

Create an Apollo Client instance and wrap your application with the ApolloProvider. This makes the GraphQL client available throughout your Next.js app.

Create a new file, e.g., lib/apolloClient.ts:

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

const GRAPHQL_ENDPOINT = process.env.NEXT_PUBLIC_WORDPRESS_GRAPHQL_ENDPOINT || 'http://your-wordpress-site.local/graphql'; // Replace with your WP GraphQL endpoint

export const apolloClient = new ApolloClient({
  link: new HttpLink({
    uri: GRAPHQL_ENDPOINT,
  }),
  cache: new InMemoryCache(),
});

Update your pages/_app.tsx to include the ApolloProvider:

import type { AppProps } from 'next/app';
import { ApolloProvider } from '@apollo/client';
import { apolloClient } from '../lib/apolloClient';
import '../styles/globals.css';

function MyApp({ Component, pageProps }: AppProps) {
  return (
    <ApolloProvider client={apolloClient}>
      <Component {...pageProps} />
    </ApolloProvider>
  );
}

export default MyApp;

Set your WordPress GraphQL endpoint in your .env.local file:

NEXT_PUBLIC_WORDPRESS_GRAPHQL_ENDPOINT=http://your-wordpress-site.local/graphql

Designing the Database Optimizer Data Model in WordPress

For a database optimizer portal, we need a way to store and retrieve information about database performance, query analysis, and optimization suggestions. We’ll use custom post types and custom fields managed by Advanced Custom Fields (ACF) for this. WPGraphQL will then expose these fields.

1. Create Custom Post Types:

We’ll create two custom post types: “Database Query” and “Optimization Suggestion”.

Add the following to your theme’s functions.php or a custom plugin:

function register_custom_post_types() {
    // Register Database Query post type
    $query_labels = array(
        'name'               => _x( 'Database Queries', 'post type general name', 'your-text-domain' ),
        'singular_name'      => _x( 'Database Query', 'post type singular name', 'your-text-domain' ),
        'menu_name'          => _x( 'Queries', 'admin menu', 'your-text-domain' ),
        'name_admin_bar'     => _x( 'Database Query', 'add new on admin bar', 'your-text-domain' ),
        'add_new'            => _x( 'Add New', 'query', 'your-text-domain' ),
        'add_new_item'       => __( 'Add New Database Query', 'your-text-domain' ),
        'edit_item'          => __( 'Edit Database Query', 'your-text-domain' ),
        'new_item'           => __( 'New Database Query', 'your-text-domain' ),
        'view_item'          => __( 'View Database Query', 'your-text-domain' ),
        'all_items'          => __( 'All Database Queries', 'your-text-domain' ),
        'search_items'       => __( 'Search Queries', 'your-text-domain' ),
        'parent_item_colon'  => __( 'Parent Queries:', 'your-text-domain' ),
        'not_found'          => __( 'No queries found.', 'your-text-domain' ),
        'not_found_in_trash' => __( 'No queries found in Trash.', 'your-text-domain' ),
    );
    $query_args = array(
        'labels'             => $query_labels,
        'public'             => true,
        'publicly_queryable' => true,
        'show_ui'            => true,
        'show_in_menu'       => true,
        'query_var'          => true,
        'rewrite'            => array( 'slug' => 'database-queries' ),
        'capability_type'    => 'post',
        'has_archive'        => true,
        'hierarchical'       => false,
        'menu_position'      => 20,
        'menu_icon'          => 'dashicons-database',
        'supports'           => array( 'title', 'editor', 'author' ),
        'show_in_rest'       => true, // Crucial for REST API and WPGraphQL
    );
    register_post_type( 'database_query', $query_args );

    // Register Optimization Suggestion post type
    $suggestion_labels = array(
        'name'               => _x( 'Optimization Suggestions', 'post type general name', 'your-text-domain' ),
        'singular_name'      => _x( 'Optimization Suggestion', 'post type singular name', 'your-text-domain' ),
        'menu_name'          => _x( 'Suggestions', 'admin menu', 'your-text-domain' ),
        'name_admin_bar'     => _x( 'Optimization Suggestion', 'add new on admin bar', 'your-text-domain' ),
        'add_new'            => _x( 'Add New', 'suggestion', 'your-text-domain' ),
        'add_new_item'       => __( 'Add New Optimization Suggestion', 'your-text-domain' ),
        'edit_item'          => __( 'Edit Optimization Suggestion', 'your-text-domain' ),
        'new_item'           => __( 'New Optimization Suggestion', 'your-text-domain' ),
        'view_item'          => __( 'View Optimization Suggestion', 'your-text-domain' ),
        'all_items'          => __( 'All Optimization Suggestions', 'your-text-domain' ),
        'search_items'       => __( 'Search Suggestions', 'your-text-domain' ),
        'parent_item_colon'  => __( 'Parent Suggestions:', 'your-text-domain' ),
        'not_found'          => __( 'No suggestions found.', 'your-text-domain' ),
        'not_found_in_trash' => __( 'No suggestions found in Trash.', 'your-text-domain' ),
    );
    $suggestion_args = array(
        'labels'             => $suggestion_labels,
        'public'             => true,
        'publicly_queryable' => true,
        'show_ui'            => true,
        'show_in_menu'       => true,
        'query_var'          => true,
        'rewrite'            => array( 'slug' => 'optimization-suggestions' ),
        'capability_type'    => 'post',
        'has_archive'        => true,
        'hierarchical'       => false,
        'menu_position'      => 21,
        'menu_icon'          => 'dashicons-lightbulb',
        'supports'           => array( 'title', 'editor' ),
        'show_in_rest'       => true, // Crucial for REST API and WPGraphQL
    );
    register_post_type( 'optimization_suggestion', $suggestion_args );
}
add_action( 'init', 'register_custom_post_types' );

2. Install and Configure ACF:

Install the Advanced Custom Fields (ACF) plugin. Then, create field groups for your custom post types.

Field Group: “Query Details” for “Database Query” post type:

• Field Name: query_text, Field Type: Textarea, Location: Post Type is equal to Database Query
• Field Name: execution_time_ms, Field Type: Number, Location: Post Type is equal to Database Query
• Field Name: rows_examined, Field Type: Number, Location: Post Type is equal to Database Query
• Field Name: query_plan, Field Type: Textarea, Location: Post Type is equal to Database Query

Field Group: “Suggestion Details” for “Optimization Suggestion” post type:

• Field Name: suggestion_code, Field Type: Textarea, Location: Post Type is equal to Optimization Suggestion
• Field Name: impact_score, Field Type: Number, Location: Post Type is equal to Optimization Suggestion
• Field Name: related_query_id, Field Type: Post Object, Post Type: Database Query, Allow Null: Yes, Location: Post Type is equal to Optimization Suggestion

3. Expose ACF Fields via WPGraphQL:

WPGraphQL automatically registers ACF fields. To ensure they are discoverable and queryable, you might need to explicitly register them if they aren’t showing up in your GraphQL schema explorer. Add this to your functions.php:

add_filter( 'graphql_register_fields', function() {
    // Register fields for Database Query
    graphql_register_field( 'databaseQuery', 'queryText', [
        'type' => 'String',
        'description' => __( 'The SQL query text.', 'your-text-domain' ),
        'resolve' => function( $post ) {
            return get_field( 'query_text', $post->ID );
        },
    ] );
    graphql_register_field( 'databaseQuery', 'executionTimeMs', [
        'type' => 'Float',
        'description' => __( 'Execution time in milliseconds.', 'your-text-domain' ),
        'resolve' => function( $post ) {
            return get_field( 'execution_time_ms', $post->ID );
        },
    ] );
    graphql_register_field( 'databaseQuery', 'rowsExamined', [
        'type' => 'Int',
        'description' => __( 'Number of rows examined.', 'your-text-domain' ),
        'resolve' => function( $post ) {
            return get_field( 'rows_examined', $post->ID );
        },
    ] );
    graphql_register_field( 'databaseQuery', 'queryPlan', [
        'type' => 'String',
        'description' => __( 'The query execution plan.', 'your-text-domain' ),
        'resolve' => function( $post ) {
            return get_field( 'query_plan', $post->ID );
        },
    ] );

    // Register fields for Optimization Suggestion
    graphql_register_field( 'optimizationSuggestion', 'suggestionCode', [
        'type' => 'String',
        'description' => __( 'The optimization code snippet.', 'your-text-domain' ),
        'resolve' => function( $post ) {
            return get_field( 'suggestion_code', $post->ID );
        },
    ] );
    graphql_register_field( 'optimizationSuggestion', 'impactScore', [
        'type' => 'Int',
        'description' => __( 'Impact score of the suggestion.', 'your-text-domain' ),
        'resolve' => function( $post ) {
            return get_field( 'impact_score', $post->ID );
        },
    ] );
    graphql_register_field( 'optimizationSuggestion', 'relatedQuery', [
        'type' => 'DatabaseQuery', // Assuming 'DatabaseQuery' is the WPGraphQL type name for your post type
        'description' => __( 'The related database query.', 'your-text-domain' ),
        'resolve' => function( $post ) {
            $related_query_id = get_field( 'related_query_id', $post->ID );
            if ( $related_query_id ) {
                // Fetch the related post object
                $related_post = get_post( $related_query_id );
                if ( $related_post && $related_post->post_type === 'database_query' ) {
                    return $related_post;
                }
            }
            return null;
        },
    ] );
} );

// Ensure WPGraphQL recognizes custom post types if not automatically picked up
add_filter( 'graphql_return_post_object', function( $post_object, $post, $context ) {
    if ( $post->post_type === 'database_query' ) {
        $post_object->databaseQuery = $post; // Map to the GraphQL type name
    }
    if ( $post->post_type === 'optimization_suggestion' ) {
        $post_object->optimizationSuggestion = $post; // Map to the GraphQL type name
    }
    return $post_object;
}, 10, 3 );

// Define the GraphQL types for custom post types if needed
add_filter( 'graphql_post_object_type_fields', function( $fields, $post_type_object, $context ) {
    if ( $post_type_object->name === 'database_query' ) {
        $fields['databaseQuery'] = [
            'type' => 'DatabaseQuery', // This should match the type name used in 'graphql_register_field' and 'graphql_return_post_object'
            'description' => __( 'Database Query details', 'your-text-domain' ),
            'resolve' => function( $post ) {
                return $post; // Return the post object itself to be resolved by other fields
            },
        ];
    }
    if ( $post_type_object->name === 'optimization_suggestion' ) {
        $fields['optimizationSuggestion'] = [
            'type' => 'OptimizationSuggestion',
            'description' => __( 'Optimization Suggestion details', 'your-text-domain' ),
            'resolve' => function( $post ) {
                return $post;
            },
        ];
    }
    return $fields;
}, 10, 3 );

Note: The exact GraphQL type names (e.g., DatabaseQuery, OptimizationSuggestion) might need to be inferred from your WPGraphQL schema or explicitly defined. You can inspect your schema using tools like GraphiQL or GraphQL Playground, often available at your /graphql endpoint.

Developing the Custom Gutenberg Block with Next.js

Now, let’s build the Gutenberg block. This block will fetch data from our WordPress backend using Next.js and display it in a user-friendly interface. We’ll use the WordPress `@wordpress/create-block` tool to scaffold the block structure.

1. Create the Gutenberg Block Scaffold:

Navigate to your WordPress theme’s directory or a custom plugin directory and run:

npx @wordpress/create-block db-optimizer-block
cd db-optimizer-block

This creates a new block plugin. We’ll modify the src/edit.js and src/save.js files. For dynamic content fetched via an API, the save.js should typically render a placeholder or static representation, as the dynamic data will be fetched client-side in the frontend.

2. Implement the Block’s Editor View (src/edit.js):

In the editor, we’ll use a placeholder and potentially a loading indicator. The actual data fetching will happen on the frontend. For the editor, we can simulate data or show a message indicating dynamic content.

/**
 * Retrieves the translation of text.
 *
 * @see https://developer.wordpress.org/block-editor/reference-guides/packages/packages-i18n/
 */
import { __ } from '@wordpress/i18n';

/**
 * React component to display the block.
 *
 * @see https://developer.wordpress.org/block-editor/reference-guides/packages/packages-block-editor/
 */
import { useBlockProps, InspectorControls } from '@wordpress/block-editor';
import { PanelBody, Placeholder, Spinner } from '@wordpress/components';
import { useState, useEffect } from '@wordpress/element';
import apiFetch from '@wordpress/api-fetch';

/**
 * Lets webpack process CSS, included for styling the editor.
 *
 */
import './editor.scss';

/**
 * The edit function describes the structure of your block in the context of the
 * editor. This represents what the editor will render when the block is used.
 *
 * @see https://developer.wordpress.org/block-editor/reference-guides/block-api/block-edit-save/#edit
 *
 * @return {Element} Block edit component.
 */
export default function Edit() {
	const blockProps = useBlockProps();
	const [ isLoading, setIsLoading ] = useState( true );
	const [ data, setData ] = useState( null );
	const [ error, setError ] = useState( null );

	// Fetch data from WordPress REST API (or GraphQL endpoint if configured for editor)
	// For simplicity, we'll use REST API here. For complex data, GraphQL is better.
	useEffect( () => {
		const fetchEditorData = async () => {
			setIsLoading( true );
			try {
				// Example: Fetching the latest 5 database queries
				const queries = await apiFetch( {
					path: '/wp/v2/database_query?per_page=5&_embed',
				} );
				setData( queries );
				setError( null );
			} catch ( err ) {
				setError( err );
				setData( null );
			} finally {
				setIsLoading( false );
			}
		};
		fetchEditorData();
	}, [] );

	return (
		<div { ...blockProps }>
			{ isLoading && <Spinner /> }
			{ ! isLoading && error && <p>Error loading data: { error.message }</p> }
			{ ! isLoading && ! error && data && data.length > 0 ? (
				<Placeholder
					icon="database"
					label={ __( 'Database Optimizer Queries', 'db-optimizer-block' ) }
					instructions={ __( 'Displaying the latest database queries.', 'db-optimizer-block' ) }
				>
					<ul>
						{ data.map( ( query ) => (
							<li key={ query.id }>
								<strong>{ query.title.rendered }</strong>
								<p>Execution Time: { query.execution_time_ms_raw }ms</p>
							</li>
						) ) }
					</ul>
				</Placeholder>
			) : (
				! isLoading && ! error && <Placeholder
					icon="database"
					label={ __( 'Database Optimizer Queries', 'db-optimizer-block' ) }
					instructions={ __( 'No database queries found or data could not be loaded.', 'db-optimizer-block' ) }
				/>
			) }
		</div>
	);
}

Note: The example above uses the WordPress REST API for simplicity in the editor. For a true headless setup, you’d ideally configure WPGraphQL to be accessible from the editor or rely on the frontend rendering. The execution_time_ms_raw field assumes it’s exposed by the REST API; you might need to register custom fields for the REST API using register_rest_field if they aren’t automatically available.

3. Implement the Block’s Save Function (src/save.js):

Since the data is dynamic and fetched client-side, the save function should render a static placeholder. The actual content will be rendered by the Next.js application.

/**
 * React component to display the block.
 *
 * @see https://developer.wordpress.org/block-editor/reference-guides/packages/packages-block-editor/
 */
import { useBlockProps } from '@wordpress/block-editor';

/**
 * Lets webpack process CSS, included for styling the content.
 *
 */
import './style.scss';

/**
 * The save function defines the way in which the different attributes of the
 * block are combined into the final markup, which is then serialized by the
 * block editor into the database. A save function must return a valid element.
 *
 * @see https://developer.wordpress.org/block-editor/reference-guides/block-api/block-edit-save/#save
 *
 * @return {Element} Block save component.
 */
export default function save() {
	const blockProps = useBlockProps.save();

	return (
		<div { ...blockProps }>
			<p>{ __( 'Database Optimizer Data (Loading...)', 'db-optimizer-block' ) }</p>
			{ /* This content will be replaced by Next.js frontend rendering */ }
		</div>
	);
}

4. Build and Activate the Block Plugin:

Navigate back to your block plugin’s directory and run:

npm run build

This compiles the block assets. Now, go to your WordPress admin, activate the “DB Optimizer Block” plugin. You should be able to add this block to your posts or pages.

Integrating the Block with Next.js Frontend

The core idea of a headless setup is that WordPress manages content, and Next.js renders it. Our custom block, when placed on a WordPress page, acts as a signal for Next.js to fetch and render specific data. We’ll create a page in Next.js that queries for content and conditionally renders our “optimizer portal” component.

1. Define GraphQL Queries:

Create a file for your GraphQL queries, e.g., graphql/queries.ts:

# Get Database Queries
query GetDatabaseQueries($first: Int) {
  databaseQueries(first: $first) {
    nodes {
      id
      title
      databaseQuery {
        queryText
        executionTimeMs
        rowsExamined
        queryPlan
      }
    }
  }
}

# Get Optimization Suggestions
query GetOptimizationSuggestions($first: Int) {
  optimizationSuggestions(first: $first) {
    nodes {
      id
      title
      optimizationSuggestion {
        suggestionCode
        impactScore
        relatedQuery {
          id
          title
          databaseQuery {
            queryText
          }
        }
      }
    }
  }
}

2. Create the Optimizer Portal Component:

Create a new React component, e.g., components/OptimizerPortal.tsx:

import React from 'react';
import { gql, useQuery } from '@apollo/client';
import styles from '../styles/Optimizer.module.css'; // Assuming you'll create this CSS module

// GraphQL Queries (defined above)
const GET_DATABASE_QUERIES = gql`
  query GetDatabaseQueries($first: Int) {
    databaseQueries(first: $first) {
      nodes {
        id
        title
        databaseQuery {
          queryText
          executionTimeMs
          rowsExamined
          queryPlan
        }
      }
    }
  }
`;

const GET_OPTIMIZATION_SUGGESTIONS = gql`
  query GetOptimizationSuggestions($first: Int) {
    optimizationSuggestions(first: $first) {
      nodes {
        id
        title
        optimizationSuggestion {
          suggestionCode
          impactScore
          relatedQuery {
            id
            title
            databaseQuery {
              queryText
            }
          }
        }
      }
    }
  }
`;

interface QueryNode {
  id: string;
  title: string;
  databaseQuery: {
    queryText: string;
    executionTimeMs: number | null;
    rowsExamined: number | null;
    queryPlan: string | null;
  };
}

interface SuggestionNode {
  id: string;
  title: string;
  optimizationSuggestion: {
    suggestionCode: string | null;
    impactScore: number | null;
    relatedQuery: {
      id: string;
      title: string;
      databaseQuery: {
        queryText: string;
      } | null;
    } | null;
  };
}

interface OptimizerPortalProps {
  queryLimit?: number;
  suggestionLimit?: number;
}

const OptimizerPortal: React.FC<OptimizerPortalProps> = ({ queryLimit = 5, suggestionLimit = 5 }) => {
  const {
    loading: queriesLoading,
    error: queriesError,
    data: queriesData,
  } = useQuery<{ databaseQueries: { nodes: QueryNode[] } }>(GET_DATABASE_QUERIES, {
    variables: { first: queryLimit },
  });

  const {
    loading: suggestionsLoading,
    error: suggestionsError,
    data: suggestionsData,
  } = useQuery<{ optimizationSuggestions: { nodes: SuggestionNode[] } }>(GET_OPTIMIZATION_SUGGESTIONS, {
    variables: { first: suggestionLimit },
  });

  if (queriesLoading || suggestionsLoading) return <div className={styles.loading}>Loading optimizer data...</div>;
  if (queriesError) return <div className={styles.error}>Error loading queries: {queriesError.message}</div>;
  if (suggestionsError) return <div className={styles.error}>Error loading suggestions: {suggestionsError.message}</div>;

  const queries = queriesData?.databaseQueries?.nodes || [];
  const suggestions = suggestionsData?.optimizationSuggestions?.nodes || [];

  return (
    <div className={styles.container}>
      <h2>Database Performance Insights</h2>

      <section className={styles.section}>
        <h3>Top {queryLimit} Slowest Queries</h3>
        {queries.length > 0 ? (
          <ul className={styles.list}>
            {queries.map((query) => (
              <li key={query.id} className={styles.listItem}>
                <h4>{query.title}</h4>
                <p><strong>Execution Time:</strong> {query.databaseQuery.executionTimeMs ?? 'N/A'} ms</p>
                <p><strong>Rows Examined:</strong> {query.databaseQuery.rowsExamined ?? 'N/A'}</p>
                {/* Optionally display query plan or text */}
              </li>
            ))}
          </ul>
        ) : (
          <p>No database queries found.</p>
        )}
      </section>

      <section className={styles.section}>
        <h3>Top {suggestionLimit} Optimization Suggestions</h3>
        {suggestions.length > 0 ? (
          <ul className={styles.list}>
            {suggestions.map((suggestion) => (
              <li key={suggestion.id} className={styles.listItem}>
                <h4>{suggestion.title}</h4>
                <p><strong>Impact Score:</strong> {suggestion.optimizationSuggestion.impactScore ?? 'N/A'}</p>
                {suggestion.optimizationSuggestion.relatedQuery && (
                  <p><strong>Related Query:</strong> {suggestion.optimizationSuggestion.relatedQuery.title}</p>
                )}
                <pre className={styles.codeBlock}>{suggestion.optimizationSuggestion.