FHIR, Vanya Client

You have an idea for an app. What should you do on Day 1?

Let’s assume you’re happy with the app you’re about to build; that your idea has legs or at least the prospect of legs based on an itch you’re scratching or a gap that needs filling. (There’s an 800 page book in that sentence, but let’s not go there right now.)

How do you start? What do you do you on Day 1?

This is where I was last week. For a couple of months an idea for an app had been floating around in my head. It was to fill a gap I saw in the market for a particular type of developer tool that came to me after working on a greenfield project for a US healthcare company.

For two months I’d been sketching out features in my head and exploring the best tech to use to build the app. Three days ago I bit the bullet and spent a long afternoon implementing the early steps, the steps that while not required on Day 1, have real value down the road when put in place at the start.

I’ve built apps in the past without putting these early steps in place, releasing 6 months of work to an unsuspecting world. No audience, no potential users waiting for version 1, no one in a position to tell their friends or colleagues about it.

It was a wasted opportunity that set me back months in terms of exposure and feedback.

So here’s the afternoon’s work designed to prevent that from happening:

1. Designed a basic logo
I’m not a graphic designer. My logo design skills are almost non-existent, but Canva makes it easy to throw some text up next to an image and call it a logo.

My aim here was rudimentary branding such that anyone in the space I was targeting would recognise the app as belonging to that space. The fire image is recognisable as part of the FHIR database space — which is where my target user lives.

SQL on Fire logo

2. Built a one-page website that described the app

Very simple and very basic. I described in a few sentences what the app will do and who it’s for. I can now link to the site from anywhere — Twitter, LinkedIn, etc. While the app doesn’t exist yet, its shadow does in the form of a website.

I used a basic theme that I’d already purchased for multi-site use. No coding was required.

https://sqlonfire.com/ — “Query your FHIR database using SQL”

3. Added a ConvertKit sign-up form to start gathering email addresses

This is the most important section of my new one-page site, and arguably the sole reason the site exists at such an early stage. I want to start collecting email addresses from potential users and interested parties so that when I have a pre-release version I have someone to show it to. If I need feedback I’ll have people in the space to ask. When I go live, there’ll be at least a small audience waiting for it.

No more releasing into a void.

ConvertKit make creating and managing a sign-up form quick and easy. They handle opt-in emails, GDPR regulations, list management, etc. And it’s all free until you hit 1,000 email addresses.

4. Hosted the site

Hosting a one-page site does not require deep thought or careful planning. You don’t need AWS or Azure, you don’t need Cloudflare or Digitalocean droplets. All you need is somewhere to drop a static HTML page.

Keep it as simple as possible.

When the time comes to release your fully featured app, complete with online documentation and payment options, that’s the time to think about where best to host the site so that it can handle traffic spikes and all the complex bits you might have added to it.

I have a dozen legacy websites hosted on a UK server that I pay £100 a year for. Adding another site didn’t cost anything and took about 5 minutes.

Changing the DNS to point to the new host took another 5 minutes, at which point my site was live.

5. Added a free Let’s Encrypt SSL certificate to the new site

Another reason to go with the quick and easy shared hosting mentioned above — a one click install for Let’s Encrypt so that the site has a valid SSL cert. I tried doing this on Azure back in February for a new site I was building and wasted a good hour playing around with Let’s Encrypt before abandoning it and buying a cert instead.

You need an SSL certificate on your site, otherwise Google will penalise you and your visitors will see browser warnings that might turn them away.

6. Set up email on the new domain

My email accounts are all with Fastmail, a paid for email provider that handles multiple domain names and wildcard email addresses. Setting up my new email addresses for the new domain took about 10 minutes.

There were a number of steps involved, such as configuring the MX and SPF records, but it’s nothing I hadn’t done before.

As I already had a Fastmail account, it didn’t cost me anything.

The new one-page site is live now at https://sqlonfire.com/, complete with unattractive logo, brief app description and email sign-up form.

All the steps outlined above took about 4 hours to complete in a single afternoon and cost me $0. Today I’m writing code — starting work on the app itself. But I’m confident that when I have something ready to show down the road I won’t be scratching my head and looking around for users to show it to.

To hear more from me, you can follow me on Twitter or on LinkedIn.


Developers need better FHIR tools

Not long ago I worked on a greenfield project for a healthcare company. I was building the API that sat between the UI screens and the backend FHIR database, hosted on one of the larger cloud providers.

SQL on Fire

It was my first exposure to FHIR.

I’ve worked with databases of all kinds: SQL Server, Postgres, MySql, Sqlite, even DB2 back in the good old days. But this was my first encounter with a database that can only be accessed via an API.

If you’re unfamiliar with FHIR, it’s an open source data format for storing healthcare data. All the big cloud providers and a host of smaller companies provide FHIR database servers accessible via a commonly structured FHIR API. It’s growing in popularity. If you’re building a new app or starting a new project in the healthcare space, someone on your team or in the business will suggest using FHIR.

One of my first questions back in the early weeks of the project was: “Is there a desktop client I can use to access FHIR or do I have to use Postman?

Postman it was.

This may have been followed by “I can’t believe there’s no desktop client I can use to query FHIR!

For those unfamiliar with Postman, it’s the most popular API platform used to build and access APIs. It’s super easy to use, free for the most part, API agnostic and requires little in the way of technical know-how to get by.

But it’s generic, not optimised to work with any particular API or type of API over another. So while it works well as an API access tool, it’s limited as a FHIR database access tool.

I wanted a desktop client.

I work as a .NET contractor and I move around a lot: different companies, different industries, different problems with different solutions. When each contract ends I do a retrospective of my time there, asking myself questions such as: “Did I learn anything here that I can use?”, “Did I encounter anything this industry needs that is missing?” and “Can I take anything from my time here and build a product from it?

I was on a flight into Dublin airport last week when I did the retrospective on this particular contract. Usually I come up with lots of ideas but they tend not to be ideas with a commercial leaning, as in: interesting, but no one will pay for that as the pain isn’t big enough.

Not this time. My mind and my pen went right back to week one: “Is there a desktop client I can use to access FHIR or do I have to use Postman?

There should be.

Sure, it’s an API not a database. I might have to call it something else instead of a database client. But Postman is not a good enough tool for accessing FHIR APIs, not for developers building products and apps on top of FHIR. Developers want something more SQL-like. They need to be able to write a query not make a GET request in Postman.

