Skip to main content

Write to the Database

We are ready to add our new comments feature.


Scaffold business logicโ€‹

We'll start by scaffolding the domain code first. As mentioned in the last chapter, we'll add this to our core package.

Open up services/core/article.ts and add the following two functions to the bottom of the file.

export function addComment(articleID: string, text: string) {
// code for adding a comment to an article
}

export function comments(articleID: string) {
// code for getting a list of comments of an article
}

Before we can implement them, we'll need to create a new table to store the comments.


Create a migrationโ€‹

Let's create a new migration for this.

Run this in the root of the project to create a new migration

npm run gen migration new

It'll ask you to name your migration. Type in comment.

? Migration name โ€บ comment

Once the migration is created, you should see the following in your terminal.

โœ” Migration name ยท comment

Loaded templates: _templates
added: services/migrations/1661988563371_comment.mjs

Open up the new migration script and replace its content with:

services/migrations/1661988563371_comment.mjs
import { Kysely } from "kysely";

/**
* @param db {Kysely<any>}
*/
export async function up(db) {
await db.schema
.createTable("comment")
.addColumn("commentID", "text", (col) => col.primaryKey())
.addColumn("articleID", "text", (col) => col.notNull())
.addColumn("text", "text", (col) => col.notNull())
.execute();
}

/**
* @param db {Kysely<any>}
*/
export async function down(db) {
await db.schema.dropTable("comment").execute();
}

This migration will create a new table called comment. While undoing the migration will drop the table.


Run a migrationโ€‹

Let's go ahead and run the migration.

Go to the RDS tab in SST Console and click Apply on our comment migration.

Console run migration

To verify that the table has been created; enter the following in the query editor, and hit Execute.

SELECT * FROM comment

Console query comments table

You should see 0 rows being returned.


Query the tableโ€‹

We are now ready to implement the addComment and comments functions.

Replace the two placeholder functions in services/core/article.ts with:

services/core/article.ts
export function addComment(articleID: string, text: string) {
return SQL.DB.insertInto("comment")
.values({
commentID: ulid(),
articleID,
text,
})
.returningAll()
.executeTakeFirstOrThrow();
}

export function comments(articleID: string) {
return SQL.DB.selectFrom("comment")
.selectAll()
.where("articleID", "=", articleID)
.execute();
}

We are using Kysely to run typesafe queries against our database.

Behind the scenes

There are a couple of interesting details here, let's dig in:

  1. SQL.DB is the Kysely instance imported from services/core/sql.ts.

    services/core/sql.ts
    export const DB = new Kysely<Database>({
    dialect: new DataApiDialect({
    mode: "postgres",
    driver: {
    secretArn: Config.RDS_SECRET_ARN,
    resourceArn: Config.RDS_ARN,
    database: Config.RDS_DATABASE,
    client: new RDSDataService(),
    },
    }),
    });
  2. You might recall us talking about the Config values back in the Project Structure chapter. They are passed in to our API in stacks/Api.ts.

    stacks/Api.ts
    function: {
    permissions: [db.rds],
    config: [...db.parameters],
    },

    And were defined in the stacks/Database.ts.

    stacks/Database.ts
    parameters: [
    new Config.Parameter(stack, "RDS_SECRET_ARN", {
    value: rds.secretArn,
    }),
    new Config.Parameter(stack, "RDS_DATABASE", {
    value: rds.defaultDatabaseName,
    }),
    new Config.Parameter(stack, "RDS_ARN", {
    value: rds.clusterArn,
    }),
    ],
  3. The Kysely instance also needs a Database type. This is coming from services/core/sql.generated.ts.

    services/core/sql.generated.ts
    export interface Database {
    article: Article;
    comment: Comment;
    }

    The keys of this interface are the table names in our database. And they in turn point to other interfaces that list the column types of the respective tables. For example, here's the new Comment table we just created:

    export interface Comment {
    articleID: string;
    commentID: string;
    text: string;
    }
  4. The sql.generated.ts types file, as you might've guessed in auto-generated. Our infrastructure code generates this when a new migration is run!

    It's defined in stacks/Database.ts.

    stacks/Database.ts
    const rds = new RDS(stack, "rds", {
    engine: "postgresql11.13",
    migrations: "services/migrations",
    types: "services/core/sql.generated.ts",
    defaultDatabaseName: "main",
    });

    Even though this file is auto-generated, you should check it into Git. We'll be relying on it later on in this tutorial.


Now with our business logic and database queries implemented, we are ready to hook up our API.