PaulieScanlon

name: Backup Neon | paulie-dev (us-east-1)

on:
  schedule:
    # Runs at midnight ET (us-east-1)
    - cron: '0 0 * * *'
  workflow_dispatch:

jobs:
  db-backup:

PaulieScanlon

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Principal": {
                "AWS": "arn:aws:iam::627917386332:role/paulie-dev-2023-github-action"
            },
            "Action": [
                "s3:ListBucket",

PaulieScanlon

import type { FunctionComponent } from 'react';
import type { AnalyticsType } from '../../schema';

type PickedProps = Pick<AnalyticsType, 'city' | 'country' | 'flag'>;

interface Props {
  analyticsData: PickedProps[];
}

const VisitorsList: FunctionComponent<Props> = ({ analyticsData }) => {

PaulieScanlon

<VisitorsList analyticsData={results} />

PaulieScanlon

const results = await db
  .select({
    city: analytics.city,
    country: analytics.country,
    flag: analytics.flag,
  })
  .from(analytics)
  .where(eq(analytics.country, 'US'))
  .orderBy(analytics.date)
  .limit(10);

PaulieScanlon

import { pgTable, serial, varchar, timestamp, decimal} from 'drizzle-orm/pg-core';

+ import { type InferSelectModel } from 'drizzle-orm';

export const analytics = pgTable('analytics', {
  id: serial('id').primaryKey(),
  date: timestamp('date').notNull(),
  slug: varchar('slug', { length: 255 }).notNull(),
  referrer: varchar('referrer', { length: 255 }),
  flag: varchar('flag', { length: 16 }),

PaulieScanlon

- import { pgTable, serial, varchar } from 'drizzle-orm/pg-core';
+ import { pgTable, serial, varchar, date } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {
  user_id: serial('user_id').primaryKey(),
  first_name: varchar('first_name', { length: 100 }),
  last_name: varchar('last_name', { length: 100 }),
  email: varchar('email', { length: 80 }).unique(),
  role: varchar('role', { length: 20 }),
+  date_of_birth: date('date_of_birth')

PaulieScanlon

.select({
  city: analytics.city,
  country: analytics.country,
  flag: analytics.flag,
  count: sql`COUNT(${reactions.id})`.as('count'),
})
.from(analytics)
.innerJoin(reactions, eq(analytics.slug, reactions.slug))
.where(and(eq(reactions.reaction, 'happy'), gte(analytics.date, new Date(new Date().getTime() - 30 * 24 * 60 * 60 * 1000))))
.groupBy(analytics.city, analytics.country, analytics.flag)

PaulieScanlon

WITH happy_reactions AS (
  SELECT analytics.city, analytics.country, analytics.flag, COUNT(reactions.id) AS count
  FROM analytics
  INNER JOIN reactions ON analytics.slug = reactions.slug
  WHERE reactions.reaction = 'happy' AND analytics.date >= CURRENT_DATE - INTERVAL '30' DAY
  GROUP BY analytics.city, analytics.country, analytics.flag
)
SELECT city, country, flag, count
FROM happy_reactions
ORDER BY count DESC

PaulieScanlon

.select({
  id: orders.order_id,
  order_date: orders.order_date,
  first_name: orders.first_name,
  last_name: orders.last_name,
})
.from(orders)
.innerJoin(customers, eq(orders.order_id, customers.customer_id))
.limit(50);