Database Project
Your project this week is to build a web app that stores data in a SQLite database.
Spike
Before you start writing features you need to design the schema for your data. Think about what different things your app needs to store, how they relate to each other, and how you can avoid duplicating information. Record your schema in your README.md
using Markdown tables. Consider embedding a diagram to help visualise the relationships.
Questions to consider
- What kinds of data relationships are there?
- What’s a foreign key? How can they help us design schemas with relational data?
Useful resources
User stories
Core
- As a user, I want to: submit information to your site for anyone to see
- As a user, I want to: come back to your site later and see what I posted is still there
Since this project is open-ended you’ll need to write your own more specific user stories once you know what you want to build.
Example project ideas
- Founders & Coders book sharing system
- Food / coffee recommendations around Founders & Coders
- Founders & Coders events calendar
Acceptance Criteria
Stretch criteria
Deploying persistent volumes to Fly.io
A simple mental model for app deployment is that we’re installing and running our app on a computer in the cloud. Yet this is an oversimplification and an important difference is that the PaaS services we use for deployment (ie Fly) have an ephemeral file system.
An ephemeral file system is a type of file system that is not permanently stored on a device or disk. It exists only for a short period of time, and any files or data stored in it are not persisted after the session ends or the file system is deleted. This type of file system is usually used in cloud computing environment as it allows for greater flexibility and efficiency compared to using permanent storage.
As Fly uses an ephemeral file system and SQLite works by storing the database as a file on the system we need to make special provisions for deployment. If we didn’t, then our database would disappear every time the file system is restarted.
To solve this we’re going to use Fly’s Volume’s feature.
Adding a volume to your app
-
Install the Fly CLI and deploy your app if you haven’t already.
-
Create a volume by running the following command
flyctl volumes create data --region lhr --size 1
This will create a 1GB volume for the app whose
fly.toml
is in the current directory.You can think of a volume as being like an external hard drive that we get to use with our app.
-
You can use the volume you just created in your app by adding it to the
fly.toml
:[mounts] source = "data" destination = "/data"
This will tell us the directory under which this volume can be used, i.e. the volume
data
that we created in the last step will be available on the path/data
when our app runs onFly.io
's servers. -
Now we have to deploy again so that the app knows about our updated
fly.toml
fileflyctl deploy
-
The last step is to set the environment variable which we use in the our app (i.e. in
database/db.js
). On fly, this needs to point to the directory we referenced in ourfly.toml
file (iedata/
)flyctl secrets set DB_FILE=/data/db.sqlite
Seeding your app
We might also want to seed our database with initial data in our deployment environment. When we do this locally we usually run a command on the terminal which calls a script to insert the data, e.g. node src/database/seed.js
.
We’re going to do something similar to seed the remote data, but instead of running a command on our local terminal, we’re going to connect to the virtual machine that Fly has spun up for our app and run a command on that virtual machine’s terminal.
-
Before we connecting to the virtual machine, we’ll need to add the
volta
key to ourpackage.json
. Volta is the tool that Fly uses to manage node versions.Let’s add the following to our
package.json
(making sure that the node version you specify is the same one as in the generatedDockerfile
){ ... "volta": { "node": "18.10.0" } }
Then run
flyctl deploy
to get these changes onto our deployed app. -
To connect to the remote virtual machine we run the following command. SSH stands for Secure Shell and is a way to securely connect to another computer over a network.
flyctl ssh console
-
Once you’ve connected to the terminal you can find your files in the
app/
directory and from there you can run your script.cd app
node src/database/seed.js
Your database should now be seeded and you can check this by going to the URL for your deployed app and checking the data is there.