Database

Glimpse uses PostgreSQL as it's RDBMS. Our current Docker Compose files run PostgreSQL 14, which is the currently recommended version of Postgres to use. Glimpse uses Prisma as its primary method of maintaining and interacting with the Glimpse database under the hood. Prisma offers two tools which we take advantage of: Prisma Client and Prisma Migrate.

ℹ️ If you are a Computer Science student at RPI and are looking to fulfill a CS elective for your concentration, I highly recommend Database Systems. It will introduce you to both the high-level concepts of relational databases, and to more in-depth concepts of how databases work under the hood, allowing you to better take advantage of indexes, routines, and other features within SQL databases that improve productivity and efficiency.

Prisma Schema

Both Prisma Migrate and Prisma Client rely on a Prisma schema, which is the single source of truth for how our database is structured, as well as the way our code is capable of interacting with the database via generated ORM code. This file is located at /prisma/prisma.schema. This article will not go into detail about the syntax of Prisma schema files, so it is recommended that you take a look at the Prisma schema reference documentation for more information about how these files are structured.

With that said, there are a few standards we implement within our schema.

• Every model (table) has an ID field which is its primary key. For some models, this may not seem necessary right now, but for the sake of future-proofing against additional fields, it's useful to have a single-field primary key, allowing us to later adjust other unique keys without interfering with the primary keys.
• All IDs are BigInts. This does complicate code a bit, since in the current JSON specification, BigInts must be represented as strings. Since integers in PostgreSQL are maxed out at 32 bits, it's not inconceivable that we could hit that 32-bit limit sometime in the far future on records which are generated extremely frequently (e.g. AuditLogs).
• Tables and columns within the database are named in snake_case, however in our schema and code, we use camelCase. The Prisma @map and @@map attributes are used frequently to map these camel case variables in the schema to the snake case equivalent in the database. Tables are also pluralized, so the model Category will be mapped to the table categories.
• @map is also used to map the names of columns with foreign keys. For example, the Group type can have a parent group. In the database, this is represented with the parent column, which is a foreign key to another group. In our Prisma schema, we map this column to instead be called parentId. This allows us to have a "virtual" field within the Prisma schema, parent, which refers to the parent group itself, as opposed to just the group's ID.
• Primary keys, foreign keys, and indexes all follow a general naming scheme:
  • Primary keys - table_pk, e.g. access_logs_pk
  • Foreign keys - source_destination_column_fk, e.g. access_logs_users_id_fk for the foreign key linking an access log to the user which it corresponds to.
  • Indexes - table_col1_col2_col3_index, e.g. alert_logs_severity_timestamp_index for the AccessLog table index which indexes severity and timestamp, in that order.

Prisma Client

Prisma Client takes our schema and generates a TypeScript library to cleanly interact with our database without having to write SQL ourselves. This TypeScript library can be generated via npx prisma generate, and is located within the node_modules/@prisma/client folder. As an example of how to use the client, the following SQL query is equivalent to the TypeScript code below it, using Prisma.

SELECT username, mail FROM users WHERE id = 1;

const user = await this.prisma.user.findFirst({
  where: {
    id: 1
  },
  select: {
    username: true,
    mail: true
  }
});

The returned user variable is completely type-safe, which is one of the most attractive features of Prisma. Prisma is a, relatively speaking, young library. You may find at times there are goals that you simply cannot accomplish with Prisma. In these scenarios, you can still use raw SQL queries via prisma.$queryRaw, however you may have to sacrifice some type safety.

Using the client via transactions

For every request to the API, we generate a Prisma transaction for the lifetime of the request. In 95% of cases, you will want to perform your database actions through this transaction. We use this transaction so that in the event of any error (whether it be a database constraint error, permissions failure, or otherwise), the transaction will roll back any and all changes that were made during the request. The transaction is available on Express.Request#prismaTx (req.prismaTx).

• For GraphQL requests, the transaction is opened in /src/prisma/prisma.plugin.ts. It will commit it's changes if no resolvers set Express.Request#passed (req.passed) to false. If any resolver sets it to false, then the changes from all resolvers are rolled back.

• For HTTP requests, the transaction is opened in /src/prisma/prisma.interceptor.ts. Similarly to GraphQL, it will commit it's changes if the endpoint controller does not set Express.Request#passed (req.passed) to false. If the controller sets it to false, then the changes from all resolvers are rolled back.

As an extra safety precaution, @rule directive and @Rule decorator handlers must set req.passed to true; leaving it undefined will have the same behavior as if it were set to false. For more information, read the Authorization article.

