Sql Data Api

With SQL Data Api you can connect to any MS SQL Database and turn it into the RESTfull database with additional access control and several other data access features:

• User Access Control
• Advanced Data Query
• Load data with Upsert/Merge, Append or Bulk Insert methods
• REST Api access to your database (JSPython, Python, C#, JavaScript(NodeJS))

We have sql-data-api client implementations for several languages:

• JavaScript
• Python
• C# (.Net 5.0)

JSPython leverages entire JavaScript eco-system (both in browser and NodeJS). And you can use all features from JavaScript library. Here is a short tutorial recap

Query tables or views

from "sql-data-api" import sqlDataApi

# returns table as array of items
query(tableOrViewName, fieldsOrQuery, queryInfoSettings)

SQL Data api allows you to safely and securely query data from SQL tables/views. And you can use SQL functions, rename SQL columns, aggregate (groupBy) and even join tables

There are several ways you can define a query to the SQL Database. But, eventually it comes down to the few properties you have to specify:

• tableName - name of SQL table or view. Also, you can specify alias e.g. myTable t, then you have to list your fields as t.Field1 etc
• fields - a list of fields to select. If fields property is not provided, then all table fields will be returned. Kind of select * from [tableName]. Also, there are several other scenarios:
  • rename fields e.g. Country CustomerCountry or cast(TransactionTime as Date) TransactionDate
  • use SQL Functions e.g. concat(FirstName, ' ', LastName) FullName
  • aggregate (group by): groupBy|Country, groupBy|City, sum(revenue) Revenue, count(*) Count
• filter - defines a filter expression e.g. country = 'uk' and city = 'London' or you can use parameters and have filter as country = @country AND city = @city and provide parameters as an object{country: 'UK', city: 'London'}. And you can use SQL functions as well: e.g.: cast(TransactionTime as Date) = '2021-11-21'
• orderBy - define a columns to sort e.g.: `OrderDate DESC, OrderId ASC
• top` - specify the number of records to return.
• join - combine rows from two or more tables, based on a related column between them. You can define array [JoinType, TableToJoin, JoinCondition] or: ['InnerJoin', 'Customers c', 'c.CustomerId = t.CustomerId']

Query Examples

A simple queries

from "sql-data-api" import sqlDataApi

return sqlDataApi("northwind-db-connection")
    .filter("title = @title", {title: "Sales Representative"})
    .top(100)
    .query("northwind.NorthwindEmployees", "Title, FirstName, LastName")

Aggregated query

Add groupBy| prefix to the field you want to group by and use aggregation functions e.g: sum, avg, count ...

return sqlDataApi("northwind-db-connection")
    .filter("title = @title", {title: "Sales Representative"})
    .top(100)
    .query("northwind.NorthwindOrders", "groupBy|ShipCountry, sum(Freight) Freight")

Saving Data

Save array of objects

Upsert(Merge), Append or BulkInsert an array of items into the table based on save options If third parameter is an array, it will delete records from the table. Only Key Fields must be provided

save(
    tableName, 
    items, 
    itemsToDeleteOrSaveOptions, 
    saveOptions
)

a simple save (upsert) example

sqlDataApi('someConnection')
    .save('someTable', arrayOfItems)

Save With Auto Id

Saves a single record into the database and returns autogenerated ID field value. SQL Table should have Auto Indentity on one of the fields

person = {
    name: 'Adam'
}

# table peopleTable should have Identity column
person.id = sqlDataApi('someConnection')
    .saveWithAutoId('peopleTable', person)

print(person)

Update

Updates data in the table based on filter parameter and returns number of rows affected

   # Updates data in the table based on filter parameters
   # @returns Number of rows affected
  updateData(
    tableName,
    updateData,
    filter,
    filterParams
  ) 

Delete

Deletes rows from the table based on a primary keys. Only key fields have to be provided

  # Deletes rows from the table. Only key fields have to be provided
  # @returns success
  delete(
    tableName,
    items
  )

Delete From

Delete records from the table based on filter criteria

  # Delete records from the table based on filter criteria
  deleteFrom(
    tableName,
    filter,
    filterParams
  ) 

SQL Execute

Executes sql script in the server and returns either raw table or array of objects

  # Executes a SQL Query or stored procedure with parameters
  # @returns Raw result (SqlQueryResponse) with a table in it
  sqlExecuteRaw(
    sql,
    params,
    paramDirections
  )

  # Executes a SQL Query or stored procedure with parameters
  # @returns result as a list of arrays 
  sqlExecute(sql, params)