Auto Tables is an automatic low code REST API generator for RAD Server. The easy to use interface allows for the quick automatic configuration of a REST server and client SDK with dynamic endpoints providing read, write, and delete access to your data. Everything can be generated from your database tables in just a few clicks. Database tables can be made available with enterprise permissions for over 30 different databases including databases such as MySQL, Microsoft SQL Server, and InterBase. Additionally, you can automatically generate endpoints for over 80 more data sources using the Embarcadero Enterprise Connectors. It provides powerful API generation functionality rivaling that of DreamFactory, LoopBack, and Sandman without the complexity.
A graphical Auto Tables for RAD Server Endpoint Editor is provided to help you set up, create, and edit your Auto Tables for RAD Server endpoints. Once you configure your endpoints you can either generate a new RAD Server project or you can save them out for loading into an existing Auto Tables for RAD Server ResourceModule. The configuration format is the standard FireDAC JSON.
Full source code for the Auto Tables RAD Server ResourceModule is provided so you control your REST API server. This gives you the freedom to enhance and modify the source code as needed for your own solutions. AutoTables for RAD Server outputs a Delphi RAD Server project, a Delphi REST client project suitable for LiveBindings, a Delphi REST SDK, an OpenAPI 2.0 api spec file, and a Swagger UI documentation interface. The OpenAPI 2.0 api spec file allows you to generate clients for your REST API in the following languages: ActionScript, Ada, Apex, Bash, C#, C++, Clojure, Dart, Elixir, Elm, Eiffel, Erlang, Go, Groovy, Haskell, Java, Kotlin, Lua, Node.js, Objective-C, Perl, PHP, PowerShell, Python, R, Ruby, Rust, Scala, Swift, Typescript
Check out and download the full Auto Tables for RAD Server source code in Delphi 10.2 Tokyo over on Github.
EndPoints
Auto Tables endpoints utilize a ResourceName for the root segment and then a single dynamic segment for the specific endpoint. The default ResourceName is v1. Parameters are passed in via the query string. An example endpoint would be:
/v1/myendpoint/?PageId=1
API Versioning
You can utilize the ResourceName segment to create different versions of your API. The default name is v1 for version 1 but you can change it to v2, v3, v4 etc. when you release different versions of the same API. By utilizing version numbers you can run different versions side by side and eventually depreciate older versions.
Request Types
Auto Tables makes the GET, POST, and DELETE HTTP/S request types available as endpoints in RAD Server.
GET
If RequestType is set to GET then a standard HTTP/HTTPS request with a query string coming in will trigger that request. GET Actions include Table, SQL, AggregateSQL, and Method.
- Table – TableName field will be used to return the full database table
- SQL – SQL field will be used to execute the SQL query and return the result
- AggregateSQL – SQL field will be used to execute the SQL query list and return the results
- Method – Method field will be used to execute the custom Method
POST
If you set Request Type to POST the defined endpoint will expect a FireDAC JSON format to be submitted in the body of the request. POST Actions include Table, SQL, AggregateSQL, and Method. The FireDAC JSON will be loaded into an TFDMemTable. If the auto increment field (ID) is zero Auto Tables will create new records with the submitted rows. If the auto increment field (ID) is greater than zero Auto Tables will edit and replace the existing record with that auto increment id using the data from the submitted row.
- Table – TableName field will be used to append or edit submitted records
- SQL – SQL field will be used to execute a SQL query and return the result
- AggregateSQL – SQL field will be used to execute a SQL query list and return the results
- Method – Method field will be used to execute the custom Method
DELETE
If you set Request Type to DELETE the defined endpoint will require an auto increment field (ID) parameter to be passed to it. A DELETE SQL statement will be executed to delete the auto increment id that was passed to the endpoint. DELETE Actions include Table, SQL, AggregateSQL, and Method.
Actions
There are a number of actions that can be taken when an Auto Tables endpoint is requested. These Actions include Table, SQL, AggregateSQL, and Method.
Table
Table will automatically return a database table from an endpoint via TFDTable as FireDAC JSON.
SQL
SQL will automatically execute the set SQL statement via TFDQuery and return FireDAC JSON when the endpoint is requested. The Params field can be used to define parameters such as PageId and Limit.
AggregateSQL
Aggregate SQL is a collection of SQL statements which are all executed and returned in a single response. Each SQL statement is executed separately and its result is added to an TFDMemTable. Once all of the SQL statements have been executed the TFDMemTable is returned from the endpoint as FireDAC JSON.
Method
Method actions allows you to define a custom method for each endpoint which will be requested. The custom methods must be defined in the Auto Tables for RAD Server ResourceModule code and have direct access to AContext, ARequest, and AResponse in RAD Server.
TableName
The TableName field is used by the GET, POST, and DELETE RequestTypes. When the RequestType is GET the TableName field is used to define the name of the table that will be returned via the endpoint when the Action is set to Table. When the RequestType is POST the TableName field is used to determine on which database table to append or edit records. When the RequestType is DELETE the TableName field will be used as the database table on which the DELETE SQL statement will be executed against.
SQL
The SQL field is used to hold the SQL the SQL Action and the SQL list for the AggregateSQL Action. The SQL list for the AggregateSQL Action consists of name=value pairs where name is the name of the field that will be returned in the FireDAC JSON from the endpoint and value is the SQL statement used to fill the name field with a result. Any number of name=value pairs may be used in the AggregateSQL field limited only by the capacity of your database to execute all of the queries and the length of the SQL field itself. The SQL field accepts FireDAC style parameters in the format of :PageId which get resolved to the parameters listed in the Params field.
SQL Field Example For SQL Action
SELECT * FROM Log LIMIT :PageId,25
SQL Field Example For AggregateSQL Action
log_count=SELECT COUNT(*) FROM Log;
most_recent=SELECT last_modified as most_recent FROM Log LIMIT 0,1;
most_recent_error=SELECT message as most_recent_error FROM Log LIMIT 0,1;
Built In Default Params
There are a number of built in parameters you can use in your SQL statements. These include :UserId, :Username, :TenantId, and :Body. UserId returns the RAD Server UserId. Username returns the RAD Server Username. TenantId returns the current TenantId that is being passed in to RAD Server. And Body returns the contents of the POST body.
Method
The Method field is used to define the name of the custom method in the Auto Tables ResourceModule for RAD Server that will be executed via the endpoint when the Action is set to Method. You can create your own methods using the sample code below. Simply rename the customMethod() function as needed and utilize that name “customMethod” in the Method field.
function TAutoTablesResource.customMethod(AContext: TEndpointContext; ARequest: TEndpointRequest; AResponse: TEndpointResponse): TMemoryStream;
end;
Params
The Params field contains a comma delimited list of parameters that can be passed in to the query string on an endpoint. The parameters are made available or utilized by the various actions.
Reserved Params
The format parameter is reserved for Auto Tables to allow you to return one of 4 formats. You can pass ?format=CSV to return the data as a CSV file. You can pass ?format=XML to return the FireDAC results as XML. You can pass ?format=BINARY to return the FireDAC results as binary. And finally the default format is FireDAC JSON so any other value or an omitted format parameter will return FireDAC JSON.
Params for the SQL and AggregateSQL Actions
For the SQL and AggregateSQL action the parameters are available as parameters in the SQL query. The parameters are automatically inserted as properties and are quoted properly to handle escape characters. For example if a PageId parameter is defined in the Params field the :PageId variable would be available for use in the SQL field such as “SELECT * FROM Log LIMIT :PageId,25”.
Params for Method Actions
The parameters defined in the Params field are directly available to you via the ARequest object in the custom methods.
Macros
The Macros field contains a comma delimited list of macros that can be passed in to the query string on an endpoint. The macros can be used in the SQL field and provide access to the FireDAC Macros functionality. Macros can contain SQL database fields and are directly substituted in the SQL string.
Macros for the SQL and AggregateSQL Actions
For the SQL and AggregateSQL action the macros are available as direct string replacement macros in the SQL query. It utilizes the !macro syntax provided in FireDAC. However, the macro strings are sanitized against the list of fields in the table.
Groups
Users and Groups are already built into RAD Server. You can have users in multiple different groups. The Groups field in Auto Tables for RAD Server should contain a comma delimited list of RAD Server Groups that should have access to this endpoint. A blank Groups field would allow users who are not logged into RAD Server to access the endpoint. Utilizing this feature you can create a wide variety of permissions for access to the endpoints.
UniqueID
The UniqueID field is used to hold the UniqueID field name for the POST and DELETE RequestTypes. Both RequestTypes utilize a primary key type field to know which field in the table to use for adding, editing, and deleting records. The UniqueID field allows you to customize the name of that unique id field. By default the field name is ID.
Auto Tables for RAD Server Endpoint Editor
The Endpoint Editor allows you to automatically create, quickly customize, and easily edit all of your RAD Server endpoints. It is built in a wizard type interface with multiple easy to use steps. Everything you need to get your REST API server up and running fast is auto generated for you.
It will automatically generate a Delphi RAD Server project, a Delphi REST Client project to connect to RAD Server, and an OpenAPI (Swagger) specification file for your REST API. Additionally, you can copy the dataset component for the server endpoints to the clipboard or you can copy the REST endpoint connection components for the client to the clipboard.
Copy DataSet Component
You can copy the TFDMemTable with all of the endpoint configuration in it to the clipboard. It can then be easily pasted into an existing project.
Generated RAD Server Project
The automatically generated Auto Tables for RAD Server project has all the code already in it for handling the endpoints you have configured in the editor. The project is created from a template in the templates subdirectory. You can edit the template prior to generating your Auto Tables for RAD Server project and customize it as needed.
Copy REST Components
You can copy all of the REST components that needed to connect to the Auto Tables for RAD Server into the clipboard. They can then be easily pasted into an existing project.
Generated REST Client Project
The automatically generated Auto Tables for RAD Server REST Client project has all the code already in it for connecting to your Auto Tables for RAD Server endpoints that you have configured in the editor. The project is created from a template in the templates subdirectory. You can edit the template prior to generating your Auto Tables for RAD Server REST Client project and customize it as needed. Additionally, a Delphi SDK is also generated for the Auto Tables for RAD Server. Generation of SDKs for other languages can easily be added to the modular SDK generator.
Generated OpenAPI
In addition to the server project and client project that can be generated by editor it will also generate an OpenAPI specification document in JSON format. The OpenAPI file can be used to automatically generate SDKs or client projects using tools like OpenAPI-CodeGen or SwaggerHub to automatically create clients in a variety of languages like ActionScript, Ada, Apex, Bash, C#, C++, Clojure, Dart, Elixir, Elm, Eiffel, Erlang, Go, Groovy, Haskell, Java, Kotlin, Lua, Node.js, Objective-C, Perl, PHP, PowerShell, Python, R, Ruby, Rust, Scala, Swift, and Typescript.
The OpenAPI spec generator has a number of different fields it needs filled out to generate a propert OpenAPI spec. These fields include Title, Version, Description, Terms of Service, Contact Name, Contact Email, Contact URL, License Name, and License URL.