Example

As an example, here is a simplified version of the createAsset GraphQL resolver.

@Mutation(() => Asset, { complexity: Complexities.Create })
@Directive("@rule(ruleType: Create, subject: Asset)")
async createAsset(
    @Context() ctx: { req: Request },
    @Args("input", { type: () => CreateAssetInput }) input: CreateAssetInput
): Promise<Asset> {
    const result = await ctx.req.prismaTx.asset.create({
        data: input
    });
    return result;
}

In this example, the Prisma transaction will be committed as long as the checks within the @rule directive all pass. If we were to add req.passed = false to the code, the changes will roll back, regardless of whether req.passed was set to false before or after the .create() call.

// ...
const result = await ctx.req.prismaTx.asset.create({
    data: input
});
ctx.req.passed = false;
return result;
// ...

The user will receive a Forbidden error.

⚠️ This implementation may have significant performance implications, and/or open us up to denial-of-service attacks. In certain scenarios, transactions will lock database tables which they interact with for the lifetime of the transaction. This means if multiple requests are coming in trying to access the same table, one request will have to wait for the other to finish completely before being able to continue. Analysis will need to be done of the impact of this system in production. This may also need to change if there ever comes a time where we need to do a significant amount of computation or await network requests mid-request.

Using the client directly

In very select circumstances, you may want to make changes to the database even when the request fails (for example, logging failed login attempts). The Prisma client is available via the PrismaService. To use it, make sure the PrismaModule is imported into the module you are working with. Here is an example from the AssetModule:

@Module({
    providers: [AssetResolver],
    imports: [PrismaModule]
})
export class AssetModule {}

After this, you can inject the Prisma service via dependency injection:

constructor(private readonly prisma: PrismaService) {}

The Prisma client can then be accessed within your service's methods via this.prisma.

ℹ️ The PrismaService injectable is an extension on the base Prisma client. All methods which would be available on new PrismaClient() are available on an injected instance of PrismaService.

Extensions

Prisma has early beta support for Prisma extensions, which allow you to add custom functionality to your queries, clients, and responses. This is still a preview feature, and we currently aren't using it (Currently held up by https://github.com/prisma/prisma/issues/17948).

With that said, we are still able to extend the Prisma client by adding our own methods to the PrismaService. Currently we are doing this to add the genAuditLog method, which removes some of the boilerplate of inserting records into the database. This method is also available on transactions, and the inserted records will be rolled back if the transaction is rolled back.

Prisma Migrate

Throughout the lifespan of an application, the database schema is bound to change. Prisma Migrate helps alleviate any "growing pains" that you might encounter when updating your schema. In simple terms, your database schema is defined within your Prisma Schema file, and then Prisma Migrate is responsible for keeping your database schema in sync. Prisma Migrate already provides great documentation to help you understand how Prisma Migrate functions. If you plan on making changes to the data schema, I highly recommend you give this article a read.

Here are a few highlights from that article, and tips when using Prisma Migrate:

• Migrations can be thought of as the database-equivalent of Git commits.
• In development, Prisma migrations can be generated via the npx prisma migrate dev command. This will sync your database with the Prisma schema, and if there are any additional changes to the schema detected, it will prompt you to provide a name for a new migration. New changes will be added to a SQL file in a folder within the /prisma/migrations directory. These SQL files are the "instructions" for your database to move from one version to the next. All files in this directory should be committed to source control.
• It is safe to modify the generated SQL files, as long as the end result of your database's schema matches the Prisma schema. In fact, modifying the generated files may often be required in order to prevent data loss.
  • E.g. If you rename a column within the Prisma schema, Prisma Migrate may interpret this as you deleting one column and adding another, which would drop all data from the original column. You can modify the migration SQL script to properly rename the column and preserve data instead of dropping and recreating.
• Your database in development is intended to be reset frequently. If you are switching between branches that have different schemas, a database reset will likely be necessary. You can reset the database via npx prisma migrate reset, and then re-bootstrap your database via npm run cli. See the Bootstrapping article for more information.
• When deploying to production, you should use npx prisma migrate deploy to deploy your database changes. It is likely a good idea to take a backup of the database before deploying your migrations.
  • This command, along with the backup, should be automated with CI/CD. See issue #73 for progress.
• Here is a simple example of how properties can be added to your schema. Future versions of the Glimpse API may require additional steps, as more features are added.

ℹ️ Running npx prisma migrate dev will automatically also run npx prisma generate to generate your Prisma Client.