Database

Introduction

When building a web-based product you usually need a database for that. There are tons of options out there for a database. There's MySQL, PostgreSQL, MongoDB, SQLite, etc. These are some factors that I considered before committing to a database.

  1. Free Tier. For the broke people (like me). The free tier is the number one factor. I am not trying to be stingy but if the provider doesn't have a free tier to help me start up my product, it's a no for me.

  2. Easy scalability. The database is something that I would like to offload on my engineering checklist, that's because maintaining and scaling it requires engineering resources, as much as possible I would like my time spent on the product itself and enhancing its features. If purely engineering, it's a different discussion 😉

  3. Simplicity. If you're starting, you don't need many database features upfront. With PostgreSQL, it tries to be everything with features like inheritance, pub/sub, and JSON support it's becoming MongoDB up to this point. 99% of the time you won't need that stuff.

For the reasons above I chose SQLite Libsql with Turso Cloud. It is easy to set up and backup. Also has a lot of support from the community. There's not a lot of fluff added to it, so it's simple to use. The only downside is the cold start, sometimes it goes around from 10 to 20 seconds but after that, it's smooth sailing.

Second is the ORM or query builder of choice. I don't really want to use an ORM, because most of the time it abstracts away the queries that are being executed, to the point that the performance degrades. Also, a declarative database schema migration support is a factor.

Prisma was one of my choices but the migration support for Turso is still meh, it's not declarative and you still have to make migration files. With this template, I decided to try Drizzle https://orm.drizzle.team/docs/overview since when I'm building queries, especially when I'm coding in non-Typescript projects like with Go, I always opt to raw dog the hell out of the SQL queries.

Also, Drizzle has a declarative way of migrating SQLite schemas to Turso. So I chose Drizzle. Lucia-auth also has an Adapter for it, so it solidified my choice of tooling.

Database Schema

db/schema.ts

export const accountTypeEnum = ['email', 'google'] as const;
export const subscriptionTierEnum = ['free', 'pro'] as const;
export const subscriptionStatusEnum = [
  'active',
  'past_due',
  'cancelled',
  'expired',
] as const;
export const todoStatusEnum = ['pending', 'ongoing', 'completed'] as const;
export const customerProfileStatusEnum = [
  'active',
  'inactive',
  'disabled',
] as const;
export const adminProfilesTypesEnum = ['superadmin', 'admin'] as const;

/* A user can be a customer or an admin*/
export const users = sqliteTable(
  'users',
  {
    id: text('id')
      .$defaultFn(() => createId())
      .primaryKey(),
    email: text('email'),
    createdAt: integer('created_at', { mode: 'timestamp' })
      .notNull()
      .default(sql`(unixepoch())`),
    updatedAt: integer('updated_at', { mode: 'timestamp' }),
  },
  (users) => {
    return {
      emailIdx: uniqueIndex('email_idx').on(users.email),
    };
  }
);

/* Stores user session and can be invalidated manually if an admin bans a customer using
the admin dashboard*/
export const sessions = sqliteTable('session', {
  id: text('id').notNull().primaryKey(),
  userId: text('user_id')
    .notNull()
    .references(() => users.id, { onDelete: 'cascade' }),
  expiresAt: integer('expires_at').notNull(),
});

export const customerProfiles = sqliteTable(
  'customer_profiles',
  {
    id: text('id')
      .$defaultFn(() => createId())
      .primaryKey(),
    userId: text('user_id').references(() => users.id, { onDelete: 'cascade' }),
    accountType: text('account_type', { enum: accountTypeEnum }).notNull(),
    // Paywalls specific features of the app
    subscriptionTier: text('subscription_tier', {
      enum: subscriptionTierEnum,
    })
      .notNull()
      .default('free'),
    // Status if the user's subscription is paid, expired, or cancelled.
    subscriptionStatus: text('subscription_status', {
      enum: subscriptionStatusEnum,
    }),
    // Used for verification code and account bans.
    accountStatus: text('account_status', {
      enum: customerProfileStatusEnum,
    })
      .default('inactive')
      .notNull(),
    password: text('password'),
    googleId: text('google_id'),
    stripeId: text('stripe_id'),
    lemonSqueezyId: text('lemonsqueezy_id'),
    createdAt: integer('created_at', { mode: 'timestamp' })
      .notNull()
      .default(sql`(unixepoch())`),
    updatedAt: integer('updated_at', { mode: 'timestamp' }),
    lastActiveAt: integer('last_active_at', { mode: 'timestamp' })
      .notNull()
      .default(sql`(unixepoch())`),
  },
  (customerProfiles) => {
    return {
      googleIdIdx: uniqueIndex('google_id').on(customerProfiles.googleId),
      stripeIdIdx: uniqueIndex('stripe_id').on(customerProfiles.stripeId),
      lemonSqueezyIdx: uniqueIndex('lemonsqueezy_id').on(
        customerProfiles.lemonSqueezyId
      ),
    };
  }
);