When you’re building a new app or debugging Production, you want to move fast. You don’t want to be fiddling around with Postman and manually skipping through pages of JSON results to find what you’re looking for.

There are tools out there to help FHIR developers. There’s a .NET sdk provided by a Dutch company. There are CLI tools sitting on top of that sdk. There are companies providing FHIR servers who have their own tools exposed for use with their servers.

But I wanted a SQL-type desktop client that I could use against any FHIR API. Somewhere to build and store common queries that I could save and share with the team. An easy tool that allowed me to drill down into the FHIR data by clicking on Resource identifiers.

I wanted a cross platform desktop client for accessing FHIR APIs on Azure, AWS, Google and other FHIR servers. It doesn’t exist but it should.

Maybe I’ll build it.

To hear more from me, you can follow me on Twitter or on LinkedIn.


Your app doesn’t need to be perfect on Day 1

We knew that Google was going to get better every single day as we worked on it, and we knew that sooner or later, everyone was going to try it. So our feeling was that the later you tried it, the better it was for us because we’d make a better impression with better technology. So we were never in a big hurry to get you to use it today. Tomorrow would be better.

~ Sergey Brin, talking to Seth Godin in The Dip

.Net Core, QuikPub, Solo Developer

From Idea to MVP to Hacker News in 44 Hours

Unfinished side projects are the bane of a hacker’s life. How many incomplete apps are languishing in forgotten folders, 80% finished but destined never to see the light of day?

It’s the Dev equivalent of writer’s block. We love working on the interesting and the challenging parts, but it’s that final 20% that kills us. The mundane and the dull — the bits at the end that make all the difference between code that runs locally and a published app that anyone can download or use. It’s the difference between a side project and a product.

But writer’s block is not real. It’s a state of mind. The writer can choose to move past it by showing up every day. And just like the writer, the developer can choose to plow through that final 20% that leads to release day. You just have to know how close you are to the finish line.

We’re inundated with release stories that make it all look easy, and we wonder what we’re doing wrong. Version 1 of Stripe took two weeks to build. Young devs fresh out of college post to Hacker News about apps they built over the weekend. What are they doing that we’re not?

They’re releasing. Shipping. Publishing.

The idea for my latest app came to me on Saturday, January 23rd. It went live on Azure twelve days later after forty four hours of development. Two days after that I posted it to Hacker News.

This is a breakdown of how I progressed the app each day — moving one step closer to release every single day. I wasn’t burning the midnight oil. A few hours a day in between other projects. It was all very calm.

Days 1 and 2: The Idea Phase [2 hours]

My idea was to build Imgur for text. A simple and quick way for anyone to post and share rich text online — all without needing to set up a website or open an account. Rapid-fire and barrier-free publishing.

I spec’d out a list of features required for an MVP that I called Stage 1, then added a host of potential additional features that might lead to a paid plan at Stage 2 and a corporate plan at Stage 3. I don’t write code for free and consider it important that everything I build has at least the potential for turning into revenue down the road.

On Day 2 I discovered Pastebins and realised my new idea wasn’t so new after all. But I decided (rightly or wrongly) that offering rich text editing and a distraction free UI was unique enough to proceed to the development stage.

Day 3: Decisions [2 hours]

Monday afternoon I fleshed out the technology I’d be using to build the app. Choosing to use the newest and shiniest frameworks is a common mistake when devs start working on side projects. What should take one day takes five; what should be problem free becomes a can of worms as you spend more time learning and battling the technology than you do building the app.

I went with boring and simple and decided to use what I was already familiar with. .Net Core and MVC for the bones of the app with a DevExpress component I’d used before for the HTML editor, all hosted on Azure and stored in a SQL server database.

Boring and simple.

Next, I needed a catchy name and a short URL. The list below gives some indication of the direction my mind was moving before I landed on QuikPub.co. I needed a domain name that was available and global, and an app name that didn’t already have a strong Google presence (Technically .co domains are not global, but in practice they are).

Potential Names

I discounted QwikPub after finding an urban dictionary reference that suggested the word Qwik had sexual connotations in some circles, and decided against anything with Writer in the name as that was suggestive of a creative writing app rather than a publishing app. Decision made, I parked buying the domain for a couple of days in case I changed my mind.

Day 4: Building the UI [5 hours]

The core feature of the app was the rich text editor, so it had to be robust and user friendly. I decided to use a DevExpress component which was based on the Quill editor. My reasoning here was that I already had a license for their component suite, and my previous experience with DevExpress showed that support issues and questions were answered quickly — something that would come in handy on Day 10.

HTML Editor

The bulk of Day 4 was spent getting the HTML editor configured and working correctly, ensuring it was displaying the buttons I wanted it to and that everything was being posted to the backend without issue.

Day 5: The Database & the Backend [4 hours]

Nothing too difficult or complex here. I wrote a script to create the required tables in SQL Server and hooked it all up to the app using the latest version of Entity Framework in .Net Core. I was working with a local instance of SQL Server at this time as I hadn’t yet set anything up in Azure — that would all come later.

Before signing off for the day I bought the QuikPub.co domain name. One year only, as who knows if the app will be around in twelve months.

Day 6: Polishing the Backend [8 hours]

This was my only full working day on the project and it was spent putting all the pieces together and making sure they flowed correctly.

The day involved fleshing out the service and repository layers of the app, putting everything in place to allow for creating and editing QuikPubs. All very mundane and nothing any dev hasn’t done a hundred times before.

I built the View page — a light weight page that did little more than render the rich text entered by the user. It was accessible via a short URL, which was a bit tricky to get working in .Net MVC, as the usual routing recognises the short URL as an unknown controller class. I got around this by implementing a “catchall” controller action.

The Logo

By now I knew I was a few working days away from going live, so I needed to get moving on the logo design. In the past I’ve used 99designs, spending a few hundred Euro a time, but this was a rough and ready MVP that might or might not have a future so I elected to go quick and cheap with a design team over on fiverr.com.

I gave them a few examples of logos I liked along with a preferred colour scheme, paid $75 and then put it out of my mind. They had a three day turnaround which would put the logo delivery at around the time I’d be polishing the UI. Perfect.

80% Complete — The Danger Zone

This is what I call the 80% stage, when most side projects come to a halt. Everything works — kind of — but there are outstanding issues that need fixing and a lot of little things that need to be done before you can release your app.

