### cURL Example for Creating Chatbot Session with Dynamic Database Source: https://www.askyourdatabase.com/docs/chatbot-db-switching Demonstrates how to use `curl` to send a `POST` request to the `/api/chatbot/v2/session` endpoint. This example shows how to include a `databaseConfig` for a PostgreSQL connection, enabling the chatbot to connect to a tenant-specific database. Authorization requires an `API_KEY` in the `Authorization` header. ```Shell curl --location 'https://www.askyourdatabase.com/api/chatbot/v2/session' \ --header 'Content-Type: application/json' \ --header 'Authorization: Bearer ${API_KEY}' \ --data-raw '{ "chatbotid": "5da7b8cf3a372f5e6e6b64af9ae189c7", "name": "Tenant A User", "email": "user@tenant-a.com", "databaseConfig": { "connectionString": "postgres://tenant_a:secret@db.tenant-a.com:5432/data" } }' ``` -------------------------------- ### SQL Server ADO.NET Connection String Example Source: https://www.askyourdatabase.com/docs/connect Presents an example of an ADO.NET format connection string for SQL Server. It includes parameters for data source, initial catalog, user ID, and password, which are essential for connecting from .NET applications. Refer to Microsoft's official documentation for more details on this format. ```Configuration Data Source=host;Initial Catalog=database;User ID=user;Password=password; ``` -------------------------------- ### PostgreSQL Database Connection String Example Source: https://www.askyourdatabase.com/docs/connect Illustrates the standard connection string format for PostgreSQL databases, specifying placeholders for user, password, host, port, and database name. This URI-like format is typical for connecting to PostgreSQL instances. ```URI postgresql://user:password@host:port/database ``` -------------------------------- ### MySQL Database Connection String Example Source: https://www.askyourdatabase.com/docs/connect Provides the standard connection string format for MySQL databases, including placeholders for user, password, host, port, and database name. This format is commonly used for establishing connections to MySQL servers. ```URI mysql://user:password@host:port/database ``` -------------------------------- ### Vertica Database Connection String Example Source: https://www.askyourdatabase.com/docs/connect Provides the standard connection string format for Vertica databases, with placeholders for user, password, host, port, and database name. This URI-like format is used to establish connections to Vertica database instances. ```URI vertica://user:password@host:port/database ``` -------------------------------- ### MongoDB Database Connection String Example Source: https://www.askyourdatabase.com/docs/connect Shows the standard connection string format for MongoDB databases, including placeholders for user, password, host, port, and database name. This format is used to connect to MongoDB instances, often in a URI style. ```URI mongodb://user:password@host:port/database ``` -------------------------------- ### cURL Example to Ban a Chatbot User Source: https://www.askyourdatabase.com/docs/ban-chatbot-user Provides a cURL command example demonstrating how to ban a user from the chatbot, including setting the required headers and a custom message in the raw JSON data. ```curl curl --location 'https://www.askyourdatabase.com/api/chatbot/v2/ban' \ --header 'Content-Type: application/json' \ --header 'Authorization: Bearer ${API_KEY}' \ --data-raw '{ "chatbotid": "${botid}", "email": "user@example.com", "action": "ban", "prompt": "You have used up all your free credits. Please upgrade your account to continue." }' ``` -------------------------------- ### cURL Example to Unban a Chatbot User Source: https://www.askyourdatabase.com/docs/ban-chatbot-user Provides a cURL command example demonstrating how to unban a user from the chatbot, including setting the required headers and specifying the 'unban' action in the raw JSON data. ```curl curl --location 'https://www.askyourdatabase.com/api/chatbot/v2/ban' \ --header 'Content-Type: application/json' \ --header 'Authorization: Bearer ${API_KEY}' \ --data-raw '{ "chatbotid": "${botid}", "email": "user@example.com", "action": "unban" }' ``` -------------------------------- ### Example Context Variables JSON Structure Source: https://www.askyourdatabase.com/docs/access-control This JSON object illustrates the structure of context variables (name, email, and userId) that are passed to the API. These variables are then accessible within row-level policies to filter data based on user identity. ```json { "name": "John", "email": "johndoe@gmail.com", "userId": 17 } ``` -------------------------------- ### Session Creation API Response Example Source: https://www.askyourdatabase.com/docs/dashboard-embed This JSON object illustrates the successful response from the session creation API. It provides a `url` property, which is a single-use, time-limited URL that is essential for embedding the dashboard widget securely. ```json { "url": "https://www.askyourdatabase.com/api/widget/auth/callback?code=abcdefg" } ``` -------------------------------- ### Add Abbreviation Explanation to Database Schema Source: https://www.askyourdatabase.com/docs/tips This command demonstrates how to add a descriptive comment to an ambiguous column name like 'WS_MAPPING', explaining its full meaning as 'Workstation Mapping'. This clarifies implicit meanings for better SQL generation by ChatGPT. ```AskYourDatabase Command Add comment to WS_MAPPING column: "WS_MAPPING" means "Workstation Mapping" ``` -------------------------------- ### Handle Chat Webhook Event in Node.js Source: https://www.askyourdatabase.com/docs/webhook A simple Node.js Express example demonstrating how to receive and process a webhook POST request. It specifically handles the 'chat' event, extracts payload details, and sends a 200 OK response to acknowledge receipt. ```javascript app.post('/webhook', async (req, res) => { const { event, payload } = req.body; if (event === 'chat') { const { email, chatbotId, question, name } = payload; // Process the chat event await updateUserChatCount(email); // Acknowledge receipt res.status(200).json({ received: true }); } }); ``` -------------------------------- ### API Database Configuration Shapes for Dynamic Connections Source: https://www.askyourdatabase.com/docs/chatbot-db-switching Illustrates the specific JSON structures for the `databaseConfig` field, tailored for various database systems. These configurations enable dynamic connection to different underlying databases on a per-session basis, supporting MySQL, PostgreSQL, SQL Server, Vertica, Snowflake, SAP HANA, BigQuery, and Trino. All examples show minimal required fields. ```JSON { "connectionString": "mysql://user:pass@host:3306/dbname" } ``` ```JSON { "accountName": "my_account", "database": "MY_DB", "username": "USER", "password": "********", "schema": "PUBLIC", "warehouse": "COMPUTE_WH", // optional "role": "ACCOUNTADMIN" // optional } ``` ```JSON { "serverNode": "hanahost:39015", "uid": "USER", "pwd": "********", "databaseName": "MY_DB", "currentSchema": "PUBLIC", "encrypt": "true", // optional "sslValidateCertificate": "false" // optional } ``` ```JSON { "projectId": "my-gcp-project", "credentials": "{...service-account-json...}" } ``` ```JSON { "server": "https://trino.example.com:8443", "catalog": "hive", "schema": "default", "username": "USER", "password": "********" } ``` -------------------------------- ### Example of Incorrect SQL Query for Order Status Source: https://www.askyourdatabase.com/docs/tips This code snippet demonstrates an incorrect SQL query generated by ChatGPT when it misinterprets the meaning of 'not paid' instead of understanding specific enum values like 'pending' for order status. This highlights the importance of adding schema comments for clarity. ```SQL SELECT COUNT(*) FROM orders WHERE status == 'not paid' ``` -------------------------------- ### Plain HTML and JavaScript for Widget Embedding Source: https://www.askyourdatabase.com/docs/dashboard-embed This example demonstrates how to embed the AskYourDatabase widget using standard HTML and JavaScript. It fetches the session URL from a backend endpoint (assumed to be `/api/widget-session`) and then dynamically creates and appends an iframe to a specified container element. ```HTML
``` -------------------------------- ### Customize Chatbot Message and Avatar Styles Source: https://www.askyourdatabase.com/docs/chatbot-custom-style This CSS example demonstrates how to apply custom styling to the chatbot's user and assistant messages, as well as the assistant's avatar. It includes properties for background, box-shadow, border-radius, transitions, and hover effects, and sets a maximum width for message containers to improve readability. Specifically, it changes the user message background to red and applies a gradient to assistant messages. ```CSS /* Enhanced 3D and modern styling for chat messages */ /* User message style */ .chatbot-user-message .chat-message { background: red; box-shadow: 5px 5px 10px #d1d5db, -5px -5px 10px #ffffff; border-radius: 12px; transition: transform 0.2s ease; } .chatbot-user-message .chat-message:hover { transform: translateY(-10px); } /* Assistant message style */ .chatbot-assistant-message .chatbot-message-collapsible { background: linear-gradient(145deg, #e7f0ff, #ffffff); box-shadow: 5px 5px 10px #d1d5db, -5px -5px 10px #ffffff; border-radius: 12px; transition: all 2s ease; } .chatbot-assistant-message .chatbot-message-collapsible:hover { transform: translateY(-2px); } /* Avatar enhancement */ .chatbot-assistant-avatar img { border: 2px solid #e5e7eb; box-shadow: 0 4px 6px -1px rgba(0, 0, 0, 0.1); transition: transform 0.2s ease; } /* Max width for message container to improve readability */ .chat-message, .chatbot-message-collapsible { max-width: 85%; padding: 16px; line-height: 1.5; } ``` -------------------------------- ### Add Enum Value Comments to Database Schema Source: https://www.askyourdatabase.com/docs/tips This command shows how to add a descriptive comment to the 'status' column of the 'orders' table, specifying its possible enum values. This helps ChatGPT understand the column's meaning and generate more accurate SQL queries. ```AskYourDatabase Command Add comment to orders->status column: "pending", "paid", "shipped", "delivered", "cancelled" ``` -------------------------------- ### Pagila Database Connection String Source: https://www.askyourdatabase.com/docs/sample-db Provides a read-only PostgreSQL connection string for the Pagila sample database, which contains data for a fictional DVD rental store. This string can be used to connect AskYourDatabase to the Pagila dataset. ```PostgreSQL Connection String postgresql://readonly_role:>sU9y95R(e4m@ep-young-breeze-a5cq8xns.us-east-2.aws.neon.tech/pagila ``` -------------------------------- ### Netflix Database Connection String Source: https://www.askyourdatabase.com/docs/sample-db Provides a read-only PostgreSQL connection string for the Netflix sample database, which contains information about movies and TV shows. This string enables AskYourDatabase to connect to the Netflix dataset. ```PostgreSQL Connection String postgresql://readonly_role:>sU9y95R(e4m@ep-young-breeze-a5cq8xns.us-east-2.aws.neon.tech/netflix ``` -------------------------------- ### Chinook Database Connection String Source: https://www.askyourdatabase.com/docs/sample-db Provides a read-only PostgreSQL connection string for the Chinook sample database, which contains data for a digital media store. This string allows AskYourDatabase to access the Chinook dataset for testing. ```PostgreSQL Connection String postgresql://readonly_role:>sU9y95R(e4m@ep-young-breeze-a5cq8xns.us-east-2.aws.neon.tech/chinook ``` -------------------------------- ### API Request Body for Chatbot Session Creation Source: https://www.askyourdatabase.com/docs/chatbot-db-switching Defines the JSON structure for the `POST /api/chatbot/v2/session` request body. It includes essential fields like `chatbotid`, `name`, and `email`, along with the optional `databaseConfig` object. The `databaseConfig` field allows specifying dynamic connection details, overriding default settings. ```JSON { "chatbotid": "", "name": "Alice", "email": "alice@example.com", "databaseConfig": { /* DB-specific connection fields */ } } ``` -------------------------------- ### Create Customer Session URL with JavaScript Source: https://www.askyourdatabase.com/docs/access-control This JavaScript snippet demonstrates how to call the AskYourDatabase API to create a new customer session. It sends user details like name, email, and custom properties, then receives a chatbot session URL for embedding. ```javascript const { url } = await fetch("https://www.askyourdatabase.com/api/chatbot/session", { method: "POST", headers: { "Content-Type": "application/json" }, body: JSON.stringify({ secretKey: "${secretKey}", name: "John", email: "johndoe@gmail.com", properties: { userId: 17 } }) }).then((res) => res.json()); ``` -------------------------------- ### Create Chatbot Session with cURL Source: https://www.askyourdatabase.com/docs/chatbot-embed This cURL command demonstrates how to initiate a new authenticated session for the AskYourDatabase chatbot. It sends a POST request to the session API endpoint, including an API key for authorization and user details (name, email) along with the chatbotid in the request body. This call is typically made from your backend. ```bash curl --location 'https://www.askyourdatabase.com/api/chatbot/v2/session' \ --header 'Authorization: Bearer ${API_KEY}' \ --header 'Content-Type: application/json' \ --data-raw '{ "chatbotid": "${botid}", "name": "Sheldon", "email": "test@gmail.com" }' ``` -------------------------------- ### Create Next.js Backend API Route for Chatbot Session Source: https://www.askyourdatabase.com/docs/chatbot-embed This Next.js API route handles fetching a new chatbot session URL from the AskYourDatabase API. It makes a POST request to the session endpoint, authenticating with an API key, and returns the session URL to the frontend. This route is designed to be force-dynamic and revalidate on every request. ```TypeScript import { NextRequest, NextResponse } from "next/server"; export const revalidate = 0; export const dynamic = "force-dynamic"; export async function POST(req: NextRequest) { const { url } = await fetch("https://www.askyourdatabase.com/api/chatbot/v2/session", { method: "POST", headers: { "Content-Type": "application/json", "Authorization": "Bearer " + process.env.AYD_API_KEY }, body: JSON.stringify({ chatbotid: process.env.CHATBOT_ID, name: "Sheldon", email: "test@gmail.com" // ➜ (Optional) databaseConfig can be added here for tenant isolation. }) }).then((res) => res.json()); return NextResponse.json({ url }); } ``` -------------------------------- ### Create Session for Dashboard Widget Embedding (cURL) Source: https://www.askyourdatabase.com/docs/dashboard-embed This `curl` command demonstrates how to create a session from your backend by calling the AskYourDatabase widget session API. It requires an API key for authorization and includes the `widgetId`, user name, email, and optional custom properties in the request body. The API returns a single-use, time-limited URL for embedding the widget. ```shell curl --location 'https://www.askyourdatabase.com/api/widget/v2/session' \ --header 'Accept: application/json' \ --header 'Content-Type: application/json' \ --header 'Authorization: Bearer $API_KEY' \ --data-raw '{ "widgetId": "${widgetId}", "name": "User Name", "email": "user@example.com", "properties": { "customField": "customValue" } }' ``` -------------------------------- ### Next.js Backend API Route for Widget Session Creation Source: https://www.askyourdatabase.com/docs/dashboard-embed This TypeScript code defines a Next.js API route (`/api/widget-session`) that makes a POST request to the AskYourDatabase widget session API. It securely uses an `API_KEY` and returns the session URL to the client, preventing API key exposure on the frontend. ```TypeScript import { NextRequest, NextResponse } from "next/server"; export async function POST(req: NextRequest) { const response = await fetch("https://www.askyourdatabase.com/api/widget/v2/session", { method: "POST", headers: { "Content-Type": "application/json", "Authorization": "Bearer $API_KEY" }, body: JSON.stringify({ widgetId: "${widgetId}", name: "User Name", email: "user@example.com", properties: { // add any custom properties here } }) }); const data = await response.json(); return NextResponse.json({url: data.url}); } ``` -------------------------------- ### Integrate Chatbot Iframe in Next.js Frontend Component Source: https://www.askyourdatabase.com/docs/chatbot-embed This React component embeds the AskYourDatabase chatbot using an iframe. It manages the iframe's URL state, initially setting a default URL and then dynamically updating it based on messages received from the iframe, handling login success and required scenarios by fetching new sessions from the backend API. ```TypeScript "use client"; import { useEffect, useState } from "react"; export default function Home() { const [iframeUrl, setIframeUrl] = useState(""); const fetchAydSession = () => { fetch("/api/ayd", { method: "POST", headers: { "Content-Type": "application/json" }, body: JSON.stringify({}) }) .then((res) => res.json()) .then(({ url }) => { setIframeUrl(url); }); }; useEffect(() => { // Set initial iframe URL - this provides a default chatbot URL using the environment variable if (typeof window !== "undefined") { setIframeUrl(`https://www.askyourdatabase.com/chatbot/${process.env.CHATBOT_ID}`); } // Listen for messages from the iframe - this handles communication between the parent window and the embedded chatbot const handleMessage = (event: MessageEvent) => { if (event.data.type === 'LOGIN_SUCCESS') { // Login successful - update iframe URL with the authenticated session URL provided in the message setIframeUrl(event.data.url); } else if (event.data.type === 'LOGIN_REQUIRED') { // Login required - fetch a new authenticated session from our API endpoint fetchAydSession(); } }; if (typeof window !== "undefined") { window.addEventListener('message', handleMessage); } return () => { if (typeof window !== "undefined") { window.removeEventListener('message', handleMessage); } }; }, []); return (