Skip to content

Apir Documentation

The easiest and fastest way to create a REST API for a SQL Server Database

A simple Stored Procedure is all the code you need.

CREATE PROCEDURE API_Products_Get (@ID int = NULL) 
AS
    SELECT ProductId, ProductName 
        FROM Products 
        WHERE ProductID = @ID OR  @ID IS NULL

In the above stored procedure the resource Products is declared. The prefix API_ is used by Apir to find API procedures. The _Get at the end means that this procedure will respond to the HTTP GET verb.

The resource itself has the fields ProductID and ProductName

When Apir is running it will create an API like:

GET    http://myserver/products    Retrieve an array of products
GET    http://myserver/products/2  Retrieve product with ID=2

Similar procedures using the verbs PUT, POST and DELETE for doing full CRUD operations on products may be created.

Installation

Download the installer from

http://apirdocs.azurewebsites.net/apir.msi

Apir must be installed on a Windows machine. After installation you will have a start menu entry for the apir command line. If your are using Bash or Powershell just add c:\apir to your path.

Register your email

Please register your email. You will get a key back which are stored in apir.json.

apir register -email:XX@YY.COM

Connect to the database

The connection string is in apir.json. You may edit it or use the GUI:

apir connection

Connection Dialog

Develop your API

Resources

A fundamental concept of REST APIs is the resource.

Resources are the objects of the API. The task of your API is to enable apps to manipulate resources.

You define resources in SQL by a simple SELECT statement in a stored procedure.

In our first example we have a Products table and want to create a Product resource.

Test table

The product table has a primary key.

CREATE TABLE Products ( 
    ProductId INT NOT NULL IDENTITY(1,1), 
    ProductName VARCHAR(100),
    CONSTRAINT [PK_Products] PRIMARY KEY ([ProductId]) 
)
GO
INSERT INTO Products(ProductName) 
    VALUES('Widget1'), ('Widget2'), ('Widget3')

Generate the API

Apir can generate the TSQL procedures for you. 

apir sqlgen
    Usage: apir sqlgen -table:myTable -resource=myResource -verbs:Get,Put,Post,Delete -alter -filter -page

apir sqlgen -t:Products -r:Product

The first command shows the syntax. The second is a short hand for generating 4 procedures for doing CRUD on the Products table.

    CREATE PROCEDURE API_Products_POST(@Name VARCHAR(100))
    CREATE PROCEDURE API_Products_GET(@ID int = NULL)
    CREATE PROCEDURE API_Products_PUT(@ID,@Name VARCHAR(100))
    CREATE PROCEDURE API_Products_DELETE(@ID int)

If you want to view the SQL source code in SSMS refresh the list of stored procedures. Hint: right click in SSMS.

You may of course edit and modify these procedures. 

--- <summary> 
--- Retrieve Product 
--- </summary>  
--- <param name = "ID" > Products ID </param> 
--- <remarks> Returns all or a single Products </remarks> 
ALTER PROCEDURE[dbo].[API_Product_Get](@ID int = NULL) 
AS 
    SELECT  ProductID, ProductName
        FROM Products  
        WHERE (@ID IS NULL OR @ID = ProductID) 
        ORDER BY ProductName

The Product:Get procedure will return a single Product or an array of products depending on the @ID parameter

Post new resources

The generated procedure for inserting new products is:

--- <summary> 
--- Insert Product 
--- </summary>  
--- <remarks> Insert new Products </remarks> 
--- <response code="200">OK</response>
ALTER PROCEDURE[dbo].[API_Product_Post]( 
        @ProductName varchar(100) = NULL
) AS
INSERT INTO Products (  ProductName   ) 
    VALUES (@ProductName)

RETURN 200 -- OK

You may do some error checking in the procedure like:

This is a more advanced implementation:

--- <summary> 
--- Add a new product to the database
--- </summary>  
--- <remarks> A link to the new product is added  </remarks> 
--- <response code="201">OK</response>
--- <response code="521">Bad productname</response>
--- <response code="522">Product with name exists</response>
ALTER PROCEDURE [dbo].[API_Product_Post](
@ProductName VARCHAR(100), @NewId int OUTPUT)
AS
    IF (@ProductName IS NULL OR LEN(@ProductName) < 2) 
    BEGIN
            RAISERROR('Bad product name',1,1)
            RETURN 521
    END
    IF EXISTS (SELECT ProductId FROM Products WHERE ProductName = @ProductName)
    BEGIN
            RAISERROR('Product with that name already exists',1,1)
            RETURN 522
    END

    INSERT INTO Products(ProductName) VALUES(@ProductName)
    SET @NewId  = @@IDENTITY
    RETURN 201

Here we check for a correct productname. A product with the new name must not exists in the database.

The output parameter @NewId returns the generated identifier and a HTTP header with the URL of the generated product will be returned.

