We've written the HTTP layer of the application; what remains is to implement the storage layer and the basic logic for the microblogging site. We'll give you an example schema and write some database interactions, but so long as the POSTs and GETs to your service return the expected json, the design of the schema and the service implementation is up to you.

📚 If you haven't read our tutorial for how to construct a relational database schema using OrderedTables, you should check that out before starting this exercise.

Schema design best practices and tips

Because we've started with the API layer, we already know the queries we'll be making and we can work backwards to design the database schema.

First, we can write a few types the database will store and retrieve. For example, we might need a UserRow, PostRow, and FollowingRow that store the data for users, posts, and the relationships between users.

{- These types have a `v2` prefix because
 the sample schema has changed over time -}

type db.v2.UserHandle = UserHandle Text
type db.v2.FollowingId = FollowingId UID
type db.v2.PostId = PostId UID

type db.v2.UserRow = { userHandle : UserHandle,
        name : Text,
        avatar : Optional URI }

type db.v2.PostRow = { id : PostId,
        createdAt : OffsetDateTime,
        body : Text,
        userHandle : UserHandle }

type db.v2.FollowingRow = { followingId : FollowingId,
        follower : UserHandle,
        target : UserHandle,
        createdAt : OffsetDateTime }

Questions you might consider when defining tables:

  • How much data normalization is preferable for the schema? Should your keys be randomly generated IDs that reference the data in another table, or should an entire data blob be stored directly in the table?
  • How can you ensure the uniqueness of a given key in your OrderedTable table? A random ID? Timestamps? Etc? (You can assume that the user's handle is unique.)
  • What requests will require keeping multiple tables in sync? Cloud databases support transactions via the Transaction ability, but they have size constraints and too many transactions can lock down your tables.

Many of the tables will likely be OrderedTable's—sorted key-value tables that support tuple key types and querying by ranges. As you're designing your schema consider leveraging both of those features.

For example, if we needed to get a user's most recent posts, rather than storing all the user's posts in a list, we could store the posts in an OrderedTable keyed by the user handle and the timestamp of the post. This way, we can query the table via the handle prefix, to get all the posts in a sorted order:

userPostsTable: OrderedTable (UserHandle, OffsetDateTime) PostRow
userPostsTable = OrderedTable.named database "userPosts" Universal.ordering

getUserPosts =
  rangeClosed.prefix userPostsTable OrderedTable.prefixOrdering targetUser targetUser
    |> Stream.toList

📝 Instructions

Once you've thought about the set of tables needed to support the app, update your deploy function to create the Database and tables.

If you'd like, you can use this sample database schema type:

type db.v2.AppStorage = { database : Database,
        userHandleToUser : OrderedTable UserHandle UserRow,
        userPostsTable : OrderedTable (UserHandle, OffsetDateTime) PostRow,
        postsTable : OrderedTable PostId PostRow,
        followingTable : OrderedTable (UserHandle, UserHandle) FollowingRow }

It's often handy to have a single type that holds all the tables you'll need to interact with, so you can pass them around as a single argument to functions (like your service routes) that need to interact with the database.

Creating an instance of this type will require creating the database and the tables. This operation is idempotent, so you can incorporate it into the deploy function, but you should be mindful that changing the types in your database schema can have adverse effects on data you've already written. That's why these table names are suffixed with a version number.

db.createAppStorage : '{Exception, Cloud} AppStorage
db.createAppStorage = do
  db = Database.named "microblog-solutions-v2"
  Database.assign db exercises.env()
  userHandleToUser = OrderedTable.named db "userHandleToUser-v2" Universal.ordering
  userPostsTable = OrderedTable.named db "userPostsTable-v2" Universal.ordering
  postsTable = OrderedTable.named db "postsTable-v2" Universal.ordering
  followingTable = OrderedTable.named db "followingTable-v2" Universal.ordering
  AppStorage db userHandleToUser userPostsTable postsTable followingTable

Update the deploy function to create the tables for the service and pass them to the service API layer.

cloud-start/main> edit ex3_microblog.deploy

Transactions and other abilities in database interactions

Now we can get started writing database interactions. Let's look at an example that creates a blog post. In this database interaction, we have two tables that need to be kept in sync:

  • One links a UserHandle to a PostRow - used to retrieve all posts by a user in time order
  • And another stores the PostRow data keyed by its PostId - used to get a post by its ID.
instantToOffsetDateTime : '{Remote} OffsetDateTime
instantToOffsetDateTime = do
  instantNow = Remote.now!
  Instant.atOffset instantNow UTC

db.createPost : AppStorage -> UserHandle -> Text ->{Exception, Storage, Remote, Random} PostRow
db.createPost storage userHandle body =
  postId = PostId.PostId (Remote.UID.random())
  timeStamp = instantToOffsetDateTime()
  postTable = AppStorage.postsTable storage
  userPostsTable = AppStorage.userPostsTable storage
  transact.random (AppStorage.database storage) do
    postRow = PostRow.PostRow postId timeStamp body userHandle
    write.tx postTable postId postRow
    write.tx userPostsTable (userHandle, timeStamp) postRow
    postRow

Cloud services don't have access to arbitrary IO, so generating timestamps or random IDs will require using the Remote ability. We're using Remote.now! and Remote.UID.random instead of IO.

The db.createPost function also uses the Transaction ability to perform multiple operations that should fail or succeed together. Most functions defined for updating records in an OrderedTable have a transactional variant, suffixed with .tx.

Because we're writing to an OrderedTable, the transaction also requires the Random ability. Depending on what abilities your combined database operations require, run the transaction in one of the following:

transact        : Database
                  -> '{Transaction, Exception} a
                  ->{Exception, Storage} a

transact.random : Database
                  -> '{Transaction, Exception, Random} a
                  ->{Exception, Storage, Random} a

📝 Instructions

Write the remaining database functions for the microblogging site and call them inside your earlier service route functions to connect the API layer to the database layer.

Your service routes should do the following:

  • They should run the necessary database operations to create, read, update, and delete data.
  • They should translate the types returned from the database layer into the types expected by the API layer.

We won't test for all of the possible workflows in your app, but we will check if your service can create a new user, have that user create a post, and then retrieve that post by its ID. We'll also check if your app allows a user to follow another user, so they can see a microblog feed of interesting posts.

When you're ready, update your codebase and submit your solution. Remember, if you're ever stuck, you can run the deploy function for the service and test your service live by hitting its endpoints!

cloud-start/main> run submit.ex3_microblog.roundTrip
Solution

The source for this service is available in the exercises.solutions.ex3_microblog namespace in the cloud-start project. Check it out if you get stuck!

Creating a user

The createUser endpoint writes the user to the table, keyed by their UserHandle, which we're also using as the "UserId" return value.

api.createUser : AppStorage -> '{Route, Exception, Storage, Remote, Random} ()
api.createUser storage = do
  userHandleText = route POST (s "api" / s "user" / Parser.text)
  newUser = decodeJson User.fromJson
  avatarURI = Optional.flatMap URI.parseOptional (User.avatar newUser)
  userName' = userName newUser
  userHandle = UserHandle userHandleText
  OrderedTable.write
    (userHandleToUser storage)
    userHandle
    (UserRow.UserRow userHandle userName' avatarURI)
  ok.json (UserId.toJson <| client.UserId.UserId userHandleText)

Getting a Post by ID

This endpoint reads the post from the postsTable and the user's name from the userHandleToUser table in a single transaction.

api.getPostById : AppStorage -> '{Route, Exception, Storage, Remote} ()
api.getPostById storage = do
  postUID =
    UID (Text.toUtf8 (route GET (s "api" / s "posts" / Parser.text)))
  post = transact (AppStorage.database storage) do
    (v2.PostRow.PostRow _ createdAt body userHandle) =
      read.tx
        (AppStorage.postsTable storage)
        (PostId.PostId postUID)
    (UserRow.UserRow
      (UserHandle userHandleAsText) userName avatar)
      =
      read.tx (userHandleToUser storage) userHandle
    timeTxt =
      OffsetDateTime.toText createdAt
    Post.toJson (Post body userName userHandleAsText timeTxt)
  ok.json post

Following a user

Following a user involves writing a row to the followingTable table. This one does not need a transaction because it's a single write.

db.followUser :
  AppStorage
  -> UserHandle
  -> UserHandle
  ->{Exception, Storage, Remote, Random} FollowingRow
db.followUser storage follower target =
  followingTable = AppStorage.followingTable storage
  followingId = FollowingId.FollowingId UID.random()
  timeStamp = Instant.atUTC now!
  followingRow =
    FollowingRow.FollowingRow followingId follower target timeStamp
  OrderedTable.write followingTable (follower, target) followingRow
  followingRow

api.followUser : AppStorage -> '{Route, Exception, Storage, Remote, Random} ()
api.followUser storage = do
  userHandle = route POST (s "api" / s "follow" / Parser.text)
  let
    (follower, target) = decodeJson FollowRequest.fromJson
    (FollowingRow.FollowingRow id _ _ _) =
      db.followUser
        storage (UserHandle follower) (UserHandle target)
    ok.json (FollowingId.toJson id)

Getting a user's feed

Getting a user's feed involves getting all the handles a user is following from one table, then getting all the posts from those users.

Our table representing follower relationships is keyed by a tuple of (userHandle, targetHandleToFollow), so we can use rangeClosed.prefix to get all the targets's handles. With the UserHandles in hand, we can then query the userPostsTable to get all the posts from those users.

db.getFeedPosts : AppStorage -> UserHandle ->{Remote} [PostRow]
db.getFeedPosts storage followerHandle =
  followerTable : OrderedTable (UserHandle, UserHandle) FollowingRow
  followerTable = followingTable storage
  targetUsers : '{Remote, Stream UserHandle} ()
  targetUsers =
    rangeClosed.prefix
      followerTable prefixOrdering followerHandle followerHandle
      |> map at2
      |> map FollowingRow.target
  userPosts : OrderedTable (UserHandle, OffsetDateTime) PostRow
  userPosts = AppStorage.userPostsTable storage
  posts : '{Remote, Stream PostRow} ()
  posts =
    Stream.flatMap
      (uhandle -> rangeClosed.prefix userPosts prefixOrdering uhandle uhandle ())
      targetUsers
      |> Stream.map at2
  Stream.toList posts

Incorporating this into the api.getFeed route looks like this:

api.getFeed : AppStorage -> '{Route, Exception, State, Remote} ()
api.getFeed storage = do
  userHandle = route GET (s "api" / s "feed" / Parser.text)
  posts : [PostRow]
  posts = getFeedPosts storage (UserHandle userHandle)
  postClient = List.map (cases
    PostRow.PostRow _ createdAt body uhandle ->
      (UserRow.UserRow (UserHandle uh) name _) =
        OrderedTable.read (userHandleToUser storage) uhandle
      Post body name uh (OffsetDateTime.toText createdAt))
    posts
  postsJson = Posts.toJson postClient
  ok.json postsJson