SQLink Documentation

Introduction

SQLink is a free and open-source Node.js library designed to simplify database interactions by providing ready-to-use APIs for MySQL. With SQLink, developers can easily perform essential database operations such as reading data (GET), creating new entries (POST), updating existing records (PUT), and deleting data (DELETE) without having to manually code these functionalities.

Additionally, SQLink offers built-in support for executing stored procedures, allowing you to pass arguments and handle more complex database logic seamlessly.

This library significantly reduces the time and effort required during the development process, particularly for those who want to focus on front-end development or need to quickly prototype their applications. Whether you're working on a personal project, learning new skills, or building a prototype, SQLink provides a robust solution that accelerates development by handling the backend for you.

Getting Started

IMPORTANT
Before Getting Started Please make sure you have MySQL and Node JS installed on your machine

Open your terminal and enter the following command to install sqlink

$ npm install -g sqlink

Once you install the package successfully, verify it by using the command as shown below

$ sqlink --version
1.0.8

Command line options

Type sqlink -h to see the available options.

$ sqlink -h
Usage: sqlink [options]

SQLink is a Node.js library that turns MySQL tables into RESTful APIs with procedure execution and full CRUD support.

Options:
 -V, --version  output the version number
 run            runs the sqlink program
 update         updates the library
 config         updates mysql configuration from CLI
 -h, --help     display help for command

Running the program

Type sqlink run to run the program.

$ sqlink run

  ____     ___    _       _           _    
 / ___|   / _ \  | |     (_)  _ __   | | __
 \___ \  | | | | | |     | | | '_ \  | |/ /
    _) | | |_| | | |___  | | | | | | |   < 
 |____/   \__\_\ |_____| |_| |_| |_| |_|\_\

                   1.0.8
           
Reading configuration file from ./properties.json
MySQL connection completed with user root
Server is running on http://172.20.10.4:3001

Sending Request to SQLink

Once sqlink program is started succesfully, you can start to query the data using postman or any other REST API tools

For any request recevied from the client there will be a log printed in the console and also for the response sent from sqlink. a sample screenshot is attached below

Configuration

To update the configuration of sqlink package, type the following command in your terminal

$ sqlink config

After executing the command, you will be prompted a input few details regarding MySQL and server port.

$ sqlink config
Please provide your MySQL configuration for connection
? Enter your MySQL host: (localhost)
? Enter your MySQL user: (root)
? Enter your MySQL password: ***********
? Enter Database name:(your database name)
? Enter your MySQL port:(3306)
? Enter the port where app needs to be hosted: (3001)

Once you enter the valid details and hit enter, next time you run sqlink run, the configuration will be applied

Reading Data

In MySQL, reading data involves retrieving records from a table. This process allows you to access and view the stored information, often filtering or sorting the data to meet specific requirements.

Usage

URL:

http://localhost:3000/table/table_name/read(...options)

Method

GET

Response:

200 OK

$select

selects all the columns in a mysql table(*) or you can query based on column names as well

Selected parameters

http://localhost:3000/table/users/read?$select=*

Selected parameters

http://localhost:3000/table/users/read?$select=username,useremail,city

$filter

filters the records based on the given condition

Example

http://localhost:3000/table/users/read?$select=*&$filter=name eq 'john'

IMPORTANT
Please note that there is a need to mention & symbol between select and filter and this will continue for rest other commands

Filter query options

Filter option	Notation	Description
Equals to	eq	Returs if the column matches the provided value
AND operator	and	Takes more then one condition as input and returns if both parameters matches the condition
OR operator	or	Takes more then one condition as input and returns if any parameter matches the condition
Not Equals to	ne	Inversion of equals to operator
Lesser than	lt	Returns if the value of the column item is lesser than the reference value
Lesser than or equal to	le	Returns if the value of the column item is lesser than or equal to the reference value
Greater than	gt	Returns if the value of the column item is greater than the reference value
Greater than or equal to	ge	Returns if the value of the column item is greater than or equal to the reference value

$orderby

Orders the query based on the provided column name in ascending or descending order

Ascending order

http://localhost:3000/table/users/read?$select=*&$orderby=name