The next few days illustrate what’s required to go from side project to product, from functional code to releasing an app. Without the next few days, all you’ve got is code, code that only you have seen and run.

If your side project was intended to be anything more than a code learning exercise, it doesn’t count until you plow through the next few days and release it.

Day 7: Page Flow [3 hours]

This was a follow on from the previous day, fixing some issues with page flow caused by the catchall routing. Once I had that fixed and working I added in a delete option so the user could delete as well as edit an existing QuikPub.

The DevExpress component suite I was using had some attractive pop-up messages built in, so I added these to the flow. By end of day everything was working and looked pretty good.

Day 8: Unique ID Generation [6 hours]

Each user post generates and stores two unique string IDs that allow viewing or editing a post. Up to now I’d been using a few made-up IDs that I’d hard coded. The time had come to automate this.

I went backwards and forwards on how to generate these IDs over the course of the day before settling on a 10 character string for viewing the post and a 32 character string (not a guid) for editing.

Decision made, I had to update the database structure and accompanying data objects, as well as make changes to the catchall controller method to route everything correctly.

Short URLs

Day 8 was a Saturday but it was raining out and we were still avoiding restaurants due to rising case numbers so I didn’t mind working. Towards the end of the day I set up the project in Azure Devops and made my first code check in. Azure Devops integration with Visual Studio is flawless, so this only took ten minutes.

Day 9: Website Theme [4 hours]

The logo arrived that morning. It was ok, but not great. I can live with it for now but if QuikPub proves to have legs I’ll have to get something more professional designed.


I picked up a bootstrap theme called Square the previous day, as one of the landing pages looked perfect for QuikPub’s home page. Up to then I’d been working with a bare bones .Net page, with the HTML editor as the sole item on the page. Now I had to flesh that out with explanatory text so that users landing on the website knew what the app was and what they could do with it.

I spent most of Day 9 getting the theme in place with its default lorem ipsum text above and below the HTML editor, and changing the css to match the colour scheme of the logo. The Publish image used at the top of the page came from DepositPhotos and cost about a euro.

Day 10 : Bug Fixing and Content Writing [5 hours]

I spent a couple of hours writing the content for the Home page that morning. Getting this right is important. If a user has to ask what your app does, then your message is failing. Clear and simple English.

I added some QuikPub examples at the bottom of the page containing blog posts I’d written and published over the past year — good enough to show what was possible with a QuickPub.

Home page

Most of the day was spent trouble shooting a jQuery issue caused by a conflict between the DevExpress components and the Square UI theme I was using for the website. Basically, none of the DevExpress components were initialising correctly, with errors popping up in the console on each page load.

I’m not a UI developer and my javascript isn’t particularly strong, so I opened a ticket with DevExpress support and sent them a sample project illustrating the problem. They came back to me within an hour with code samples showing how to initialise their components without using jQuery.

It worked. No more time wasted.

This is a perfect example of where paid-for components out perform free open source components. Try getting a one hour turnaround for support on something you’re not paying for!

Day 11: Release Day [5 hours]

I was ready to go live. Everything was working, the page content was all there, colour schemes and images were all chosen and in place. But there were a few little items that needed to be finished off, as there always are with any project just before it goes live.

I needed a small Admin area so that I could keep an eye on things in case they got out of hand. But the app had no user accounts, so how and where would I build that admin page?

I went with obscurity over security. A quick and dirty Admin page accessible via a URL that could not be guessed, coupled with a requirement for a random cookie to be pre-set in the browser. It’s not fool proof and anyone with access to the C# code in the backend could get around it by identifying the cookie, but it was good enough considering the app was not storing any private or sensitive user data or information. It took a couple of hours to put in place.

Around midday on Wednesday, 3rd of February, I created the new App Service in Azure and a new Azure SQL database. I spent about an hour trying to get Let’s Encrypt working for the app service SSL cert, all to no avail. There are a host of long form articles written about how to do this, as well as plugins to help you along, none of which worked.

Microsoft doesn’t support Let’s Encrypt out-of-the-box, so every implementation is a workaround and it looks like they break frequently. In the end I’d had enough. I wanted to launch that day, not waste any more time on Let’s Encrypt, so opted to pay for the SSL cert via Azure. It took 10 minutes and about €50 to get it all working.

The final step was to set up the support email address on the QuikPub.co domain and point it to my Fastmail account. This involved a few DNS changes and took another ten minutes. I have multiple domains feeding wildcard email addresses into a single Fastmail account — well worth the money.

I was ready to deploy.

A final code check-in with a suitably celebratory comment and I published direct from Visual Studio to my new app service. As a first deployment it took about three minutes, and the home page opened without issue.

I had a couple of problems with database access, outlined here in the first ever production QuikPub: https://quikpub.co/3K5MIPZGWP

There was one last loose end to tie up — screenshots on the home page that showed examples of short URLs , all pointing to localhost. I had to create some test posts on production and re-take those images, then re-deploy. The same with the sample QuikPub links on the home page.

And that was it. I spent half an hour testing the new app on production, then called it a day.

There was no post to Hacker News, no Twitter or blog entry, no email to my mailing list. Experience has taught me that it’s useful to let things sit for a day or two before telling the world. Something always comes up — a bug or a spelling mistake that needs fixing or a change you want to make.

Day 12: Show Hacker News

It was on Friday afternoon, February 5th, after forty four hours of development that I posted to Hacker News. For anyone unfamiliar with Hacker News, it’s THE social news site for developers. They have a Show section that allows developers or start-ups who’ve built something new to post it and talk about it on the site, where it then moves up or down the rankings in a Reddit-like fashion.

Dropbox, Stripe and many other mega start-ups began their public life on Hacker News. It’s a big deal and can lead to a lot of early exposure, feedback and page loads.

Here’s the post: https://news.ycombinator.com/item?id=26035551

QuikPub spent about a day near the top of the Show page and a similar time near the bottom of the main page. Not a spectacular result but good enough to lead to 5,000 page loads, over 600 new QuikPubs created and a host of comments on the app itself. The comments were particularly useful in tracking down and fixing HTML sanitization and css issues, while the 600 new QuikPubs helped me track down some backend bugs.

Feedback was light when it came to the functionality of the app itself, with not a lot of enthusiasm being shown. Ideally I would have liked to have seen more comments on the app and how it might be used. But that in itself is feedback, which I’ll need to ponder on.


