Skip to content

Instantly share code, notes, and snippets.

What would you like to do?
Setting up an Knex SQLite3 project from scratch
Configuring the setup
1. Setting up the dependices
yarn init -y (Initalises a packag.json with the default values)
yarn add knex sqlite3 (Add knex sqlite module)
yarn add jest --dev (Add jest to dev dependices)
2. Adding scripts to the package.json file
"scripts": {
"knex": "knex",
"test": "jest"
3. Optional: Set up file so its an executable file
You can set up a file so that it knows what application should be running it e.g. node, python, ruby
We can set up the file to run as a script by making it an executable. So instead of saying node todo list, we can write ./todo rather than node todo
To make todo have this functionality run ```chmod +x todo```
4. Set up the knex configuration file
yarn run knex init
5. Edit the knex configuration file so it has informtion about our testing setup
Update the development so it has a useNullAsDefault
development: {
client: 'sqlite3',
connection: {
filename: './dev.sqlite3'
useNullAsDefault: true
Add a test object to the script
test: {
client: 'sqlite3',
connection: {
filename: ':memory:'
seeds: {
directory: './tests/seeds'
Setting up the database
6. We need a migration information. Migration describes the structure of the tables.
yarn run knex migrate:make todos
This will create a migration directory in the project - and add a js file with a very long name (date and time plus the file name). This will contain info about setting up the tables for our database. Remember the maxim: We migrate, before we create the tables
!!!!!!! Don't change the file name of your migration files - These are recorded in your database to figure out what happened when. Doing so may corrupt data !!!!!!!!
With setting up database structure there are two parts the Schema (The structure of the columns) and the content (The records)
You set up two functions: One to apply the table changes (knex referes to these as the latest), the other to remove them (in knex speak a rollback)
When setting up a schema go to migrations and open that js file
The createTableIfNotExists takes two arguments, tablename and a callback function
increment -sets up an incrementing id
string - sets up a text field
integer - adds a number column
exports.up = function(knex, Promise) {
return knex.schema.createTableIfNotExists('todos', function (table){
exports.down = function(knex, Promise) {
return knex.schema.dropTable('coffee')
Run yarn run knex migrate:latest to set up the schema
7. Add some seed data for testing.
Once you've applied the inital migration
yarn run knex seed:make test-tasks
This will create a new directory seeds with the name of the file you pass it. You then edit the table names and the objects so they point to the right table and conatin the right columns and information
run yarn knex seed:run
exports.seed = function(knex, Promise) {
// Deletes ALL existing entries
return knex('todos').del()
.then(function () {
// Inserts seed entries
return knex('todos').insert([
{id: 1, tasks: 'Go get coffee'},
{id: 2, tasks: 'Go to the gym'},
{id: 3, tasks: 'Go to EDA'}
If you want to use the database with a web interface you then need to hook up knex to your serve and export the app ouut of it
So in your server.js you'ld require the following files in
You'll also want to make the app available to other files
module.exports = function (db) {
app.set('db', db)
return app
Resetting a database
!!!! WARNING Delete the database file - You wouldn't do this in a production environement however but while testing and troubleshooting we can do it . !!!!!
rm dev.sqlite3
Add the last migration to the databse file
yarn run knex migraet:latest
Add the seed data
yarn run knex seed:run
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.