Descending order

http://localhost:3000/table/users/read?$select=*&$orderby=name desc

$top

Works similar to limit query of mysql (returns number of records based on the given limit)

Example (Returns top 5 users)

http://localhost:3000/table/users/read?$select=*&$orderby=name desc&$top=5

$skip

Takes the number of records that needs to be skipped for a given query (Useful when paginating)

Example (skip 5 records)

http://localhost:3000/table/users/read?$select=*&$orderby=name desc&$top=5&$skip=5

Inserting Data

In MySQL, inserting data refers to adding new records into a table. This process populates the table with fresh entries, each containing specific values for the defined columns.

Usage

URL:

http://localhost:3000/table/table_name/create

Method

POST

Payload

{
     column1: "value1",
     column2: "value2",
     column3: "value3",
     column4: "value4"
   }

Response:

200 OKResponse

IMPORTANT
Please make sure the key name in your payload matches the column of mysql table to avoid errors

Example

URL:

http://localhost:3000/table/table_name/create

Payload

{
     username: "John Doe",
     useremail: "johndoe@email.com",
     city: "Bangalore",
   }

This API will insert a record in users table

Updating Data

In MySQL, updating data involves modifying existing records in a table. The update process allows you to change the values of specific fields within rows to reflect new information or correct inaccuracies.

Usage

URL:

http://localhost:3000/table/table_name/update(update_key,update_value)

Parameters

update_key: reference to the column on which data has to be updated
update_value: value of that record

Method

PUT

Payload

{
     column1: "updated_value1",
     column2: "updated_value2"
   }

Response:

200 OKResponse

Example

URL:

http://localhost:3000/table/users/update(user_id,10)

Payload

{
     username: "John Doe",
   }

This API will update the record with username as John Doe where user_id is 10

Deleting Data

In MySQL, data is deleted by removing records from a table. Deletion is a process that permanently erases specific rows of data from a database table, reducing the dataset by eliminating unwanted or obsolete entries.

Usage

URL:

http://localhost:3000/table/table_name/delete(delete_key,delete_value)

Parameters:

delete_key: reference to the column on which data has to be deleted
delete_value: value of that record

Method:

DELETE

Response:

200 OKResponse

Example

URL:

http://localhost:3000/table/users/delete(user_id,10)

The URL shown above will select a record from users table where user_id is equal to 10

Hosting Files

This feature allows users to host and access any files on their local machine with a single command.

Command

$ sqlink host YOUR_PORT YOUR_PATH

PATH: The path to the directory or file you wish to host.
PORT: The port number to serve the files (e.g., 3002).

Accessing Hosted Files

Once the hosting command is running, files are accessible through URLs in the following format:

http://localhost:PORT/static/YOUR_FILE_PATH

Replace YOUR_FILE_PATH with the relative path to the specific file from the hosted directory. For instance, if you are hosting a file at /home/user/docs/file.txt and started the server with sqlink host /home/user/docs 3002, the access URL would be:

http://localhost:3002/static/file.txt

Example

sqlink host /home/user/documents 3002

Directory being hosted: /home/user/documents
Port: 3002
Access URL for a file: http://localhost:3002/static/sample.txt (for /home/user/documents/sample.txt)

This setup provides a quick and straightforward way to access files on your local machine over HTTP, making it useful for sharing or accessing files remotely as long as the server remains active.

Uploading Files

This feature enables users to upload files to a specified endpoint and store them on disk.

Endpoint

URL: /upload
Method: POST
Required Field: files (multipart form-data key)

Note: Ensure the field name in the request body is specified as "files". Any other field name will prevent the file from being uploaded.

Directory: USER_BASE_PATH/.sqlink/uploads

Example Response

      {
          "success": true,
          "data": [
              {
                  "fieldname": "files",
                  "originalname": "YOUR_FILE_NAME",
                  "encoding": "7bit",
                  "mimetype": "text/csv",
                  "destination": "YOUR_DESTINATION",
                  "filename": "YOUR_FILE_NAME",
                  "path": "YOUR_PATH",
                  "size": 512,
                  "accessURL": "http://192.168.1.4:3001/file/YOUR_FILE_NAME.csv"
              }
          ]
      }