There’s nothing particularly difficult about anything I did over the course of those forty four hours. Any developer with a little experience could have done the same. A strong UI developer could probably have shaved 10–15 hours off that time frame.

What makes it different from so many side projects is that it got as far as release day. By end of Day 6 most of the heavy code had been written and most of the problems had been solved. But it was those final few messy days of bug fixing, content writing, colour picking and production setup that made all the difference.

They are what made QuikPub a releasable app.

To hear more from me, you can follow me on Twitter or on LinkedIn.


Working From Home When Home is an Island in the Sun

I’m a software developer living and working in Dublin, Ireland. Except I’m not.

For the past three months I’ve been living on the Canarian island of Lanzarote, 100 kilometres off the North African coast. My job is the same as it always was: writing code, building new features into the company’s SaaS app, going to meetings, writing documentation — all the usual dev tasks that you’ll encounter at any startup anywhere.

Three months ago, as the world was tumbling into its Covid nightmare, myself and my girlfriend took a long weekend break in the sun. A couple of days after arriving the Spanish government initiated one of the strictest lockdowns in the world and we had a choice to make: stay on this sunny island as all the tourists fled home, or return to a cold and wet Dublin where the virus was taking hold in earnest.

We chose to stay. And we didn’t tell anyone.

There were only a handful of cases on the island. Villas up and down the coast were empty. The supermarkets that fed hundreds of thousands of tourists and residents were spacious and well stocked.

We abandoned our holiday hotel and spent a day finding a spacious three bed villa by the sea. The WiFi was fast enough to handle Teams and Zoom calls and the swimming pool out front was cool and inviting.

My office in Ireland had gone remote the previous week and I’d lugged the company lap top along with me. I was good to go. My girlfriend was in a similar boat. With face-to-face meetings cancelled, her working day is spent on the phone or in online meetings.

It hasn’t all been rosy. The eight week lockdown before things began to open was rough. The community spirit was absent as we had no Irish TV to tune into each night and neighbours were few and far between. Most of the surrounding villas were empty, and the once bustling promenade was void of people.

But the negatives were far fewer than they would have been in Dublin. The sun was shining every day. We weren’t locked down in a small apartment six floors up. The supermarkets were quiet and the sea breeze kept us cool each afternoon as we took a break from work and lay out by the pool. We had little fear of contracting the virus.

Once the lockdown began to lift, a small number of cafés and restaurants started to open their outdoor terraces and life began to get back to a kind of normal. Despite most of the tourist businesses in town being closed, enough local eateries were open with outdoor areas that we could have a coffee or a beer on the promenade each day.

This experience has been eye opening for us.

We’re both paying rent on two expensive apartments in Dublin — recently identified as the most expensive city to rent in in Europe. We’re both used to getting stuck in traffic each day as we travel to and from work or meetings. Winter flu season in Ireland is a nightmare of office sickness that has ruined many a Christmas and New Year holiday for us.

Why do we do this to ourselves each year?

Why spend a fortune renting in Dublin when we could live a less stressful and far more affordable life in a sunnier climate? Why put up with all that sickness each Winter when we could spend those dangerous months walking by the sea and eating in outdoor restaurants, where the possibility of infection is so much less? Why put up with Dublin traffic when we can do our work just as well from a sunny island?

We get so used to the lives we live day to day and year to year in Ireland that we assume it’s the only life we could live. If there’s one thing that this crisis has demonstrated, it’s that for many workers location is not important. Face-to-face meetings are not as crucial as we once assumed. Bums on seats is an outdated policy that has no place in a modern company.

There’s been a lot of talk in recent weeks about the long term changes that the Covid outbreak could bring to working and living. That workers could soon be abandoning expensive cities like Dublin in favour of more affordable towns across the country where the quality of life would be better and the costs so much less.

No long commutes. Kids who see their parents at normal hours of the day. Spacious houses instead of cramped apartments. It all sounds so appealing.

But if you’re of a more adventurous nature, why stop at abandoning the city for a town an hour or two’s drive away? Why not look to more distant locations such as the Canary islands, southern Spain or France, or even more exotic locations in the far east?

Working from home has always been common for tech workers like me, but the Covid crisis has accelerated its uptake and pushed it mainstream. What might have taken ten or twenty years to spread has now done so in the space of a couple of months.

Once you do the work, why should your employer care where you park your lap top? A desk at the end of the office, your home in South Dublin, a small village on the west coast of Ireland, or a far off villa on a Spanish island.

The work is still the work, and the Teams meetings are still Teams meetings.

Gryphon SQL, Solo Developer

Excel to SQL — Bridging the Gap Between Business Analysts and Developers

The Problem

Business analysts and developers work with data in different ways. By and large, analysts work with Excel spreadsheets output from company applications, from Power BI reports, or from CSV extracts provided by developers. The developers themselves tend to be more comfortable accessing raw data via SQL queries.

The difficulty with this divergence is that it’s often the business analysts who decide that selected data records need to be updated, and it’s then the job of the developer to perform that update.

The problem then becomes how to update a SQL database from an Excel file that was manually created or edited by an analyst. The developer has a few options:

  • She can train the analysts to produce a file formatted in just the right way and write a common script to parse that file (not practical when different data is being updated in different ways).
  • She can take a crash advanced course in Excel to try and work out how to extract SQL from rows of data (possible for simple INSERTS, but not for complex scripts).
  • She can write one-off custom code to produce the SQL script required. Different code for each update.

For anything more complex than a simple INSERT script that mirrors the Excel file, the developer is likely to take the more time consuming and costly option of writing custom code.

I’ve encountered this problem repeatedly in many different companies over the past 10 years. Small eCommerce operations, large multi-national financial services companies, and the SMEs in between. Even large government agencies are not exempt.

Existing Solutions

Google “Excel to SQL” and you’ll find a host of free and commercial solutions to this problem. But none of them are robust solutions capable of solving genuine business problems and use cases. They work if you’re playing with student or hobby projects, when all you want to do is take a CSV file and create one INSERT statement per row.

But that kind of simplicity rarely exists outside the classroom. Here’s an example of a more common use case, where business analysts worked with user data of an Enterprise App that was undergoing a major improvement. An update script was required for each user that met certain criteria.

  • An INSERT record to a separate table for dormant users, followed by an UPDATE to a core user table.
  • An UPDATE to connect each user with a newly created companies table, if the user email exists and if the company name exists in the database’s new Company table.
  • A bulk update of roles assigned to a user — again, only if that user’s email address exists. The bulk update might include a DELETE followed by a series of INSERTS for the user.

