From Lazarus wiki
Revision as of 17:22, 24 February 2019 by Michael (talk | contribs) (TRestBasicAuthenticator)


The SQLDBRestBridge unit offers a means to expose any database that can be accessed by SQLDB using a REST API.

It is not meant as a general REST API framework, for this other frameworks exist. It is also not a RPC framework.

The design is such that a complete database can be exposed using a single line of code, with reasonable default.


SQL Database side.

  1. Every REST resource is defined by up to 4 SQL statements, corresponding to the 4 CRUD operations.
  2. The CRUD Statements can be auto generated on the fly, based on a table name and field definitions.
  3. For every field, an alias can be provided.
  4. The following client-side types are available, automatically mapped from the native database type:
    1. String
    2. 64-bit integer
    3. 32-bit integer
    4. Boolean
    5. Date, Time, DateTime
    6. Blob (base64-encoded)
  5. Support for a sequence to generate IDs is built-in.
  6. Full control over which operations (GET,POST,PUT,DELETE) are allowed.
  7. Resources are collected in a schema.
  8. Multiple schemas can be attached to the service.
  9. Multiple databases (connections) can be defined.
  10. A schema can be tied to a single connection, or connections can share a schemas (use case: a different connection per client).
  11. Business processor components can be hooked to a resource, to make it easier to implement business logic.
  12. SQL Statements can contain parameters, values for the parameters will be picked up from the request URL.
  13. Support to get custom datasets.
  14. Support for client-side provided SQL SELECT statements (optional, disabled by default)
  15. Full support for configuration through an .ini file out of the box.

HTTP side

  1. Authentication is handled using the HTTP protocol.
  2. Basic authentication is included by default, but is completely pluggable.
  3. Basic authentication can look up valid users in a database (by default the database being exposed)
  4. Output format can be fixed or detected per request (?fmt=format). Detection based on content-type is also available.
  5. list of fields to include in output can be specified in the URL:
  6. Field list of fields to exclude from output can be specified in the URL:
  7. Various output formats are available out of the box
    1. JSON (the default)
    2. XML (a custom format)
    3. CSV (for in and output)
    4. CDS (Format used by Delphi TClientDataset)
    5. Planned: ADO data packets (as used by MS Access)
  8. A factory pattern is used, new formats can be added at will.
  9. Simple URL schemes. 2 basic schemes are available


    or using the connection as prefix:

  10. Support for introspection/discovery or metadata:


    returns the list of available resources and their operation.

    returns the structure of the resource: fields, types, etc.
  11. limit and offset parameters for paging of results:

    A maximum limit can be enforced. When the SQL statements support it, the limit and offset are translated to SQL fetch/offset clauses.
  12. Sort order can be specified in the request URL, if the resource definition allows it, using ?sort=fieldname. The ability to sort can be specified on a field basis.

  13. Filtering can be specified on a field basis in the request URL, and the filter operation is translated to SQL Where:


    is translated to a SQL where clause:

    (MyField is null)
    (MyField is not null)
    The ability to filter on a field can be specified in the schema for each field.
  14. The request can specify whether metadata should be included in the response:

  15. The request can specify whether the result should be human-readable or not


    If set to 1, the request result will be formatted. Not all formats support this.

Available Components


Represents a REST schema : a list of available resources, with their allowed operations, fields definitions, SQL statements.


  • Resources A collection of resources. Each item is of type TSQLDBRestResource
  • ConnectionName Set this to a connection name if you want all resources of this schema to use this connection.


  • SaveToFile save the resource definitions to file (as JSON)
Procedure SaveToFile(Const aFileName : UTF8String);
  • SaveToFile save the resource definitions to stream (as JSON).
Procedure SaveToStream(Const aStream : TStream);
  • AsJSON return the resource definitions as a JSON object.

function AsJSON(const aPropName: UTF8String=''): TJSONData;

If specified, aPropName is the name of the property below which all definitions are stored.

  • LoadFromFile Load the resource definitions from a JSON file.
