From Postgres to Production API: A Step-by-Step Integration Guide

You have valuable data locked away in your PostgreSQL database. The business needs access to it. Your front-end application needs to fetch it. Your partners want to query it. The classic solution? Build an API.

But as any developer knows, turning a simple SELECT statement into a secure, scalable, and maintainable production API is anything but simple. It involves boilerplate code, setting up servers, managing authentication, implementing rate limiting, and handling logging. What if you could skip all that and focus only on the query itself?

Welcome to the world of Business-as-Code. With Searches.do, you can transform your complex data queries into robust, reusable API services in minutes. Let's walk through how to take a standard Postgres query and deploy it as a production-ready Data Retrieval API.

The Challenge: More Than Just a Query

Let's say we want to retrieve a customer's details from our Postgres database using their email address. The SQL is straightforward:

SELECT
  customer_id,
  first_name,
  last_name,
  email,
  account_status,
  created_at
FROM
  customers
WHERE
  email = 'jane.doe@example.com';

Easy, right? But to expose this as a production endpoint, you need to build an entire service around it. This infrastructure tax includes:

• An HTTP server (e.g., Express.js, Fastify).
• An API gateway for security and traffic management.
• Authentication and authorization logic.
• Input validation to prevent SQL injection.
• Scalable hosting and load balancing.
• Consistent logging and monitoring.

This is a lot of work for one simple lookup. Now, multiply that by the dozens of different data retrieval needs across your organization. The complexity quickly balloons.

The Solution: Searches.do - Your API as a Service

Searches.do is an agentic workflow platform that abstracts away the infrastructure. You define your data retrieval logic—your business rule—as a simple "search agent" using TypeScript. The platform handles the rest, instantly deploying it as a secure, scalable API as a Service.

Here’s how you can turn that Postgres query into a live API endpoint step-by-step.

Step 1: Define Your Search Agent

First, we define the structure of our search agent using the Searches.do TypeScript SDK. We'll name our search find-customer-by-email and specify that it requires one input parameter: email.

// ./searches/find-customer-by-email.ts
import { defineSearch } from '@searches.do/sdk';
import { Pool } from 'pg';

// Initialize your Postgres connection pool
// Best practice: Use environment variables for credentials
const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
});

export default defineSearch({
  // The unique ID for this search agent
  searchId: 'find-customer-by-email',

  // Define the expected input parameters and their types
  input: {
    email: {
      type: 'string',
      required: true,
    },
  },

  // The handler contains your core data retrieval logic
  handler: async ({ input }) => {
    const { email } = input;
    const query = `
      SELECT
        "customerId", "firstName", "lastName", "email", "accountStatus", "createdAt"
      FROM
        customers
      WHERE
        email = $1;
    `;

    try {
      const { rows } = await pool.query(query, [email]);

      if (rows.length === 0) {
        return {
          status: 'not_found',
          message: `No customer found with email: ${email}`,
        };
      }

      // Return the successfully retrieved data
      return {
        status: 'success',
        result: rows[0],
      };
    } catch (error) {
      console.error('Database query failed:', error);
      return {
        status: 'error',
        message: 'An internal error occurred while fetching customer data.',
      };
    }
  },
});

In this file, we've accomplished three key things:

1. Defined the Contract: We specified that anyone calling this search must provide an email string.
2. Encapsulated Logic: We wrote the exact business logic for connecting to Postgres and fetching the data.
3. Handled Outcomes: We return a structured success or not_found response, creating a predictable and reliable service.

Step 2: Deploy Your Agent

With Searches.do, deployment is the easy part. Once your code is committed, the platform automatically picks it up, packages it, and deploys it behind a secure API gateway. There are no servers to provision or Dockerfiles to write. You've just created a piece of Business-as-Code—a tangible, executable asset that represents a business capability.

Step 3: Call Your New Production API

Your data retrieval logic is now live and accessible via a standard HTTP POST request. You can call it from any application, service, or tool that can speak HTTP.

Here's how you'd call it using curl:

curl -X POST https://api.searches.do/v1/search \
  -H "Authorization: Bearer YOUR_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{
    "searchId": "find-customer-by-email",
    "parameters": {
      "email": "jane.doe@example.com"
    }
  }'

And the response you get back is clean, predictable, and fast:

{
  "status": "success",
  "query": {
    "searchId": "find-customer-by-email",
    "parameters": {
      "email": "jane.doe@example.com"
    }
  },
  "result": {
    "customerId": "cust_1A2b3C4d5E6f",
    "firstName": "Jane",
    "lastName": "Doe",
    "email": "jane.doe@example.com",
    "accountStatus": "active",
    "createdAt": "2023-10-26T10:00:00Z"
  },
  "executionTimeMs": 78
}

That’s it. You went from a SQL query to a fully managed, secure, and production-ready Headless Search API in just a few minutes.

The Power of Agentic Search: Beyond Simple Lookups

The real power emerges when your data retrieval needs grow more complex. Because you're writing the logic in TypeScript, a search agent can do more than just query a single table. You can:

• Aggregate Data: Fetch a user from Postgres, their recent orders from a different microservice, and their support ticket status from Zendesk—all within a single agent—and return one unified response.
• Implement Complex Rules: Add conditional logic, data transformation, and calculations to the handler function.
• Chain Agents: One search agent can call another, allowing you to build sophisticated, composable data workflows.

This is Agentic Search: small, intelligent agents that are experts at one specific data retrieval task, ready to be composed into more powerful services.

Get Started with Searches.do Today

Stop building boilerplate infrastructure and start defining your business logic as code. By converting essential data lookups into standardized APIs, you create a library of reusable, secure, and scalable services that can power your entire organization.

Ready to turn your data queries into APIs? Visit Searches.do to learn more and deploy your first search agent for free.