Individually, there’s nothing overly complex here. The complexity lies in generating a single SQL script based on the Excel file that contains the changes required. The script generator would need to read each row, check the contents of specific columns for certain values, and generate one of many scripts based on what it finds, inserting values from other columns into the script automatically.

This is far beyond the scope of any “Excel to SQL” SaaS app you’re likely to find from a Google search. Which leaves the developer with custom code, every time.

I first encountered this problem years ago and went looking online for a solution. It seemed to me that if I was having this problem, so too were other developers. I was expecting to find an off-the-shelf app or library that allowed me to open an Excel file, tweak a few parameters, enter some SQL and click a button to generate my script.

I was mistaken. No app. No .NET library. Not even an over priced SaaS product that would help me.

My Solution — Gryphon SQL

This is the problem that I’m trying to fix with Gryphon SQL [1]. It functions as a text replacement tool that combines Excel file parsing, conditional checks on row and column data, and smart SQL-aware string replacement. At its core is the ability of developers to generate a custom SQL script based on their own SQL template of one or many queries.

It can handle the above User data example with relative ease, generating a series of scripts to UPDATE dormant records, DELETE and then UPDATE roles for a user, and UPDATE user records to apply company IDs based on the supplied string values of “Email Address” and “Company Name”.

It’s early days for Gryphon SQL. With Version 1 I was very much scratching my own itch, building a tool that met my own unique requirements and use cases. Version 2 will be a more fully featured app, ready for prime time use by developers everywhere.

Oh, and a website. That would be good too. 🙂

[1] More about Gryphon SQL.

To hear more from me, you can follow me on Twitter or on LinkedIn.

Solo Developer

From Side-Project to Alpha Release — What Developers Can Learn from Writers

“Ideas are easy, implementation is hard.” Does this sound familiar?

If you read the same blogs as I do, hang out on the same websites, listen to the same podcasts, you’ll have heard this gem a thousand times. But rarely are we offered advice on what to do about it. How do you get from idea to product? How do you get through that implementation phase and see your app released to market?

So many side-project developers hit the coding equivalent of writer’s block. We have the idea, we know how to write code, but we can’t seem to progress our app. Disparate bits of code, and Word documents filled with feature lists languish in folders called ‘Ideas’ or ‘Projects’. They sit there as a constant reminder of our failure to see things through, perpetually signalling to us that we’re not one of those people; that we’re not Mark Zuckerberg, Jack Dorsey, or Stewart Butterfield.

The American writer Anne Lamott wrote a great book called Bird by Bird. Subtitled “Some Instructions on Writing and Life,” it offers up pearls of wisdom that are as well suited to the solo developer building an app as they are to a writer beginning work on a novel. Her two most helpful pieces of advice are: Short Assignments and Shitty First Drafts.

Short Assignments

Just like a writer is not writing their whole novel in one sitting, you’re not building your entire app today. You need to focus on one small thing, not the entire app with all its dependencies, complexities, and unknowns.

As you start out, there are a host of things you don’t know, problems that have to be solved that you haven’t even begun investigating. You’ll need to work with technologies you’re unfamiliar with, build layers on top of layers into your code, interact with third party services that you know are going to be buggy or difficult to understand. You’ll have a feature list as long as your arm, a vision in your head of a polished UI, and a fear of releasing something sub-standard that gets you mocked on Hacker News.

Anne Lamott’s advice to writers in a similar situation is to focus on something small when they sit down in front of their keyboard. A paragraph where one character meets another, a setting introducing a small town. No more. Forget the novel, the grand themes, the huge scope of what you want to achieve. Today, the writer’s job is to write that one paragraph.

For the developer, when you sit in front of your screen your job too is to build one thing, and to keep that one thing as small and as simple as possible. Today, a database schema with a few interacting tables. Tomorrow, an API with a single endpoint that returns data from that database. The next day, a page in your app that calls your API and displays that data.

One small step at a time. No need to investigate authentication at this stage. Park that for later. Ditto for building your database on Azure or AWS. Keep it local and keep it simple. This way you achieve something every day. You progress your app each time you sit down to work.

It’s the slow accumulation of all these short assignments that grows your side project from idea to fully implemented product. One small step at a time.

Shitty First Drafts

A published and acclaimed author presents an almost picture-postcard image to the world. That image is often of a great writer who writes poetic or flowing prose, captivating characters, and engrossing novels. But it rarely starts out like this. Not for Hemingway, not for John Grisham and not for JK Rowling. First there was the shitty first draft. Then the second draft that was better but not quite there yet, and so on until a polished novel appears on the shelves of the local bookstore.

If a writer doesn’t finish a first draft, they’ll never finish a second or a final draft. The same goes for every developer working on a side project in the evenings and weekends. Allow yourself the luxury of creating an imperfect and clunky alpha version of your app. Don’t be the perfectionist who writes some great modules of code but never releases a version 1.

You’re working in isolation with no eyes on you. It’s more important that you get that early version finished and released than it is that every line of code is optimised and every feature immaculately constructed.

Your early users will be few and far between. As you grow your app you’ll have time to edit and improve: to polish the code, refine the UI, streamline the user experience and add richer features. If you try to make it perfect in every way before you release that first version, or if you stop work because you don’t think you have what it takes to build a great product, your app will never see the light of day.

Facebook, Twitter and Slack were not built overnight. However polished they looked when they were in their prime, there was a shitty first draft. If your early versions are messy, that’s ok. You can clean up the mess later. And who knows, inside the mess you may discover the soul of your app, that special something that makes it truly unique and yours.

To hear more from me, you can follow me on Twitter or on LinkedIn.

.Net Core, DevExtreme, Vue.js

How to build a Vue.js application using DevExtreme UI components and a .Net Core API

I’m a backend developer with little experience of the frontend. My JavaScript skills are basic at best. A new app I’m working on requires a polished and maintainable UI, which lead me to investigate the top three frameworks of the day: React, Angular and Vue. I chose Vue as the learning curve seemed smallest, and after watching a couple of Pluralsight courses felt confident enough to make a start. [1]

DevExtreme [2] is a full JavaScript component suite from DevExpress. I’ve used DevExpress desktop components in the past and have always been impressed with the polished and professional look and feel as well as with the ease of integration and vast library of sample code. DevExtreme looks to be made in the same mold, with code samples for each component in all three frameworks mentioned above.

