', end_tag => '') AS code_highlight
FROM snippets
WHERE code @@@ 'pool';
```
This feature is particularly valuable in a code snippet manager as it allows developers to quickly see where their search terms appear in potentially lengthy code blocks.
## Building the Search API and Frontend
Now that we've set up the `pg_search` indexes and the backend database, let's integrate the search functionality into a simple API and frontend. This section walks you through setting up a Node.js API to handle search queries and a React-based frontend to display the results.
Start by creating a new directory for your project:
```bash
mkdir snippet-search
cd snippet-search
```
Then you are ready to create the backend API and frontend application.
### 1. Setting Up the API
We'll create a simple Node.js API to handle incoming search requests. The API will query the Neon Postgres database using the `pg_search` extension and return the results to the frontend.
#### Install Required Dependencies
First, we need a few packages to set up the backend. These include Express for the server, pg for interacting with the Postgres database, and `dotenv` for managing environment variables like your Neon database connection string.
```bash
mkdir snippet-search-api
cd snippet-search-api
npm init -y
npm install express pg dotenv cors
```
#### Create the Database Connection
We'll start by setting up the database connection in a separate file (`db.js`). This makes it easier to manage the connection and reuse it in other parts of the application.
```javascript
// db.js - Database connection
const { Pool } = require('pg');
require('dotenv').config();
const pool = new Pool({
connectionString: process.env.DATABASE_URL,
ssl: true,
});
module.exports = pool;
```
In this file, we are creating a connection pool to manage connections to the Neon database. Make sure your `DATABASE_URL` is stored in a `.env` file like so:
```
DATABASE_URL=postgres://[username]:[password]@[endpoint]/[dbname]
```
For more information on how to work with Neon and Node.js, check out the [Neon documentation](https://neon.com/docs/guides/node).
#### Search Logic
Next, we'll write the search logic in a `searchService.js` file. This function will handle querying the database, applying the search filters, and returning the relevant results.
```javascript
// searchService.js - Search functionality
const db = require('./db');
async function searchSnippets({ query, language, tag, page = 1, limit = 10 }) {
const offset = (page - 1) * limit;
let searchQuery = `
SELECT
s.snippet_id,
s.title,
s.description,
l.name AS language,
u.username AS created_by,
paradedb.snippet(s.code) AS code_highlight,
paradedb.score(s.snippet_id) AS relevance
FROM snippets s
JOIN languages l ON s.language_id = l.language_id
JOIN users u ON s.user_id = u.user_id
`;
let whereConditions = [];
let queryParams = [];
let paramIndex = 1;
if (query) {
whereConditions.push(
`(s.title @@@ $${paramIndex} OR s.description @@@ $${paramIndex} OR s.code @@@ $${paramIndex})`
);
queryParams.push(query);
paramIndex++;
}
if (language) {
whereConditions.push(`l.name = $${paramIndex}`);
queryParams.push(language);
paramIndex++;
}
if (tag) {
searchQuery += ` JOIN snippet_tags st ON s.snippet_id = st.snippet_id JOIN tags t ON st.tag_id = t.tag_id`;
whereConditions.push(`t.name = $${paramIndex}`);
queryParams.push(tag);
paramIndex++;
}
if (whereConditions.length > 0) {
searchQuery += ` WHERE ${whereConditions.join(' AND ')}`;
}
searchQuery += ` ORDER BY relevance DESC LIMIT $${paramIndex} OFFSET $${paramIndex + 1}`;
queryParams.push(limit, offset);
const results = await db.query(searchQuery, queryParams);
let countQuery = `
SELECT COUNT(*)
FROM snippets s
JOIN languages l ON s.language_id = l.language_id
`;
if (tag) {
countQuery += ` JOIN snippet_tags st ON s.snippet_id = st.snippet_id JOIN tags t ON st.tag_id = t.tag_id`;
}
if (whereConditions.length > 0) {
countQuery += ` WHERE ${whereConditions.join(' AND ')}`;
}
const countResult = await db.query(countQuery, queryParams.slice(0, -2));
const total = parseInt(countResult.rows[0].count);
const snippetIds = results.rows.map((row) => row.snippet_id);
if (snippetIds.length > 0) {
const tagsQuery = `
SELECT st.snippet_id, array_agg(t.name) as tags
FROM snippet_tags st
JOIN tags t ON st.tag_id = t.tag_id
WHERE st.snippet_id = ANY($1)
GROUP BY st.snippet_id
`;
const tagsResult = await db.query(tagsQuery, [snippetIds]);
const snippetTags = {};
tagsResult.rows.forEach((row) => {
snippetTags[row.snippet_id] = row.tags;
});
results.rows.forEach((row) => {
row.tags = snippetTags[row.snippet_id] || [];
});
}
return {
results: results.rows,
pagination: {
total,
page: parseInt(page),
limit: parseInt(limit),
totalPages: Math.ceil(total / limit),
},
};
}
module.exports = {
searchSnippets,
};
```
This function receives search parameters and constructs a SQL query with the appropriate filters. It also handles pagination by calculating `limit` and `offset` values, so only a subset of results is returned at a time.
#### Set Up the Express Server
Now, let's set up the Express server in a `server.js` file. This file will expose a simple `/api/search` endpoint that accepts GET requests with query parameters, handles them using the `searchSnippets` function, and returns the search results.
```javascript
// server.js - Express server
const express = require('express');
const cors = require('cors');
const { searchSnippets } = require('./searchService');
const app = express();
const port = process.env.PORT || 3000;
app.use(cors());
app.use(express.json());
app.get('/api/search', async (req, res) => {
try {
const { query, language, tag, page, limit } = req.query;
if (!query && !language && !tag) {
return res.status(400).json({ error: 'At least one search parameter is required' });
}
const result = await searchSnippets({
query,
language,
tag,
page: parseInt(page || 1),
limit: parseInt(limit || 10),
});
res.json(result);
} catch (error) {
console.error('Search error:', error);
res.status(500).json({ error: 'An error occurred while searching' });
}
});
app.listen(port, () => {
console.log(`Server running on port ${port}`);
});
```
This server listens for search requests on the `/api/search` endpoint. It supports query parameters like `query`, `language`, and `tag`, and it passes these to the `searchSnippets` service to fetch and return the results.
### 2. Frontend: React Search Interface
Now that we have the API set up, let's create a simple frontend in React to allow users to perform searches.
#### Set Up the React App
Create a new React app and install the necessary dependencies:
```bash
npx create-react-app snippet-search-ui
cd snippet-search-ui
npm install axios highlight.js
```
#### Create the API Service
In the `src/services/api.js` file, we'll set up an Axios service to make requests to the backend API.
```javascript
// src/services/api.js
import axios from 'axios';
const API_URL = 'http://localhost:3000/api';
// Function to search snippets
export const searchSnippets = async (params) => {
const response = await axios.get(`${API_URL}/search`, { params });
return response.data;
};
export const getLanguages = async () => {
// Mock API call to fetch languages
return [
{ id: 1, name: 'JavaScript' },
{ id: 2, name: 'Python' },
{ id: 3, name: 'Go' },
{ id: 4, name: 'SQL' },
{ id: 5, name: 'TypeScript' },
];
};
export const getTags = async () => {
// Mock API call to fetch tags
return [
{ id: 1, name: 'algorithm' },
{ id: 2, name: 'utility' },
{ id: 3, name: 'frontend' },
{ id: 4, name: 'database' },
{ id: 5, name: 'middleware' },
];
};
```
#### Build the Search Form Component
We need a form component where users can enter a search query, select a language, or filter by tags. The form will trigger the search when submitted.
```jsx
// src/components/SearchForm.js
import React, { useState, useEffect } from 'react';
import { getLanguages, getTags } from '../services/api';
const SearchForm = ({ onSearch }) => {
const [query, setQuery] = useState('');
const [language, setLanguage] = useState('');
const [tag, setTag] = useState('');
const [languages, setLanguages] = useState([]);
const [tags, setTags] = useState([]);
useEffect(() => {
const loadFilters = async () => {
const languagesData = await getLanguages();
const tagsData = await getTags();
setLanguages(languagesData);
setTags(tagsData);
};
loadFilters();
}, []);
const handleSubmit = (e) => {
e.preventDefault();
onSearch({ query, language, tag });
};
return (
);
};
export default SearchForm;
```
#### Code Snippet Component
Let's now create a component that displays a single code snippet:
```jsx
// src/components/CodeSnippet.js
import React from 'react';
const CodeSnippet = ({ snippet }) => {
return (
{snippet.code}
{snippet.description}
Loading...
}