This setup provides a convenient way to store and access uploaded files directly.

Authentication

Authentication is the process of verifying the identity of a user or entity trying to access a system or resource. It is essential in software applications to ensure that only authorized users can access sensitive data and perform specific actions. By confirming the user's identity, authentication helps protect against unauthorized access and potential security threats.

Enabling the Authentication

To enable authentication in your application using sqlink, run the following command in your terminal:

$ sqlink run auth

This will activate the authentication module, allowing users to register, log in, and receive tokens for accessing protected resources. The authentication library in sqlink uses JSON Web Tokens (JWT) for secure, stateless authentication. Upon successful login, a JWT token is generated and returned to the user, which can then be used for authorized access.

Authentication APIs

1. Register a New User

Endpoint: /auth/register
Method: POST
Description: This API registers a new user into the system.

2. Login a User

Endpoint: /auth/login
Method: POST
Description: This API logs in an existing user and provides a JWT token upon successful authentication.

Request Payload (for both APIs):

Both /auth/register and /auth/login endpoints expect the following JSON payload as input:

Payload:

{
               email: "YOUR_EMAIL",
               password: "YOUR_PASSWORD"
}

Success Response

If the request is successful, you will receive a 200 OK response from the server.

IMPORTANT
If you attempt to access any data or endpoints while the application is in authentication mode without providing a valid JWT token, you will receive an Access Denied message.
Using this approach ensures that your application is secure and that only authenticated users can access protected routes and resources.

Generate Enum

Starting from v1.1.8 SQLink provides a feature to generate enums for your project. Enums are a set of named constants that represent a set of possible values. Enums are useful when you have a fixed set of values that are unlikely to change.

Usage

$ sqlink enum YOUR_TABLE_NAME

Example: Generate enum for customers table

As shown in the image above, the enum for the customers table is generated successfully. The enum is also going to be copied to the clipboard for easy access 😊.

Procedures

MySQL procedures are a powerful feature that allows you to encapsulate a sequence of SQL statements into a single unit that can be executed repeatedly with different inputs. Stored procedures are often used to streamline and automate repetitive database tasks, enforce business logic, and enhance the security of database operations

Learn about procedures and how to create it from click here

Usage

URL:

http://localhost:3000/procedure/procedure_name

Method:

GET

Response:

200 OKResponse

Example

URL:

http://localhost:3000/procedure/topFiveUsers()

Example with params

URL:

http://localhost:3000/procedure/topFiveUsers('Male')

The arguments for procedure depends on the number of arguments that the procedure takes which is decided when creating a procedure

Updating Package

To update the sqlink package, open your terminal and type the following command

$ sqlink update

After executing the command, please wait a few seconds for the package to update. You should then be able to see the results.

$ sqlink update
Package updated successfully!!

You can verify if the installed package is the latest version

$ sqlink --version
1.0.7

Distinct Query

In MySQL, a `DISTINCT` query involves retrieving unique records from a table by eliminating duplicate entries. This process allows you to access only the distinct values from the specified columns, ensuring that each result is unique according to the criteria set in the query.

Usage

URL:

http://localhost:3000/table/table_name/distinct(column_name)

Method

GET

Response:

200 OKResponse

Example

URL:

http://localhost:3000/table/users/distinct(username)

This API will return distinct values of username from users table

Upserting Query

An upsert operation is a database action that combines the functionalities of update and insert operations. When performing an upsert, the database checks if a specific record (usually identified by a unique key or primary key) already exists:

If the record exists, the operation updates the existing record with the new data.
If the record does not exist, the operation inserts a new record into the database.

Usage

URL:

http://localhost:3000/table/table_name/upsert(id,value)

Method

POST

Payload

{
      column1: "value1",
      column2: "value2",
      column3: "value3",
      column4: "value4"
}

Response:

200 OKResponse

Example

URL:

http://localhost:3000/table/users/upsert(userid,10)

Payload

{
      username: "John Doe",
      useremail: "johndoe@email.com",
      city: "Bangalore",
}

IMPORTANT
This query will update the record if the record with userid with 10 exists or else it will just insert a new one