The sample project I’m going to walk through includes a new Vue (2.6) application using the DevExtreme Treelist component, pulling data from a .Net Core (3.1) backend API. It’s a bare bones, getting started app, with no authentication, designed to demonstrate the power of off-the-shelf components like DevExtreme and how easy it is to incorporate them into your Vue app and to populate them from a .Net Core API.

1. Creating the Vue app

The first step is to install NPM and the Vue CLI if they’re not already installed. NPM comes as part of the Node.js installation. [3] Once installed, open a terminal and run the following command to install the Vue CLI:

npm install -g @vue/cli

To create the Vue app, we’re going to use the Vue Project Manager. To launch the Project Manager, run the following command from a terminal:

vue ui

The Vue Project Manager is a simple app designed to create and manage Vue projects. It handles project creation and configuration, dependencies and plugins, and build tasks. I created my new project inside my “C:\Vue Projects” folder, and called it “Employees”.

On the presets page, choose Default Presets. We’ll start off with the basic set of dependencies and plugins and add to them later as required. Clicking Create Project will create the new project inside the selected folder. It can take a couple of minutes to complete as a large number of dependencies are downloaded and installed. Once complete, you’ll see the Project Dashboard for your new project. We’ll come back to this later.

2. Building and running your new Vue app

I’m using Visual Studio Code for my Vue apps. As a backend .Net developer I’m more familiar with Visual Studio, but find VS Code faster and easier to work with for Vue projects.

Open Visual Studio Code, select “File | Open Folder”, and browse to your “Vue Projects\Employees” folder. A new Vue project is a simple Hello World app, but all the core dependencies are in place and the project is properly configured and structured for a Single Page Application.

Open a terminal from inside VS Code (Terminal menu | New Terminal) and run the following command to install the NPM packages to the project:

npm install

If the command throws up any errors, ensure that your terminal is active in the correct folder: “C:\Vue Projects\employees”. Next, build and run the new app:

npm run serve

When the build completes without errors, you should see a local and network address for your app, in this case http://localhost:8080/.

A control + click on the link will open the app and you’ll see the default Vue Hello World application. Congratulations on your first successful, if limited, Vue app.

3. Adding the DevExtreme Treelist component

A working demo of the DevExtreme Treelist component can be found here. This includes sample Vue code and data that we’ll be using later, but for now it’s worth taking a look just to see what your new app will be displaying in place of the Hello World page.

To add DevExtreme to your project, go back to the Vue Project Manager we had open earlier and open the Dependencies section. Click on “Install Dependency” and search for “devextreme”. From the list available select and install the following three packages, one after the other: devextreme, devextreme-vue and devextreme-themebuilder.

Once installed, return to the Treelist demo page mentioned above. We’re now going to copy some code and data across to replace the Hello World project with the Treelist page code. We’ll be taking code from the App.vue, index.html and data.js pages.

First, copy the Vue code from App.vue. Go back to the project in Visual Studio Code and open the App.vue file. In the editor replace the content with the copied content from DevExtreme. Do the same for the index.html file. In your project, this file is located inside the public folder. After copying, remove the two script blocks from the bottom of the HEAD section that reference config.js and index.js.

Next, create a new file called data.js inside your project’s src folder. Copy and paste the contents of the DevExtreme data.js sample into this file.

Open a new Terminal and build and run your updated Vue app:

npm run serve

You should now see the DevExtreme TreeList demo content appearing instead what was the Vue Hello World application, as shown below. The app is fully functional, allowing you to add, edit and delete entries in the tree list, all using the predefined data source in the data.js file.

But what if we want to pull data from an API?

4. Create the .Net Core API

The next step is create a simple, bare-bones API using .Net Core 3.1 to return employee data. We’re still going to use the predefined data instead of a database, but this time that data will be accessible only from your new API.

I’m using the latest Visual Studio 2019 to create the API. Select “Create a new project” and choose “ASP.NET Core Web Application” from the list of available templates in the dialog. On the next screen, browse to your “Vue Projects” folder and name the Project “EmployeeApi”. On the Create screen, select API as the application type.

The default .Net Core app that’s created will contain some unnecessary files, so let’s remove those first. Delete the Weatherforcast.cs and WeatherForecastController.cs files. Open the launchSettings.json file inside Properties and set the launchUrl values to empty strings.

Now let’s fill out the code so that we have an endpoint to call and some data objects to send back.

First, create an empty API controller called Employee. Then add a new endpoint to it called GetEmployees and include a reference to an IWebHostEnvironment interface that you’ll need to access your data file.

Next, create your Employee object. The parameter names should be an exact match for the names in the data.js data source, as your DevExtreme Treelist expects to find these values in any data it uses.

Finally, you need to add the data source to your project. In the root project folder, create a file called data.json. Copy the data contents of the employees list in the data.js file from the Vue project into your new data.json file in the .Net Core project. This should be a Json list, not a JavaScript list, which means only copying the [ … ] block of code.

Once you’ve done this, you’re good to go. Build and run the EmployeeApi project. On my system, it’s running on https://localhost:44303/. You can test that everything is working by running a Get request to your new endpoint inside Postman, as shown below.

You’re now ready to go back to the Vue project and make the necessary changes to get the employee data from the API instead of from its own internal static data list.

5. The Vue.js app calls the .Net Core API

The first thing you need to do is go back to the Vue Project Manager and install a HTTP client so you can call your new API. As before, click “Install Dependency”, and search for and install “axios”.

Back in your Vue project, open the data.js file and delete the static list of employees. You now need to add a call to your new .Net Core endpoint, as shown below.

If your Vue app is still running, the tree list will now appear empty. This is because you haven’t updated your App.vue code to correctly call your new data access method. Replace the script section at the bottom of App.vue with the following code:

And that’s it for the Vue app. Everything should be working correctly. You’re making your call to the new API endpoint, and your Treelist should be loading the returned data.

But… it’s not.

You’re probably seeing console error messages relating to CORS and the Access-Control-Allow-Origin header. This relates to cross site scripting. Your Vue app and your API are not using the same port — something that is widely considered a security vulnerability. You can fix this by making some changes to the API. A shout out to Scott Stoecker for this.

Open the startup.cs file in the EmployeeApi project. In the ConfigureServices method, add the following line: services.AddCors();

