
In the era of cloud computing, the concept of self-hosting databases might seem a bit retrograde. However, for those passionate about data control, privacy, and learning the ins and outs of database management, self-hosting your database can be an incredibly rewarding project. Today, we're diving into how you can set up, manage, and secure a PostgreSQL database on a Raspberry Pi, bringing powerful database capabilities to your home network.

## Setting up your Raspberry Pi for Database Hosting

Before diving into PostgreSQL, ensure your Raspberry Pi is ready to serve as a database host. Here's a quick checklist:

**Raspberry Pi Setup**: Choose a Raspberry Pi model with at least 2GB of RAM for better performance, though models with 4GB or more are recommended. Install the latest version of Raspberry Pi OS on an SD card.

**Network Configuration**: Ensure your Pi has a static IP address on your network to avoid connection issues.

**Installing PostgreSQL**: Connect to your Raspberry Pi via SSH and update your package list. Install PostgreSQL with `sudo apt-get install postgresql`. Once installed, start the PostgreSQL service and enable it to start on boot.

**Initial Database Setup**: Create a new database user and a test database to ensure everything is working as expected.

## Remotely Connecting to Your Self-Hosted Database

To connect remotely, you'll need to adjust PostgreSQL's configuration:

**Edit `postgresql.conf`**: Locate your `postgresql.conf` file (typically found in `/etc/postgresql/XX/main/`, where `XX` is the version number) and set `listen_addresses` to `'*'` to allow connections from any IP.

**Update `pg_hba.conf`**: Modify `pg_hba.conf` to add a line allowing connections from your network, such as `host all all 192.168.1.0/24 md5`.

**Restart PostgreSQL**: Apply the changes by restarting the PostgreSQL service.

## Securing Your Self-Hosted PostgreSQL Database

Security is paramount. Here are some steps to secure your database:

**Strong Passwords**: Use strong, unique passwords for all database accounts.

**Firewall Configuration**: Utilize UFW (Uncomplicated Firewall) or another firewall tool to restrict access to your Raspberry Pi, allowing only specific IPs to connect on the PostgreSQL port (default 5432).

**Regular Updates**: Keep your Raspberry Pi and PostgreSQL installation up to date with security patches.

## Integrating Prisma for Schema Migrations

Prisma is a next-generation ORM that can help manage your database schema with ease. To integrate Prisma with your PostgreSQL database:

**Install Node.js**: Prisma requires Node.js, so install it on your Raspberry Pi.

**Install Prisma**: Use npm or yarn to install Prisma globally and initiate it in your project directory.

**Configure Prisma**: Point Prisma to your PostgreSQL database by updating the `datasource` block in your `schema.prisma` file.

**Migrate**: Use Prisma's migration commands to manage your database schema.

## Monitoring and Maintaining Your PostgreSQL Database

Regular monitoring and maintenance are crucial for the health of your database:

**Backup Regularly**: Set up automated backups to ensure data safety.

**Monitor Performance**: Use tools like `pg_stat_activity` and `pg_top` to monitor database performance and identify potential issues.

**Vacuum and Analyze**: Regularly vacuum your PostgreSQL database to reclaim storage and run the analyze function to update statistics for the query planner.

## Troubleshooting Common Issues with Self-Hosting PostgreSQL

Here are a few common issues and their remedies:

**Connection Issues**: Verify network settings, firewall configurations, and PostgreSQL's `pg_hba.conf` and `postgresql.conf` files.

**Performance Problems**: Check for hardware limitations, optimize queries, and ensure indexes are properly used.

## Exploring Advanced Features and Customizations for PostgreSQL on Raspberry Pi

PostgreSQL offers a wide range of advanced features:

**Replication**: Set up replication for data redundancy.

**Partitioning**: Use table partitioning to improve query performance on large datasets.

**Custom Functions**: Write PL/pgSQL functions for complex logic.

Self-hosting a PostgreSQL database on a Raspberry Pi is not just about saving costs; it's a fantastic learning opportunity that offers deep insights into database management, security, and optimization. Whether you're building a home project or just curious about database administration, this setup provides a compact, powerful platform to explore the vast capabilities of PostgreSQL.


