Category Archives: databases

Using Microsoft Power BI With PostgreSQL

Microsoft Power BI is an advanced business intelligence suite that allows you to perform robust data analysis from a variety of different data sources. One common data source is PostgreSQL. Although Microsoft PowerBI does support PostgreSQL, getting started can be a … Continue reading

Posted in databases | Leave a comment

SQLite DB Migrations with PRAGMA user_version

This blog used a simple homegrown blogging engine that I wrote backed by a SQLite database. I have a function in the flask app that performs database migrations. My current approach has been to keep a folder full of migrations and run … Continue reading

Posted in databases | Tagged | Leave a comment

Backing up and Restoring MySQL with mysqldump

I backup and restore databases across servers every few months, but each time I have to resort to reading this very verbose documentation. The steps below are a no fuss way to do this each time. Backup Server SSH into the … Continue reading

Posted in databases | Leave a comment

Tunnel to Production PostgreSQL Database

As any responsible sys admin, I only allow local connections to my production database server. This means that if I need to access my DB from my local machine I would most likely need to use an SSH tunnel in … Continue reading

Posted in databases | Leave a comment

Remote Root Access for MariaDB on Ubuntu 16.04 LTS

Typically, when you install MySQL on Ubuntu, it asks you for a root password which you can then use to log into the database. It seems that MariaDB uses a plugin instead to authenticate the root user. This means that … Continue reading

Posted in databases | 3 Comments

Recreating Foreign Keys with Alembic

Alembic is a great tool for keeping track of schema changes in python applications. I am using it to manage DB migrations for braindump along with Flask SQL Alchemy as my ORM. One challenge is managing proper foreign key constraints. By default if you define … Continue reading

Posted in databases | Leave a comment

Types and Roles If Not Exists in PostgreSQL

For certain operations such as creating types and roles in PostgreSQL you are not able to use the handy IF NOT EXISTS parameter. This makes creating reusable scripts a bit more challenging, especially if you are going to be running these scripts … Continue reading

Posted in databases | Leave a comment

Removing a Public Facing User Page in OS X Server Wiki

OS X Server has some pretty neat tools that are easy to set up and use for a team collaboration. The problem is that some of these tools are a bit quirky, especially when it comes to removing users or … Continue reading

Posted in databases | Leave a comment

Connect LibreOffice Base to MySQL

I think LibreOffice Base has so much underutilized potential as a rapid application development platform, business intelligence platform and just a general reporting platform. Not to mention the fact that registered data objects can be used in all of the … Continue reading

Posted in databases | Leave a comment