In the Configure method, add a call to app.UseCors as shown below:

Restart the API and refresh the Treelist page in your Vue app.

You’re still not seeing data, but this time there are no console errors. Instead, you might see a DevExtreme error message telling you “The ‘ID’ key field is not found in data objects.” The solution to this can be quickly found by making the API call directly from Postman as we did earlier, and looking at the results. You should be able to see that all the variable names have been changed to camel case. DevExtreme variable names are case sensitive — it doesn’t recognise the ‘id’ field as ‘ID’.

You can fix this by making a further and final change to your startup.cs file in the .Net Core API. In ConfigureServices, adjust the call to AddControllers() as shown below:

Thanks to Ron Rebennack on Stack Overflow for that. Back in your Vue project, refresh the page and the data will now load without any console or other errors.

If you’re as new to frontend development as I am, you’ll probably agree that there are a lot of moving parts in getting even as basic a Vue app as this working and connecting to a .Net Core backend. The addition of a third party component suite like DevExtreme only adds to this, as do the changes that seem to come with every second release of .Net Core.

And that’s all without even looking at the authentication and user management that every app must have, not to mention the routing and state management required by even a moderately sized Single Page Application. Fun and games for next week.

[1] Vue.js: Big Picture, by Daniel Stern. Vue: Getting Started, by John Papa

[2] DevExtreme JavaScript component suite.

[3] Node.js installation.

To hear more from me, you can follow me on Twitter or on LinkedIn.

Solo Developer

6 Stage Development Plan for a Solo Developer Building a New App

A solo developer building a fully featured commercial application faces a number of challenges not faced by startups and dev teams, the largest of which is he’s on his own: one decision maker, one perspective, one opinion, one set of skills and experience.

There’s no one to shout out when a bad decision is made, there’s no voice of dissent in the room, no one suggesting alternative solutions. The solitary developer has only his own experience and knowledge to rely upon when building something new and pushing it out to market.

What’s generally absent from the solo developer’s toolkit, and what would help mitigate the effects of this isolation is a detailed development plan, a full roadmap taking in the first year of the app’s development.

My six stage development plan for building a new app is designed for solo developers just like me, looking to take a new product from initial idea all the way to a commercial application selling to users and companies around the world.

Saying “Ship it” is easy. Actually shipping that polished app is hard. Most solo developers fall down because of a failure to plan. They’re devs, so they want to code, not write documentation or outlines. But it’s that strict development plan that makes the difference between success and failure for so many side-project devs.

Why six stages? Why not a long list of features and to-do items?

You need milestones. You need to feel that sense of achievement as you complete Stage 2 and move on to Stage 3. You need production release builds with sets of features planned out in advance. You need to see those release dates coming and celebrate as they happen.

You need to be able to drag a feature from Stage 3 and drop it into Stage 4 or even Stage 6 after early users express complete disinterest. You need a structured place to add new features and ideas that will come to you as you work, instead of rushing headlong into building them now.

Before I dig into the six stages, a word of caution. This plan is all about the technical process of building your app; it does not address the product / market fit question. Are you building something people want? Something that people will pay for? Are you solving a genuine problem or addressing a real pain point? That’s the subject of another post (or book), but for now I’ll point you to a truly excellent book written by Rob Fitzpatrick called The Mom Test. I wish I’d had access to this book ten years ago.

Once you have your idea firmly fixed in your mind, you need to identify the niche market you’ll be building your early versions for. You should select the smallest niche possible so you can more easily dominate that sector. You’re a one man band, not Microsoft. You don’t have the resources or the time to build a generic app right now, and if you did you have no way to reach all those potential users.

Start small and stay focused on your niche.

Stage 1. Decisions

Before you write a single line of code, before you pull together any wire-frames or sketches, you need to make some decisions on the tech stack you’ll be using for your new app. To do this, look at two things: the requirements of the app and your own skill set.

If you’re building a desktop app, will it need to be cross platform? For a web app, will it be used primarily on mobile devices, on lap tops and desktops, or on both? Once you’ve worked out what the use cases will be, you can decide what kinds of tech you should be looking at.

The next step is to look at your own areas of expertise and see if these can be used to build everything you need. If you’re a .Net guy like me, is .Net Core strong enough to build out your backend? If your database experience is all Oracle, is that a viable database to use for a SaaS app? If not, you’ll have to step outside your comfort zone and choose something else.

What about the UI? If you’re a full stack developer with strong experience with Angular, then you’re in luck. If your UI experience is all jQuery version 1, you need to take a hard look at whether that particular library is strong enough for the app you’re looking to build. If you’re a backend developer with little UI experience, you need to look for a UI framework that’s quick and intuitive to learn.

Let’s assume that your experience gives you 80% of the skills you need out of the box. That leaves 20% of the tech you’ll be using that you’re unfamiliar with. You need to start learning this right now. Watch some videos on Pluralsight, Coursera, or YouTube. Buy a book or two on Amazon and plow through it quickly.

Look into third party tools that can save you time. You don’t have to decide on these right now, but you should be aware of them so that when you do reach the stage that they might be useful you don’t find yourself spending a month building your own solution when an off-the-shelf package would do all the work for you at a small cost. Do this before you move to Stage 2, before you write any code for your app.

What about the hosting? Do you need to decide on this right away or can it wait? My experience here tells me to wait it out. Your early pre-release versions will all be running locally. You can decide on Azure, AWS, DigitalOcean, etc. further down the road as you get closer to that first public release.

Stage 2. Localhost

Trim your Stage 2 feature list to the bone. In Stage 2 you don’t want a list of features your early users must have, you want a core set of features without which there is no app. Why? Because you don’t truthfully know what that must-have feature list is yet — you’ll learn that over time as your app starts interacting with users.

So what does a basic working instance consist of? It should allow you to open the app, edit or make changes, and save those changes to the database at the backend.

The goal of Stage 2 is to pull the disparate parts of your application together and make sure they work and flow in sync, that all the bits are in place. Your UI framework and code is all building, it’s communicating with your backend correctly, which in turn is reading and writing to your database.

That’s it.

It doesn’t need to be pretty. It doesn’t need to have a user log in mechanism — at this stage you are the sole user. It doesn’t need to be communicating with any services hosted on Azure or pulling in any external data sources.

Make it run locally, and then start using it yourself as much as possible. Let the dogfooding begin.

Stage 3. Show Hacker News

