Lately I’ve been doing a lot more data-driven work, including data analysis. From this work, I’ve needed to import and export a lot of data. Here is my best quick-and-dirty way to copy a table from one Postgres table to another. I frequently use this method to refresh my local environment with production data.

Of course, if you’re using a tool like pgAdmin, you can export data at the click of a button, but depending on what you plan to do with the data, that may not be what you want to do. Using the pgAdmin export will include all of the record IDs, so if you’re using this data to augment another database (or in my case, refresh), this might not be what you want. But if that doesn’t matter, using a built-in import/export tool is definitely the easiest solution.

Unfortunately this usually doesn’t work for me, so this is what I do instead:

1. Export the prod data into a CSV

\copy (select columnA, columnB, columnC from tablename) to 'absolute/path/plus/file.csv' csv DELIMITER ',';

Make sure you include the absolute filepath, otherwise you’ll have a lot of trouble finding your csv. (This is the absolute path to your local machine.)

You can include any query above, so if you need to filter or sort in a certain way, adjust your query accordingly.

1. Drop the data from my local database and reset the sequence

Make triple-sure you’re in the correct environment before you do this!!

truncate tablename;

select nextval('sequencenameforprimarykey');
alter sequence sequencename restart with 1;

select nextval('sequencenameforprimarykey');
select count(*) from tablename

After you truncate your table and reset your sequence, you can select each and see that you’re ready to start fresh with your prod data.

1. Insert data from the CSV

\copy table_name(columnA, columnB, columnC) from 'absolute/path/plus/file.csv' csv DELIMITER ',';

Notice that the above command is basically just the inverse of the first \copy command in step 1.

Depending on how much data you’re copying over, this step might take a while. At the end, you’ll see something like COPY 137 in your terminal, which indicates the copy is complete and 137 records were copied into your table.

I’ll usually use one more select count(*) from tablename to see the total number of records in the table, as a sanity check.

A few gotchas

1. Permission denied errors

If you used \COPY without root permissions, try again with \copy. The \COPY command has a slightly different signature and requires root access, but \copy will work regardless.

1. “Cannot insert {type} into column”

An issue of incorrect data to column type. When this happens to me, it usually means I forgot to specify the columns in my \copy to command, or I specified them in the wrong order. If you are going to ignore the IDs when you move data from one place to another – which is a good idea – you must specify the columns in your \copy to command!

1. “Missing data for column”

When this happens to me, it usually means I forgot to specify the delimiter to be a comma in my \copy to command. Check your CSV file and ensure that you’re using the correct delimiter for your data.

1. “Duplicate key value violates unique constraint”

Most likely cause is that you copied your entire table, including ids, but forgot to drop your secondary table’s data and/or reset the sequence.

And that’s about it! At this point, I’ve copied production data into a local or sandbox environment where its safe to tinker with or even delete data. Just be careful and always, always, always make sure you’re not in production when tinkering!

Happy SQL-ing!

As I write this, I have scheduled Accounta-Billie to launch a beta this Friday, 10-23-2020. Looking back I have been working on it for about 3 months, which makes me chuckle now because my initial goal was to finish version 1 in a weekend(!). Three months as a side project is honestly pretty respectable in my opinion, especially since it is actually launching Friday, versus many, many other projects of mine that have been started but have never seen the light of day.

You might be wondering why I gave in to the hype of a “weekend startup” and made that audacious goal in the first place. Firstly, I guess part of me really likes hack-a-thons. I don’t really participate in them outside of my home, but I do really like getting loads of soda and chips and staying up super late hacking away and watching Silicon Valley.

via GIPHY

But beyond that, I also really believe in taking an idea from start to finish in the shortest amount of time possible. If you have an idea and you aren’t sure if it is viable, doable, whatever, its a smart idea to answer that question as soon as possible. If this is something you’re working on in your spare time, you don’t have a ton of it (that’s why it’s ‘spare’) and so it’s important to make it count. And it’s really easy to lose momentum on something that previous really excited you, especially if you’re super busy (like say, the world is in the middle of a pandemic, you’re working your full time job while doing virtual school with kids, and also generally trying to maintain some sanity). So in the interests of all of those things, I personally am a big believer in getting a project from start to finish in the shortest amount of time possible.

Of course, like all things in software and life, there are about a million caveats to that. The first caveat is that it has to be reasonable. To that end, you probably aren’t going to build Facebook in a weekend. This is where I made my first mistake.

via GIPHY

So here’s the thing: while I believe really strongly in taking an idea from start to finish as quickly as possible, it has to be reasonable and that means it has to be small. Take your idea for an app that does all the things and start trimming. When you think it’s reasonable, trim some more, and then trim even more. You know that rule where you 3x your estimate at work? Do that here too.

