DEV Community: cychu42

Starchart: 1.0.0 Release

cychu42 — Fri, 21 Apr 2023 14:42:01 +0000

Working Toward First Release

Finally, the Starchart project has reached 1.0.0 release!

After the handwork of the team, we managed to have it running on staging and then production. We encountered issues, such as incorrect AWS credential in production, having to deal with certificate request duplication (See PR), and splitting Docker image deployment between staging and production (See PR), but it's working now!

In the previous week, I was working on code for migration for both staging and production, as mentioned in the previous blog post.

This week, I made a PR to remove all mentions of deactivated user field, as well as related functions: deactivateUserByUsername, and isUserDeactivated.
This is because we decided we no longer need it and would simply delete user, which would cascade the delete to all DNS records and certificates belonged to the user, due to how it's set up in Prisma schema. Then, the reconciler code would sync the changes with AWS Route 53 we use.
The PR is important because it changes schema, and the migration code from my previous work successfully perform migration to update the databases in staging and production.

Reflection: The Work

I'm glad I had this experience and know that this is something I can do. It was great to be part of a team and build a project from the ground up, which is very valuable to me as a developer. When it started, I choose to be the expert on the database side of the project, because I'm interested in database and the management. While the actual maintenance is handled by Seneca ITS, I still got to work with database and have the experience of being the go-to person for that area on the team.

I got to learn new technologies, such as Prisma, and use them for the project. It's nice to experience having to pick up new technologies to work on a project, which I imagine won't be the last time I do it.

The people on the team are great. They are open and helpful. When you step out of of specialty on a project, it can be a bi intimidating to pick up what has been done in other areas of the project, and having someone who's willing to walk you through their area and answer your question is very valuable for the process and learning. I'm thankful for all the teammates who gave me a hand.

Reflection: Prisma

Prisma is the chosen tool for bridging the rest of the code and the database, and it has been pretty pleasant to use. Partially due to use of TypeScript, it's very robust in catching mistakes even before you have to run tests for your code. It also simplifies the work of issuing SQL statements interact with database. For example, instead of writing a lot of SQL statements to create a table, you can just code a visual layout with far less things, like:

model DnsRecord {
  id           Int             @id @default(autoincrement())
  username     String
  subdomain    String
  type         DnsRecordType
  value        String
  description  String?
  course       String?
  ports        String?
  challengeId  Int?
  createdAt    DateTime        @default(now())
  updatedAt    DateTime        @updatedAt
  expiresAt    DateTime
  lastNotified DateTime?
  challenge    Challenge?      @relation(fields: [challengeId], references: [id], onDelete: Cascade)
  user         User            @relation(fields: [username], references: [username], onDelete: Cascade)
}

I wrote about how this work in another blog post when working on the first iteration of this schema.
It's easy to write and see visually. The varies Prisma API calls also lets you interact with database from rest of your code in a project.

Starchart: Migration!

cychu42 — Fri, 14 Apr 2023 01:05:48 +0000

This week, I created worked on utilized Prisma migration for Starchart project.

The Work

This PR allows staging and production to use Prisma migration, through deploying migration files.

Changes

Add migration in docker-entrypoint.sh, when environment variable DATABASE_SETUP=1
Add migration file creation script, with required permission, in package.json
Create an initial migration file in prisma/migrations
Add documentation for making migration files in DEPLOY.md
Adjust docker deployment for update

Update (April 20, 2023): we later decided to change DATABASE_SETUP to DANGER_DATABASE_WIPE_REINITIALIZE in this PR to warn future devs about turning this variable on.

Essentially, a migration file is essentially a set of SQL queries Prisma generates that would get a database to a desired state, by comparing your current schema and the latest migration file. Think of Prisma migration as git tracking your commits. For more details, see Prisma documentation.

Migration normally keep data in the database while updating the database schema. A part of this is done through shell script.

Shell Script Snippet (file from the PR)

