19.0.0 released Nov 08, 2023
|
Multitenant SaaS with MariaDB and Apache
Cloud applications typically run as Software-as-a-Service (SaaS). This article will demonstrate typical functionality you need to have for a minimal functioning SaaS, and how to achieve it. The example shown here is a complete application you can run on virtually any Linux computer.
The "notes" is a multi-tenant web application that you can run on the Internet as SaaS. Each user has a completely separate data space from any other. This web application will let user sign up for Notes service - a place where a user can create notes, and then view and delete them.
In a nutshell: MariaDB; web browser; Apache; Application path; Unix sockets; 7 source files, 315 lines of code.
Screenshots of application
Create new user by specifying an email address and password (you can style these anyway you like, such as with CSS):
Verifying user's email:
User logs in with own user name and password:
Adding a note once the user logged in:
Listing notes:
Ask for confirmation to delete a note:
After user confirms, delete a note:
Install Vely - you can use standard packaging tools such as apt, dnf, pacman or zypper.
Because they are used in this example, you will need to install Apache as a web server and MariaDB as a database.
After installing Vely, turn on syntax highlighting in vim if you're using it:
The source code is a part of Vely installation. It is a good idea to create a separate source code directory for each application (and you can name it whatever you like). In this case, unpacking the source code will do that for you:
tar xvf $(vv -o)/examples/multitenant-SaaS.tar.gz
cd multitenant-SaaS
The very first step is to create an application. The application will be named "multitenant-SaaS", but you can name it anything (if you do that, change it everywhere). It's simple to do with vf:
sudo vf -i -u $(whoami) multitenant-SaaS
This will create a new application home (which is "/var/lib/vv/multitenant-SaaS") and do the application setup for you. Mostly that means create various subdirectories in the home folder, and assign them privileges. In this case only current user (or the result of "whoami" Linux command) will own those directories with 0700 privileges; it means a secure setup.
Before any coding, you need some place to store the information used by the application. First, you will create MariaDB database "db_multitenant_SaaS" owned by user "vely" with password "your_password". You can change any of these names, but remember to change them everywhere here. And then, you will create database objects in the database.
Execute the following logged in as root in mysql utility:
create database if not exists db_multitenant_SaaS;
create user if not exists vely identified by 'your_password';
grant create,alter,drop,select,insert,delete,update on db_multitenant_SaaS.* to vely;
use db_multitenant_SaaS;
source setup.sql;
exit
Connect Vely to a database
In order to let Vely know where your database is and how to log into it, you will create database-config-file named "db_multitenant_SaaS". This name doesn't have to be "db_multitenant_SaaS", rather it can be anything - this is the name used in actual database statements in source code (like run-query), so if you change it, make sure you change it everywhere. Create it:
echo '[client]
user=vely
password=your_password
database=db_multitenant_SaaS
protocol=TCP
host=127.0.0.1
port=3306' > db_multitenant_SaaS
The above is a standard mariadb client options file. Vely uses native MariaDB database connectivity, so you can specify any options that a given database lets you.
Use vv utility to make the application:
vv -q --db=mariadb:db_multitenant_SaaS --path="/api/v2/multitenant-SaaS"
Note usage of --db option to specify MariaDB database and the database configuration file name.
--path is used to specify the application path, see request-URL.
Start your application server
To start the application server for your web application use vf FastCGI process manager. The application server will use a Unix socket to communicate with the web server (i.e. a reverse-proxy):
This will start 3 daemon processes to serve the incoming requests. You can also start an adaptive server that will increase the number of processes to serve more requests, and gradually reduce the number of processes when they're not needed:
See vf for more options to help you achieve best performance.
If you want to stop your application server:
vf -m quit multitenant-SaaS
This shows how to connect your application listening on a Unix socket (started with vf) to Apache web server.
- Step 1:
To setup Apache as a reverse proxy and connect your application to it, you need to enable FastCGI proxy support, which generally means "proxy" and "proxy_fcgi" modules - this is done only once:
- For Debian (like Ubuntu) and OpenSUSE systems you need to enable proxy and proxy_fcgi modules:
sudo a2enmod proxy
sudo a2enmod proxy_fcgi
- For Fedora systems (or others like Archlinux) enable proxy and proxy_fcgi modules by adding (or uncommenting) LoadModule directives in the Apache configuration file - the default location of this file on Linux depends on the distribution. For Fedora (such as RedHat), Archlinux:
sudo vi /etc/httpd/conf/httpd.conf
For OpenSUSE:
sudo vi /etc/apache2/httpd.conf
Add this to the end of the file:
LoadModule proxy_module modules/mod_proxy.so
LoadModule proxy_fcgi_module modules/mod_proxy_fcgi.so
- Step 2:
Edit the Apache configuration file:
- For Debian (such as Ubuntu):
sudo vi /etc/apache2/apache2.conf
- for Fedora (such as RedHat), Archlinux:
sudo vi /etc/httpd/conf/httpd.conf
- and for OpenSUSE:
sudo vi /etc/apache2/httpd.conf
Add this to the end of file ("/api/v2/multitenant-SaaS" is the application path (see request-URL) and "multitenant-SaaS" is your application name):
ProxyPass "/api/v2/multitenant-SaaS" unix:///var/lib/vv/multitenant-SaaS/sock/sock|fcgi://localhost/api/v2/multitenant-SaaS
- Step 3:
Finally, restart Apache. On Debian systems (like Ubuntu) or OpenSUSE:
sudo systemctl restart apache2
On Fedora systems (like RedHat) and Arch Linux:
sudo systemctl restart httpd
Note: you must not have any other URL resource that starts with "/api/v2/multitenant-SaaS" (such as for example "/api/v2/multitenant-SaaS.html" or "/api/v2/multitenant-SaaS_something" etc.) as the web server will attempt to pass them as a reverse proxy request, and they will likely not work. If you need to, you can change the application path to be different from "/api/v2/multitenant-SaaS", see request-URL.
This example uses email as a part of its function. If your server already has capability to send email, you can skip this.
Otherwise, you can use local mail, and that means email addresses such as "myuser@localhost". To do that, install postfix (or sendmail). On Debian systems (like Ubuntu):
sudo apt install postfix
sudo systemctl start postfix
and on Fedora systems (like RedHat):
sudo dnf install postfix
sudo systemctl start postfix
When the application sends an email to a local user, such as <OS user>@localhost, then you can see the email sent at:
sudo vi /var/mail/<OS user>
Access application server from the browser
Use the following URL(s) to access your application server from a client like browser (see request-URL). Use actual IP or web address instead of 127.0.0.1 if different.
# Get started
http://127.0.0.1/api/v2/multitenant-SaaS/notes/action/begin
Note: if your server is on the Internet and it has a firewall, you may need to allow HTTP traffic - see ufw, firewall-cmd etc.
The following are the source files in this application:
- SQL setup (setup.sql)
The two tables created are: "users", which contains information about each user; and "notes" which contains notes entered by the user.
Each user in "users" table has its own unique ID ("userId" column) along with other information such as email address and whether it's verified. There's also a hashed password - an actual password is never stored in plain text (or otherwise), rather a one-way hash is used to check the password.
The "notes" table contains the notes, each along with "userId" column that states which user owns them. The "userId" column's value matches the namesake column from "users" table. This way, every note clearly belongs to a single user.
create table if not exists notes (dateOf datetime, noteId bigint auto_increment primary key, userId bigint, note varchar(1000));
create table if not exists users (userId bigint auto_increment primary key, email varchar(100), hashed_pwd varchar(100), verified smallint, verify_token varchar(30), session varchar(100));
create unique index if not exists users1 on users (email);
- Run-time data (login.h)
In order to properly display the Login, Sign Up and Logout links, you will need some flags that are available anywhere in the application. Also, the application uses cookies to maintain a session, so this needs to be available anywhere, for example to verify that the session is valid. Every request sent to the application is verified that way. Only requests that come with cookies we can verify are permitted.
So to that effect, you will have a global-request-data type "reqdata" (request data) and in it there's "sess_userId" (ID of user) and "sess_id" (user's current session ID). You'll also have rather self-explanatory flags that help render pages.
#ifndef _VV_LOGIN
#define _VV_LOGIN
typedef struct s_reqdata {
bool displayed_logout;
bool is_logged_in;
char *sess_userId;
char *sess_id;
} reqdata;
void login_or_signup ();
#endif
- Session checking and session data (_before.vely)
Vely has a notion of a before-request-handler. It's the code you write that executes before any other code that handles a request. To do this, all you need is to write this code in file that's named "_before.vely" and the rest will be automatically handled.
That's very useful here. Anything that a SaaS application does, such as handling requests sent to an application, must be validated for security. This way, the application knows if the caller has permissions needed to perform an action.
So, this checking of permission will be done here in a before-request handler. That way, whatever other code you have handling a request, you will have the information about session already provided.
To keep session data (like session ID and user ID) available anywhere in your code, you'll use global-request-data. It's just a generic pointer (void*) to memory that any code that handles a request can access. This is perfect for handling sessions.
#include "vely.h"
#include "login.h"
void _before() {
out-header default
reqdata *rd;
new-mem rd size sizeof(reqdata)
rd->displayed_logout = false;
rd->is_logged_in = false;
set-req data rd
_check_session ();
}
- Checking if session is valid (_check_session.vely)
One of the most important tasks in a multi-tenant SaaS application is to check (as soon as possible) if the session is valid. This means to check if a user is logged in. It's done by getting the session ID and user ID cookies from the client (i.e. web browser), and checking these against the database where sessions are stored.
#include "vely.h"
#include "login.h"
void _check_session () {
reqdata *rd;
get-req data to rd
get-cookie rd->sess_userId="sess_userId"
get-cookie rd->sess_id="sess_id"
if (rd->sess_id[0] != 0) {
char *email;
run-query @db_multitenant_SaaS = "select email from users where userId='%s' and session='%s'" output email : rd->sess_userId, rd->sess_id row-count define rcount
query-result email to email
end-query
if (rcount == 1) {
rd->is_logged_in = true;
if (rd->displayed_logout == false) {
get-app path to define upath
@Hi <<p-out email>>! <a href="<<p-out upath>>/login/actions/logout">Logout</a><br/>
rd->displayed_logout = true;
}
} else rd->is_logged_in = false;
}
}
- Signing up, Logging in, Logging out (login.vely)
The basis of any multi-tenant system is the ability for a user to sign up, and once signed up, to log in and log out.
Typically, signing up involves verifying email address, and more often than not, the very same email address is used as a user name. That will be the case here.
There are several tasks implemented here that are necessary to perform the functionality. Each has its own "URL request signature" or a URL path.
- When Signing Up a new user, display the HTML form to collect the information. The URL request signature for this is "/login/actions/newuser"
- As a response to Sign Up form, create a new user. The URL request signature is "/login/actions/createuser". input-param is used to obtain "email" and "pwd" POST form fields (i.e. query string). Password is one-way hashed and an email verification token is created as a random 5-digit number. These are inserted into "users" table, creating a new user. A verification email is sent out, and the user is prompted to read the email and enter the code.
- Verify email by entering verification code sent to that email. The URL request signature is "/login/actions/verify"
- Display a Login form for user to login. The URL request signature is "/login" (i.e. "action" is empty)
- Login by verifying email address (i.e. user name) and password. The URL request signature is "/login/actions/login"
- Logout at user's request. The URL request signature is "/login/actions/logout"
- Landing page for application. The URL request signature is "/login/actions/begin"
- If the user is currently logged in, go to the application's landing page.
#include "vely.h"
#include "login.h"
%% /login
task-param actions
reqdata *rd;
get-req data to rd
if (rd->is_logged_in) {
if (strcmp(actions, "logout")) {
_show_home();
exit-request
}
}
if-task "begin"
_show_home();
exit-request
else-task "newuser"
@Create New User<hr/>
@<form action="<<p-path>>/login/actions/createuser" method="POST">
@<input name="email" type="text" value="" size="50" maxlength="50" required autofocus placeholder="Email">
@<input name="pwd" type="password" value="" size="50" maxlength="50" required placeholder="Password">
@<input type="submit" value="Sign Up">
@</form>
else-task "verify"
input-param code
input-param email
run-query @db_multitenant_SaaS = "select verify_token from users where email='%s'" output db_verify : email
query-result db_verify to define db_verify
if (!strcmp (code, db_verify)) {
@Your email has been verifed. Please <a href="<<p-path>>/login">Login</a>.
run-query @db_multitenant_SaaS no-loop = "update users set verified=1 where email='%s'" : email
exit-request
}
end-query
@Could not verify the code. Please try <a href="<<p-path>>/login">again</a>.
exit-request
else-task "createuser"
input-param email
input-param pwd
hash-string pwd to define hashed_pwd
random-string to define verify length 5 number
begin-transaction @db_multitenant_SaaS
run-query @db_multitenant_SaaS no-loop = "insert into users (email, hashed_pwd, verified, verify_token, session) values ('%s', '%s', '0', '%s', '')" : email, hashed_pwd, verify affected-rows define arows error define err on-error-continue
if (strcmp (err, "0") || arows != 1) {
login_or_signup();
@User with this email already exists.
rollback-transaction @db_multitenant_SaaS
} else {
write-string define msg
@From: vely@vely.dev
@To: <<p-out email>>
@Subject: verify your account
@
@Your verification code is: <<p-out verify>>
end-write-string
exec-program "/usr/sbin/sendmail" args "-i", "-t" input msg status define st
if (st != 0) {
@Could not send email to <<p-out email>>, code is <<p-out verify>>
rollback-transaction @db_multitenant_SaaS
exit-request
}
commit-transaction @db_multitenant_SaaS
@Please check your email and enter verification code here:
@<form action="<<p-path>>/login/actions/verify" method="POST">
@<input name="email" type="hidden" value="<<p-out email>>">
@<input name="code" type="text" value="" size="50" maxlength="50" required autofocus placeholder="Verification code">
@<button type="submit">Verify</button>
@</form>
}
else-task "logout"
if (rd->is_logged_in) {
run-query @db_multitenant_SaaS = "update users set session='' where userId='%s'" : rd->sess_userId no-loop affected-rows define arows
if (arows == 1) {
rd->is_logged_in = false;
@You have been logged out.<hr/>
}
}
_show_home();
else-task "login"
input-param pwd
input-param email
hash-string pwd to define hashed_pwd
random-string to rd->sess_id length 30
run-query @db_multitenant_SaaS = "select userId from users where email='%s' and hashed_pwd='%s'" output sess_userId : email, hashed_pwd
query-result sess_userId to rd->sess_userId
run-query @db_multitenant_SaaS no-loop = "update users set session='%s' where userId='%s'" : rd->sess_id, rd->sess_userId affected-rows define arows
if (arows != 1) {
@Could not create a session. Please try again. <<.login_or_signup();>> <hr/>
exit-request
}
set-cookie "sess_userId" = rd->sess_userId path "/"
set-cookie "sess_id" = rd->sess_id path "/"
_check_session();
_show_home();
exit-request
end-query
@Email or password are not correct. <<.login_or_signup();>><hr/>
else-task ""
login_or_signup();
@Please Login:<hr/>
@<form action="<<p-path>>/login/actions/login" method="POST">
@<input name="email" type="text" value="" size="50" maxlength="50" required autofocus placeholder="Email">
@<input name="pwd" type="password" value="" size="50" maxlength="50" required placeholder="Password">
@<button type="submit">Go</button>
@</form>
else-task other
end-task
%%
void login_or_signup() {
@<a href="<<p-path>>/login">Login</a> <a href="<<p-path>>/login/actions/newuser">Sign Up</a><hr/>
}
- General-purpose application (_show_home.vely)
With this tutorial you can create any multitenant SaaS application you want. The multitenant-processing module above (login.vely) calls _show_home() function, which can house any code of yours. In here, you'll have Notes application, but it can be anything. _show_home() simply calls any code you wish, and is a general-purpose multitenant application plug-in.
#include "vely.h"
void _show_home() {
notes();
exit-request
}
- Notes application (notes.vely)
The application will be able to add notes, list them, and delete any given note. It operates under a request "notes", and the tasks it serves are:
- /notes/subreqs/add_note - add a note,
- /notes/subreqs/list - list notes,
- /notes/subreqs/delete_note - delete a note
while a few other tasks are user-interface-only, such as:
- /notes/subreqs/add_note - display an HTML form to add a note,
- /notes/subreqs/delete_note_ask - ask user to confirm intent to delete a note
#include "vely.h"
#include "login.h"
%% notes
reqdata *rd;
get-req data to rd
if (!rd->is_logged_in) {
login_or_signup();
}
@<h1>Welcome to Notes!</h1><hr/>
if (!rd->is_logged_in) {
exit-request
}
task-param subreqs
@<a href="<<p-path>>/notes/subreqs/add">Add Note</a> <a href="<<p-path>>/notes/subreqs/list">List Notes</a><hr/>
if-task "list"
run-query @db_multitenant_SaaS = "select dateOf, note, noteId from notes where userId='%s' order by dateOf desc" : rd->sess_userId output dateOf, note, noteId
query-result dateOf to define dateOf
query-result note to define note
query-result noteId to define noteId
(( define web_enc
p-web note
))
match-regex "\n" in web_enc replace-with "<br/>\n" result define with_breaks status define st cache
if (st == 0) with_breaks = web_enc;
@Date: <<p-out dateOf>> (<a href="<<p-path>>/notes/subreqs/delete_note_ask?note_id=<<p-out noteId>>">delete note</a>)<br/>
@Note: <<p-out with_breaks>><br/>
@<hr/>
end-query
else-task "delete_note_ask"
input-param note_id
@Are you sure you want to delete a note? Use Back button to go back,\
or <a href="<<p-path>>/notes/subreqs/delete_note?note_id=<<p-out note_id>>">delete note now</a>.
else-task "delete_note"
input-param note_id
run-query @db_multitenant_SaaS = "delete from notes where noteId='%s' and userId='%s'" : note_id, rd->sess_userId \
affected-rows define arows no-loop error define errnote
if (arows == 1) {
@Note deleted
} else {
@Could not delete note (<<p-out errnote>>)
}
else-task "add_note"
input-param note
run-query @db_multitenant_SaaS = "insert into notes (dateOf, userId, note) values (now(), '%s', '%s')" : rd->sess_userId, note \
affected-rows define arows no-loop error define errnote
if (arows == 1) {
@Note added
} else {
@Could not add note (<<p-out errnote>>)
}
else-task "add"
@Add New Note
@<form action="<<p-path>>/notes/subreqs/add_note" method="POST">
@<textarea name="note" rows="5" cols="50" required autofocus placeholder="Enter Note"></textarea>
@<button type="submit">Create</button>
@</form>
else-task other
end-task
%%
Examples
example-client-API
example-cookies
example-create-table
example-develop-web-applications-in-C-programming-language
example-distributed-servers
example-docker
example-encryption
example-file-manager
example-form
example-hash-server
example-hello-world
example-how-to-design-application
example-how-to-use-regex
example-json
example-multitenant-SaaS
example-postgres-transactions
examples
example-sendmail
example-shopping
example-stock
example-uploading-files
example-using-mariadb-mysql
example-using-trees-for-in-memory-queries
example-utility
example-write-report
See all
documentation
You are free to copy, redistribute and adapt this web page (even commercially), as long as you give credit and provide a dofollow link back to this page - see full license at
CC-BY-4.0. Copyright (c) 2019-2023 Dasoftver LLC. Vely and elephant logo are trademarks of Dasoftver LLC. The software and information on this web site are provided "AS IS" and without any warranties or guarantees of any kind. Icons from
table-icons.io copyright Paweł Kuna, licensed under
MIT license.