So if you could “probably” finish this thing in a weekend? It’s too big. Could you finish it on a Saturday before dinner? That’s the right size! There are going to be things you haven’t thought of, and styling is going to take longer than you anticipated, and so on. By getting your idea down to a single feature (or two, if you’re Wonder Woman) you’re setting yourself up for success. And if you are the unicorn everyone talks about who has perfect estimates? Well you either have some free time this weekend, or you can start on the next feature. Win-win!

So now that I’ve preached the good word of keeping MVPs (minimum viable products) as small as possible, let me regale you with all the ways I did not take my own advice.

via GIPHY

The original feature list included account creation (both in-app and social login), group creation and management, multiple calendar views, multiple permissions for views (a user deciding who can see a specific task), filters for viewing only certain group member’s tasks, include both a desktop and mobile view, and make it a PWA.

Phew, that is a lot of features. In hindsight, it’s easy for me to see that was never going to happen over a weekend, but I dream big. In the moment, it’s a much harder balancing act to both maintain the excitement you feel over a new project, as well as realistically determine what you can accomplish. I wish I had good advice for how to balance those two things, but the best I can really offer is to try to choose a project that you’ll use yourself. For me, that is Accounta-Billie, and knowing that it’s going to solve a problem I have is encouraging enough to keep me working on it.

I started off the project pretty well doing lots and lots of planning. I had everything broken down (I’ll write another post detailing how I handled the project management.), and I think that also helped keep me excited and focused. As long as I’m talking about planning, I want to mention Phil Schatz’s awesome project for adding extra functionality to a GitHub project board. It’s a wonderful and helpful project!

Over the first weekend, while I did not complete the first set of requirements, and honestly I still haven’t. A few of the features won’t be included in the beta, but will be added once the app moves to production. I ran into some troubles - especially with auth! It felt really good to get auth to completion, as well as deploying a project even though its incomplete. That’s right! It was deployed that first weekend, even without groups!

via GIPHY

Unfortunately auth did take a lot longer than I had originally intended, and so I wasn’t able to create multiple calendar views. I also didn’t finish the filters or different permissions layers. Ultimately I spent probably half of my working time on auth, which really makes my auth win that much greater!

For my next project or features, I know a lot better how to plan and what to plan so that I can actually complete whatever I’m working on in a weekend. Let me know what you thought of the post, and please go check out Accounta-Billie and sign up for the beta!

Hit me up on Twitter!

I wanted to take some time to write about a project I’ve been working on called Accounta-Billie. Accounta-Billie is a productivity and goals app. The concept is simple: you and your friends make up a group within the app. When one person in the group creates a task, such as “complete Accounta-Billie V1”, the whole group can see it. Once you’ve made this commitment to complete your task, your friends can help hold you accountable, and you can help hold your friends accountable to their goals as well.

It may seem counter-intuitive to try to make big goals (like building an app) during this time of COVID. I’ve seen tweets about how much people have “gotten done” during this time, and other tweets imploring people to “use this time wisely”. I find that kind of gross. Life is so much more than just checking items off a checklist. During a time like this, I really think we should just let everyone be. There’s no need to use this time to complete all of your life’s accomplishments if you’re having trouble right now - or any time for that matter! Life is too short to spend your time doing anything that you don’t want to be doing.

If you’re wondering how I got from the previous paragraph to “Let’s build an accountability app,” let me tell you a bit about my own quarantine experience. I started quarantine thinking, like a lot of people, that this wouldn’t last too long. I can remember thinking back in March that I only had to get to the summer, and then my kids would be in daycare, and I could I get some work done and also have my sanity back. And then when summer came and cases were worse, we made the decision to keep them home. I told myself that I only needed to get through the summer, and once school started back up, everything would go back to normal. Since then, the tough decision was made to keep my kids home and do virtual school. And even since that decision was made, my kids’ school district has decided not to have in-person classes at all.

During this never-ending (and also somehow always changing) quarantine, I adopted some pretty unhealthy habits, with the idea that I was just coping with a short-term situation. I justified it, telling myself that it was only until, only until, only until…. But the habits weren’t the kinds of habits that even made you feel better. Sure, they might have helped in the short term, but, if anything, they just compounded the depressed feelings even more in the long term. So once we decided to keep my kids out of school, I knew something had to change. In that moment, I suddenly snapped out of my short term thinking and started to accept this situation as a long term sort of “new normal.”

I wanted these new habits to start small, so my goal was to begin exercising regularly (of course, I didn’t know then what “regularly” meant to me), in hopes that exercise would help lift my spirits. I mentioned this to my sister, and she was really excited about the idea of us being accountability buddies. This made a lot of sense, both to help us each stick with our goals, but also because right now, we can’t see each other. We hoped that this would help keep us in contact.

But we couldn’t find an app that was quite right. She suggested the typical fitness apps, but they didn’t allow you to share tasks. I suggested a spreadsheet, but she said she probably wouldn’t use anything that she couldn’t access on her phone (she also called me lame for suggesting a spreadsheet). Since we couldn’t agree on what to use, naturally, the next step was to begin building my own app. I’ll detail everything over the next few blog posts, so stay tuned!

