Discuss

Setting Up a Lightweight Dev Environment with NodeJS and SQLite

On this page

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_2|690x431

Select Web > Blank:

Screenshot_3|690x431

Add a name for your project:

Screenshot_2|690x431

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

Screenshot_3|690x431

Set the Development Environment to Wappler Local Server:

Screenshot_4|690x431

And select NodeJS as a server model:

Screenshot_5|690x431

And click Save:

Screenshot_6|690x431

You are done setting up your project. You don't need anything else to be installed or configured, your Local Node server is up and running.

Note: If you want to use custom node modules or run an external NodeJS Server, make sure to select Own Server as a Development Environment.

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|690x431

Right click Database Connection:

Screenshot_12|690x431

And select New Connection:

Screenshot_13|690x431

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|690x431

Select SQLite in the Database menu:

Screenshot_15|690x431

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

Screenshot_16|690x431

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|690x431

Click Save:

Screenshot_18|690x431

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|690x431

And select New Table:

Screenshot_20|690x431

Let's call this table users:

Screenshot_21|690x431

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|690x431

Right click the users table and click New Field:

Screenshot_23|690x431

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

Screenshot_24|690x431

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

Screenshot_25|690x431

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|690x431

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

Screenshot_27|690x431

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|690x431

And select View/Edit Data:

Screenshot_30|690x431

Click the Add New button:

Screenshot_31|690x431

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|690x431

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

Screenshot_33|690x431

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

Screenshot_8|690x431

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|690x431

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|690x431

It defaults to Server Actions section. Expand Globals:

Screenshot_38|690x431

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

Screenshot_39|690x431

Right click API and select Add API Action:

Screenshot_41|690x431

We call it list_users:

Screenshot_42|690x431

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

Screenshot_43|690x431

Under Database Actions select Database Query:

Screenshot_44|690x431

And click the Query Builder button:

Screenshot_45|690x431

Open the Database Tables dropdown:

Screenshot_46|690x431

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

Screenshot_47|690x431

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|690x431

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

Screenshot_49|690x431

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|690x431

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

Screenshot_51|690x431

And select Data > Server Action:

Screenshot_52|690x431

Then click the Select Server Action button:

Screenshot_53|690x431

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

Screenshot_54|690x431

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|690x431

And add a container:

Screenshot_56|690x431

Then click the add element button inside the container:

Screenshot_57|690x431

And select Generators > Bootstrap Table Generator:

Screenshot_58|690x431

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

Screenshot_59|690x431

This is the database query, in our API Action:

Screenshot_1|690x431

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

Screenshot_3|690x431

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

Screenshot_4|690x431

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

Screenshot_7|690x431

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

Screenshot_67|690x431


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