![\"Speeding](\"https:\/\/blog.logrocket.com\/wp-content\/uploads\/2020\/05\/speeding-up-development-environment-sqlite.jpeg\")
\nAs we seek different tools and techniques to speed up our development workflow, database management remains overlooked. It\u2019s still rigorous and time-consuming to set up and maintain when working with relational databases like PostgreSQL.<\/p>\n
Fortunately, SQLite, a self-contained SQL database engine, reduces a lot of the labor of working with databases in our development environment.<\/p>\n
From the official docs<\/a>:<\/p>\n SQLite is a C-language library that implements a small<\/a>, fast<\/a>, self-contained<\/a>, high-reliability<\/a>, full-featured<\/a> SQL database engine.<\/p><\/blockquote>\n Apart from being fast, SQLite is self-contained, and this makes it a great choice for a lot of applications, whether for mobile or web. It eliminates the need for applications to rely on external databases in order to function. With this in mind, SQL can become a very good option for working with databases in our development environment.<\/p>\n Since our SQLite database will exist inside our project, its content will be consistent across all our development environments. The database setup is done just once, and with every other initialization of our project across different development environments, we won\u2019t need to do any additional work to set up a database.<\/p>\n This will significantly speed up our workflow because it eliminates the issue of data inconsistency as well as the difficulty of connecting to a new database, migrating, and then seeding data every time we decide to work with a new development environment.<\/p>\n SQLite is a good option in many cases, but considering the size and type of your application, it might not be the best choice for your production environment.<\/p>\n SQLite does not support concurrency. This means that only one user can write to the database at a time, so when working with applications that require multiple users to write to the database concurrently, PostgreSQL might be a better option for your production environment.<\/p>\n Also, SQLite works well with applications that don\u2019t receive traffic of more than 100,000 hits per day. If your application is expected to scale past this limit, then SQLite is probably not the best option for production. Since our focus is on our development environment, however, this likely won\u2019t be an issue.<\/p>\n Let\u2019s demonstrate how we can use SQLite to speed up our development environment by building a Node.js application. We\u2019ll show how SQLite can be used alongside other relational databases like PostgreSQL, with SQLite used for development and PostgreSQL used for production.<\/p>\n Let\u2019s start by scaffolding a new Node.js app with Express.js. First, we\u2019ll ensure that we have Node installed in our local environment. Let\u2019s do that by running the following command on our terminal:<\/p>\n This should return the version of Node we have installed. Click here<\/a> to see Node installation instructions if the command returns an error message.<\/p>\n Next, we\u2019ll install the Express application generator, which we\u2019ll use to scaffold our Node app:<\/p>\n Now let\u2019s run the following command on our terminal:<\/p>\n Then If you\u2019ve followed all the steps correctly, you should be able to view your Node app when you Our new app directory should look like this:<\/p>\n Let\u2019s install our packages with the following command:<\/p>\n Before we initialize Sequelize, we\u2019ll add the following script to our This makes it easier for us to access the Sequelize commands from our terminal. With our newly added script, our Now let\u2019s go ahead and initialize Sequelize by running the following command on our terminal:<\/p>\n This generates a We want to store our production database URL as an environment variable. This eases the process of updating it in the future. To do this, we\u2019ll create a file named Notice that we also added a variable Now that we have that set up, let\u2019s navigate to the Since we\u2019ll be accessing our production database URL (which we stored in our Next, we\u2019ll fill in the details for our development, test, and production environments. Let\u2019s edit our Notice how we used For our next step, we\u2019ll be using Sequelize to create a table in our database. If our Node environment is set to either Let\u2019s create a table called This creates a table When not to use SQLite<\/h3>\n
Scaffolding our Node.js application<\/h2>\n
node --version<\/pre>\n
npm install -g express-generator<\/pre>\n
express node_sqlite<\/pre>\n
npm install<\/code> to install the default npm packages.<\/p>\ncd<\/code> into your new app directory and run npm start<\/code>:<\/p>\ncd node_sqlite\nnpm start<\/pre>\n
\u251c\u2500\u2500 bin\n\u251c\u2500\u2500 public\n\u251c\u2500\u2500 routes\n\u251c\u2500\u2500 views\n\u251c\u2500\u2500 package.json\n\u251c\u2500\u2500 app.js<\/pre>\n
Installing the required dependencies<\/h2>\n
\n
\n
dotenv<\/code> package<\/li>\n<\/ul>\n<\/blockquote>\nnpm i -s sequelize sequelize-cli dotenv<\/pre>\n
package.json<\/code> file, which can be found in the root directory:<\/p>\n\"sequelize\": \"node_modules\/.bin\/sequelize\"<\/pre>\n
package.json<\/code> file should like this:<\/p>\n\/\/ .\/package.json\n\n{\n \"name\": \"sqlite-test\",\n \"version\": \"0.0.0\",\n \"private\": true,\n \"scripts\": {\n \"start\": \"node .\/bin\/www\",\n \"sequelize\": \"node_modules\/.bin\/sequelize\"\n },\n \"dependencies\": {\n ...\n }\n}<\/pre>\nnpm run sequelize init<\/pre>\n
models<\/code> folder for housing model files, which we\u2019ll be using to describe the logical structure of our database, and a config<\/code> folder for our database configuration file.<\/p>\n.env<\/code> in our root directory and add a variable DATABASE_URL<\/code> to it:<\/p>\n\/\/ .env\n\nDATABASE_URL= your database url here\nNODE_ENV=development<\/pre>\n
NODE_ENV<\/code> to our .env<\/code> file. This indicates the environment in which we will be running our application. Here, we\u2019ve used development<\/code>.<\/p>\n.\/config\/config.json<\/code> file. This is the file that Sequelize generates for our app. By default, it should look like this:<\/p>\n{\n \"development\": {\n \"username\": \"root\",\n \"password\": null,\n \"database\": \"database_development\",\n \"host\": \"127.0.0.1\",\n \"dialect\": \"mysql\",\n \"operatorsAliases\": false\n },\n \"test\": {\n \"username\": \"root\",\n \"password\": null,\n \"database\": \"database_test\",\n \"host\": \"127.0.0.1\",\n \"dialect\": \"mysql\",\n \"operatorsAliases\": false\n },\n \"production\": {\n \"username\": \"root\",\n \"password\": null,\n \"database\": \"database_production\",\n \"host\": \"127.0.0.1\",\n \"dialect\": \"mysql\",\n \"operatorsAliases\": false\n }\n}<\/pre>\n.env<\/code> file) from here, let\u2019s convert this to a JavaScript module instead of a JSON file. To do this, we\u2019ll first rename the file config.json<\/code> to config.js<\/code> and then replace its content with the following code block:<\/p>\nmodule.exports = {\n development: {\n },\n test: {\n },\n production: {\n },\n};<\/pre>\nconfig.js<\/code> file to look like this:<\/p>\nrequire('dotenv').config();\n\nmodule.exports = {\n development: {\n dialect: \"sqlite\",\n storage: \".\/sqlite-dev.db\"\n },\n test: {\n dialect: \"sqlite\",\n storage: \".\/sqlite-test.db\"\n },\n production: {\n url: process.env.DATABASE_URL,\n dialect: 'postgres',\n },\n};<\/pre>\nsqlite<\/code> for our development and test environments and then postgres<\/code> for our production environment. Depending on the type\/size of our application, we could go ahead and also use sqlite<\/code> for our production environment. We also initialized our dotenv<\/code> module on line 1<\/code>.<\/p>\nGenerate our database model<\/h2>\n
development<\/code> or test<\/code> in our dotenv<\/code> file (as we\u2019ve done), Sequelize will generate a new SQLite database in our root directory with the name we used in our config file before creating the table.<\/p>\nUser<\/code> by running the following command on our terminal:<\/p>\nnpm run sequelize -- model:generate --name User --attributes username:string,password:string<\/pre>\n
User<\/code> with columns username<\/code> and password<\/code> in our database. When we navigate to our root directory, we\u2019ll see a file named sqlite-dev.db<\/code>. This is our newly created SQLite database.<\/p>\n