I hope you’ll check out Accounta-Billie. I’m looking for people for the beta, which is launching really soon. If you head on over to accountabillie.com, you can sign up there. I would love for you to use it, check it out, and then to get your honest feedback. Check back soon for my post about my first weekend working on Accounta-Billie and finally conquering auth.

Let me know what you think on Twitter!

As someone who loves JavaScript as much as I do, I never expected to be working in Xamarin. But that’s where I’ve been since I started my awesome new job! Xamarin brings some interesting challenges. You might think it’s just regular old C#, but since Xamarin uses MVVM architecture versus MVC architecture, I found a few things very tricky to manage. In this blog post, we’ll talk about how I managed navigation in a master detail app.

So in this app we used a master-detail app, which included a hamburger menu. The master property contained the hamburger menu, while the detail contained whichever partial view page was meant to be the current content. The code behind included a built-in Navigation property. You can use it like this:

await Navigation.PushAsync(new MyPage());

According to Xamarin docs, most or all of our app’s logic should be moved to the ViewModel. You can access the navigational properties via App.Current.MainPage.Navigation and push pages to the stack, as well as remove pages. However, I ran into a problem where the hamburger menu was disappearing when I would use App.Current.MainPage.Navigation. I realized I needed to find a way to actually update the Detail property itself. Unfortunately the Detail property doesn’t seem to be easily accessible. So I added a new constructor to the MainPage like this:

// MainPage.xaml.cs
// MainPage constructor
public MainPage(string page, params object[] args) {
    Type myPage = Type.GetType(page);
    Detail = new NavigationPage((Page)Activator.CreateInstance(myPage, args));
}

// FlowerPage.xaml.cs
// call MainPage page and pass in page, and any parameters
string type = typeof(FlowerDetailPage).AssemblyQualifiedName;
List<string> params = new List<string>();
params.Add("I am param");

App.Current.MainPage.Navigation.PushAsync(new MainPage(), params.ToArray());

This works well enough, however it seems a little yucky to keep adding new MainPages to the navigation stack. It seems like it would be better to directly update the Detail property, but how can we access it?

We ended up creating a navigational client with a method like this:

// NavigationClientManager.cs
public static async Task SetDetail(Type page, param object[] args) {
    MainPage mpage = ((MainPage)((NavigationPage)App.Current.MainPage).CurrentPage);
    Page detailPage = (Page)Activator.CreateInstance(page, args);
    mpage.Detail.Navigation.PushAsync(detailPage);
}

// FlowerPage.xaml.cs
// set the detail page
List<string> params = new List<string>();
params.Add("I am param");

await NavigationClientManager.SetDetail(typeof(FlowerDetailPage), params.ToArray());

This allows the ViewModel to simply call the SetDetail method, and avoid all of the casting of types in the nav client. It really cleans up the ViewModels and makes it very clear what is going on.

If you have another way of managing navigation in a master-detail app, I would love to hear it. Let me know what you think!

Shout out to my friend Jacob Danks who helped me come to this solution. Thanks Jacob!

Thanks so much for reading! If you want to learn everything you need to know about service workers, head over to serviceworkerbook.com and order my book, “Let’s Take This Offline”! Be sure to use caches-match for 10% off!

Arguably, the biggest part of service workers is being able to fetch data off of a user’s machine, rather than making trips across the network. There’s a variety of reasons reaching over the network isn’t ideal – lack of connection, intermittent connection, speed of the network. Fortunately service workers help with all of these things.

In this post, we’ll look at caches.match – the function to fetch data from a service worker.

There are two matching methods within the Cache API – caches.match and cache.matchAll.

While caches.match requires at least one parameter, cache.matchAll has no required parameters. First, let’s dig into cache.matchAll.

cache.matchAll can only be run on a single cache. It has two optional parameters – request and options.

(async function() {
	var cache = await caches.open(cacheName);
	var rez = await cache.matchAll(request);
}())

If the cache contained a key-value pair matching that request, the associated response would be returned. If there were no match, rez will be undefined.

The steps look like this:

1. Check that the request exists as a key in the list

2. If it does, add it to a response list

3. Return list of responses

_{Source: Service worker spec}

You can call cache.matchAll without a request, and it will return all the responses in the specified cache.

So what’s the difference between caches.match and cache.matchAll?

caches.match must be run with a request parameter. Additionally it’ll check over each and every cache in the app. It might look like this:

(async function() {
	var rez = await caches.match(request);
}())

Unfortunately checking over each cache can result in it being a little slow, especially if there are multiple caches created for an app. It can be better to specify a cache and search it specifically, or even only have only a single cache. That would look really similar to our code up above with matchAll.

(async function() {
	var cache = await caches.open(cacheName);
	var rez = await cache.match(request);
}())

Thanks so much for reading! If you liked this post, you should head on over to serviceworkerbook.com and buy my book! Be sure to use caches-match for 10% off!