SQL API

Introduction

FrontQL is a lightweight front-end querying language designed to streamline client-server communication for web applications. It simplifies data retrieval by allowing clients to send exactly what they need to the server. One of the key features of FrontQL is its SQL API, which enables clients to fetch, insert, update, and delete data from the server using SQL queries.

The SQL API of FrontQL is based on a powerful structured query language that is easy to understand. When a client makes a SQL request to a FrontQL-enabled server, it includes a query parameter that specifies the SQL query to be executed. The server then processes this query and executes the SQL query on the database.

By using the SQL API of FrontQL, developers can reduce the amount of data transferred over the network, minimize the need for multiple API endpoints, and ensure that applications are more performant and scalable.

Example

Here’s a simple example of a FrontQL SQL API request:

API
Fetch

async function getUsers() {
  const response = await Api.sql("/users", {
    body: {
      sql: "SELECT * FROM users",
    },
  });

  return response;
}

async function getUsers() {
  const url = `${BASE_URL}/sql-users`; // Replace BASE_URL with the base URL of API server
  const response = await fetch(url, {
    method: "POST",
    headers: {
      "Content-Type": "application/json",
      app: DATABASE, // Replace DATABASE with the name of your database
      token: TOKEN, // Replace TOKEN with the token
      // OR
      // Authorization: `Bearer ${AUTH_TOKEN}`, // Replace AUTH_TOKEN with the authorization token,
    },
    body: JSON.stringify({
      sql: "SELECT * FROM users",
    })
  })

  const data = await response.json();
  return data;
}

Output

This example fetches all users from the server and returns them in a JSON format.

{
  "err": false,
  "result": [
    {
      "id": 1,
      "name": "John Doe",
      "email": "[email protected]",
      "created_at": "2022-01-01 00:00:00.000",
      "updated_at": "2022-01-01 00:00:00.000"
    },
    {
      "id": 2,
      "name": "Jane Doe",
      "email": "[email protected]",
      "created_at": "2022-01-01 00:00:00.000",
      "updated_at": "2022-01-01 00:00:00.000"
    }
  ]
}

Parameters

body : object | array

`body`

It is required for data submission.
It is passed to the body of the request.

It can be an object or an array of the following type:

type Body = { sql: string; params?: (string | number)[] };

Use of params

It is possible to pass parameters to the SQL query through the body.params.
Example

API
Fetch

async function getUsers() {
  const response = await Api.sql("/users", {
    body: {
      sql: "SELECT * FROM users WHERE id = ?",
      params: [1],
    },
  });

  return response;
}

async function getUsers() {
  const url = `${BASE_URL}/sql-users`; // Replace BASE_URL with the base URL of API server
  const response = await fetch(url, {
    method: "POST",
    headers: {
      "Content-Type": "application/json",
      app: DATABASE, // Replace DATABASE with the name of your database
      token: TOKEN, // Replace TOKEN with the token
      // OR
      // Authorization: `Bearer ${AUTH_TOKEN}`, // Replace AUTH_TOKEN with the authorization token,
    },
    body: JSON.stringify({
      sql: "SELECT * FROM users WHERE id = ?",
      params: [1],
    })
  })

  const data = await response.json();
  return data;
}

Multiple Queries

It is possible to execute multiple SQL queries in a single request.
Simply passing an array of objects to the body parameter is enough.
Example

API
Fetch

async function getUsers() {
  const response = await Api.sql("/users", {
    body: [
      {
        sql: "SELECT * FROM users WHERE id = ?",
        params: [1],
      },
      {
        sql: "SELECT * FROM users WHERE id = ?",
        params: [2],
      },
    ],
  });

  return response;
}

async function getUsers() {
  const url = `${BASE_URL}/sql-users`; // Replace BASE_URL with the base URL of API server
  const response = await fetch(url, {
    method: "POST",
    headers: {
      "Content-Type": "application/json",
      app: DATABASE, // Replace DATABASE with the name of your database
      token: TOKEN, // Replace TOKEN with the token
      // OR
      // Authorization: `Bearer ${AUTH_TOKEN}`, // Replace AUTH_TOKEN with the authorization token,
    },
    body: JSON.stringify([
      {
        sql: "SELECT * FROM users WHERE id = ?",
        params: [1],
      },
      {
        sql: "SELECT * FROM users WHERE id = ?",
        params: [2],
      },
    ])
  })

  const data = await response.json();
  return data;
}