database_setup() {
  echo "Running database reset and setup..."
  DATABASE_URL=$(</run/secrets/DATABASE_URL)
  export DATABASE_URL

  npx prisma migrate reset --force --skip-seed

  unset DATABASE_URL
  echo "Database setup complete"
}

database_migration() {
  echo "Running database migration..."
  DATABASE_URL=$(</run/secrets/DATABASE_URL)
  export DATABASE_URL

  npx prisma migrate deploy

  unset DATABASE_URL
  echo "Database migration complete"
}

if [[ $DATABASE_SETUP == "1" ]]; then
  database_setup
  clear_redis

else
  database_migration
fi

Only when DATABASE_SETUP =1 is an environment variable, we would wipes the existing database and apply migration files with database_setup. npx prisma migrate reset --force --skip-seed resets/wipes the database and applies migration files, and Prisma would know what migration is applied already and skip them, instead of causing conflict.
--force skips confirmation, and --skip-seed skips seeding.

Otherwise, database_migration applies migration files for normal database updates, assuming no changes to the database schema is done without Prisma migration. npx prisma migrate deploy deploys migration files to change schema without deleting data.

In both database_migration() and database_setup(), we pull the database URL out of secrets, since Prisma requires it to know which database to connect to. After using it, we clear that variable.

Creating A Migration File For Schema Change

Because staging and production use Prisma migration, changes in the schema need to become migration files for Prisma migration to run the files to apply changes, via prisma migrate dev --create-only --skip-seed. It would ask for a name for the file.
The script doesn't seed database or apply migration, just creating a migration file and also generating Prisma client (default behavior) in case a dev forgets.

When Prisma creates a migration file, it requires permission to create a temporary shadow database in a database to do this, which means a user of said database needs CREATE, ALTER, DROP, and REFERENCES privilege to do this. Make sure such user is used for DATABASE_URL environment variable that Prisma use to connect to a database.

Docker

