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:

Select Web > Blank:

Add a name for your project:

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

Set the hosting type to Custom Hosting:

And select NodeJS as a server model:

And click Save:

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

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

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

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:

Right click Database Connection:

And select New Connection:

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:

Select SQLite in the Database menu:

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

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

Click Save:

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:

And select New Table:

Let’s call this table users:

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:

Right click the users table and click New Field:

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

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

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:

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

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:

And select View/Edit Data:

Click the Add New button:

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.

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

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

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:

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:

It defaults to Server Actions section.
Expand Globals:

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

Right click API and select Add API Action:

We call it list_users:

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

Under Database Actions select Database Query:

And click the Query Builder button:

Open the Database Tables dropdown:

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

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:

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

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:

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

And select Data > Server Action:

Then click the Select Server Action button:

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

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:

And add a container:

Then click the add element button inside the container:

And select Generators > Bootstrap Table Generator:

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

This is the database query, in our API Action:

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

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

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

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


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

14 Likes

WOW!
This is a Must Read Tutorial from Teodor!

A great start to learning Wappler AND a great Refresher for developers with some Wappler familiarity, as well.

3 Likes

Can SQLite databases be used in web apps? I thought an sqlite database is just a file, stored locally in your device. I don’t understand how that works?

Also, what are the hosting/publishing options? Does it have to be deployed with Docker like it says in your blog post today? Is it Node.js only?

What is a good real life scenario where this would be used?

Excellent tutorial.

SQLite database is just a file indeed. You can choose to use it for local development as light database but you can have a totally different database for your live target. Like having MySQL database on your live server.

Because the database manager it Wappler is very generic and keeps all the schema changes you make, you can easily deploy the the live target but just switching and applying the changes.

A part 2 of this tutorial series will show indeed how to use remote docker target for your live server, but for development just NodeJS and SQLite as shown here.

1 Like

Thanks George. I look forward to part two.

I don’t think these new features are a fit for me personally as I can’t host Node sites and don’t use Docker. But I think this will be useful for me to just play around with Node locally in my spare time.

Not in front of computer at the moment, but is there a quick fix for wappler package.json requirements and local node on Mac M1 ?

npm install fails due to some dependencies not being arm64 compatible.

NodeJS in Wappler 4 is running just perfectly fine with all dependencies, native on Mac arm64 M1.
We have multiple M1 mac minis and they work all fine.

If you have troubles, those are probably because mixing of older NodeJS for Intel. Otherwise post a new topic about it.

7 posts were split to a new topic: Using NodeJS locally and on shared hosting