Updating the resource

The generated procedure will let the user update all non key columns. The default value of a new column is NULL, which means that the value is not altered.

--- <summary> 
--- Update Product 
--- </summary>  
--- <param name = "ID" > Products ID </param> 
--- <remarks> Updates Products </remarks> 
--- <response code="200">OK</response>
--- <response code="404">Not Found</response>
ALTER PROCEDURE[dbo].[API_Product_Put](
    @ID varchar(max) = NULL, @ProductName varchar(100)   = NULL 
) AS
IF NOT EXISTS(SELECT ProductID FROM Products WHERE @ID = ProductID)  
BEGIN
   RAISERROR('Unknown Product',1,1) 
   RETURN 404
END
UPDATE Products  SET 
    ProductName = COALESCE(@ProductName,ProductName)  
     WHERE @ID = ProductID 
 RETURN 200 -- OK

Deleting a resource

The implementation is straight forward. If the product is unknown an error is returned.

--- <summary> 
--- Delete Product 
--- </summary>  
--- <param name = "ID" > Products ID </param> 
--- <remarks> Delete Products </remarks> 
--- <response code="200">OK</response>
--- <response code="404">Not Found</response>
ALTER PROCEDURE[dbo].[API_Product_Delete](@ID varchar(max) = NULL) 
AS
IF NOT EXISTS(SELECT ProductID FROM Products WHERE @ID = ProductID)  
BEGIN
   RAISERROR('Unknown Product',1,1) 
   RETURN 404
END
DELETE Products  
    WHERE @ID = ProductID 
 RETURN 200 -- OK

Filtering and paging on the server

Clients may want to limit the rows returned by using a filter and/or paging. Implementing these on the server leads to a faster system with less data going down the line. For mobile clients there is also a cost saving with less data transfer.

You may generate the API_Product_Get procedure again with new options:

apir sqlgen -t:Products -r:Product -v:Get -page -filter -alter

The API_Resource_Get procedure will be generated as:

ALTER PROCEDURE[dbo].[API_Product_Get](@ID varchar(max) = NULL  
    , @filter varchar(max)=NULL 
    , @pageNo int = 1, @pageSize int=40 
) AS 
    SELECT  ProductID, ProductName
    FROM Products  
    WHERE (@ID IS NULL OR @ID = ProductID) 
    AND (@filter IS NULL OR CHARINDEX(@filter,CAST(ProductName AS varchar)) > 0)
    ORDER BY ProductName
    OFFSET (@pageNo-1)*@pageSize ROW 
    FETCH NEXT @pageSize ROW ONLY

Self Host API

The apir.exe app can host the api without a webserver. Just start with 

apir run

In apir.json these settings will be used

BaseAddress: "http://localhost:18092",
RelayHost: false,

Relayhost will not be used and the selfhosting will be done on port 18092

Test the API

The Swagger test and documentation framework may be enabled in the apir.json config file by:

apir config -swagger:yes

When you start apir a browser window will be opened with Swagger. Apir provides the swagger UI which uses a description of the API.

Use Relay hosting

Production

When you run the API on localhost it is generally not available on other machines and not on the internet. You may configure the firewall and routers in your company to open a port and also route request to the machine.

If you want to use the API from a mobile device you must make it available on the internet.

Apir can host the API on the internet in a safe way by using some magic called relay hosting. A cloud endpoint is used. The only local requirement is that outbound http is available. It will work across NAT and firewalls.

If you have registered your email you may try the Relay. First find a unique name for the api. Use that instead of myapi in the command below

apir registerApi -name:myapi

You will also get encryption since we always use SSL with the cloud endpoint.

Now start apir 

apir run

Host in ASP.NET web app on IIS

If you have a ASP.NET web application you may use our nuget package

Install-Package ApirIO

It is that simple. You must have a connection string with DefaultConnection defined in your web.config file

<configuration>
  <connectionStrings>
    <add name="DefaultConnection" connectionString="Data Source=localhost\sqlexpress;Initial Catalog=myDatabase;Integrated Security=True" />
  </connectionStrings>

When your app starts, the API code will be generated and compiled on the fly. If you change the API, just restart the app.

Try running the api directly in the browser

http://localhost:50608/api/products

Add Swagger to your ASP.NET website

You can install the same Swagger test harness to your ASP.NET website. 

Install-Package Swashbuckle -Version 5.5.3

Hit F5 and then start Swagger with:

 http://localhost:50608/swagger

Install as Windows Service

Windows has services which runs independent in the background. This is great in production since the api will be available even if no user is logged into the machine. If the machine is rebooted, the API will be started.

Apir.exe has commands for installing and uninstalling itself as a Windows service.

You may also start and stop the windows service. The start may me automatic with windows and it may be delayed, since we want to allow SQL Server to start first.

