--- title: "Read/write to SQL databases (Sql.Db.Mine)" slug: "readwrite-to-sql-databases-sqldbmine" tags: ["custom provider", "luminesce ", "sql database"] status: "update" updated: 2026-08-03T14:28:48Z published: 2026-08-03T14:29:33Z canonical: "support.lusid.com/readwrite-to-sql-databases-sqldbmine" --- > ## Documentation Index > Fetch the complete documentation index at: https://support.lusid.com/llms.txt > Use this file to discover all available pages before exploring further. # Read/write to SQL databases (Sql.Db.Mine) You can request a `Sql.Db.Mine` provider from FINBOURNE and run it locally to create and publish a custom provider in your LUSID domain that is able to read from, write to, or configure your own SQL database. > **Note:** You can connect to the following databases: DuckDb, MySql, Odbc, Oracle, PostgreSQL, SqLite, SqlServer, GoogleBigQuery, AwsAthena, Db2, Snowflake. The goal is to enable end users to write simplified or more performant queries using the custom provider available from your domain's Luminesce catalog rather than interacting with the database directly, which they might not have the knowledge, infrastructure or permissions to do. > **Note:** To query your custom provider, an end user must have a valid user account in your LUSID domain, and suitable access control permissions to use that custom provider. [More on permissions](/v1/docs/readwrite-to-sql-databases-sqldbmine#understanding-access-control-permissions). Consider the example of a `CustomProviders.RetrieveInternalTransactionData` custom provider you publish that: - Retrieves particular transaction data from your database in a highly performant manner, for example instrument names, unique identifiers, settlement dates, quantities and prices. - Calculates the cost of each transaction on-the-fly by multiplying quantity by price. An end user can then write a simple Luminesce SQL query that retrieves data from your SQL database and processes it, perhaps by upserting it to LUSID. For example, the following query uses `CustomProviders.RetrieveInternalTransactionData` to retrieve all the transactions that occurred on 22 April 2022, and then the [Lusid.Portfolio.Holding.Writer](/v1/docs/lusidportfolioholdingwriter) provider (supplied by FINBOURNE) to set them as holdings in LUSID. > **Note:** The column names retrieved from your database must match those expected by the FINBOURNE provider (hence some have been renamed). ```sql @table_of_data = select  'Finbourne-Examples' as PortfolioScope, 'UK-Equities' as PortfolioCode,  SettleDate as EffectiveAt, Quantity as Units, Currency as CostCurrency, Cost as CostPrice,  Figi,  'Set' as WriteAction from  CustomProviders.RetrieveInternalTransactionData where SettleDate = #2022-4-22#; select * from Lusid.Portfolio.Holding.Writer where toWrite = @table_of_data; ``` ## Hosting the `Sql.Db.Mine` provider To run `Sql.Db.Mine` locally: 1. Make sure your computer has .NET 8 installed. 2. As a LUSID user with the `lusid-administrator` role, navigate to `<your-domain>.lusid.com/honeycomb/swagger/index.html`, where `<your-domain>` is the root of your LUSID domain, for example `acmecorp`. If you do not have the `lusid-adminstrator` role, [see the appendix](/v1/docs/readwrite-to-sql-databases-sqldbmine#appendix-a-allowing-nonadministrative-users-to-download-binaries). 3. Use Swagger's **Try it out** button for the `DownloadBinary` API to request the appropriate `SqlDb_Providers_*` binary, for example for PostGreSQL: ![](https://cdn.document360.io/d575ad81-c0ed-4980-bbd1-d59ac5c3de82/Images/Documentation/0755990c-c91f-452e-b924-6859f1ca26f0.png) 4. Click the **Download file** link in the response to download the binary to your browser's download location: ![](https://cdn.document360.io/d575ad81-c0ed-4980-bbd1-d59ac5c3de82/Images/Documentation/04cbcb32-e5b7-49dd-9713-845371682c68.png) 5. Navigate to the download location and run an appropriate install command (omitting the `.nupkg` file extension), for example for PostgreSQL: ```powershell C:\Users\JohnDoe\Download> dotnet tool install Finbourne.Luminesce.DbProviders.PostgreSql -g --add-source "." ``` You can locate the installation: - Windows: `%USERPROFILE%\.dotnet\tools\.store\` - Linux/MacOS: `$HOME/.dotnet/tools/.store/` 6. Run the following command to start the service (omitting the `.exe` extension on Linux or MacOS): ```powershell C:\Users\JohnDoe\.dotnet\tools> luminesce-database-providers.exe --routeAs=User --quiet --authClientDomain= --certificateFallback=DownloadMayMint ``` ...where `<your-domain>` is the root of your LUSID domain, for example `--authClientDomain=acmecorp`. The `DownloadMayMint` option mints a new certificate if you do not have one already. [Read more on managing certificates.](/v1/docs/managing-certificates-for-luminesce-providers) For information on all the command line options, run the command `luminesce-database-providers.exe --help`. > [!NOTE] > Note > > The above command shows a user setup; once you are ready to [make your providers globally available](/v1/docs/readwrite-to-sql-databases-sqldbmine#routing-your-providers-globally) you can switch to `--routeAs=Global`. You may need to switch to global routing if you experience the following error: > > ```plaintext > ACCESS_REFUSED - read access to topic '.#' in exchange 'external_fs_updates' in vhost 'honeycomb-grid' refused for user 'client-_< > ``` 7. When prompted in a browser window, authenticate to LUSID as a user with administrative privileges. With the `Sql.Db.Mine` provider running locally, you can now write a Luminesce SQL query using [any tool](/v1/docs/what-tools-are-available-to-write-luminesce-queries), for example the LUSID web app. Note the following: - You may need to click the **Refresh** button in the web app catalog (highlighted in red) to see `Sql.Db.Mine`. - FINBOURNE host a `Sql.Db` provider that may also appear in your catalog. Do not use this. Use `Sql.Db.Mine`. - You can [rename](/v1/docs/readwrite-to-sql-databases-sqldbmine#renaming-sqldbmine) `Sql.Db.Mine`. ![](https://cdn.document360.io/d575ad81-c0ed-4980-bbd1-d59ac5c3de82/Images/Documentation/a8fc861d-fb8b-4be5-90e2-d71c3b95d4d0.png) To stop `Sql.Db.Mine`, press `CTRL-C`. To start it again, rerun the `luminesce-database-providers.exe` command (it should be faster subsequent times). Your LUSID credentials are remembered for one hour, after which you must authenticate again. To uninstall the package, perhaps in order to upgrade to a newer version, run the command `dotnet tool uninstall Finbourne.Luminesce.DbProviders.PostgreSql -g`. ### Understanding access control permissions You, and any other LUSID administrative user wishing to run queries against `Sql.Db.Mine` in order to publish custom providers for end users, must have access control permissions to use the `Sql.Db.Mine` provider. The general principle of creating policies for Luminesce providers, assigning them to roles and roles to users, is explained [in this article](/v1/docs/creating-policies-to-control-access-to-providers-for-different-luminesce-end-users). Note you should automatically have permission if you are the LUSID domain owner with the built-in `lusid-administrator` role. You, and any other LUSID administrative user wishing to run queries, should also have access control permissions to use [connection string aliases](/v1/docs/readwrite-to-sql-databases-sqldbmine#connecting-to-your-sql-database) in order to connect `Sql.Db.Mine` to your SQL database. Your end users should ***not*** have access control permissions to use `Sql.Db.Mine`. Instead, they require access control permissions just for those published custom providers you wish them to use. ### **Renaming** `Sql.Db.Mine` You can optionally rename the `Mine` suffix (though not the `Sql.Db` prefix). To do this, open `<binary-install-loc>/api-settings.json` and change: ```sql "ProviderNameFormat": "{0}.Mine", ``` to (for example): ```sql "ProviderNameFormat": "{0}.Oracle", ``` The new name is reflected in the Luminesce catalog the next time you sign in. Note you will need to restart `Sql.Db.Mine` if you change this file while the service is running. ## Connecting to your SQL database You can connect to any supported SQL database for which you have a valid connection string. You *can* hard-code the connection string [in the provider](/docs/readwrite-to-sql-databases-sqldbmine#options) (using the `--connection` option), and this might be more convenient when creating and testing your provider. For publication, however, we recommend specifying a connection string alias in `<binary-install-loc>/database-provider-settings.json`, for example: ```json "ConnectionStrings": [   {     "Name":"MyDb",     "ConnectionString":"DataSource=./Tests/TestData/sakila.sqlite",     "LicenceCode": "honeycomb-standard-access"   } ], ``` You can then use the alias `Name` with the `--connection` option. Note you will need to restart `Sql.Db.Mine` if you change this file while the service is running. To connect via aliases you must also [create a policy consisting of the following JSON](/v1/docs/how-do-i-create-a-policy-from-a-json-document), assign it to a suitable role, and assign that role to your LUSID user (or any LUSID administrative user wishing to run queries against `Sql.Db.Mine`): ```json {   "description": "Enable the Sql.Db.Mine provider to use connection string aliases",   "applications": [       "Honeycomb"   ],   "grant": "Allow",   "selectors": [       {           "idSelectorDefinition": {               "identifier": {                   "code": "Connection/*",                   "scope": "Honeycomb"               },               "actions": [                   {                       "scope": "Honeycomb",                       "activity": "Execute",                       "entity": "Feature"                   }               ],               "name": "Luminesce connection string aliases",               "description": "Enable the Sql.Db.Mine provider to use connection string aliases"           }       }   ],   "when": {       "activate": "2022-03-03T00:00:00.0000000+00:00",       "deactivate": "9999-12-31T23:59:59.9999999+00:00"   } } ``` ## Getting help You can access the latest documentation in the help page for the `Sql.Db.Mine` provider at any time by running this query: ```sql @x = use Sql.Db.Mine --help enduse; ``` Alternatively, run the following query to retrieve the help content as a table: ```sql select distinct CustomSyntax from Sys.Registration where Name = 'Sql.Db.Mine' ``` ## Understanding the `Sql.Db.Mine` provider syntax `Sql.Db.Mine` is a direct provider, and therefore follows [these syntactic conventions](/v1/docs/what-are-a-data-provider-and-a-direct-provider#direct-provider). The precise syntax is as follows*:* ```sql @x = use Sql.Db.Mine [with @@scalar_var1 [, @@scalar_var2...]] ---- ---- ---- enduse; -- The following statement is optional but useful while creating the provider to test it returns meaningful data select * from @x ``` Note the following: - Each `<section>` must be separated by a line consisting of at least four hyphens (and no other characters). - [](/v1/docs/readwrite-to-sql-databases-sqldbmine#options) An option takes the form `--<option>=<value>`, for example `--provider=Provider.MyProvider`. Note no spaces are allowed either side of the `=` operator. If an option takes a boolean value, then specifying that option (for example `--allDomains`) sets it to True; omitting the option specifies False. - [](/v1/docs/readwrite-to-sql-databases-sqldbmine#reading-from-your-sql-database) [](/v1/docs/readwrite-to-sql-databases-sqldbmine#writing-to-your-sql-database) [](/v1/docs/readwrite-to-sql-databases-sqldbmine#configuring-your-sql-database) Nominally optional, at least one of these sections is mandatory in order for your custom provider to perform a useful operation. They must be in the specified order. An `ENDUSE` statement must terminate whichever is the last of these sections. ### Options > **Note**: For the latest information, and for options not included in the list below, [access the help page](/v1/docs/readwrite-to-sql-databases-sqldbmine#getting-help). #### `--type` Mandatory. Valid arguments are: `MySql`, `Odbc`, `Oracle`, `PostgreSql`, `SqLite`, `SqlServer`, `GoogleBigQuery`, `AwsAthena` #### `--connection` Mandatory. This can either be: - A connection string, for example `--connection="Data Source=./Tests/TestData/sakila.sqlite;"` - The `Name` of a [connection string alias](/v1/docs/readwrite-to-sql-databases-sqldbmine#connecting-to-your-sql-database), for example `--connection="MyDb"` #### `--provider` Mandatory in order to [publish](/v1/docs/readwrite-to-sql-databases-sqldbmine#publishing-your-custom-provider) your custom provider with the specified name in the Luminesce catalog, for suitably-permissioned end users to discover and query. Note it may be more performant to omit this option while creating and testing your provider. #### `--protected` Prevents a user other than you editing or deleting a custom provider. Can only be used in conjunction with `--provider` when you are ready to publish. Ownership is captured the first time a provider is created. If the provider already exists when this option is specified, the original creator retains ownership; it is not transferred to you. You can republish a provider without this option, in which case it becomes editable again. Note that administrators with `Sql.Db/AdditionalAccess` permissions can override protection and retain the ability to edit or delete the provider. #### `--withWriter` Mandatory if you include a `<sql-for-writing-section>` in order to write to your SQL database. #### `--commitEvery` When writing data, performs a commit every `<value>` rows. For example, specifying `--commitEvery=10000` performs a commit every 10,000 rows. If this option is not specified, one commit is performed at the end of the operation; if an error occurs, all data the operation has written so far is cancelled. #### `--parameters` ```sql --parameters ,,,true/false[,description] ``` Defines [scalar variables](/v1/docs/understanding-the-luminesce-sql-query-syntax#variables) that can be passed into the custom provider by end users. Each declaration: - Must be on a separate line after `--parameters`, so if specified this option should always be last in the list. - Must have a name and data type (either `Boolean`, `Int`, `BigInt`, `Double`, `Decimal`, `Text`, `Date`, `DateTime`). - Must have an initial value appropriate to the data type. To include quote or comma characters in a `Text` value, encapsulate it in double quote marks (see the example below). Specify `true` as the fourth argument to make the initial value also the default value; specify `false` to use the initial value only when setting up the provider, and not when end users query the published version. - Can have a description that appears as a tooltip for end users in the Luminesce catalog. To include quote characters in the tooltip, encapsulate them in double quote marks. For example: ```sql @x = use Sql.Db.Mine --type=SqLite --connection="MyDb" --description="This is a tooltip for the provider as a whole" --parameters AssetClass,Text,Equity,true Isin,Text,GB124892739,true BondType,Text,"Vanilla, Complex and Inflation-Linked",true,"This is a parameter tooltip" AsAt,DateTime,0001-01-01,false,"This will ""appear"" as a parameter tooltip" ---- ``` See also the `#PARAMETERVALUE` macro in the section below for inserting parameter values into the provider at the appropriate location. #### `--deleteProvider` Removes a published provider from the Luminesce catalog. [More information](/v1/docs/readwrite-to-sql-databases-sqldbmine#deleting-your-custom-provider). #### `--limit`, `--offset`, `--groupBy`, `--select`, `--filter`, `--distinct` ```sql --limit= --offset= --groupBy= --select= --filter= --distinct ``` Specifying these options optimises performance in conjunction with the appropriate macros (see the section below) while you are creating and testing the custom provider. Consider the following example: ```sql @x = use Sql.Db.Mine    --type=SqLite    --connection="MyDb"    --distinct    --filter=Isin is not null    --limit=42    ----    select        #DISTINCT        #SELECT { { Isin : Isin } }        from my_database_table where        #RESTRICT { { Isin : Isin } }    #LIMIT    enduse;    select * from @x ``` Each time you run this query during the development phase, a maximum of 42 different bonds with ISIN identifiers is returned, enabling you to test the provider returns meaningful data in a reasonable timeframe. Note these options have no effect on a published provider, so you can either retain or omit them when you are ready to add the `--provider` option and go live. ## Reading from your SQL database You must write a SQL query in the `<sql-for-reading-section>` that is valid for the SQL database you are connecting to. We recommend including the macros in the following table in your SQL query to optimise performance. > **Note**: For more information on each macro, and for available macros that are not included below (such as `#CHECKACCESS`, `#USERID` and `#CLIENTDOMAIN`), [access the help page](/v1/docs/readwrite-to-sql-databases-sqldbmine#getting-help). | **Macro** | **Use # of times** | **Explanation** | | --- | --- | --- | | `#SELECT{{<column>}, [{<column>}…]}` | Many | Optimises the performance of retrieving data from your SQL database. Each `<column>` argument consists of a column name and an SQL statement that defines how that column is populated, separated by a colon. For example: ```sql @x = use Sql.Db.Mine ---- select #SELECT { { Instrument: i.DisplayName }, } from my_database_table i enduse; ``` …specifies that an end user can write a query returning an `Instrument` column that is populated with values retrieved from the `DisplayName` column in `my_database_table`. If the end user does not explicitly request the `Instrument` column in a particular query, the custom provider does not retrieve the `DisplayName` column from your SQL database. You *could* omit the `#SELECT` macro and create this view as follows: ```sql @x = use Sql.Db.Mine ---- select DisplayName as Instrument from my_database_table enduse; ``` …but this would impair performance. The custom provider will always retrieve the `DisplayName` column from your SQL database whether the end user requests the `Instrument` column or not, increasing bandwidth and round-trip time unnecessarily. Note you can append one or more characters to a column name to distinguish that column in the custom provider: - Append `+` to make the column a primary key. - Append `^` to make the column a 'main' field (one returned by a query that starts `select ^ from`...) - Append `~` followed by either `Boolean`, `Int`, `BigInt`, `Double`, `Decimal`, `Text`, `Date` or `DateTime` to explicitly set the data type. - Append `\|"<string>"` to give the column a description in the catalog (only `"` may not appear in that description). For example: ```sql select #SELECT { { some-column-name+^~Int\|"This is an important column" : sql-for-special-column }, { next-column-name : sql-for-ordinary-column }, } ``` | | `#SELECT_AGG{{<column>}, [{<column>}…]}` | One | Use this macro once instead of `#SELECT` in order to pass any aggregation operations that an end user performs (such as count, min, max or average) down to your SQL database, further improving performance. | | `#RESTRICT{{<column>}, [{<column>}…]}` | Many | Optimises the performance of any `WHERE` clause filters specified by an end user on the named set of columns, passing the filters down to your SQL database. | | `#RESTRICT_AGG` | One | Use this macro once instead of `#RESTRICT` and in conjunction with `#SELECT_AGG` to optimise the performance of filters specified by the end user on the same set of columns as `#SELECT_AGG` (which is why this macro has no `<column>` arguments). | | `#PARAMETERVALUE(<NAME>)` | Many | Inserts the named `--parameters` value specified by an end user at an appropriate point in the custom provider. For example: ```sql @x = use Sql.Db.Mine --parameter AssetClass,Text,Equity ---- select * from my_database_table where Type is #PARAMETERVALUE(AssetClass) enduse; ``` Note parameters with a data type of `Date` or `DateTime` must be assigned to an intermediate scalar variable; see [this example](/v1/docs/creating-custom-views-sysadminsetupview#writing-a-sysadminsetupview-query-to-create-your-custom-view) from the similar `Sys.Admin.SetupView` provider. This is a temporary restriction that may be removed shortly. | | `#LIMIT` | One | Inserts a `LIMIT` clause specified by an end user at an appropriate point in the custom provider, optimising performance. Since this macro has no effect if the end user does not specify a `LIMIT` clause, it is typically always useful to include this macro. | | `#OFFSET` | One | Fills in the `OFFSET` clause as required by the SQL dialect. | | `#DISTINCT` | One | Inserts a `SELECT DISTINCT` statement specified by an end user at an appropriate point in the custom provider, optimising performance. Since this macro has no effect if the end user does not specify a `SELECT DISTINCT` statement, it is typically always useful to include this macro. | | `#GROUPBY` | One | Inserts a `GROUP BY` statement specified by an end user at an appropriate point in the custom provider, optimising performance. Since this macro has no effect if the end user does not specify a `GROUP BY` statement, it is typically always useful to include this macro. | | `#ORDER_BY` | One | Allows more limits to be applied, and in the case where they exist can significantly optimise performance. Requires the presence of both `#SELECT_AGG` and `#RESTRICT_AGG` in the query. | For example, to create our example `CustomProviders.RetrieveInternalTransactionData` provider: ```sql @x = use Sql.Db.Mine --provider=CustomProviders.RetrieveInternalTransactionData --type=SqLite --connection="MyDb" ---- -- Note the syntax in this section depends on the SQL database you are connecting to select #DISTINCT -- Using the #SELECT_AGG macro here (as opposed to #SELECT): -- 1. Avoids duplicating the work of the #RESTRICT macro -- 2. Improves performance by passing aggregate queries down to the SQL database. -- Here we rename database columns for end users, as well as defining how the Cost column is calculated #SELECT_AGG {  { Name: instrument },  { Figi: figi },  { SettleDate: txn_date},  { Currency: currency },  { Quantity: units },  { Price: price },  { Cost: price * units }, } from my_database_table where -- Enables filtering on all the columns specified in the #SELECT_AGG macro. #RESTRICT_AGG #GROUP_BY #LIMIT enduse; select * from @x ``` Note the following: - When run, this query publishes a `CustomProviders.RetrieveInternalTransactionData` provider in the Luminesce catalog. - An end user can filter/return seven columns from the SQL database table (`Name`, `Figi`, `SettleDate`, `Currency`, `Quantity`, `Price` and `Cost`). - Values in the virtual `Cost` column are calculated on-the-fly by multiplying the database `price` by `units` columns. - If an end user writes a query that aggregates any of the columns in the `SELECT_AGG` macro, the aggregation is passed down by the `RESTRICT_AGG` macro to the SQL database, improving performance. An example of such a query might be: ```sql select SettleDate, count(*) TransactionCount, max(Cost) as MostExpensive from CustomProviders.RetrieveInternalTransactionData where Currency = 'GBP' group by 1 ``` ## Writing to your SQL database In addition to a SQL query in the [](/v1/docs/readwrite-to-sql-databases-sqldbmine#understanding-the-sqldbmine-provider-syntax), you can specify the `--withWriter` option and write a SQL query in the `<sql-for-writing-section>`. This creates a writer provider, alongside the read provider, that end users can use to write to the SQL database. For example, to create a `CustomProviders.RetrieveInternalTransactionData.Writer` provider: ```sql @some_data = select 'AVIVA PLC' as Name, 'BBG000DCK9D2' as Figi, #2024-07-15# as SettleDate, 'GBP' as Currency, 300 as Quantity, 392 as Price; @x = use Sql.Db.Mine with @some_data      --provider=CustomProviders.RetrieveInternalTransactionData      --type=SqLite      --connection=MyDb      --withWriter      ---- -- Create a provider to read from `my_database_table` called `CustomProviders.RetrieveInternalTransactionData`: select #DISTINCT #SELECT_AGG {  { Name: instrument },  { Figi: figi },  { SettleDate: txn_date},  { Currency: currency },  { Quantity: units },  { Price: price },  { Cost: price * units } } from my_database_table where #RESTRICT_AGG #GROUP_BY #LIMIT      ---- -- Use an upsert query to write to `my_database_table`. -- This creates a writer provider called `CustomProviders.RetrieveInternalTransactionData.Writer`:    INSERT INTO my_database_table (#INSERT_COLUMN_NAMES        {          { Name : instrument },          { SettleDate : txn_date },          { Quantity : units },          { Price : price }        })      VALUES (#INSERT_COLUMN_VALUES)      ON CONFLICT (Figi)      DO UPDATE SET      updated_at = CURRENT_TIMESTAMP,      #UPDATE_COLUMN_ASSIGNMENTS      {      { Name : instrument = excluded.instrument },      { Figi : figi = excluded.figi },      { SettleDate : txn_date = excluded.txn_date },      { Currency : currency = excluded.currency },      { Quantity : units = excluded.units },      { Price : price = excluded.price }      }      WHERE      #INSERT_COLUMN_CHANGED_FILTER    enduse; select * from @x ``` This publishes two providers in the Luminesce Catalog: a read provider `CustomProviders.RetrieveInternalTransactionData` and a write provider `CustomProviders.RetrieveInternalTransactionData.Writer`. **Note:** The SQL required for the `<sql-for-writing-section>` varies depending on the database type you’re connected to. [Appendix B](/v1/docs/readwrite-to-sql-databases-sqldbmine#appendix-b-syntax-for-writing-to-different-sql-database-types) provides an example of the form required for each supported database type. We recommend including the macros in the following table in your SQL query to optimise performance. For more information on each macro, and for available macros that are not included below, access the help page. | **Macro** | **Use # of times** | **Explanation** | | --- | --- | --- | | `#INSERT_COLUMN_NAMES({{<column>}, [{<column>}…]}})` | One | Passes column names the end user specifies down to the SQL database. Use the `{{ <provider-column-name> : <database-column-name> }}` syntax to define how a provider column name that doesn’t match a column name in the database should be provided to the SQL database. For example: ```sql INSERT INTO my_database_table (#INSERT_COLUMN_NAMES { { Name : instrument }, { SettleDate : txn_date }, { Quantity : units }, { Price : price } }) ``` | | `#INSERT_COLUMN_VALUES` | One | Assigns values to columns and passes them down to the SQL database. | | `#UPDATE_COLUMN_ASSIGNMENTS` | One | Specifies how to assign values to columns. Use the `{{ <column> : <value> }}` syntax to define how values provided by the end user are provided to the SQL database. Each `{{ <column> : <value> }}` argument consists of a column name and an SQL statement that defines how that column is populated, separated by a colon. For example: ```sql INSERT INTO my_database_table (#INSERT_COLUMN_NAMES) VALUES (#INSERT_COLUMN_VALUES) ON CONFLICT (Figi) DO UPDATE SET updated_at = CURRENT_TIMESTAMP, #UPDATE_COLUMN_ASSIGNMENTS { { Name : instrument = excluded.instrument }, { Figi : figi = excluded.figi }, { SettleDate : txn_date = excluded.txn_date }, { Currency : currency = excluded.currency }, { Quantity : units = excluded.units }, { Price : price = excluded.price } } ``` …assigns column values in the event of a conflict with `Figi` (if concurrent sessions perform actions that modify rows). Note the `excluded` table references values originally proposed for insertion. | | `#INSERT_COLUMN_CHANGED_FILTER` | One | Applies column assignments from `#UPDATE_COLUMN_ASSIGNMENTS` only where something has changed. | ## Configuring your SQL database You can use `Sql.Db.Mine` to create a new database table if one does not already exist in the `--connection` location. For example: ```sql @some_data = select 'AVIVA PLC' as Name, 'BBG000DCK9D2' as Figi, #2024-07-15# as SettleDate, 'GBP' as Currency, 300 as Quantity, 392 as Price; @x = use Sql.Db.Mine with @some_data      --provider=CustomProviders.RetrieveInternalTransactionData      --type=SqLite      --connection=MyDb      --withWriter      ---- --    select #DISTINCT #SELECT_AGG {      { Name: instrument },      { Figi: figi },      { SettleDate: txn_date},      { Currency: currency },      { Quantity: units },      { Price: price },      { Cost: price * units },    }    from my_database_table    where    #RESTRICT_AGG    #GROUP_BY    #LIMIT      ---- --    INSERT INTO my_database_table (#INSERT_COLUMN_NAMES {        { Name : instrument },        { SettleDate : txn_date },        { Quantity : units },        { Price : price }      })    VALUES (#INSERT_COLUMN_VALUES)    ON CONFLICT (Figi)      DO UPDATE SET      updated_at = CURRENT_TIMESTAMP,      #UPDATE_COLUMN_ASSIGNMENTS      {        { Name : instrument = excluded.instrument },        { Figi : figi = excluded.figi },        { SettleDate: txn_date = excluded.txn_date },        { Currency : currency = excluded.currency },        { Quantity : units = excluded.units },        { Price : price = excluded.price }      }      #INSERT_COLUMN_CHANGED_FILTER      ---- -- -- Optionally, create the `my_database_table` table itself if one does not already exist:    CREATE TABLE IF NOT EXISTS my_database_table (      instrument VARCHAR NOT NULL,      figi VARCHAR UNIQUE,      txn_date DATETIME NOT NULL,      currency VARCHAR NOT NULL,      units BIGINT NOT NULL,      price NUMERIC NOT NULL,      updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,      created_at DATETIME DEFAULT CURRENT_TIMESTAMP    );    enduse;    select * from @x ``` …creates a table `my_database_table` with the columns `instrument`, `figi`, `txn_date`, `currency`, `units`, `price`, `updated_at` and `created_at`. Note you **must** specify a `<sql-for-reading-section>` and `<sql-for-writing-section>` when configuring your database using `Sql.Db.Mine`. This means the query creates a table and also publishes: - A read provider, for example `CustomProviders.RetrieveInternalTransactionData` that users can query to read from `my_database_table`. - A write provider, for example `CustomProviders.RetrieveInternalTransactionData.Writer` that users can query to write to `my_database_table`. ## Publishing your custom provider Each time you run a query with the `--provider` option, a custom provider with the specified name is automatically created and made available in the Luminesce catalog (note you may need to click the highlighted **Refresh** button to see it): ![](https://cdn.document360.io/d575ad81-c0ed-4980-bbd1-d59ac5c3de82/Images/Documentation/5b2e87ec-c514-4eef-904e-9d16638092fa.png) > **Note:** It is more performant to omit the `--provider` option while you are creating and testing your provider. To grant end users the necessary permissions to query a custom provider, [create a suitable access control policy](/v1/docs/creating-policies-to-control-access-to-providers-for-different-luminesce-end-users) and assign it to each LUSID end user. Once published, an end user can write Luminesce SQL queries using any of the data fields published in the catalog, so for example: ```sql select Name, Figi, Cost from CustomProviders.RetrieveInternalTransactionData where Currency = 'USD' and SettleDate > #2022-01-01#; ``` ## Managing your live custom provider Once published, you can manage a custom provider by running queries, for example: - To retrieve the original query used to create the view: ```sql select Sql, At from sys.logs.hcquery where Sql like '%Sql.Db%' and Sql like '%RetrieveInternalTransactionData%' and Sql not like '%HcQuery%' and ShowAll = true order by At desc limit 1 ``` - To examine the current content of the view: ```sql select Content from Sys.File where Name like '%RetrieveInternalTransactionData%' and Extension = '.sql' ``` ## Routing your providers globally After setting up and testing your providers as a single user, you can specify `--routeAs=Global` in your start-service command to make the database configuration global. To make the switch from user to global, you may need to first delete existing certificates from your binary installation location. For a default Windows installation, this can be found in `%USERPROFILE%\.dotnet\tools\.store\finbourne.luminesce.dbproviders.<*>\<version>\finbourne.luminesce.dbproviders.<*>\<version>\tools\net8.0\any`: ![](https://cdn.document360.io/d575ad81-c0ed-4980-bbd1-d59ac5c3de82/Images/Documentation/delete certs.png) Once any existing certificates have been deleted, the start-service command will automatically download the required domain-wide certificates when it next runs. For example, you could run: ```powershell C:\Users\JohnDoe\.dotnet\tools> luminesce-database-providers.exe --routeAs=Global --quiet --authClientDomain= --config "DataProvider:RoutingTypeGlobalMachineWhitelist=>" ``` > [!WARNING] > Important > > Setting the `DataProvider:RoutingTypeGlobalMachineWhitelist` config option to your device name explicitly grants permission for users with [suitable access control permissions](/v1/docs/readwrite-to-sql-databases-sqldbmine#understanding-access-control-permissions) to access and pull data from files on your machine. Ensure you have set up the correct access control permissions before running the command. [Read more on certificate management for Luminesce providers.](/v1/docs/managing-certificates-for-luminesce-providers) ## Deleting your custom provider To remove a custom provider from the Luminesce catalog (which means it will no longer be available for end users to query), run your query with the `--deleteProvider` option. For example: ```sql @x = use Sql.Db.Mine --provider=CustomProviders.RetrieveInternalTransactionData --type=SqLite --connection="Data Source=./Tests/TestData/sakila.sqlite;" --deleteProvider ---- select 1 as deleting enduse; select * from @x ``` Note if using Oracle, you must supply a `from` for your `select 1 as deleting` statement. For example: ```sql @x = use Sql.Db.Mine --provider=CustomProviders.RetrieveInternalTransactionData --type=Oracle --connection="Data Source=;" --deleteProvider ---- select 1 as deleting from dual enduse; select * from @x ``` ## Appendix A: Allowing non-administrative users to download binaries Once a license is granted, users with the `lusid-administrator` role have permission to download any `SqlDb_Providers_*` binary out-of-the-box. To enable less privileged users to download, [create a feature policy](/v1/docs/how-do-i-create-a-feature-policy) consisting of the following JSON, assign it to a role, and the role to their LUSID user. Make sure the code in the policy matches the download binary name: ![](https://cdn.document360.io/d575ad81-c0ed-4980-bbd1-d59ac5c3de82/Images/Documentation/d05ee7ef-8e0b-478e-b604-3a0a2eb9a499.png) So the code for PostgreSQL is as follows: ```json {    "description": "User can download PostgreSQL binary",    "applications": [        "Honeycomb"    ],    "grant": "Allow",    "selectors": [        {            "idSelectorDefinition": {                "identifier": {                    "code": "SqlDb_Providers_Postgresql",                    "scope": "Honeycomb"                },                "actions": [                    {                        "scope": "Honeycomb",                        "activity": "DownloadBinary",                        "entity": "Feature"                    }                ],                "name": "PostgreSQL binary",                "description": "User can download PostgreSQL binary"            }        }    ],    "when": {        "activate": "2023-01-01T00:00:00.0000000+00:00",        "deactivate": "9999-12-31T23:59:59.9999999+00:00"    } } ``` ## Appendix B: Syntax for writing to different SQL database types The form required of your query to write to your SQL database can greatly vary by database type. The following table provides an example of the required SQL query form for each supported database type: | **SQL database type** | **SQL query example** | | --- | --- | | PostgreSQL | ```sql INSERT INTO my_table (#INSERT_COLUMN_NAMES) VALUES (#INSERT_COLUMN_VALUES) ON CONFLICT (name) DO UPDATE SET updated_at = CURRENT_TIMESTAMP, #UPDATE_COLUMN_ASSIGNMENTS { { name : name = excluded.name }, { email : email = case when (my_table.email like '%' \|\| excluded.email \|\| '%') then my_table.email else excluded.email \|\| ';' \|\| my_table.email end }, } WHERE #INSERT_COLUMN_CHANGED_FILTER ``` | | SQLite | ```sql INSERT INTO my_table (#INSERT_COLUMN_NAMES) VALUES (#INSERT_COLUMN_VALUES) ON CONFLICT (name) DO UPDATE SET #UPDATE_COLUMN_ASSIGNMENTS { { name : name = excluded.name }, { email : email = excluded.email }, } WHERE #INSERT_COLUMN_CHANGED_FILTER ``` | | DuckDb | ```sql INSERT INTO my_table (#INSERT_COLUMN_NAMES) VALUES (#INSERT_COLUMN_VALUES) ON CONFLICT (name) DO UPDATE SET #UPDATE_COLUMN_ASSIGNMENTS { { name : -- this line must be here to generate the correct SQL inserts as the pkey cannot be assigned in DuckDb }, { email : email = excluded.email }, } WHERE #INSERT_COLUMN_CHANGED_FILTER ``` | | Oracle | ```sql MERGE INTO my_table T USING (SELECT #INSERT_COLUMN_VALUES FROM DUAL) X ON (T.name = X.name) WHEN MATCHED THEN UPDATE SET #UPDATE_COLUMN_ASSIGNMENTS { { name : -- a comment of any form as no assignment is allowed but this must be present or name is unknown }, { email : T.email = X.email }, } ,updated_at = CURRENT_TIMESTAMP WHEN NOT MATCHED THEN INSERT (#INSERT_COLUMN_NAMES, updated_at, created_at) VALUES (X.name, X.email, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP) ----------------- -- This must be done manually once, then commented out as Oracle doesn't support CREATE TABLE IF NOT EXISTS -- CREATE TABLE test_table_4 ( -- name varchar2(50) NOT NULL, -- email varchar2(150) NOT NULL, -- updated_at TIMESTAMP NOT NULL, -- created_at TIMESTAMP NOT NULL, -- ) enduse; select * from @x ``` | | MySql | Coming soon | | Odbc | Coming soon | | SqlServer | Coming soon | | GoogleBigQuery | Coming soon | | AwsAthena | Coming soon | | Db2 | Coming soon | | Snowflake | Coming soon |