App - Rockwind

The Rockwind website shows an example of combining multiple websites in a single Web App - a Rockstars Content Website and a dynamic data-driven UI for the Northwind database which can run against either SQL Server, MySql or SQLite database using just configuration. It also includes API Pages examples for rapidly developing Web APIs.

You can run this Gist Desktop App via URL Scheme from (Windows Desktop App):


Or via command-line:

$ app open rockwind

Cross platform (Default Browser):

$ x open rockwind


/rockstars is an example of a Content Website that itself maintains multiple sub sections with their own layouts - /rockstars/alive for living Rockstars and /rockstars/dead for the ones that have died. Each Rockstar maintains their own encapsulated mix of HTML, markdown content and splash image that intuitively uses the closest _layout.html, and splash.jpg from the page they're referenced from. This approach makes it easy to move entire sub sections over by just moving a folder and it will automatically use the relevant layout and partials of its parent.

Rockwind WebApp screenshot


/northwind is an example of a dynamic UI for a database containing a form to filter results, multi-nested detail pages and deep-linking for quickly navigating between referenced data. #Script is also a great solution for rapidly developing Web APIs where the /api/customers.html API Page below:

{{ limit ?? 100 |> to => limit }}

{{ 'select Id, CompanyName, ContactName, ContactTitle, City, Country from Customer' |> to => sql }}