export const adminProfiles = sqliteTable('admin_profiles', {
  id: text('id')
    .$defaultFn(() => createId())
    .primaryKey(),
  adminType: text('admin_type', {
    enum: adminProfilesTypesEnum,
  })
    .default('admin')
    .notNull(),
  userId: text('user_id').references(() => users.id, { onDelete: 'cascade' }),
  password: text('password').notNull(),
  createdAt: integer('created_at', { mode: 'timestamp' })
    .notNull()
    .default(sql`(unixepoch())`),
  updatedAt: integer('updated_at', { mode: 'timestamp' }),
  lastActiveAt: integer('last_active_at', { mode: 'timestamp' })
    .notNull()
    .default(sql`(unixepoch())`),
});

export const usersRelations = relations(users, ({ one }) => ({
  customer_profile: one(customerProfiles),
  admin_profile: one(adminProfiles),
}));

export const todos = sqliteTable('todos', {
  id: text('id')
    .$defaultFn(() => createId())
    .primaryKey(),
  userId: text('user_id')
    .references(() => users.id, { onDelete: 'cascade' })
    .notNull(),
  title: text('title').notNull(),
  description: text('description').notNull(),
  status: text('status', {
    enum: todoStatusEnum,
  })
    .default('pending')
    .notNull(),
  createdAt: integer('created_at', { mode: 'timestamp' })
    .notNull()
    .default(sql`(unixepoch())`),
  updatedAt: integer('updated_at', { mode: 'timestamp' }),
});

export const emailVerificationCodes = sqliteTable('email_verification_codes', {
  id: text('id')
    .$defaultFn(() => createId())
    .primaryKey(),
  customerProfileId: text('customer_profile_id')
    .references(() => customerProfiles.id, { onDelete: 'cascade' })
    .notNull(),
  code: text('code').notNull(),
  expiresAt: integer('expires_at', { mode: 'timestamp' }).notNull(),
  createdAt: integer('created_at', { mode: 'timestamp' })
    .notNull()
    .default(sql`(unixepoch())`),
});

export const subscriptionPlans = sqliteTable('subscription_plans', {
  id: text('id')
    .$defaultFn(() => createId())
    .primaryKey(),
  title: text('title').notNull(),
  tierCode: text('tier_code', {
    enum: subscriptionTierEnum,
  })
    .notNull()
    .default('free'),
  description: text('description').notNull(),
  monthlyPrice: real('monthly_price').notNull(),
  yearlyPrice: real('yearly_price').notNull(),
  stripeProductIdMonthly: text('stripe_product_id_monthly'),
  stripeProductIdYearly: text('stripe_product_id_yearly'),
  lemonSqueezyProductIdMonthly: text('lemonsqueezy_product_id_monthly'),
  lemonSqueezyProductIdYearly: text('lemonsqueezy_product_id_yearly'),
  // Automatically asserts the type into string[]. Prisma doesn't have this afaik.
  features: blob('features', { mode: 'json' }).$type<string[]>(),
  isFeatured: integer('is_featured', { mode: 'boolean' }),
  createdAt: integer('created_at', { mode: 'timestamp' })
    .notNull()
    .default(sql`(unixepoch())`),
  updatedAt: integer('updated_at', { mode: 'timestamp' }),
});

Set up Database

  1. Install all dependencies using npm, pnpm or yarn

  2. Setup Turso database https://docs.turso.tech/quickstart

  3. Paste the database url and the database auth token on your .env file

  4. Run the script called

db:push

  1. Add subscription plan rows on Turso dashboard

PreviousComponentsNextDeployment

Last updated