Working with windows services requires administrator access. You may use the --sudo flag on the command line or just start a new command line with "Run as Adminstrator". Hint: right click on the menu.

Goto c:\apir 

apir install
apir start

Hopefully the service started. If there was a problem see the logs.

If you make changes to the API_* stored procedures you may need to stop and start the service

Reference for Windows Service commands

The Windows service part of apir.exe is provided by the Topshelf library. 

   apir.exe [verb] [-option:value] [-switch]

    run                 Runs the service from the command line (default)

    help or --help      Displays help

    install           Installs the service

      -username         The username to run the service
      -password         The password for the specified username
      -instance         An instance name if registering the service
                        multiple times
      --autostart       The service should start automatically (default)
      --disabled        The service should be set to disabled
      --manual          The service should be started manually
      --delayed         The service should start automatically (delayed)
      --localsystem     Run the service with the local system account
      --localservice    Run the service with the local service account
      --networkservice  Run the service with the network service permission
      --interactive     The service will prompt the user at installation for
                        the service credentials
      --sudo            Prompts for UAC if running on Vista/W7/2008

      -servicename      The name that the service should use when
                        installing
      -description      The service description the service should use when
                        installing. 
                        Eg: -description: MyService
                        Eg: -description "My Service"
      -displayname      The display name the the service should use when
                        installing
                        Eg: -displayname: MyService
                        Eg: -displayname "My Service"

    start             Starts the service if it is not already running

      -instance           The instance to start

    stop              Stops the service if it is running

      -instance           The instance to stop

    uninstall         Uninstalls the service

      -instance         An instance name if registering the service
                        multiple times
      --sudo            Prompts for UAC

Documenting the API

The users of your API needs documentation. Your apir API can document itself with Swagger. A standard for describing an API. Also the swagger-ui is provided with apir. It is a test harness for the API.

There is also tools like code generators that uses the Swagger definition.

c# developers have documented code with XML comments for years. These comments are used by tools such as Visual Studio. ApirIO lets you comment the stored procedures and moves any comments to the generated c-sharp code. It is great for API documentation since it can be used in Swagger and lives with the database.

The comments are written before the stored procedure. They are XML tags.

--- <summary>
---     Update Product
--- </summary>  
--- <param name = "ID" > Products ID </param> 
--- <remarks> Updates Products </remarks> 
--- <response code="200">OK</response>
--- <response code="404">Not Found</response>
ALTER PROCEDURE[dbo].[API_Product_Put](
    @ID varchar(max) = NULL , 
    @ProductName varchar(100) = NULL
)

Swagger will display this as:

Product doc

Logging

apir uses the NLog library for logging.

By default, log entries are written to the console and to a logfile at ./logs

A file is created with the format YYYY-MM-DD.log

If you run as a Windows service the logfile is very important.

The Nlog configuration file is called NLog.config and its format is described here

Save and Update your API as SQL Script file

If you want to create a TSQL script file:

apir sqlretrieve -file:mySqlFile.sql

You may execute a TSQL script file by:

apir sqlexec -file:mySqlFile.sql

The script may also be stored on the internet:

apir sqlexec -url:http://myserver/myurl

The apir.json config file

Open the config file and have a look. Unfortunately there is no way of adding comments to the json file itself.

When hosting locally, this URL will be used    
    "BaseAddress": "http://localhost:18092",

Where to store compiled sourcecode for WebApi  
    "OutPath": "cs\\",

Should a Swagger description of the API be available 
    "Swagger": true,

Should the Swagger test GUI be available  
    "SwaggerUI": true,

A TSQL procedure to authenticate with user / password
    "UserValidator": "",

The name of machine to authenticate 
    "MachineValidate": "",

A domain for which to authenticate
    "DomainValidate": "",

Should relay hosting be used    
    "RelayHost": true,

A key returned when email is registered.    
    "ApirKey": "",

A key for a service when using Relay host  
    "ServiceKey": "",

Resources which should not be protected. (Auth needed)  
    "FreeResources": "Login,Register",

Connection string to database. 
    "ConnectionString": "Server=;Database=;User ID=;Password=;",

Experimental autorestart when TSQL procedures change
    "AutoRestart": false,

Securing the API

Two things must be implemented for securing the API. All communication between the client and the API must be encrypted. Client apps or users must be authenticated.

If you use our relay hosting only SSL encrypted access is allowed.

The authentication is done using Basic Authentication. The user / password may be verified by

  1. A stored procedure you write. The username and password i passed as parameters to the procedure.

  2. A user on the machine running apir.exe may be used. Just add the name of the machine to apir.json machineValidate. You can find the machine name from DOS by using the command hostname

  3. A user on a windows domain. Add the domain name to apir.json DomainValidate

Securing API on ASP.NET

If you host your API as part of a website in IIS, you may use ASP.NET and IIS to secure the API