Self-Hosting a PostgreSQL Database on a Raspberry Pi


I'd like to introduce you to a tool I now use in both my work and personal life called AudioPen. This AI-powered tool records your voice and generates summaries of your thoughts, filtering out any stutters or unnecessary words. It's perfect for creating elegant emails, preparing voicemails, or taking notes during meetings.

When using AudioPen at work, I often let it listen while discussing projects with coworkers. Afterward, the app provides a summary that serves as a task list which can be edited if needed. You can pause the recording if conversations go off track and easily copy-paste the output into task managers or project management tools.

Moreover, AudioPen allows you to add tags to your notes for better organization. The creator, [@louispereira](https://twitter.com/louispereira), adds new features every week making it an incredible game changer for productivity.

In my opinion, it would be amazing to have a feature that allows me to conveniently copy the transcript in Markdown format. This would facilitate the transfer of the transcript to [Linear](https://linear.app/), the task manager I rely on for work projects.

I highly recommend [giving AudioPen a try](https://audiopen.ai/?aff=Mq2RY) and sharing your thoughts on its effectiveness. Hopefully, this tool will boost your productivity just as much as it has mine!

P.S. You guessed it! This was written with the help of AudioPen.


Introducing AudioPen: AI-Powered Productivity Tool


Now that I've got a lovely [Next.js personal website](https://raskin.me) up and running, I'm ready for my next project. I've always enjoyed grabbing CSV files from my credit card or bank accounts and playing around with the financial data. It allowed me to run super custom reports for properly managing personal finances. One method of doing this was using Jupyter Notebook and running some Matplotlib plots, which I explain on [this GitHub repo](https://github.com/perryraskin/TransFormer).

For a while now I've been meaning to integrate Plaid into a web app and try manipulating some financial data. After inevitably using it in the many budgeting apps I've encountered, I knew I had to give it a try myself. Currently I use [Copilot](https://copilot.money), an iOS budgeting app that uses Plaid, and I absolutely love it. My code MRJA4Q will get you **1 free month**!

I followed a [blog post](https://medium.com/@dereksams/building-a-react-app-with-the-plaid-api-93e45ae61b58) by Derek Sams that showed me how to get a React app running with Plaid. To get it working with Next.js, I had to make a few changes you may notice if you decide to compare the two.

## Our Starter App

What we're focusing on here is to simply get Plaid working with a working React app. It will make a call to Plaid's transactions API and display transactions to the console. Of course once you get this done you can build upon this thing to make it do some really useful stuff!

## Get Set Up

If you'd like, you can get started from scratch using [Next.js's guide](https://nextjs.org/learn/basics/getting-started/setup) on starting a new project. For this post I'll focus on the [boilerplate repo](https://github.com/perryraskin/nextjs-plaid-starter) I published to GitHub. Once you clone the repo you'll want to run `npm install` in the root folder to get all set up. Please see the ReadMe file for more information.

## The Code

Notice our API routes in `/pages/api`. Next.js automatically handles routing for us simply based on files it detects in this folder, like magic! We will take advantage of this along with our handy fetching utility called `isomorphic-unfetch`. These are important for working with Plaid's API.

### The API

Let's take a look at the API in `/pages/api/plaid/index.js`. First we need to import `next-connect,` which will allow us to utilize the Next.js request handler. In our case, we will be sending a `POST` request. Then of course we'll need `plaid` for connecting to Plaid's API, and `moment` to do work with a few dates.

    import nextConnect from 'next-connect';
    import plaid from 'plaid';
    import moment from 'moment';

Next, we need to initialize some variables. As our `.env` file defines the API tokens received from Plaid, I defined them here. These are used to create the Plaid client object, which allows us to exchange keys and grab transactions as will be seen later in the file.

    const PLAID_CLIENT_ID = process.env.PLAID_CLIENT_ID;
    const PLAID_SECRET = process.env.PLAID_SECRET_SANDBOX;
    const PLAID_PUBLIC_KEY = process.env.PLAID_PUBLIC_KEY;
    const PLAID_ENV = process.env.PLAID_ENV;

    var ACCESS_TOKEN = null;
    var ITEM_ID = null;

    // Initialize the Plaid client
    export const client = new plaid.Client(
      PLAID_CLIENT_ID,
      PLAID_SECRET,
      PLAID_PUBLIC_KEY,
      plaid.environments[PLAID_ENV],
      { version: '2019-05-29', clientApp: 'Plaid Quickstart' }
    );

    const handler = nextConnect();

`handler.post` will begin the API call. We are doing two things here:

- Calling `client.exchangePublicToken` to provide us with the access token
- Calling `client.getTransactions` to return all transactions from the past 30 days

Once we receive the access token from `client.exchangePublicToken` we will use it when we call `client.getTransactions`. That's pretty much all that's going on here. The rest of the code is console logging the results so we can see what's going on as it happens.

Upon a successful response, we will receive the following JSON:

    res.json({
      ok: true,
      message: 'Success!',
      access_token: ACCESS_TOKEN,
      item_id: ITEM_ID,
      transactions: transactions
    })

If you use Postman, you should see this response. Clicking the "View Transactions" button after you connect the (sandbox) bank account, the dev console will output the `{ transactions: transactions }` part of that response. More on front end aspects in the next section. Be sure to use the following sandbox credentials, provided by Plaid:

- **User ID:** user_good
- **Password:** pass_good

### The Front End Component

I recently learned that if we want to work with state when using Next.js, we need to call `useState()`. For our little project, we'll need `transactions` to be stateful.

    const [transactions, setTransactions] = useState(Object);

That's it! We're basically just declaring a variable along with a matching setter for its state. In this case, `transactions` is the variable and `setTransactions` is the setter function. If you are familiar with deconstructing, you cant think about it as deconstructing the imported `useState` functionality.

    function handleOnSuccess(public_token: any, metadata: any) {
        // send token to client server
        fetchSwal
          .post('/api/plaid', {
            public_token: public_token,
            metadata: metadata,
          })
          .then((res) => {
            if (res.ok !== false) {
              setTransactions({ transactions: res.transactions });
            }
          });
      }

This is the function that handles the click of our “Connect your bank!” button to grab transactions. You'll see in the `PLink` component how it all comes together. This basically just makes an API call to grab the transactions from the account you just logged into through Plaid! Once we have the transactions, our `setTransactions` setter function sets the state to the value we just received.

We will then print the transactions to the dev console (in Chrome, for example) by clicking the "View Transactions" button.

    function handleClick(res: any) {
      console.log('transactions:', transactions);
    }

## Conclusion

It's amazing that we have access to such a great API like Plaid. It really allows us devs to get creative with all kinds of data.

Feel free to leave comments and let me know how this went for you! I'd love to hear about any cool features you implemented following this initial setup.


Quick Start: Next.js and Plaid to pull financial data


It definitely took me way too long to get code blocks correctly highlighted on my blog. Next.js seems to have less tutorials out there in general, especially for specific use cases like these. I hope to fix that, at least a little bit, with my own blog. As I experience issues and overcome each challenge, my plan is to write up a post about it. Here's #1!

## Quick Background

As a starting point, I want to outline the architecture of my website. As Next.js goes, I have page components in the `/pages` directory. Blog posts are generated dynamically via the `/pages/blog/[slug].tsx` page, where the `getInitialProps` function pulls content from the `.md` files located in the `/posts` folder.

If this is new to you, there are tutorials that explain how this works (e.g. [Next.js docs](https://nextjs.org/learn/basics/create-dynamic-pages)). Feel free to also view the code on the [GitHub repo](https://github.com/perryraskin/raskin.me).

## Before Highlight.js

For a visual, below is what the `BlogPostTemplate` component looked like before I did anything fancy. I will only include the relevant code to keep it short, but feel free to view the [entire file](https://github.com/perryraskin/raskin.me/blob/master/pages/blog/%5Bslug%5D.tsx) in the repo.

```javascript
<article className="mb-10 markdown">
  <header>
    <h1 className="text-5xl">{frontmatter.title}</h1>
  </header>
  <div className="mb-5 my-auto text-sm font-semibold text-neutral-400">
    {reformatDate(frontmatter.date)}
  </div>
  <div>
    <ReactMarkdown source={markdownBody} />
  </div>
</article>
```

Notice that this code simply applies a title, date, and the contents of the `.md` file. By default, inline code and code blocks were not nicely styled, and of course I wasn't satisfied with that. Luckily, `react-markdown` takes an optional parameter called `renderers` where we can provide a custom style to HTML tags of our choosing. This is where it got confusing - I had to dive into the [source code](https://github.com/rexxars/react-markdown/blob/master/src/renderers.js) to figure out what exactly I needed to pass to it.

## The CodeBlock Component

First and foremost, make sure you have your imports for highlight.js. You can choose any theme from the `/styles` folder that's provided.

```javascript
import Highlight from "react-highlight";
import "../../node_modules/highlight.js/styles/nord.css";
```

After **a lot** of trial and error, I realized I needed to create a custom component to pass to `renderers`. I'm sure there are other ways to do this, but this made the most sense to me, and it worked quite nicely.

```javascript
interface CodeBlockProps {
  value: any;
}

const CodeBlock: NextPage<CodeBlockProps> = ({ value }) => {
  return (
    <div>
      <Highlight>{value}</Highlight>
      <br />
    </div>
  );
};
```

Since the `code` parameter of `renderers` requires a component with a `<code>` element that's inside of a `<pre>` element, this is what I came up with. `<Hightlight />` includes all of that, so I added it inside this component, and passed the `value` (the actual code) as props.

```javascript
<ReactMarkdown
  source={markdownBody}
  renderers={{
    code: CodeBlock,
  }}
/>
```

Then, I simply passed the `CodeBlock` component to `code` and finally it worked! Just be sure you don't have PurgeCSS stripping your highlight.js CSS, and you should be good to go.


Beautify code in your Next.js blog


I've been meaning to start my own blog for a while now. One thing I really enjoy doing is self-teaching web development topics, and I know I'll learn better if I actually write about it. I believe in this movement of both learning in public and building in public. Just from following different developers on Twitter and/or on podcasts, I've seen that it's really a win-win situation.

While further strengthening development skills, the provider of the content also increasingly becomes more personable and a better writer. I'm sure I have quite a way to go in that respect. The receiver of this content gets to learn something new, even if the topic is something they've already learned.

Every individual has a unique way of teaching and delivering material. No matter how many educational resources exist out there on a certain topic, anyone can step in and teach it in their own way. There's always a handful of people who can better identify with a different approach when learning something new.

I hope this blog will allow me to contribute to the online community in the same way that I've benefited from it.

## Some Goals

1. Display relevant data (GitHub projects, Tweets, etc)
2. Publish quick posts about web development, ideally as I learn new things
3. Become super familiar with building with React
4. Learn and implement UI best practices with TailwindCSS


init(blog)

<p>I've been meaning to start my own blog for a while now. One thing I really enjoy doing is self-teaching web development topics, and I know I'll learn better if I actually write about it. I believe in this movement of both learning in public and building in public. Just from following different developers on Twitter and/or on podcasts, I've seen that it's really a win-win situation.</p>
<p>While further strengthening development skills, the provider of the content also increasingly becomes more personable and a better writer. I'm sure I have quite a way to go in that respect. The receiver of this content gets to learn something new, even if the topic is something they've already learned.</p>
<p>Every individual has a unique way of teaching and delivering material. No matter how many educational resources exist out there on a certain topic, anyone can step in and teach it in their own way. There's always a handful of people who can better identify with a different approach when learning something new.</p>
<p>I hope this blog will allow me to contribute to the online community in the same way that I've benefited from it.</p>
<h2>Some Goals</h2>
<ol>
<li>Display relevant data (GitHub projects, Tweets, etc)</li>
<li>Publish quick posts about web development, ideally as I learn new things</li>
<li>Become super familiar with building with React</li>
<li>Learn and implement UI best practices with TailwindCSS</li>
</ol>
