Excited to introduce the new SharpData .NET Core project which provides an instant UI around multiple RDBMS’s that I hope many of you will find useful:
Initially it started out as a demonstration to show how productive #Script can be as there are a number of tasks where a dynamic scripting language like #Script can provide far superior productivity then trying Type an entire code-base and all models with C#.
For example a single #Script page can provide a lot of the functionality in AutoQuery where it provides an instant HTTP API (in all registered ServiceStack formats) around all registered RDBMS tables, in all OrmLite supported RBDMS’s where it includes supports custom fields, multiple querying options, paging, multi OrderBy’s utilizing parameterized values and async APIs:
/db/_db/_table/index.html
The _ prefixes in the path utilizes #Script Page Based Routing allowing for dynamic Clean URL routes without needing to define & maintain separate route definitions.
The routes also support multiple RDBMS’s where the same script can be used to query all of your
registered multitenancy databases.
Instant Customizable RDBMS UI
The SharpData .NET Core project essentially provides a UI around this script to surface its features & give it instant utility. This ended up being so useful that I’ve switched to using it when needing to perform fast adhoc queries. Normally I’d reach for JetBrains DataGrip whenever I need a RDBMS Admin UI to manage RDBMS instances, but since I can easily configure which RDBMS’s & tables I’m interested in with a simple text file, easily customize its UI, utilize 1-click export into Excel I’m finding it much faster to find what I need in the many adhoc queries I commonly run.
You’ll need latest app dotnet tool to run SharpData in a .NET Core Desktop App:
$ dotnet tool update -g app
If on macOS/Linux you can use the x dotnet tool instead.
Quick Tour!
I’ll quickly go through some of its features to give you an idea of its capabilities, from the above screenshot we can some of its filtering capabilities. All results displayed in the UI are queried using the sharpdata #Script HTTP API which supports the following features:
Filters
All query string parameter except for db,fields,format,skip,take,orderBy are treated as filters, where you can:
- Use
=nullor!=nullto searchNULLcolumns - Use
<=,<,>,>=,<>,!=prefix to search with that operator - Use
,trailing comma to perform anIN (values)search (integer columns only) - Use
%suffix or prefix to perform aLIKEsearch - Use
=prefix to perform a coerced “JS” search, for exactnumber,boolean,nulland WCF date comparisons - Otherwise by default performs a “string equality” search where columns are casted and compared as strings
Here’s the filtered list used in the above screenshot:
/db/main/Order?Id=>10200&CustomerId=V%&Freight=<=30&OrderDate=>1997-01-01
Custom Field Selection
The column selection icon on the top left of the results lets you query custom select columns which is specified using ?fields:
Multiple OrderBy’s
You can use AutoQuery Syntax to specify multiple Order By’s:
Paging
Use ?skip and ?take to page through a result set
Format
Use ?format to specify which Content-Type to return the results in, e.g:
Multitenancy
You can specify which registered DB to search using the path info, use main to query the default database:
/db/<named-db>/<table>
Configure RDBMS from command-line
You can override which database to connect to by specifying it on the command line, e.g. here’s what I use to connect to the https://techstacks.io RDBMS:
$ app open sharpdata -db postgres -db.connection $TECHSTACKS_DB
Which will open SharpData listing all of its RDBMS tables. If you have a lot of tables the Sidebar filter provides a quick way to
find the table you want, e.g:
URL Schemes
Most of what can be done on the command line can be done from a custom URL Scheme, a feature I’m especially excited about as it means you can provide a dashboard of links to different Sharp Apps that anyone can access, especially useful as the only software that’s needed to run any Sharp App is the app dotnet tool which thanks to all ServiceStack .dll’s & dependencies being bundled with the tool, (including Vue/React/Bootstrap fontawesome and Material SVG Icon assets), the only files that need to be published is the App’s specific resources which is how Sharp Apps like SharpData can be compressed in a 20kb .zip which makes being viable to download the latest app each on each run, making it painless to distribute updates as everyone’s naturally running the latest version.
Shoud you need to (e.g. large Sharp App or github.com is down) you can run your previously locally cached version using run, e.g:
$ app run sharpdata
With Custom URL Schemes everyone with app installed can view any database they have network access to from specifying the db type and connection string in the URL:
app://sharpdata?db=postgres&db.connection={CONNECTION_STRING}
CONNECTION_STRING needs to be URL Encoded, e.g. with JS’s
encodeURIComponent()
My preference instead is to maintain each Connection String in Environment variables so I can specify the name instead:
app://sharpdata?db=postgres&db.connection=$TECHSTACKS_DB
Another exciting potential is that along with SharpData being downloaded and run on the fly, is also being able to take advantage of the mix support in the dotnet tools to also download another Gist’s content into the Sharp App’s working directory.
With this you can publish a custom dataset in an SQLite database save it as a gist and generate a URL that everyone can use to download the database and open it in SharpData, e.g:
We can use the user-friendly northwind.sqlite alias as it’s published in the mix.md
directory where it links to the northwind.sqlite gist. For your custom databases you use the Gist Id instead or if you plan to use this feature a lot you can override which mix.md document that app should source its links from by specifying another Gist Id in the MIX_SOURCE Environment variable.
But if you’re already mixing in an external gist you can also include a custom app.settings in the Gist and provide the custom RDBMS registrations and table list, e.g:
Which applies the northwind.sharpdata gist, which can also be referenced by Gist Id:
Alternatively you may instead prefer to publish it to a private GitHub repo instead of a Gist which you can load with:
app://user/sharpdata-private?token={TOKEN}
The app dotnet tools will use the latest published GitHub release if there are any otherwise will use the master.zip archive, so you could use this feature to maintain a working master repo and choose when to publish new versions of your custom SharpData App.
Custom App Settings
Each time a Gist Desktop App is opened it downloads and overrides the existing Gist with the latest version which it loads in a Gist VFS where any of its files can be overridden by providing a local copy.
As the App’s working directory is preserved between restarts you can provide a custom app.settings at:
%USERPROFILE%\.sharp-apps\sharpdata\app.settings
Where you can customize which RDBMS’s and tables you want to be able to access, e.g:
debug false
name Northwind SharpData
appName sharpdata
db sqlite
db.connection northwind.sqlite
db.connections[techstacks] { db:postgres, connection:$TECHSTACKS_DB }
args.tables Customer,Order,OrderDetail,Category,Product,Employee,EmployeeTerritory,Shipper,Supplier,Region,Territory
args.tables_techstacks technology,technology_stack,technology_choice,organization,organization_member,post,post_comment,post_vote,custom_user_auth,user_auth_details,user_activity,page_stats
Which
Open in Excel
Sharp Data detects if you have Excel installed and allows you to open the un-paged filtered resultset directly by clicking on the Excel button.
This works seamlessly as it “by-passes” the browser download where the query is performed by the .NET Core Server who streams the response directly to the Users Downloads folder and launches it in Excel as soon as it’s finished.
Custom Row Components
Whilst a tabular grid view might be a natural UI for browing a database for devs, we can do better since we have the full UI source code that’s built using Vue components. Whilst a filtered tabluar view makes it fast to find the record you’re interested in, it’s not ideal for finding quickly related information about an Entity.
To provide a richer more customized UX for different App users, SharpData includes the concept of “Row Components” which you can use to provide rich information at a glance for any record. In SharpData you can create them in /components/Custom, e.g. When viewing an Order, it’s natural to want to view the Order Details with it, which we can enable by registering a custom Vue component.
@Component({ template:
`<div v-if="id">
<jsonviewer :value="details" />
</div>
<div v-else class="alert alert-danger">Order Id needs to be selected</div>`
})
class Order extends Vue {
@Prop() public db: string;
@Prop() public table: string;
@Prop() row: any;
@Prop() columns: ColumnSchema[];
details:any[] = [];
get id() { return this.row.Id; }
async mounted() {
this.details = await sharpData(this.db,'OrderDetail',{ OrderId: this.id });
}
}
registerRowComponent('main','Order', Order, 'order');
All Row components are injected with the db, table properties, the entire row object that was selected as well as the Column Schema definition for that table. Inside the component you’re free to display anything, in this case we’re using the sharpData helper for calling the server #Script HTTP API to get it to fetch all OrderDetail entries for this order.
If the user filters the resultset and doesn’t include the Order
Idwe wont be able to fetch its referenced data so we display an error message instead.
The jsonviewer component used here is siimilar to ServiceStack’s HTML5 pages auto viewer to quickly display the contents of any object.
The registerRowComponent(db,table,VueComponent,componentName) API is used to register this component with Sharp Data to make it available to render any order.
With our component registered we can now drill down into any Order to view its Order Details:
You’re free to render any kind of UI in the row component, e.g. for Customer.ts
row component we render a richer view:
@Component({ template:
`<div v-if="id" class="pl-2">
<h3 class="text-success">{{customer.ContactName}}</h3>
<table class="table table-bordered" style="width:auto">
<tr>
<th>Contact!</th>
<td>{{ customer.ContactName }} ({{ customer.ContactTitle }})</td>
</tr>
<tr>
<th>Address</th>
<td>
<div>{{ customer.Address }}</div>
<div>{{ customer.City }}, {{ customer.PostalCode }}, {{ customer.Country }}</div>
</td>
</tr>
<tr>
<th>Phone</th>
<td>{{ customer.Phone }}</td>
</tr>
<tr v-if="customer.Fax">
<th>Fax</th>
<td>{{ customer.Fax }}</td>
</tr>
</table>
<jsonviewer :value="orders" />
</div>
<div v-else class="alert alert-danger">Customer Id needs to be selected</div>`})
class Customer extends Vue {
@Prop() public db: string;
@Prop() public table: string;
@Prop() row: any;
@Prop() columns: ColumnSchema[];
customer:any = null;
orders:any[] = [];
get id() { return this.row.Id; }
async mounted() {
this.customer = (await sharpData(this.db,this.table,{ Id: this.id }))[0];
const fields = 'Id,EmployeeId,OrderDate,Freight,ShipVia,ShipCity,ShipCountry';
this.orders = await sharpData(this.db,'Order',{ CustomerId: this.id, fields })
}
}
registerRowComponent('main','customer', Customer, 'customer');
Which looks like:
SharpData .NET Core Project
I want to point out that NetCoreApps/SharpData is just a regular ServiceStack .NET Core App with a Startup.cs and AppHost that can be developed, published and deployed as you’re used to, here’s an instance of it deployed as a .NET Core App on Linux:
https://sharpdata.netcore.io
We recommend running it locally for best experience as latency can be noticable from our Hetzner server in Germany
It’s a unique ServiceStack App in that it doesn’t actually use any custom ServiceStack Services since it’s just using pre-existing functionality already built into ServiceStack, #Script for its HTTP APIs and a Vue SPA for its UI.
It uses the same Vue SPA solution as vue-lite to avoid requiring npm where you only need to run TypeScript’s tsc -w to enable its live-reload dev UX which provides its instant feedback during development.
Some other special qualities it utilizes is instead of manually including all the Vue framework .js libraries, it instead references the new ServiceStack.Desktop.dll for its Vue framework libraries and its Material design SVG icons which are referenced as normal file references:
{{ [
`/lib/js/vue/vue.min.js`,
`/lib/js/vue-router/vue-router.min.js`,
`/lib/js/vue-class-component/vue-class-component.min.js`,
`/lib/js/vue-property-decorator/vue-property-decorator.min.js`,
`/lib/js/@servicestack/desktop/servicestack-desktop.min.js`,
`/lib/js/@servicestack/client/servicestack-client.min.js`,
`/lib/js/@servicestack/vue/servicestack-vue.min.js`,
] |> map => `<script src="${it}"></script>` |> joinln |> raw }}
But instead of needing to exist on disk & deployed with your project it’s referencing the embedded resources in ServiceStack.Desktop.dll and only the bundled assets need to be deployed with your project which is using the built-in NUglify support in the dotnet tools so you still get a highly optimized/minified bundle without needing to rely on any npm tooling:
<Target Name="Bundle" BeforeTargets="AfterPublish">
<Exec Command="x run _bundle.ss -to /bin/Release/netcoreapp3.1/publish/wwwroot" />
</Target>
The framework lib/js that are included are just the TypeScript definitions for each library which TypeScript uses for its static analysis & its great dev UX in IDEs & VS Code, but they’re only needed during development and not at runtime or deployed with your project.
Publish to Gist Desktop App
The other nice feature of this project is that it can be deployed as a Gist Desktop App, a feature I’m particularly excited up because it provides instant utility that makes it more suitable for a much broader use-case as a fast, lightweight, always up-to-date Desktop App & Windows integration all packaged in a tiny 20kb .zip footprint.
To run, test & publish it as a Desktop App you can use the pre-made scripts in package.json, where Rider provides a nice UX here as it lets your run each script directly from their json editor:
Essentially to package it into a Sharp App you just need to run the pack script which will bundle & copy all required assets into the /dist folder which you can then run in a .NET Core Desktop App
by running app in that folder.
The init-test script just copies an example northwind.sqlite database and sample app.settings so you have something to test it with if you need it.
The publish-app script is if you want to publish it to a Gist, you will need it to provide the GitHub AccessToken with write access to the Gist User Account you want to publish it to. Adding an appName and description to app.settings will publish it to the Global App Registry, make it publicly discoverable and allow anyone to open your App using your user-friendly appName alias, otherwise they can run it using the Gist Id or Gist URL.
Alternatively you can publish the contents of the dist/ folder to a GitHub repo (public or private) and run it with:
$ app open <user>/<repo>
If it’s in a private repo they’ll need to either provide an AccessToken in the GITHUB_TOKEN Environment variable or using the -token argument:
$ app open <user>/<repo> -token <token>
RDBMS Configuration
If running as a .NET Core App you’d just need to register which RDBMS’s you want to use with OrmLite’s configuration, e.g. the screenshot above registers an SQLite northwind.sqlite database and the https://techstacks.io PostgreSQL Database:
container.Register<IDbConnectionFactory>(c => new OrmLiteConnectionFactory(
MapProjectPath("~/northwind.sqlite"), SqliteDialect.Provider));
var dbFactory = container.Resolve<IDbConnectionFactory>();
dbFactory.RegisterConnection("techstacks",
Environment.GetEnvironmentVariable("TECHSTACKS_DB"),
PostgreSqlDialect.Provider);
By default it shows all Tables in each RDBMS, but you can limit it to only show a user-defined list of tables with #Script Arguments:
Plugins.Add(new SharpPagesFeature {
//...
Args = {
//Only display user-defined list of tables:
["tables"] = "Customer,Order,OrderDetail,Category,Product,Employee,EmployeeTerritory,Shipper,Supplier,Region,Territory",
["tables_techstacks"] = "technology,technology_stack,technology_choice,organization,organization_member,post,post_comment,post_vote,custom_user_auth,user_auth_details,user_activity,page_stats",
}
});
When running as a Sharp App you’d instead configure it in its app.settings, here’s the equivalent to the above configuration:
# Configure below. Supported dialects: sqlite, mysql, postgres, sqlserver
db sqlite
db.connection northwind.sqlite
# db.connections[techstacks] { db:postgres, connection:$TECHSTACKS_DB }
args.tables Customer,Order,OrderDetail,Category,Product,Employee,EmployeeTerritory,Shipper,Supplier,Region,Territory
args.tables_techstacks technology,technology_stack,technology_choice,organization,organization_member,post,post_comment,post_vote,custom_user_auth,user_auth_details,user_activity,page_stats
Feedback
Anyawy I hope SharpData serves useful in some capacity, whether it’s being able to quickly develop and Ship a UI to stakeholders or as a template to develop .NET Core Apps that you can distribute as Sharp Apps, as an example to explore the delivery and platform potential of URL schemes and install-less Desktop Apps or just as an inspiration for different kind of Apps you can create with it and areas where #Script shines.
As a side note whilst app is Windows 64 only, you can use the x cross-platform tool and its xapp:// URL scheme to run Sharp Apps on macOS/Linux, you just wont have any Window Integration features.
Happy to answer any questions or listen to any feedback on this thread.