Bohr + Planetscale - Step-by-step tutorial

Bohr project Example using PlanetScale.

Prerequisites

• Bohr account.
• Planetscale account.

Set up the database

After login in to Planetscale, create a new database on your prefered region.

Navigate to the 'Branches' tab and chose the branch you created.

Find the 'Console' tab, and type the following code to create the "users" table.

CREATE TABLE `users` (
  `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `email` varchar(255) NOT NULL,
  `first_name` varchar(255),
  `last_name` varchar(255)
);

After creating the users table, now we have to insert data into it. So type the following code on the console:

INSERT INTO `users` (id, email, first_name, last_name)
VALUES  (1, '[email protected]', 'Lucas', 'Nachtigall');

To read your user data, now use the following SELECT query:

SELECT * FROM users;

Now you have a working mysql database!

Using the template

To use the planetscale template, you'll have to connect to your own database. So on your branch page, click on connect

Select "Node.js" on the "Connect with" selector, and copy your connection string.

Now that you have the connection string, login on Bohr.io, go to the projects page and add a new project:

Select the Planetscale template

Fill this page with your data, on the environment variable "DATABASE_URL", use your connection string which you got from planetscale and click PUBLISH.

Remember to use only the connection string, not the variable name. For example:

if your full connection string is: DATABASE_URL='mysql://*****************************@aws-sa-east-1.connect.psdb.cloud/user_data?ssl={"rejectUnauthorized":true}'

You should copy only mysql://*****************************@aws-sa-east-1.connect.psdb.cloud/user_data?ssl={"rejectUnauthorized":true} to your "DATABASE_URL" environment variable, without the quotes.

After the publishing, you can see your new project on Bohr.io.

if you visit your project's url and access the url/api/users, you'll be able to see your API working and fetching the data on the users table at Planetscale's database.

Coding the function.

If you want to create your own project using Bohr.io and Planetscale, here's how everything was coded.

Github

After creating your Planetscale account and keeping your connection string saved, create a new repository on github.

Importing and configuring

Now, on Bohr.io, navigate to add a new project, and click on "import".

Select the repository of your project(you may need to add your github account if you haven't before) and click import.

Now, navigate to your projects Settings> environment variables. You will need to add one environment variable:

• DATABASE_URL - your_connection_string

And navigate to build & development and add:

• Install Command - npm install
• Root Directory - ./

Remember to use only the connection string, not the variable name. For example:

if your full connection string is: DATABASE_URL='mysql://*****************************@aws-sa-east-1.connect.psdb.cloud/user_data?ssl={"rejectUnauthorized":true}'

You should copy only mysql://*****************************@aws-sa-east-1.connect.psdb.cloud/user_data?ssl={"rejectUnauthorized":true} to your "DATABASE_URL" environment variable, without the quotes.

After adding your environment variables, click "save" to save your data, don't forget to mark your "DATABASE_URL" variable as a secret.

Coding the project - Installing

After importing your project to Bohr, clone the repository to your computer, open your prefered terminal on the project's folder and type npm init to initialize the project.

Now type npm install mysql2 lambda-api to install the required packages.

Coding the project - Fetching all users

On the root folder create a new folder called "api" and inside the "api" folder create a new folder called "core". After that, inside the core folder create a new file called index.js. and type the following code:

const  mysql = require('mysql2/promise');
const  api = require('lambda-api')();

api.get('/', async (req, res) => {
	res.status(200).json("Hello!");
});

api.get('/users', async (req, res) => {
	try {
		const  connection = await  mysql.createConnection(process.env.DATABASE_URL);
		const [rows] = await  connection.execute('SELECT * FROM users');
		res.status(200).json(rows);
	} catch (error) {
		console.error(error);
		res.error();
	}
});
  
exports.handler = async (event, context) => {
	return  await  api.run(event, context);
};

On this code, we configurate our API to reply to GET requests on the "/users" route. Also, on our reply we run the SELECT * FROM users query to fetch our user data from Planetscale.

If you commit and push your files, Bohr will automatically deploy your project and you will be able to see the users that you created on Planetscale on the api/users route.

Coding the project - Fetching user by id

To fetch a specific user id, we need to create a new route, so add the following code to your index.js file:

api.get('/user/:id', async (req, res) => {
	try {
		const  connection = await  mysql.createConnection(process.env.DATABASE_URL);
		const [rows] = await  connection.execute(`SELECT * FROM users WHERE id='${req.params.id}'`);
		res.status(200).json(rows);
	} catch (error) {
		console.error(error);
		res.error();
	}
});

This code will reply to requests on {YOUR-PROJECT-URL}/api/user/1 for example, and fetch the user data with the selected id. After pushing and deploying your code, you can also test it on the same way as before.

Coding the project - Insert new user

To insert new users on the database, we need a PUT request. So add the following code to your index.js file:

api.put('/user', async (req, res) => {
	try {
		const  first_name = req.body.first_name;
		const  last_name = req.body.last_name;
		const  email = req.body.email;
		  
		const  connection = await  mysql.createConnection(process.env.DATABASE_URL);
		await  connection.execute(`INSERT INTO users (first_name, last_name, email) VALUES('${first_name}','${last_name}','${email}')`);
		res.status(200).json();
	} catch (error) {
		console.error(error);
		res.error();
	}
});

After deploying your code, you can test it sending PUT requests to {YOUR-PROJECT-URL}/api/user. Here we can see that the new user as added to our database.

Coding the project - Delete an user

Now to delete users, we need a new route with the DELETE method. Add the following code to your index.js:

api.delete('/user/:id', async (req, res) => {
	try {
		const  connection = await  mysql.createConnection(process.env.DATABASE_URL);
		await  connection.execute(`DELETE FROM users WHERE id='${req.params.id}'`);
		res.status(200).json();
	} catch (error) {
		console.error(error);
		res.error();
	}
});

To delete an user, we need to send DELETE requests to our endpoint, as shown bellow.