Using SQLite /w Node.js for Rapid Prototyping

Perhaps, you already know that Node.js and JavaScript are widely used for web app prototyping. Even companies, like Facebook, whose main stack on the backend is PHP/Hack, still use Node.js for building small web apps and testing business ideas. The entry level to Node.js stack is low; it’s easy to learn and adopt. Spending time, effort, and money building a product that will, ultimately, never be used is a huge risk and a genuine waste of time for any product team. When choosing a tech stack for your next web app, take into consideration how fast this stack will allow you to move — write code, refactor, onboard new developers to the team. When your product succeeds, you will have plenty of time to adjust your tech stack as often as you need.

One piece of advice here — use modern JavaScript syntax, the latest version of Node.js (Node.js v6 covers 96% of ES2015 language features), Harmony Modules, and async/await via Babel that will make your code clean, more compact, and easier to read and understand.

What database to choose for my Node.js app?

It seems like in 90% of use cases, a relational database should be a more pragmatic solution as opposed to MongoDB or some other flavor of NoSQL databases. Search for “MongoDB” on Hacker News, and you will find lots of articles similar to these:

Why you should never use MongoDB
Why you should never, ever, ever use MongoDB

If you’re building a small app or a side project, you might use SQLite. Having a relational data structure from the beginning will allow you more easily to migrate to a cloud-based RDBMS solution later, such as Amazon RDS PostgreSQL or Azure SQL Database. Check this out:

Appropriate uses for SQLite

The questions often arise on how to write non-blocking async code for it, how to migrate database schema, and how to deploy and administer this database. Let’s look how the code using Node.js, Express and SQLite may look:

It uses sqlite npm package that is a Promise-based wrapper on top of the popular sqlite3 SQLite driver for Node.js. The best part is it comes with a built-in SQL-based migrations API that allow you to keep your database up-to-date in both development and production environments. To use it, you just create a “migrations” folder with one or more SQL-based migration files that may look as follows:

Before launching the app, you just call db.migrate() (in production) or db.migrate({ force: ‘last’ }) (in development) from inside your Node.js app code; that ensures, by the time your Node.js app started, the database schema will be updated to the most recent version. The { force: ‘last’ } option tells this module to rollback and re-apply the latest migration, which is very handy feature to use in development environment. You can easily make it work, so that as soon as you edit and save the latest migration file, your Node.js app is restarted and db schema is automatically updated to the most recent version.

How to deploy

When you push your code, including the “migrations” folder, to production server, the database will be deployed automatically right after the Node.js app is started. A good place to deploy your Node.js app with a SQLite backend is Windows Azure App Service, which supports Git-based deployments and has a great infrastructure for hosting Node.js apps that work similarly to Heroku, except that Heroku’s file system doesn’t work well with SQLite databases. In Azure Web Apps your SQLite database file will be safe, surviving app restarts and scaling up/down.

How to manage

Upload the official SQLite tools (sqlite3.exe, sqlite3_analyzier.exe, sqldiff.exe) downloaded from sqlite.org to your Azure Web App host machine and you’re ready to go! Whenever you need to manipulate your production or staging database directly, just open https://<example>.scm.azurewebsites.net in your browser (where “example” is the name of your app in Azure), click Debug Console > CMD in the main menu, getting remote access to the console window on the machine where your Node.js app is hosted:

CMD console in Windows Azure App Service

Having access to this console, it shouldn’t be a problem manually tweaking and tuning your database when needed (e.g., making backups, enabling/disabling logging, comparing db schema between staging and production databases, searching for performance bottlenecks etc.).

Additional Resources

SQLite Client for Node.js Apps on GitHub
Membership Database Boilerplate for Web Apps
SQLite Documentation, e.g. SQL Syntax, Data Types etc.
Single User Database Performance: SQLite vs SQL Server
Creating your first Node.js app in Azure

One clap, two clap, three clap, forty?

By clapping more or less, you can signal to us which stories really stand out.