Procedure LoadFromFile(Const aFileName : UTF8String);
  • LoadFromStream Load the resource definitions from a JSON file.
Procedure LoadFromStream(Const aStream : TStream);
  • FromJSON Load the resource definitions from a JSON file.
Procedure FromJSON(aData: TJSONData;const aPropName: UTF8String='');

If specified, aPropName is the name of the property below which all definitions are located.

  • PopulateResourceFields create resource fields based on table name.
procedure PopulateResourceFields(aConn: TSQLConnection; aRes: TSQLDBRestResource; aMinFieldOpts : TRestFieldOptions = []);

PopulateResourceFields populates the field definitions in aRes by checking the table in database connection aConn. Every created field gets at least aMinFieldOptions in its Options property.

  • PopulateResources creates a resource definition for each table in a database connection.
procedure PopulateResources(aConn: TSQLConnection; aTables: array of string; aMinFieldOpts: TRestFieldOptions= []);
Procedure PopulateResources(aConn : TSQLConnection; aTables : TStrings = Nil; aMinFieldOpts : TRestFieldOptions = []);
  • PopulateResources retrieves the list of tables from aConn, creates a resource definition for the table and calls PopulateResourceFields to fill the resource with fields. If aTables is specified, definitions will only be created for the tables in that list.



  • Active (Boolean) Register or unregister HTTP routes.
  • Connections (TSQLDBRestConnectionList) List of database connections to connect to.
  • Schemas (TSQLDBRestSchemaList) List of REST schemas to serve
  • BasePath (UTF8String) Base URL for REST URLS.
  • DefaultConnection (UTF8String) Default connection to use if none is detected from request/schema
  • Strings (TRestStringsConfig Property) Input/Output strings configurations
  • OutputOptions (TRestOutputOptions) default Output options, modifiable by query. One or more of
  1. ooMetadata Send metadata in request
  2. ooSparse Do not send null values (if format allows it)
  3. ooHumanReadable Pretty-print output.
  • InputFormat (string) Name of Input format to use for all requests.
  • OutputFormat (string) Name of Output format to use for all requests.
  • DispatchOptions (TRestDispatcherOptions) Dispatcher options. One or more of
  1. rdoConnectionInURL Use the connection as a path element in the URL.
  2. rdoExposeMetadata Expose the metadata URL.
  3. rdoCustomView allow the use of a customview URL (Select SQL specified in request)
  4. rdoHandleCORS Handle CORS preflight request.
  • Authenticator (TRestAuthenticator) Authenticator for requests. If OnBasicAuthentication is set, this can be left empty.
  • EnforceLimit (Integer) If larger than zero, Enforce a limit on output results.
  • CORSAllowedOrigins (String) Comma separated list of Domains that are allowed to use this REST service. If empty, * will be returned in preflight request.


  • ExposeDatabase exposes a database as a REST service. This will creates a connection definition with the provided parameters and calls ExposeConnection with this definition.

    Function ExposeDatabase(Const aType,aHostName,aDatabaseName,aUserName,aPassword : String; aTables : Array of String; aMinFieldOp
    Function ExposeDatabase(Const aType,aHostName,aDatabaseName,aUserName,aPassword : String; aTables : TStrings = nil; aMinFieldOpt
    The connection gets the name ConnectionN, where N is a unique number, and the schema is named SchemaConnectionN. The Tables and aMinFieldOpt arguments are the same as in the PopulateResources call of TSQLDBRestSchema
  • ExposeConnection exposes a database (represented by aConnection) as a REST service. This will create a schema with a resource definition.

    Function ExposeConnection(Const aConnection : TSQLDBRestConnection; aTables : TStrings = nil; aMinFieldOpts : TRestFieldOptions

    The Tables and aMinFieldOpt arguments are the same as in the PopulateResources call of TSQLDBRestSchema


This class implements HTTP Basic authentication for the SQLDB REST Dispatcher

  • AuthConnection (TSQLConnection) a SQL connection that will be used to run the SQL. By default the connection of the request is used. Set this if your users are in a separate database.
  • AuthenticateUserSQL (TStrings) an SQL Statement that will be executed on the connection (AuthConnection or connection of the request). This must contain 2 parameters userName and password and return a single field. This field will be used as UserID (available in the rest of the request flow.
  • DefaultUserName (UTF8String) a valid username, not in the database.
  • DefaultPassword (UTF8String) password of a default user.
  • DefaultUserID (UTF8String) the user ID reported if the default user is authenticated.
  • AuthenticationRealm : (UTF8String) Real sent in the WWW-Authenticate challenge to the client.
  • OnBasicAuthentication (event) triggered when basic authentication is requested. Here you can enter custom code to validate the user.


  • (todo) This is a simple TWebModule descendant which is usable in Lazarus IDE. It has a single Dispatcher property which must be set to a TSQLDBRestDispatcher instance.


The TSQLDBREstResource has a collection of fields (TSQLDBRestField) that determine the output fields of the REST resource

It has the following properties;

  • FieldName (UTF8String Read FFieldName Write FFieldName;
  • PublicName (UTF8String Read GetPublicName Write FPublicName;
  • GeneratorName (String) name of a generator to privide a unique value.
  • FieldType (TRestFieldType) Data type of the exposed field.One of (the names speak for themselves)
    • rftInteger
    • rftLargeInt
    • rftFloat
    • rftDate
    • rftTime
    • rftDateTime
    • rftString
    • rftBoolean
    • rftBlob (sent as base64-encoded data)
  • NativeFieldType (TFieldType) Native database field type of this field.
  • Options (TRestFieldOptions) options for this field. One of more of:
    • foInKey This is a key field.
    • foInInsert The field can be inserted (if statement is autogenerated)
    • foInUpdate The field can be updated (if statement is autogenerated)
    • foRequired A field value is required.
    • foFilter Filtering is allowed on this field.
    • foOrderBy Ordering is allowed on this field.
    • foOrderByDesc Ordering (descendent) is allowed on this field.
  • Filters (TRestFieldFilters) Allowed filter expressions for this field. One or more of:
    • rfEqual
    • rfLessThan
    • rfGreaterThan
    • rfLessThanEqual
    • rfGreaterThanEqual
    • rfNull
  • MaxLen (integer) for string fields, the maximum allowed length of the field.

Available Output formats

JSON (the default)

  • A simple JSON object encapsulating data, metadata and possibly error nodes.
  • rows are exported as JSON objects, field names are property names of the object.
  • Name: 'json'
  • Content-type: 'application/json'
  • Input and output.
  • Data by default under 'data'
  • Metadata by default under 'metaData'
  • Errors reported under 'error'
  • Property names are configurable in REST dispatcher.

XML (a custom format)

  • A simple XML document encapsulating data, metadata and possibly error nodes, under a root element 'datapacket'
  • rows are exported as XML elements (named 'row') , the contents as text in the element.
  • Name: 'xml'
  • Content-type: 'text/xml'
  • Input and output.
  • Data by default under 'data'
  • Metadata by default under 'metadata'
  • Errors reported under 'error'
  • Element names are configurable in REST dispatcher.


  • Simple comma-separated CSV list.
  • Name: 'csv'
  • Content-type: 'text/csv'
  • Input and output.
  • Separator: Comma
  • Quotes when needed.
  • Metadata means first line contains fieldnames.


  • XML package using the XMLFormat used by Delphi TClientDataset.
  • Name: 'cds'
  • Content-type: 'text/csv'
  • Input and output.

A Delphi demo program is provided.

Planned: ADO

  • XML format as used by ADO recordsets (as used by MS Access)
  • Name: 'ado'
  • Input and output.

Ini file Support

The following classes can load/save their configuration settings from/to an .ini file

  • TSQLDBRestDispatcher
  • TRestBasicAuthenticator
  • TSQLDBRestConnection

The support for this is implemented in 2 units;

  • the sqldbauthini unit has a type helper for the TRestBasicAuthenticator class.
  • The sqldbrestini unit has the type helpers for TSQLDBRestDispatcher and TSQLDBRestConnection

The helpers implement LoadFromFile/SaveToFile methods, as well as LoadFromIni/SaveToIni methods. There are some options to control the level of depth.

The schemas (if so required) are saved as JSON files in the same directory as the .ini file, using the name of the schema as the filename.

Lazarus Support

Lazarus support for the SQLDB REST Bridge is in the lazsqldbrest.lpk packages, under the fpweb directory. It registers the TSQLDBRestDispatcher, TSQLDBRestSchema and TRestBasicAuthenticator components on the component palette:


Additionally it registers some component- and property editors:

Further integration with Lazarus is planned: 1. Add a nicer schema editor 1. Let the IDE act as a REST server. (alternatively, add a tool with a menu entry under tools for easy configuration)

Pas2JS Support

The SQLDB Rest bridge will be integrated in the compile server that comes with pas2JS: This means that pas2js will come with a toll that

  • Can recompile your web project on the fly
  • Can serve files on disc
  • Can act as a REST server.

So no actual server is needed during development.


In a fcl-web application.

The rest bridge was designed to be simple to use.

Expose a single database, no authentication.

Simple expose of a database. Run the following code in the startup of your program (for example, DoRun of the web application class, or startup code of the program)


This exposes all tables of the database 'expenstracker', allows to filter,update and sort on all fields. The connection will be named 'Connection1', the schema 'SchemaConnection1'

The configuration will be saved in a file called 'demo.ini'.
It can be reused to quickly set up a REST dispatcher.

Expose single database, HTTP Basic authentication.

Similar to the previous example, but with HTTP BASIC authentication for a single user:


This exposes all tables of the database 'expenstracker', allows to filter,update and sort on all fields. The connection will be named 'Connection1', the schema 'SchemaConnection1'. No updates are possible: the fields will not be in update/insert statements. Sorting is also not allowed.

Expose single database, HTTP Basic authentication (2)

Smilar to the previous example, but with HTTP BASIC authentication, using the database to authenticate a user:

FAuth.AuthenticateUserSQL.Text:='select uID from users where (uLogin=:UserName) and (uPassword=:Password)';

Here only the 'projects' and 'expenses' tables are exposed, read-only.

Using a Config file and schema files.

Start a dispatcher, loading the file demo.ini. The dioSkipReadSchemas tells it not to load schema files, the schemas will be created from the connections.


The .ini file can be created using SaveToFile on a configured dispatcher instance.

In the lazarus IDE.

Using a datamodule

In this approach, a datamodule is created that is in memory throughout the whole lifetime of the application (fcl-web application)

  1. Create a HTTP server application or FCGI application. (a CGI will also work but is very inefficient)
  2. Add a plain datamodule to the application.
  3. Drop a TSQLDBRestSchema class from the 'FCL-Web' tab of the component palette
  4. Edit the schema (property resources), or import it from file using the component editor context menu
  5. Drop a TSQLDBRestDispatcher class from the 'FCL-Web' tab of the component palette
  6. Edit the connections
  7. Add an item to the Schemas property and point it to the TSQLDBRestSchema added previously
  8. For HTTP Basic authentication, add a TRESTBAsicAuthenticator class, and set the TSQLDBRestDispatcher instance's Authentocator property to it.

The end result should look more or less like this:


That's it. The project is ready to run.

Using a SQLDBRestdatamodule

(under construction)



  • in FPC Packages, the fcl-web/examples/restbridge directory, there is a small example that serves a expenses tracker database.
  • The directory (delphiclient) below contains a small Delphi project that shows how to connect a TClientDataset to the SQLDB Rest bridge.


  • The demo/restbridge directory of Lazarus/Components contains the same example program, using a datamodule.

More examples will follow, in particular a pas2js client application for the expenses tracker;.


The following extensions are still planned:

  • ADO XML data packets
  • FPC's TBUFDataset XML data packets.
  • Support for ?q=filterexpression in URL filters.
  • Connection management API. (zero-config service)
  • Use HTTP credentials to connect to the database.