Discuss

Setting Up a Lightweight Dev Environment with NodeJS and SQLite

Intro

You don’t need to install and configure any local servers to run your dynamic Wappler web app or site. You can use the powers of the integrated in Wappler NodeJS server in combination with a SQLite database to quickly setup a development environment in just a couple of minutes.

That’s the easiest and fastest way to get started with web apps/sites using dynamic data in Wappler.

SQLite a small, fast, self-contained, full-featured, SQL database engine. As it’s self contained, this means you don’t have to run any database server locally, which makes it really easy to use.

In this tutorial we will show you a simple example, which explains how to setup your project, create a database, add some data in the database and then display the data from the database on your page.

Project Setup

First we need to set up a project in Wappler. Click the Add New Project button:

Screenshot_1

Select Web > Blank:

Screenshot_2

Add a name for your project:

Screenshot_3

Then select a path on your hard drive, where the project and all its files should be created:

Screenshot_4

Set the hosting type to Custom Hosting:

Screenshot_5

And select NodeJS as a server model:

Screenshot_6

And click Save:

Screenshot_7

You are done setting up your project.
In order to start using it, Node needs to install some required packages. Click yes:

Screenshot_8

And the packages will start installing. You can check the progress in the terminal dialog:

Screenshot_9

When the packages have been successfully installed, you will see the NodeJS server running (and the green dot showing its status):

Screenshot_10

The next step is to setup our SQLite database.

Database Setup

Thanks to the database manager in Wappler, it’s really easy to setup a SQLite database, create tables and add data to the tables.

Creating the SQLite Database

Open the Database Manager:

Screenshot_11

Right click Database Connection:

Screenshot_12

And select New Connection:

Screenshot_13

Add the name db to your database connection. This is required so you can later apply changes easily to a remote docker target for example:

Screenshot_14

Select SQLite in the Database menu:

Screenshot_15

And click the Add New button, in order to create the SQLite database file:

Screenshot_16

Add a name for your database and save it in your project root (and make sure not to place it in the public folder!):

Screenshot_17

Click Save:

Screenshot_18

And you are done! Your database is now ready to be used.

Creating Tables in the Database

Now let’s create a table in our SQLite database. Right click Tables under the db connection in the Database Manager:

Screenshot_19

And select New Table:

Screenshot_20

Let’s call this table users:

Screenshot_21

The Database Manager automatically creates an id column for you. It’s type is increments, this is the identity column of your database, which auto increments its values:

Screenshot_22

Right click the users table and click New Field:

Screenshot_23

We call this new column first_name and set its type to string as it will store the users’ first names:

Screenshot_24

We add 2 more columns called last_name and email and also set their types to string:

Screenshot_25

Now this is our database table structure. We need to apply these changes to the database so they are stored there. Click the Apply Changes button:

Screenshot_26

We enter an informative text in the changes message dialog and click OK:

Screenshot_27

Our changes are now saved to the database and the users table with its structure are saved.

The next step is to add some data in our database.

Adding Data to the Dabase

Right click the users table in the Database Manager:

Screenshot_29

And select View/Edit Data:

Screenshot_30

Click the Add New button:

Screenshot_31

You can see that the cursor is in the second column - user_name. That is perfectly fine, as the identity column is handled automatically by the database so you don’t have to worry about this.

Screenshot_32

We enter the first name, last name and email of the first used in the table:

Screenshot_33

We add a few more records the same way and then hit the Save Changed Records button:

Screenshot_8

Our records are now saved in the database. You can refresh the data in the Database Editor buy clicking the Fetch data button. This will also show the identity column values:

Screenshot_6

You can close the database editor dialog, as we are done entering the data in the database.

API Action Setup

We need an API Action (also known as Server Action) to fetch the data from the database and use it on our page.

Open the Workflows Panel:

Screenshot_37

It defaults to Server Actions section.
Expand Globals:

Screenshot_38

Under Database Connections you can find the database connection called db, which we created in the database manager:

Screenshot_39

Right click API and select Add API Action:

Screenshot_41

We call it list_users:

Screenshot_42

This API Action will automatically be loaded in a new tab. Right click its steps:

Screenshot_43

Under Database Actions select Database Query:

Screenshot_44

And click the Query Builder button:

Screenshot_45

Open the Database Tables dropdown:

Screenshot_46

And here you will see our users table. It’s the only table in our database currently.
Select the users table:

Screenshot_47

We won’t do any filtering and sorting here, so just click OK to return all the records in the order they are stored in the database table:

Screenshot_48

And then click Cttrl+s / Cmd+s to save your API Action:

Screenshot_49

Listing Data on the Page

You can close the Workflows panel and open the index page, which has been created automatically when we created the project:

Screenshot_50

First we need to add our data source - the API Action we just created.
Right click App in the App Structure panel:

Screenshot_51

And select Data > Server Action:

Screenshot_52

Then click the Select Server Action button:

Screenshot_53

And select the list_users action we created in the previous steps:

Screenshot_54

We will show you how to generate a dynamic table, listing your records on the page. Of course you can use any other structure to list them if you like.

Click the add new element button in Design View:

Screenshot_55

And add a container:

Screenshot_56

Then click the add element button inside the container:

Screenshot_57

And select Generators > Bootstrap Table Generator:

Screenshot_58

Click the dynamic data picker, to select the data which should be used to populate the table:

Screenshot_59

This is the database query, in our API Action:

Screenshot_1

The data from your database query will be populated in the table:

Screenshot_3

Adjust the appearance options of the table as you like and click OK:

Screenshot_4

Save your page and you can see the results listed in the table:

Screenshot_7

Also don’t forget the save the layout page, which contains all the required scripts, before previewing your page in the browser:

Screenshot_67


That’s how you can easily setup a lightweight development environment using NodeJS and SQLite.