This is when you show Hacker News. And this is when you ask users to pay you money.

Your first beta release should contain the basic functionality that allows a user to do something valuable and important to them. It’s an MVP where the primary emphasis is on Viable. When you first came up with the idea for your app, you had a starting point, a basic set of functionality that lay at the heart of your offering. That functionality needs to be there now, and it needs to work well and look slick and professional.

At this point you need to investigate and decide on where you’ll be hosting. You also need to allow for user authentication and identity management — preferably using a third party resource that you may have to pay for. I’d recommend taking a look at AuthO.

You should be focusing on what makes your product unique, what separates it out from the competition. You should not be reinventing the wheel, building out login and authentication modules, or rolling your own payment methods. Investigate and decide on third party tools to do these mundane but super important tasks for you. Keep your focus on the unique features of your app, not on the things it shares with all other apps.

Release your beta version and then tell people about it. Your target user at Stage 3 should be the visionaries, pre-early adopters who love trying out new things. Hacker News is where you’ll find them.

Stage 4. Tell the world

Your app is ready for prime time. Now is the time to tell the world, or at least the world where your niche users live and breathe. It has a rich, if incomplete set of features, and it meets most of the needs of the niche market you’ve chosen to target. It should contain many of those enticing features you dreamed up over the weeks or months prior to writing code.

This is the product that a dev team at a large company can often spend months or even years building, and that you’ve now pushed out rapidly. How do you achieve this?

Way back in Stage 1 you made decisions about your tech stack. You decided on languages and frameworks. You should also have decided on whether you were going to roll your own UI components or go with commercial suites of components. Most developers love building new things. Their default answer is always to build their own.

As with authentication and payment modules, this is often a mistake. Sure, it gives you total control over how your components look and feel and behave, but it adds hugely to the time it takes to build your app. Remember, you’re a one man shop on a budget, you don’t have the luxury of spending weeks on every component. You need to be building faster and with greater confidence.

Professional third party component suites will allow you to do that, and will add a consistency that is often missing from solo developer apps. I’d recommend taking at look at DevExtreme for a frontend example of what third party tools can provide off-the-shelf. Don’t shy away from backend components either. Remember, you should be spending your time on what makes your product unique, not reinventing the wheel.

Now go tell the world about your fantastic new app.

Stage 5. Integration

Now is the time to release that Pro or Enterprise edition. If you’re building a B2B app, add in a single sign on option coupled with premium support, all accessible from a new pricing tier targeting larger companies.

If you followed the earlier advice in Stage 3 and used a third party to handle your authentication and identity management, this should be a simple box ticking exercise instead of a major piece of development.

Stage 5 is also where you should look at integrating with other apps and services. Grow your users and audience through add ins and plugins, make it as easy as possible for a new user to fit your app into their existing workflow. Mailchimp is a prime example of how a small company can grow through integrating with other apps.

Stage 6: Expansion

You now have a polished, professional app that meets the needs of small and large users in your target niche. Time to expand and take over the world.

Just as Facebook began at Harvard, dominating a small segment of users before expanding outwards to the wider world, you too should use the beachhead you established building your core app for a small niche and expand outwards.

All those generic features that you left out because they were not specific or targeted enough for your initial users need to be re-opened and looked at with a wider user base in mind.

At this stage you’re no longer a solo developer. Expansion has enabled you to start hiring more devs. You have UX people, dedicated UI designers who bemoan the fact that you chose that third party component suite way back when, and experienced backend guys who constantly critique your early architecture.

Such is life. Welcome to success.

To hear more from me, you can follow me on Twitter or on LinkedIn.

Tech Decisions

Choosing a Tech Stack for a New SaaS App

These past few days I’ve been thinking through the tech stack that I’ll be using for my new Dev Knowledge Base app. The prevailing opinion is to use what you know, what you’re good at; that building a new app is not the time to be learning a whole new stack.

There is truth in this. By day I’m a back-end web developer. Choosing the latest .Net Core for the back-end of DevRecall was a no-brainer. Why pick something different when there’d be a steep learning curve with many unknowns along the way? Especially considering that the latest .Net Core is as sweat as any trendy new language of the day.

But my front-end skills are weak, at least on the web side. I can build strong UIs for desktop, not so strong for web apps. It’s something I’ll have to learn regardless, so why not learn using a framework that adds value to the day job as well as to the app I’m building?

Years ago I did some work with jQuery, but it looks old and clunky now. My raw JavaScript skills are barely there — uncalled for in the day jobs of recent years, and unnecessary in the desktop UIs I’ve been building lately.

So I chose Vue.

I’ve dabbled with React but never liked the feel of it. Angular looks a bit on the complex side for putting together what will be little more than a prototype app, an alpha version — at least initially. There’s always a lot to learn when you build something new, especially when part of what you have to build lies outside your comfort zone. And speed is important. A version 1 has to go out the door as soon as possible. Learning React or Angular would push that release date too far.

I’ve been working through some beginner and intermediate Pluralsight videos on Vue. Pluralsight is an interesting learning resource. I find the quality of the material to be consistently strong, though the voice overs tend to the slow side — easier to follow at a decent pace if you increase the speed to 1.2. The supplementary material, Github repos and such, is also weighty.

If you’re new to Vue, or unsure whether you should be looking to Vue, React or Angular for your app, I’d recommend Vue.js: Big Picture by Daniel Stern, followed by Vue: Getting Started by John Papa.

For the hosting, I was initially thinking of Azure, which is the cloud of choice in recent years at the various companies I’ve contracted at. But I’ve never been happy with Azure — at least not with their managed instances — which is what I’d be looking to use for initial versions of DevRecall. Too many app restarts for no obvious reason, and too many issues with resources maxing out when pushing new releases.

Much as I hate to add yet another new piece of tech to my have-to-learn list, I’ll probably be looking at AWS for version 1. It helps that I burned though my years of free Azure stuff by way of the now rebranded Bizspark program years ago. No more free resources from Microsoft, but Amazon seem to have a never ending stream of free credits for start-ups that I should be able to avail of via one of the many back doors that seem to be made deliberately available.

SQL Server for the database of course — why change that? I’ve used SQL Server just about everywhere for the past ten years, and if it’s good enough and fast enough and robust enough for Stack Overflow, it should be good enough for DevRecall.

In terms of front-end components, well that’s another post.

To hear more from me, you can follow me on Twitter or on LinkedIn.