{{#if !isEmpty(PathArgs)}}
   {{ `${sql} where Id = @id` |> dbSingle({ id: PathArgs[0] }) 
      |> return }}

{{#if id}}      {{ 'Id = @id'           |> addTo: filters }} {{/if}}
{{#if city}}    {{ 'City = @city'       |> addTo: filters }} {{/if}}
{{#if country}} {{ 'Country = @country' |> addTo: filters }} {{/if}}

{{#if !isEmpty(filters)}}
  {{ `${sql} WHERE ${join(filters, ' AND ')}` |> to => sql }}

{{ `${sql} ORDER BY CompanyName ${sqlLimit(limit)}` |> to => sql }}

{{ sql |> dbSelect({ id, city, country }) 
       |> return }}

Is all the code needed to generate the following API endpoints:

/customers API
All Customers
Accept HTTP Header also supported
Alfreds Futterkiste Details
As List
Customers in Germany
Customers in London
Combination Query /api/customers?city=London&country=UK&limit=3

Multi platform configurations

In addition to being a .NET Core 3.1 App that runs flawlessly cross-platform on Windows, Linux and OSX, Sharp Apps can also support multiple RDBMS's and Virtual File Systems using just configuration.


SQLite uses a file system database letting you bundle your database with your App. So we can share the northwind.sqlite database across multiple Apps, the contentRoot is set to the /apps directory which can only be accessed by your App, whilst the webRoot is configured to use the Sharp Apps folder that hosts all the publicly accessible files of your App.

debug true
name Rockwind SQLite Web App
contentRoot ..
webRoot .
db sqlite
db.connection ~/northwind.sqlite

To run the Rockwind app using the northwind.sqlite database, run the command below on Windows, Linux or OSX:

x app.sqlite.settings

To switch to use the Northwind database in SQL Server we just need to update the configuration to point to a SQL Server database instance. Since the App no longer need access to the northwind.sqlite database, the contentRoot can be reverted back to the Sharp Apps folder:

debug true
name Rockwind SQL Server Web App
port 5000
db sqlserver
db.connection Server=localhost;Database=northwind;User Id=test;Password=test;

The /support/northwind-data project lets you quickly try out Rockwind against your local RDBMS by populating it with a copy of the Northwind database using the same sqlserver identifier and connection string from the App, e.g:

dotnet run sqlserver "Server=localhost;Database=northwind;User Id=test;Password=test;"


You can run against a MySql database in the same way as SQL Server above but using a MySql db connection string:

debug true
name Rockwind MySql Web App
port 5000
db mysql
db.connection Server=localhost;Database=northwind;UID=root;Password=test;SslMode=none

The example Azure configuration is also configured to use a different Virtual File System where instead of sourcing Web App files from the filesystem they're sourced from an Azure Blob Container. In this case we're not using any files from the App so we don't need to set a contentRoot or webRoot path. This also means that for deployment we're just deploying the WebApp binaries with just this app.settings since both the Web App files and database are sourced remotely.

# Note: values prefixed with '$' are resolved from Environment Variables
debug false
name Azure Blob SQL Server Web App
bind *
port 5000
db sqlserver
files azure
files.config {ConnectionString:$AZURE_BLOB_CONNECTION_STRING,ContainerName:rockwind-fs}

# Reduces a Blob Storage API call, but takes longer for modified pages to appear
checkForModifiedPagesAfterSecs 60
defaultFileCacheExpirySecs     60

The /support/copy-files project lets you run Rockwind against your own Azure Blob Container by populating it with a copy of the /rockwind App's files using the same configuration above:

dotnet run azure "{ConnectionString:$AZURE_BLOB_CONNECTION_STRING,ContainerName:rockwind}"


As #Script is unable to use a Typed ORM like OrmLite to hide the nuances of each database, we need to be a bit more diligent in #Script to use parameterized SQL that works across multiple databases by using the sql* DB Filters to avoid using RDBMS-specific SQL syntax. The /northwind/customer.html contains a good example containing a number of things to watch out for:

{{#if id}}
    {{ `select o.Id, 
            ${sqlConcat(["e.FirstName", "' '", "e.LastName"])} Employee, 
            OrderDate, ShipCountry, ShippedDate, 
            ${sqlCurrency("sum((d.Unitprice * d.Quantity) -")} Total 
        from ${sqlQuote("Order")} o
            inner join
            OrderDetail d on o.Id = d.OrderId
            inner join 
            Employee e on o.EmployeeId = e.Id
        where CustomerId = @id
        group by o.Id, EmployeeId, FirstName, LastName, OrderDate, ShipCountry, ShippedDate`
        |> dbSelect({ id }) 
        |> to => orders }}

Use sqlConcat to concatenate strings using the RDBMS-specific SQL for the configured database. Likewise sqlCurrency utilizes RDBMS-specific SQL functions to return monetary values in a currency format, whilst sqlQuote is used for quoting tables named after a reserved word.

Of course if you don't intend on supporting multiple RDBMS's, you can ignore this and use RDBMS-specific syntax.

Rockwind VFS

/rockwind-vfs is a clone of the Rockwind Web App with 3 differences: It uses the resolveAsset filter for each .js, .css and image web asset so that it's able to generate external URLs directly to the S3 Bucket, Azure Blob Container or CDN hosting a copy of your files to both reduce the load on your Web App and maximize the responsiveness to the end user.

To maximize responsiveness when using remote storage, all embedded files utilize caching:

{{ "" |> includeFileWithCache |> markdown }}

The other difference is that each table and column has been quoted in "double-quotes" so that it works in PostgreSQL which otherwise treats unquoted symbols as lowercase. This version of Rockwind also works with SQL Server and SQLite as they also support "Table" quotes but not MySql which uses `BackTicks` or [SquareBrackets]. It's therefore infeasible to develop Apps that support both PostgreSQL and MySql unless you're willing to use all lowercase, snake_case or the sqlQuote filter for every table and column.

Rockwind VFS WebApp screenshot


If using a remote file storage like AWS S3 or Azure Blob Storage it's a good idea to use the resolveAsset filter for each external file reference. By default it returns the same path it was called with so it will continue to work locally but then ServiceStack effectively becomes a proxy where it has to call the remote Storage Service for each requested download.

<link rel="stylesheet" href="{{ 'assets/css/bootstrap.css' |> resolveAsset }}" />

<img src="{{ 'splash.jpg' |> resolveAsset }}" id="splash" alt="Dave Grohl" />

ServiceStack asynchronously writes each file to the Response Stream with the last Last-Modified HTTP Header to enable browser caching so it's still a workable solution but for optimal performance you can specify an args.assetsBase in your app.settings to populate the assetsBase ScriptContext Argument the resolveAsset filter uses to generate an external URL reference to the file on the remote storage service, reducing the load and improving the performance of your App, especially if it's configured to use a CDN.

Pure Cloud Apps


The AWS settings shows an example of this where every external resource has been replaced with a direct reference to the asset on the S3 bucket:

# Note: values prefixed with '$' are resolved from Environment Variables
debug false
name AWS S3 PostgreSQL Web App
db postgres
db.connection $AWS_RDS_POSTGRES
files s3
files.config {AccessKey:$AWS_S3_ACCESS_KEY,SecretKey:$AWS_S3_SECRET_KEY,Region:us-east-1,Bucket:rockwind}

# Reduces an S3 API call, but takes longer for modified pages to appear
checkForModifiedPagesAfterSecs 60
defaultFileCacheExpirySecs     60

With all files being sourced from S3 and the App configured to use AWS RDS PostgreSQL, the AWS settings is an example of a Pure Cloud App where the entire App is hosted on managed cloud services that's decoupled from the .NET Core 3.1 binary that runs it that for the most part won't require redeploying the Web App binary unless making configuration changes or upgrading the x dotnet tool as any App changes can just be uploaded straight to S3 which changes reflected within the checkForModifiedPagesAfterSecs setting, which tells the Web App how long to wait before checking for file changes whilst defaultFileCacheExpirySecs specifies how long to cache files like for.


Deployments are also greatly simplified as all that's needed is to deploy the WebApp binary and app.settings of your Cloud App, e.g. here's the DockerFile for - deployed to AWS ECS using the deployment scripts in rockwind-aws and following our .NET Core Docker Deployment Guideline:

FROM AS build
COPY app /app
RUN dotnet tool install -g x

# Build runtime image
FROM AS runtime
COPY --from=build /app app
COPY --from=build /root/.dotnet/tools tools
ENTRYPOINT ["/app/tools/x", "app/app.settings"]

We can also create Azure Cloud Apps in the same we've done for AWS above, which runs the same /rockwind-vfs Web App but using an Azure hosted SQL Server database and its files hosted on Azure Blob Storage:

# Note: values prefixed with '$' are resolved from Environment Variables
debug false
name Azure Blob SQL Server Web App
bind *
db sqlserver
files azure
files.config {ConnectionString:$AZURE_BLOB_CONNECTION_STRING,ContainerName:rockwind}

# Reduces an S3 API call, but takes longer for modified pages to appear
checkForModifiedPagesAfterSecs 60
defaultFileCacheExpirySecs     60

made with by ServiceStack