We have multiple instance for production and staging, using Docker.
While Prisma does have advisory locking, which would skip repeated deploy within last 10 seconds (can't be configured), it's probably safer to run only one instance to initiate changes to database schema, then bring up the other instance.

Snippet: (file from the PR)

    deploy:
    ...
      update_config:
        parallelism: 1
        delay: 20s
        failure_action: rollback
        order: stop-first

This is the relevant change in our docker-compose YAML files for staging and production.
It would stop existing containers, and then update one container at a time. It would waiting 20 seconds in-between. If something fails, it would rollback.

Recycle Code: CNAME & Re-request

cychu42 — Fri, 07 Apr 2023 09:15:52 +0000

Recycle Code

This week, I did some work on the Starchart project where I reuse existing codes to accomplish my tasks. It's always a neat feeling when I find out how I can leverage existing code to achieve a task.

No Duplicate CNAME!

The Starchart project allows user to easily create SSL certificate by taking care of a lot of required steps. Under a certificate, users can have multiple DNS Records.
By design, CNAME DNS Records forward you from one domain to another, so it makes little sense to have two CNAME DNS Records forwarding you from one same domain to two other domains at the same time.

Because of this, we need to prevent users from creating these kind of duplicate CNAME records.

A Small Restriction

I added a piece of code to achieve that:

  const count = await prisma.dnsRecord.count({
    where: {
      username,
      type,
      subdomain,
      value: type === 'CNAME' ? undefined : value,
    },
  });

The project already has some code to check for existing DNS records that are the same with the name, type, and value. Name is the subdomain name. Type is the type of record, such as CNAME. Value would be things like IP address or domain, depending on the type of DNS record.

Most of the relevant code already exist to check for count of duplicate DNS record, and I ended up adding a condition to not care about whether value is the same for CNAME records. As long as an existing CNAME record belongs to the same user and has same subdomain, another one counts as a duplicate. The value doesn't matter. These two criteria are chosen because username and subdomain name are used to create the domain name where a CNAME record would redirect people from, in this project.
Recall that we don't want to redirect from one domain to multiple domain. This is why we check for CNAME records with same user and subdomain.

It utilizes Prisma's count to do the counting in the database. where specifies what criteria for a row to count.

Re-request Certificate

Speaking of certificate, I also did a PR with a teammate together with peer programming, to add the ability for users to re-request a new certificate when it's about to expire.

What we did

The code checks for whether a certificate's validTo date is less than 30 days from present. If so, a re-request button will become available on the certificate page to allow the user to request another certificate.
Because we already have existing code to allow users to request their first certificate, we simply use/trigger that code again for the button.

Code Snippet for the button:

              <Flex justifyContent="flex-end">
                <Form method="post" onSubmit={() => setIsDisabled(true)}>
                  <IconButton
                    type="submit"
                    aria-label="renew-certificate"
                    icon={<RepeatIcon />}
                    backgroundColor="transparent"
                    color="black"
                    _hover={{ backgroundColor: 'brand.500', color: 'white' }}
                    isDisabled={isDisabled}
                  />
                </Form>
              </Flex>

Because the certificate request code is under an action function build with remix.js, and it's triggered by submit, we simply make this new button submit to reuse that code.

We did found out a minor bug where the program was only recognizing the second newest certificate for a user, because the code was sorting certificates by validTo date in descending order, to fetch the newest one, but a really new one would not have that field filled out for a short period. This caused the new certificate to be placed at the bottom of the sorting and not be select as the current certificate.

Peer programming

It's interesting sitting with another developer to work on code together. You get tow different perspectives, and you can learn form each other, whether it's expertise with tools and languages, coding style, or new ideas.

Because I'm unfamiliar with the front-end of the project, it was very helpful to sit with another developer who is familiar with that area.
He walked me through how the area of the front-end code we wanted to work on is constructed, and we put our ideas together to figure out how to tackle the issue.

Creating Releases And Returned Error

cychu42 — Sat, 01 Apr 2023 00:17:03 +0000

Tasks

This week, for the Starchart project, I added some explanation to how to create releases in GitHub repo, and I added some tests too.

I wanted to mention how to create releases because I noticed multiple people have been asking how to do it. It seems like a good idea to add some pointers in the wiki.

As for test, thing are quite usual except one interesting bug I found. (PR)

Create GitHub Releases

This is about making a release version of your application from your GitHub repo, in the repo's release page. This will generate copies of the source code for download, in the release.
GitHub has documentation on how to manage releases, including creating one.

The Page to create a release looks like this:

Assuming you have the access, you do it by:

Click "Releases" on a repo's main page, then click "Draft a new release" to get to the page shown.
Choose or create a tag, which can be a version.
Choose a branch, which might be main.
Give it a title and some description of what's added/changed. You can click "Generate release notes" to get some information from GitHub, including contributors, change log, and newly committed PRs.
You can set it as pre-release or latest release. You can even allow discussion under the release note. 6.(Optional) You can attach some files to the release.

As part of making a release, you might increment project version for a release. You can use CLI command npm version in the project directory.

For example, assuming you have initialized npm and git, npm version 0.8.0 -m "sample commit" will change the version in package.json and create a new commit with the commit message sample commit by -m option. This new commit will have a tag v0.8.0, which you can use for creating a release.

NOTE: locally created tags don't exist on GitHub repo unless you push them. To push the commit with the tag, you can push with --tags flag, such as git push origin main --tags. For more information on tags, see here.

Odd Bug

While writing a test, I noticed an odd behavior when I tested this code:

export function setIsReconciliationNeeded(
  reconciliationNeeded: SystemState['reconciliationNeeded']
) {
  try {
    return prisma.systemState.update({
      data: { reconciliationNeeded },
      where: { unique: StateEnumType.unique },
    });
  } catch (error) {
    return initialize();
  }
}

The code is meant to update a row in the SystemState table, to set reconciliationNeeded field by the parameter, which is Boolean (True/False). It uses Prisma's Update function to do the update.
The main and interest point is that this try block will not catch errors throw by [Update] when it fails to find such row, such as when it simply doesn't exist.
Given that the documentation states the function returns the exception when it can't find the target to update, my guess was that it's returning the error from the function with the return keyword, which makes the error land outside of the try block.

The expected behavior is to catch the error and then call and return initialize.
Therefore, I tried to avoid having return in the try block by doing it later:

export async function setIsReconciliationNeeded(
  reconciliationNeeded: SystemState['reconciliationNeeded']
) {
  let result;
  try {
    result = await prisma.systemState.update({
      data: { reconciliationNeeded },
      where: { unique: StateEnumType.unique },
    });
  } catch (error) {
    return initialize();
  }
  return result;
}

This way, the try block properly catches the error and execute the catch block.
Alternatively, a teammate come up with another solution:

export function setIsReconciliationNeeded(
  reconciliationNeeded: SystemState['reconciliationNeeded']
) {
  return prisma.systemState
    .update({
      data: { reconciliationNeeded },
      where: { unique: StateEnumType.unique },
    })
    .catch(() => {

      return initialize();
    });
}

This one instead deal with the error before the code hits return. The error is caught in catch and dealt with, before anything goes to return.

Starchart: 1,2,3, Testing!

cychu42 — Fri, 24 Mar 2023 16:30:32 +0000

What is it?

After having created a test database for the Starchart project, I went on to working on tests for functions relate to Prisma API calls. (See PR)

We use Vitest for unit testing, which is very similar to Jest. A test suite looks something like this:

describe('createUser()', () => {
  let user: User;

  beforeAll(async () => {
    user = await createUser(
      'jsmith',
      'John Smith',
      'jsmith@myseneca.ca',
      'mycustomdomain-students'
    );
  });

  afterAll(async () => {
    await prisma.user.deleteMany().catch(() => {});
  });

  test('creates an User row with expected fields', async () => {
    expect(typeof user).toEqual('object');
    expect(user.username).toEqual('jsmith');
    expect(user.displayName).toEqual('John Smith');
    expect(user.email).toEqual('jsmith@myseneca.ca');
    expect(user.group).toEqual('mycustomdomain-students');
    expect(user.createdAt).not.toBe(null);
    expect(user.createdAt).toEqual(user.updatedAt);
    expect(user.deactivated).toBe(false);
  });

describe() is a test suite, which can include many related individual test()s. Both let you include a string to state what it's for, which will show up in a formatted display when you run tests.
beforeAll() is a block of code that executes before all tests of a suite is ran, which can be handy for setting up things, like creating an user for testing.
afterAll() is a block of code that executes after all tests of a suite is ran, which can be handy for clean-up, like deleting all existing user.

Each expect() is a condition that must be satisfied for a test to pass. The code is more or like like plain English language, such as expect(user.createdAt).not.toBe(null); really means expecting user.createdAt to not be null. You can find all the syntax in their documentation.

You can put code, such as variable declaration an function calls, under describe() or test(). It's just a matter of scope for what you want to do.

Assuming you already setup the tool properly (see official guide), you can run the tests via npx vitest. A result can look like this:

For seeing test coverage, run npx vitest run --coverage.

As you can see, it tells you how much of the files are covered by tests, in terms of statements, branching paths, functions, and lines. It also shows uncovered lines.

Why do this?

This provides repeatable and standardized tests that are easy to run and view. You don't have to risk making mistake or forgetting a test like when one does manual testing of codes. The tool also provide good feed back by showing you what tests fail and what files aren't covered.
It's also something that can be shared in a repository and make collaboration easier.
An interesting way to use this is to write out some tests and have them act as technical requirements that guide the development of code. Essentially, you write out tests that tells developers what the software is supposed to do, and the developers write codes that would satisfy these tests that act as technical requirements.

Starchart: "Mocking" A Database Part 2

cychu42 — Fri, 17 Mar 2023 23:47:44 +0000

As a continuation from the previous post, I managed to make the test database work.
It's not mocking anymore, but I'm keeping the title for consistency.

The Solution

Let's have a snippet of the dock-compose YAML file, which is what I used to make the test database, so it's easier to talk about this:

This file is an instruction that will be followed to create docker containers, when you run docker compose up command.
For this bit of code, it tells docker to:

create a container using image of MySQL version 8.x.x
name it "database"
bind port 3306 (left) to the container's port 3306 (right)
use local machine's directory (left) as source of container's virtual directory (right), such as ./mysql-data:/var/lib/mysql
set certain environment variables

MYSQL_DATABASE indicates the database to be made with the name starchart.
MYSQL_USER and MYSQL_PASSWORD are the account and password with superuser access to that database.
MYSQL_ROOT_PASSWORD sets the password for the root user, which is a super user. Only this environment variable is mandatory.

SQL File for Test Database

As you can see in that code snippet, I source a file 01-databases.sql, which as a few SQL commands to setup a test database on top of starchart database. The file looks like this:

-- create test databases
CREATE DATABASE IF NOT EXISTS `starchart_test`;

-- create root user and grant rights
CREATE USER 'root'@'localhost' IDENTIFIED BY 'root_password';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';

First line creates the database if it doesn't exist already. The second line creates that user with specified password, and then it grants all access to that user.
When the MySQL database container is made, Docker checks for SQL files under /docker-entrypoint-initdb.d to run, so these lines would be executed.

What was the issue?

It turns out there were two issues with my connecting string to the test database.
As an example:
DATABASE_URL="mysql://root:root_password@127.0.0.1:3306/starchart_test"

starchart is the account to use to connect.
root_password is the password for the account.
127.0.0.1 the IP of the database.
3306 is the port.
starchart_test is the database name.

First, your system might refuse to connect to a high number port to avoid suspicious ports, so I needed to set it to be lower. A MySQL database docker container lists 2 ports: 3306 and 33060. I was trying to bind the 33060 port, which was too high, so a lower number port did the trick. Another issue was that I had the wrong name for the database. I mistakenly thought it was meant to be container name instead.

Starchart: Matching Database With SAML Claims

cychu42 — Sat, 11 Mar 2023 03:04:38 +0000

Task

What I did this week was matching User table of database in Starchart project with what useful information SAML would return about users when users log in.
This meant adding group column in User table in database, given it's one of the claims. group claim is used as role assignment, such as user being part of student group, which has specific access attached to the group.
This project uses Prisma to interact with the database.

There were 3 things I needed to do:

Edit the schema file (prisma\schema.prisma).
Edit the file (prisma\seed.ts) for seeding to make sure these seeded users have groups.
Chang how relevant functions interact with the database, so a function that make Prisma API call to create a user would need to be changed for the addition of group. When changing a function definition, one needs to remember to change how the function is used in other area of your code. For example, I changed the parameter of createUser() to allow argument for group, so I must change how the function is used elsewhere in the project too.
Cover new use cases for the column. This meant I added 3 functions to check whether a user belongs to one of 3 groups. The functions isStudent(), isFaculty(), and isAdmin() accept username as parameter. They would return true if they have the role/group, false if not. The code look something like this:
```
 export async function isFaculty(username: 
 PrismaUser['username']) {
   const { group } = await prisma.user.findUniqueOrThrow({ where: { username } });
   // The group will have -dev in it on staging but not on prod
   return /mycustomdomain(-dev)?-faculty/.test(group);
 }
```
async marks this function as asynchronous, since it needs to interact with the database and await a response.

The group value will be returned by the Prisma function after it fetches the user, by using the primary key username. Notice typescript syntax is used to limit the type of username according to Prisma User model's username column type.]
findUniqueOrThrow() will throw error if nothing is found, as the function name suggests. You can see the documentation here.

After group is returned, a regular expression is used and test against the group value. If there's a match, it will return true, or false in the case of no match. This looks for any mycustomdomain-faculty or mycustomdomain-dev-faculty in group.
Consider other adjustment to codes relating to User. There was no need for additional adjustment for this, but I could easily needed to do so. For example, if I changed a column name, I would need to change other references to the name in the project.

Also, I merged firstName and lastName columns into one displayName column, given that's what SAML gives back. The same ideas for such changes in database schema apply: edit the schema file, edit the seeding file, change/add relevant functions, and other relevant code adjustment. In this case, no new function was added.

Change Log Of The PR:

Add group in User model as String in prisma\schema.prisma
Add examples in prisma\seed.ts
Modify createUser() in app\models\user.server.ts accordingly
Add isStudent(), isFaculty(), and isAdmin() in app\models\user.server.ts.
Combine firstName and lastName into displayName
Modify arguments of createUser() in app\routes\login\callback.tsx

isStudent(), isFaculty(), and isAdmin() accept username as parameter. They would return true if they have the role/group, false if not.

Starchart: Mocking A Database Part 1

cychu42 — Fri, 24 Feb 2023 16:27:07 +0000

The story

This week, I'm trying to write some tests for CRUD operation functions I made last week.
The project uses Vitest, so I had to go over documentation for that to see what I need to do. It similar enough to Jest that syntax feels familiar, but there are some differences like how to import certain functions.
Actually writing tests is familiar enough for me that it doesn't cause any issue, but I'm rather stumped about how to mock a database that's interfaced via Prisma, as this is really new to me. Thus, the progress has been slow for the task.

When I first started, I found Prisma documentation for writing test and mocking PrismaClient instance that API calls go through, but it's not appropripate for the project, as we use Vitest. Then, I found a blog post on Prisma official site that covers using Vitest, but I'm now stuck with an issue of the code not being able to find deeply nested properties that are common for API calls, such as prisma.user.create().

How Mocking Prisma Is Supposed To Works In Vitest

This explanation assumes you are familiar with the basics of witting test in Jest.

You install an extension package via npm i -D vitest-mock-extended
Make a __Mocks__ folder that has a file named after the module you want to mock. In this case, it would be a file that exports an instance of PrismaClient. Make sure to place this folder next to the real module.
In the mocked module, place code to mock it:
```
 import { PrismaClient } from '@prisma/client'
 import { beforeEach } from 'vitest'
 import { mockDeep, mockReset } from 'vitest-mock-extended'

 beforeEach(() => {
 mockReset(prisma)
 })

 const prisma = mockDeep<PrismaClient>()
 export default prisma
```
This imports all the tools needed, resets the mocked instance before each test, and exports an mocked instance to be used in a test. Because we are dealing with deeply nested properties like prisma.user.create(), you want to use mockDeep<>().
In the test file you are writing, include import { expect, test, vi } from 'vitest' to import tools you need from Vitest, and use vi.mock(<route to the real module>) to tell Vitest you are mocking any mention of that route using the mocked modules in the Mocks folder next to the real one. You also need to import that mocked instance from the mocked module in step 3.
Like using Jest, write test and import any function you want to test. Also, make sure to Vitest what the expected result of a function would be from the mocked module. For example, prisma.user.create.mockResolvedValue({ id: 1 }) tells it that you expect {id:1} object to be returned from the API call. See other functions here.

What now?

Either I try to figure out why it's not getting deeply nested properties as it's supposed to, or....I got a suggestion from the team that I can alternatively try to look into making a test database instead of mocking, so I need to look more into how to work with the dock-compose YAML file in the project to make it happen. So far, I managed to make a test database by editing the file, but something is preventing Prisma from connecting to it and throws Error: P1017: Server has closed the connection, which is an issue I need figure out in order to continue this approach.

Starchart: Schema Tweak and CRUD Operations

cychu42 — Fri, 17 Feb 2023 14:53:54 +0000

This week, I'm tweaking column names and adding CRUD functions to the Startchart project.

What I did

Because it's in Typescript, I tried to set type for the parameters, and that makes things easier to change. Example: username: Record['username']
They will look to the Record model for the type, and I only need to make changes in there to change all related parameters.

The CRUD functions are essentially simple exported functions that wrap around Prisma related code to allow developers to not worry about how Prisma works. They just enter the arguments and let the functions do what they are supposed to do.

I tried to write functions to enable basic CRUD operations that will be useful for developers to interact with the database via Prisma.
Part of that was trying to be mindful of how other people might use the functions, so I favored more accessible parameter like username over things like index id that might be more obscured, whenever possible. I also try to be consistent, so it's less confusing, which means I try to ask for the same thing in the parameter, such as username, or id if username isn't available or doesn't' make sense.
Another part of that is to think about whether the name properly explains what the function is supposed to do, such as updateRecordById(), and whether the parameter conveys enough information. I think, if someone sees the functions name and the parameter, they would be able to infer that this is for updating Record model by id, and they need an id and other optional field that they want to change. The parameter is:

(
  id: Record['id'],
  username?: Record['username'],
  name?: Record['name'],
  type?: Record['type'],
  value?: Record['value'],
  description?: Record['description'],
  course?: Record['course'],
  ports?: Record['ports'],
  expiresAt?: Record['expiresAt'],
  status?: Record['status']
)

As you can see, optional fields are marked by ?, and they all have type associated from columns in the model. There was a discusion in the team abotu using this as a way to "document" functions, and I hope this does that.

Not everyone is going to be comfortable writing Prisma related code, and it's nice to have reusable code across the project.
I also tried to follow the format of the existing CRUD function for User model, for project consistency.

Experience

It involved a bit of juggling changes, since I want to change column names while also thinking about CRUD operation, so I operated on the assume of my column name changes when I wrote the functions. If I change something in schema, I then have to follow suit in the functions.
The changes got merged, so all is well, and I was able to push the pull request in a timely manner.

Because a lot of people from different part of the development team will be using the database, I also got me thinking about not doing too frequent changes to the schema. I tried to wait to accumulate some non-critical changes and pick a good time (such as before CRUD functions are made available) to make the changes.

Because Prisma client requires you to regenerate every time you change the schema to sync to it, I need to do npx prisma generate a few times (It's a different command for this project due to scripts used). Otherwise, it would not acknowledge the changes and will complain about it. I do notice I have to restart VS code for complain/error to go away even after regenerating the client.

Starchart: Seeding

cychu42 — Fri, 10 Feb 2023 20:22:59 +0000

This week, my main focus is to work on seeding the database for Starchart project. (see PR)
I thought it would be more complicated, but it's actually just writing query with Prisma to insert rows of data into database tables, and there's a bit of script setup.

Setup (official documentation)

Requirement:
Make sure you have Prisma setup already. You need to first have a pakcage.json generated from npm init too.

Inside pakcage.json, add the following:

"prisma": {
    "seed": "ts-node prisma/seed.ts"
}

If your project needs to use compile option, you should use this instead:

"prisma": { 
     "seed": "ts-node --compiler-options {\"module\":\"CommonJS\"} prisma/seed.ts" 
},

Add seed.ts under prisma folder.
Add code for "create" query inside the file to add rows of data.
Run npx prisma db seed.

There! If the steps are done correctly, you just seeded the database with some data for testing or whatever you need to do!

Experience

I realized there are a few things to consider as I worked on this.
You need to thing about cleaning existing data. Because there are some foreign keys in the schema, I need to think about the order I delete data from each table. Otherwise, it can run into errors. It would be easier if one sets up cascading delete, where a row is automatically deleted if the referenced row (usually from another table) for the row's foreign key is deleted.
Because of the foreign keys, another thing to consider is to create things in the correct order, so there's something to be referenced before referencing it.
Lastly, it would be intuitive to let index auto-increment and make index the thing to be referenced for foreign keys. However, doing both would present a problem when you are doing seeding. The referenced value need to stay consistent so that you can properly connect one row to a specific row. Therefore, you actually need to specify the index in such scenario, such as the id here:

Startchart: Prisma Schema

cychu42 — Sat, 04 Feb 2023 02:40:41 +0000

Background

This week, I focus on creating an initial schema for the project through using Prisma. This is for a MySQL database, and the PR for the initial schema is here.

This is an example of a model:

A model describes how a table would look like on a database.

Basics

Record would be the table name.
Green texts are the value types for the columns.
The blue texts are the names of the columns.
@id marks the primary key.
? marks a column as nullable.
@default sets the default value as what's in the parameter.
now() returns the current time.
@updatedAt would provide the value that's the time a row is updated.
Foreign key:
In the example, the line with user is used to record a relationship between this and the other table, for the purpose of marking a foreign key. fields's value is the foreign key, while references's value points to a column on another table.
On the other table, it need to have another corresponding line to fully establish the relationship, like:
[] means it's a to-many relationship. You don't have that if it's to-one relationship.
You might notice some odd type like RecordType. That's enumeration, and you can declare that in the same file:
As you can see, the green text is the name of the type, and the blue texts are the possible values for such type.

Experience

As far as creating schema goes, Prisma is very friendly and easy to use, once you learn the basic, such as how to mark primary key or make values in a column unique. Establishing foreign key and the relationship between tables take a bit more work to understand. It helps if you already know how a database works.

Because I need to create the database to serve the need of other parts of the project, such as what user information people who handle login want to store, I ask around the team. I sort of feel like someone who goes around with a clip board and ask what they would like to order for lunch. I ask them what they would like to include and why...to have better comprehension of the project and for my own learning, and it's always interesting to learn more.

Starchart: Planning Toward Release 0.1

cychu42 — Sat, 28 Jan 2023 03:49:52 +0000

This week, we some planning for Starchart release 0.1.
The first waves of issues were filed, and tasks were assigned to the team members. I'm to contribute in setting up database schema and the ORM, Prisma, as well as finding out how to run it in Docker.

My Issues

I filed 7 issues in total:
Issue #6 is about adding a wiki entry for Prisma to serve as a start point for helping team members looking into the technology.
Issue #9 is about notifying users about their SSL certificate request status via email
Issue #10 centers around creating a page for user to view information of SSL certificate they have requested.
Issue #11 talks about a feature to notify users about their SSL certificate expiration and next-steps
Issue #12 is regarding documentation for helping developers setup their local environment for development of this project.
Issue #13 is regarding documentation for helping developers follow flow of contribution to contribute to the project.
Issue #16 is for creating a certificate table in the database to record information of SSL certificate created.

Technology Research

Because much of the tech stack is new to me, I have to spend time reading the documentations to familiarize myself with it.
Since I try to focus on database, my primary focus is on MySQL and Prisma, which is the primary way the project uses to interact with a MySQL database.
In my research, I found information for how to set up Prisma for a MySQL database, and I have tried the steps.
Through commands, one can run a .prisma file to push/pull table schema to/from the target database. One can also run commands to run a file with code that essentially act like SQL statements.

Here are some basic CLI commands:

npx prisma db pull to pull database tables into your schema file.
npx prisma generate to sync client to current schema in the local schema file; do this before you do CRUD SQL operation via prisma.
npx ts-node <file location> to execute CRUD operation code in the file.
npx prisma migrate dev --name <any migration name> to migrate your schema.prisma file to target database. Because prisma make a shadow database (temporary) every time to make sure database is sync, use npx prisma database push if you don’t have right to make another database (no benefit from shadow database)
npx prisma studio to start Prisma studio, the visual editor UI for database.

Also, I tried to look up how to run MySQL database in Docker. It seems you need to download an image of a MySQL database, and then create an instance in Docker, as detailed on MySQL's website.
The Docker documentation suggests an one-line command to do so:docker run --name some-mysql -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:tag.