1 June 2017, 11:23
node js ~ sequelize and sqlite
connecting to a sqlite database in node.
The added layers of complexity along with the cornucopia of modules available have made for an interesting time as I wrestle with selecting the best tools for the job.
here is the stack that I have been working with:
- database: sqlite3
- server: node.js (obviously)
- frontend: vue.js
Let's look at a basic example to get up and running. You will need:
Once everything is installed fire up a terminal ( on Linux or Mac ) or command prompt in Windows and make your project directory ( I'm on Linux here so these commands will not work on Windows ):
1.Make a directory --> enter that directory --> make two files one for our script ( app.js ) and one to hold the data ( test.sqlite ) --> create a package.json file for our dependencies
note: the '-y' flag on npm init will stub out a default package.json perfect for testing.
2.install our dependencies
note: the sqlite module will not be used directly but is used by the sequelize module, '-S' on npm install is shorthand for '--save', this will add the dependency to the package.json file you can also us 'i' instead of 'install'.
We're all set, we can now start to write in our app.js file. Fire up your favourite text editor and code along.
3.Grab the sequelize module --> create an instance of a sequelize object which we'll call 'con' short for connection which is what the instance represents.
4.Call the define method on that instance to define a table called article, note the proprieties of this object represent column names and the values are the data types.
6.Call sync and query the table that will be created when we run the script. I have used both the findAll and findById methods here findById is commented out feel free to play around here and see what queries you can come up with. One thing to note is that sequelize automatically pluralises queries that return many results. I.e. article is articles.
7.Now we can return to the terminal ( make sure your in the project directory ) and enter 'node app.js'.
Notice how we can see the SQL queries being logged in the terminal. This is the leg work that sequelize is doing for us behind the scenes.
8.We can double check that everything has worked correctly by using a tool like SQLite manager which is a firefox extension perfect for messing around with sqlite database in the browser.
This is a very basic example, but does cover the most basic steps to get you up and running. It would be worth while to check out some of these resources to gain a deeper understanding or this process.