DEV Community: Cesare Ferrari

How to create an API endpoint query with Knex

Cesare Ferrari — Tue, 10 Mar 2020 14:37:10 +0000

Finding one single record with a User model in Knex

In the previous article we started working on a User model that we can use to make calls to the database, instead of making the calls directly in the route handlers.

We have created a user-model.js file that keeps all the database logic in one place, so it's easy to read and modify in the future.

In our user-model.js file we have started by creating a method to retrieve all the users.
We then used this method in the router in this way:

const users = await User.all();

Let's now create a method to find one single user by id. We'll call the method find and it's defined like this:

// user-model.js

const find = id => {
  return db('users').where({id})
}

We then export the method:

module.exports = {
  all,
  find
}

And we use it in the router:

router.get('/:id', async (req, res) => {
  const { id } = req.params;  // extract id from params

  try {
    const user = await User.find(id)

    // ... code that returns the user ...
  } 
});

If we fire up our trusty REST client and test this endpoint, we should get our user back, right?

Let's send a GET request to localhost:4000/api/users/2. This is what we get back:

Well, we do get the user. Sort of.
What we didn't expect is to see the user wrapped inside an array. Why is it so?

The reason is that this Knex call:

db('users').where({id})

returns an array of records. The array only contains one element, because that's the only record that matches the id provided.

The .where() method in Knex always returns an array of records because we could potentially have a where clause that finds multiple records.

In our case, we only want one single user, though, so we need to extract the record from the array somehow.

This is very easy because Knex provides a method called .first() that returns the first record from a query. We can simply attach .first() at the end of the query to return the single user:

db('users').where({id}).first();

If we now test out the endpoint, we will get back a single user object:

Creating a User model file in a REST API

Cesare Ferrari — Mon, 09 Mar 2020 17:19:49 +0000

Handle database logic in a model file with Knex

In previous articles we've seen how to query a database by joining two tables in order to pull out data about posts and users that could be returned in the same API call.

We generated the database query with Knex inside the router. Here's the code we used:

// user-router.js

router.get('/:id/posts', async (req, res) => {
  const { id } = req.params;

  try {

    const posts = await db('posts as p')
      .join('users as u', 'u.id', 'p.user_id')
      .select('p.id', 'u.username', 'p.contents')
      .where({user_id: id})

    res.status(200).json(posts)
  } catch (err) {
    res.status(500).json({message: "can't get posts"})
  }
})

Although this code works for our purposes, it's not a best practice to stick all the database query code inside a router file.

To better organize our application, routers should only have code related to routes. We should move the actual database handling logic inside its own separate file.

Having separate files for different chunks of code makes the code more readable, more easy to debug, and allows adding functionality later in a more simple and straightforward way.

We can put all code related to the database into what is called a model file. In our case, since we are dealing with the user, we will create a user-model.js file where we define all the methods that deal with the users table.

Users model

In our application we already have a users folder, where we keep the user-router.js file. We can simply add a user-model.js file to this folder.

The first thing we need to do is create a db object with which we will interact with the database. We do it the same way we did it in the router, by requiring the db-config.js file which pulls in knex and all the database configuration details from the knexfile:

// users/user-model.js

const db = require("../data/db-config.js");

In this file we need to define and export methods that we'll use in the router when we operate with the database.
In the router, we have a GET endpoint that returns all the users defined with this code:

router.get('/', (req, res) => {
  db('users')
  .then(users => {
    res.json(users);
  })
  .catch (err => {
    res.status(500).json({ message: 'Failed to get users' });
  });
});

We can create an all method in our user model that achieves the same result, finding all the users:

// user-model.js

const all = () => {
  return db('users');
}

We then export the method, so we can use it elsewhere:

module.exports = {
  all
}

We now need to call this functionality from our router, so we first need to import the user model into the router file:

// user-router.js

const User = require('./user-model.js');

We then use the all() method defined earlier inside the route. Note that we modified the route helper syntax to use async/await syntax:

router.get('/', async (req, res) => {
  try {
    const users = await User.all();
    res.status(200).json(users);
  } catch (err) {
    res.status(500).json({ message: 'Failed to get users' });
  }
})

If we access this endpoint now, we should get all the users back.

This is a very small change in our router file, but it's the beginning of removing all database related logic from the router and putting it into its own model file.

We can stop here for now. We will refactor other route helpers in following articles.

I write daily about web development. If you like this article, feel free to share it with your friends and colleagues.

You can receive articles like this in your inbox by subscribing to my newsletter.

How to create a join query with Knex

Cesare Ferrari — Mon, 02 Mar 2020 16:09:52 +0000

Retrieving data from two join tables in a REST API

In the previous article we have seen how to set up an API endpoint that retrieves posts associated with an user id.
What we got back from the database, apart from the post content, was an integer that represented the user id.

We know that each user also has a name saved in the users table. What if we want to display the user name in addition to the post?
To achieve this outcome, we need to join two tables (users, and posts) and create a query in Knex that pulls data from both tables.
This is the code we currently have in the User router:



router.get('/:id/posts', async (req, res) => {
  const { id } = req.params;

  try {
    const posts = await db('posts').where({user_id: id})
    res.status(200).json(posts)
  } catch (err) {
    res.status(500).json({message: "can't get posts"})
  }
})

This code creates a route that retrieves the posts with a specific user id.
If we want to grab data from the users table, though, we need a join statement in our query.

We have seen in an earlier article how to retrieve data from two joined tables with plain SQL.
The syntax would look something like this:



SELECT posts.id, users.username, posts.content 
FROM posts 
JOIN users
ON users.id = posts.user_id;

When using Knex we write a similar syntax. We can refactor our query to use a join statement like this:



const posts = await db('posts')
  .join('users', 'users.id', 'posts.user_id')
  .select('posts.id', 'users.username', 'posts.contents')
  .where({user_id: id})

The .join() method above takes the join table name as the first parameter. The next two parameters are the columns that contain the values on which the join is based, that is, the user id and the post user_id.

We also add a .select() method, that lets us pick the columns we want to display, similar to the SELECT statement in SQL.

If we now try out this endpoint with a REST client, we correctly get back the full username from the users table:

Using aliases

Our code works fine, but we can do better.

When we use joins, since the data is coming from two or more tables, we must specify which table we refer to in our code.
We have to write things like 'posts.id', 'users.username', and 'posts.contents' because SQL needs to know without ambiguity which tables and columns we refer to.

That's a lot of typing. If we want to avoid some keystrokes, SQL provides us with a feature called aliases. An alias is an alternative name we give to an entity. For example, we could refer to the posts table with the alias 'p', which is much shorter to write than 'posts'.

And we could refer to the users table with the alias 'u'.

This makes it much easier, and faster, to type table names. Here's an example, using aliases:



const posts = await db('posts as p')
  .join('users as u', 'u.id', 'p.user_id')
  .select('p.id', 'u.username', 'p.contents')
  .where({user_id: id})

We first define aliases in our code with the keyword 'as', like 'posts as p', and 'users as u'. After that, we can use the shortcuts any time we need to type in the full table name.

I write daily about web development. If you like this article, feel free to share it with your friends and colleagues.

You can receive articles like this in your inbox by subscribing to my newsletter.

Query a database with Knex

Cesare Ferrari — Fri, 28 Feb 2020 15:15:30 +0000

How to retrieve data from two database tables with Knex

In a previous article we have seen how to write SQL statements that can retrieve data from different database tables, provided related records in these tables are connected by what is called a foreign key.

Let's see how we can use this concept to retrieve data from two tables with Knex.

Imagine our application has a database with two tables: users and posts. In our application, users create posts, and when a post is created it's saved in the posts table. One attribute of the posts table is user_id, which is set to the id of the user that created the post.

Here's how those related tables are structured:

Table users:

id          username
----------  ----------
1           lao_tzu
2           socrates
3           seneca

Table posts:

id      contents              user_id
------  --------------------  -------
1       Let your workings re  1
6       Things arise and she  1
7       Beware of the barren  2
10      Trusting everyone is  3

As you can see, each post has a user_id column which is the foreign key for the user that created that post.

In our Express application, we provide an API that consumes this data.Our goal for now is to retrieve all posts by a particular user, using this endpoint:

GET /api/users/:id/posts

where :id is the user id we are interested in.

User router

In our application, we have already defined a UserRouter bound to /api/users, in the server.js file:

// server.js

const UserRouter = require('./users/user-router.js');
server.use('/api/users', UserRouter);

so, what we need to do in the user router is create an endpoint to handle /:id/posts with a GET method.
Let's start stubbing out the route handler method:

router.get('/:id/posts', async (req, res) => {
})

Inside the route handler, the first thing to do is to extract the id from the parameters (we have already seen similar code before, refer to previous articles for more details):

const { id } = req.params;

We then call the database object, defined in our router as db, passing the table 'posts' to it. This will generate a SQL statement similar to SELECT * FROM posts. We assign the posts to a variable posts:

const posts = await db('posts')

The above code returns all the posts, but we only want posts from a particular user, so we need to limit the posts returned.We do that by using a where clause in our query. We pass an object to where that looks like this:

{user_id: id}

The object specifies the column we are interested in (user_id) and the value we want for that column, contained in the id variable set previously.Finally, in our response, we send the found posts back to the caller, along with a 200 OK status.
This is our code so far:

const posts = await db('posts').where({user_id: id})
res.status(200).json(posts)

Handling errors

Of course, we need to handle the case where an error occurs, so in the catch block we send back an error message if the posts cannot be found:

res.status(500).json({message: "can't get posts"})

This is the full code for the route handler:

router.get('/:id/posts', async (req, res) => {
  const { id } = req.params;

  try {
    const posts = await db('posts').where({user_id: id})
    res.status(200).json(posts)
  } catch (err) {
    res.status(500).json({message: "can't get posts"})
  }
})

If we now make a request to the API with Insomnia, or some other REST client, we get back all the posts for a particular user:

This works well, and the response includes the user id. But we would like to include the actual user name in the response instead of just the id.How do we achieve that?

If we want the user name, we need to learn how to join both posts and users table together. We'll see how to do that in the next article.

I write daily about web development. If you like this article, feel free to share it with your friends and colleagues.

You can receive articles like this in your inbox by subscribing to my newsletter.

Joining database tables

Cesare Ferrari — Thu, 27 Feb 2020 15:14:53 +0000

How to display records from different database tables with SQL

In this article we'll introduce the concept of foreign keys in a database.
Let's look at an example in order to understand this concept.

In previous articles we played with SQL queries using something called "W3 Schools SQL Try It" tool, that we can find here.

This tool provides us with a database and several tables, like Products, Customers, Categories, and so on, so we can try out different SQL queries and learn how to select and display records.

Let's select all records from the table Products:

SELECT * FROM Products;

This query brings up a bunch of Product records with several attributes, organized in columns:

The first column, labeled ProductID, represents the primary key of this table. As we have seen before, the role of the primary key is to identify uniquely each record in a table. A primary key is typically a unique integer but it can be any value, as long as it's unique.

In the Products table, we also notice some descriptive columns like ProductName, Unit and Price, specific to each product.

A couple of other columns are different, though. SupplierId and CategoryId only contain an integer, and as we can guess, they somehow refer to records inside other tables: Suppliers and Categories, respectively.

ID fields in the Products table, that refer to other tables are called foreign keys, because they refer to a separate (or foreign) entity.

The role of foreign keys

But why do we even refer to other tables for this kind of information? After all, a supplier is specific to a product and its information could easily be stored inside the Products table, in a Supplier column.

Well, if each product had only one supplier, it would probably make sense to keep supplier data in one table. But typically, in the real world, several products may come from the same supplier, and we would have to enter the same supplier data in several records inside the Products table.

This means we would have the same, repeated information throughout our database table. This is not a good practice. If our supplier changed address, we would have to update the address information in several places in our table, and this would not be very efficient if there are thousands of records.

By creating a separate table for Suppliers (and product Categories), we can have a better separation of concerns. The Suppliers table can have all the data it needs and can change it at any time. The Products table, on the other hand, is only concerned with the SupplierID to find the supplier for its products, no matter what data the supplier keeps.

Finding and displaying connected data

We have just seen that keeping data in separate but related tables has its advantages. But, how do we access data in two different tables?

Let's say we want to display a list of products showing the supplier name next to each product. How can we find the right supplier for each product?

This is where the concept of a multi-table query comes into play. We can collect data from different tables with a SQL statement called JOIN. Here's how we use it.

We know, by looking at our database schema, that the Products table has a column called ProductName and the Suppliers table has a column called SupplierName.
We also know that Products has a SupplierID column, that acts as a foreign key to join Products and Suppliers.

Based on this knowledge, We can write a statement that joins these two tables by the SupplierID column, with this syntax:

SELECT ProductName, SupplierName 
FROM Products 
JOIN Suppliers
ON Products.SupplierID = Suppliers.SupplierID;

Let's take a close look at what happens in the query above.

We first select the two columns we want displayed: ProductName and SupplierName. Since these columns belong to two different tables we must specify which tables they come from. They come from Products and Suppliers, so we join the two tables, with the JOIN statement.

But we can't stop here. We also need to specify the conditions of the join, that is, we must tell SQL which column to use to join the tables.

Since we know that both table have a SupplierId column that contains the same key, we specify this condition after the ON statement on the last line.

The expression Products.SupplierID means the SupplierID column of the Products table. In the same way, Suppliers.SupplierID means the SupplierID column of the Suppliers table.

The resulting SQL statement describes exactly which columns we want, which tables we need to connect (or join) and which columns in each table we want to use as the basis of the join.

These are the records pulled up by the query:

And with this, we have seen how to use JOIN to connect two tables based on a foreign key. We will see more options in future articles.

I write daily about web development. If you like this article, feel free to share it with your friends and colleagues.

You can receive articles like this in your inbox by subscribing to my newsletter.

Database seeding with Knex

Cesare Ferrari — Tue, 25 Feb 2020 15:57:47 +0000

Why and how to seed a database in a REST API application

In previous articles in this series we have used Knex migrations to create a database and to add tables and columns to it. We have also seen how to modify tables, add a column, and rollback migrations.

Migrations operate on the database schema itself, but what about the actual data?

Knex can be of help in pre-populating the database programmatically with actual data, so we don't have to do it manually every time we set up a new database. This operation is called seeding the database.

Why seed data?

There are a couple of instances while developing our application, where we may want to work with live data.

One instance is during the testing phase.
Testing is a huge subject and we won't go into much detail here, but typically tests need to run with some set of pre-defined data to make sure the application logic is correct.

Another instance is for configuring the application in development.
We almost always need sample data while building and deploying the application to make sure our database is configured properly.

Seeding

Files that contain data for pre-populating our database are called seed files.
Knex needs to know where seed files live, so we need to first configure knexfile.js by adding a new property called seeds that specifies the seed directory:

// knexfile.js

development: {

  seeds: {
    directory: './data/seeds'
  }

}

Similar to when we created migration files, Knex gives us a tool to create seed files on the command line. We use this command:

knex seed:make

followed by the seed file name.

If you remember, Knex automatically adds a timestamp in front of migration files, to keep them in the proper order. However, it won't add any timestamps to seed files.

Generally speaking, we should also keep seed files in order, because we may have seed files that rely on previous seeds for their data. For this reason, it's up to us to add an order to the file names.

The easiest way of doing this is to add a progressive number at the beginning of the seed files. So, our first seed file will be called 01-fruits and will be created with this command:

knex seed:make 01-fruits

Additional seed files will begin with 02-, 03-, etc.

An example of a seed file

Upon running the command, Knex will create a file and pre-populate it with some boilerplate code to get us started:

exports.seed = function(knex) {
  // Deletes ALL existing entries
  return knex('table_name').del()
    .then(function () {
      // Inserts seed entries
      return knex('table_name').insert([
        {id: 1, colName: 'rowValue1'},
        {id: 2, colName: 'rowValue2'},
        {id: 3, colName: 'rowValue3'}
      ]);
    });
};

In a seed file we don't need up and down methods like in a migration, because seeds are just meant to add data to the table, not remove it.

Changes to the template

The templated code generated by Knex provides some placeholders that we need to replace before running the seed.

The first thing we need to replace is table_name with the actual name of the table we want to operate on, in our case fruits.

The second thing we need to change is the method that deletes data from the table. The Knex template uses the del() method, but in general it's better to use the truncate() method instead.
While del() will remove all the existing records, truncate() will also reset the primary key, so the table will be brought back to its pristine state.

Finally, we need to add objects that represent data in the correct format for our specific table.

Having made these changes, this is the code we will use:

exports.seed = function(knex) {
  // Deletes ALL existing entries
  return knex('fruits').truncate()
    .then(function () {
      // Inserts seed entries
      return knex('fruits').insert([
        {id: 1, name: 'apple', weight: 20, delicious: true, color: 'red'},
        {id: 2, name: 'pinapple', weight: 40, delicious: true, color: 'yellow'},
        {id: 3, name: 'grape', weight: 4, delicious: false, color: 'purple'},
      ]);
    });
};

We run the seed file with this command:

knex seed:run

After running the command, if we look at the database we'll see that the fruits column has now been populated with the data we entered.

I write daily about web development. If you like this article, feel free to share it with your friends and colleagues.

You can receive articles like this in your inbox by subscribing to my newsletter.

Knex Migrations

Cesare Ferrari — Mon, 24 Feb 2020 14:08:55 +0000

How to add a column to a database through migrations

Now that we have a database with a table and some columns, we decide to add an additional column to our fruits table. The column name will be "color", and it will contain a string of characters.

In order to add the column, we need to create a new migration file.
The command to add a new migration file with Knex is knex migrate:add passing a name for the migration as the last parameter.

I like to name my migrations in a descriptive way, so I know what they are going to do just by looking at the file name. In this case we are adding a color attribute to the fruits table, so the name will be add_color_to_fruits.

knex migrate:make add_color_to_fruits

Using environment: development
Created Migration:
/data/migrations/20191222090615_add_color_to_fruits.js

When creating the migration file, Knex will handle prefixing the migration name with a timestamp, so to keep the chronological order among all migration files.

The new migration file already has some boilerplate code in it, setting up the up and down methods for us.

exports.up = function(knex) {

};

exports.down = function(knex) {

};

In this migration we don't want to create any table, but just want to make a change to an existing one. In order to do this we call the table() method of Knex, passing in the table name and a callback that will receive the table object.

Using this table object, we create a new column on it, by calling the string() method, since we are creating a column of type string, and passing a character limit of 128 characters.

exports.up = function(knex) {
  return knex.schema.table('fruits', table => {
    table.string('color', 128);
  })
};

Of course, we also need to take care of the down method in the migration file.
In it, we add code that reverses our migration by removing the "color" column. Knex has a method for that: dropColumn()

exports.down = function(knex) {
  return knex.schema.table('fruits', table => {
    table.dropColumn('color');
  })
};

The command for running the migration is the usual:

knex migrate:latest

This will apply the migration and create the "color" column.

Now that we have our table created, we may want to add some initial data to it for development purposes.

We could fire up a REST client, like Insomnia, or Postman, and create data manually.
But it would be nice if this operation could be somewhat automated, so it could be done more easily and be repeatable if necessary.

It turns out that Knex has a way to add data automatically through an operation called seeding.
We will look at seeding in the next article.

I write daily about web development. If you like this article, feel free to share it with your friends and colleagues.

You can receive articles like this in your inbox by subscribing to my newsletter.

Rolling back a migration in Knex

Cesare Ferrari — Fri, 21 Feb 2020 12:10:30 +0000

Dropping and recreating a table in Knex.js

In the previous article, we saw how to set up a migration file. We saw how to create a table, named fruits, with an id, name, weight, and delicious columns.

We set the name column to be required, by calling the .notNullable() method of Knex. notNullable() makes sure that an error is raised if we try to create a record without a name.

This is how the code for creating the table and columns looks like at this point:

exports.up = function(knex) {
  return knex.schema.createTable('fruits', tbl => {
    tbl.increments('id');
    tbl.text('name', 128).unique().notNullable();
    tbl.decimal('weight');
    tbl.boolean('delicius');
  })
};

After we set up the migration, we run it with this command:

knex migrate:latest

Since we didn't have a database already, this command created one for us with a fruits table and the specified columns.

So far so good, right?

Oh, no! We just remembered that the weight column has another requirement. It cannot have NULL values. In other words, it’s a required field.

This means that we should have added the notNullable() method call to the call to tbl.decimal('weight')
The problem is that we have already run the migration. How can we fix this situation?

Rollback to the rescue.

At this point we have a couple of options to fix our little mistake.

We could just create a second migration, that will run after the first one, and inside the up method we could modify the column to make the weight required.
This is certainly a valid option, especially if we already had some data in the database.

The second option would be to simply roll back the migration and try again. Rolling back the migration means running the code in the down method in the migration file, which destroys the whole fruits table and all the data in it.

We could then adjust the line where the weight column is created by adding the call to notNullable(). We could finally run the migration again. This would fix our little mistake and have us up and running in no time.

Migration rollback

Since we haven't added any data to our database yet, probably the best option in this case is the second one, just roll back the migration and start over. We have nothing to lose.
To run a migration rollback we simply issue this command at the console:

knex migrate:rollback

Knex will run the code in the down method, in the last migration file, and respond with a message telling us the migration has been rolled back and the database has been brought to its previous state.

Using environment: development
Batch 1 rolled back: 1 migrations

Notice that we only want to roll back a migration if we are working on the latest migration file and if we don't have any data that we may need in the database.
In our case, rolling back will destroy the fruits table, so it's not a viable option if we need the data in it.
Once the migration has been rolled back, we can chain on the .notNullable() method to that column:

tbl.decimal('weight').notNullable();

We then save the migration file, and run the migration again with:

knex migrate:latest

This will recreate the table with the correct setting for the weight column.

I write daily about web development. If you like this article, feel free to share it with your friends and colleagues.

You can receive articles like this in your inbox by subscribing to my newsletter.

Migrations in Knex

Cesare Ferrari — Thu, 20 Feb 2020 13:02:38 +0000

How to create and apply migrations with Knex.js

In a previous article we discussed migrations and how they help in setting up and keeping the database schema updated.

As a brief recap of what we have seen so far:

we first define a database connection and a migrations directory in knexfile.js
we then create migration files invoking the knex migrate:make command
this command will create a file with a timestamp
the migration file contains two functions, up and down
the up function is where we write code that updates the database to the next version
the down function is where we write code that restores the previous version of the database

Schema builder methods

Knex has methods that help build the database schema. They are called schema builder methods and you can learn more about them in the Knex documentation.

These methods can create tables, delete them, create columns, add constraints, and much else.

We call these methods on knex.schema, which returns an object containing the query that will be run in the database.

Here we'll look at some of them.

The `createTable()` method

Let's start with createTable().

Like the name says, createTable() is a function that creates a new database table. We pass the table name as the first argument and a callback function as the second argument.

The callback function takes a table object and creates columns on it using additional schema builder methods.

exports.up = function(knex) {
  return knex.schema.createTable('fruits', tbl => {

    // create columns here

  })
};

Creating columns

To create columns we call methods on the tbl object. Depending on the column we want to create, we call different methods.

One column that most tables have is a primary key column. This column is used to keep the record id, and it usually contains an auto incremented integer.

This column may be implemented in different ways across different databases, but we don't have to worry about it because Knex takes care of the underlying details.

Here's how we create a primary key column named 'id' that will be incremented each time a record is created:

tbl.increments('id')

Next, we'll create a text column named 'name', that contains the name of our records.

For that we use the .text() method on the tbl object. The first argument to .text() is the column name, and the second argument is the character length. We want to constrain the length to 128 characters in this case.

We can chain schema builder methods one after the other, in order to add constrains to our columns.

Since we want the name to be unique for each of our records, we chain the .unique() method as well.
And we want to set the value in it as required, so we add .notNullable(), which will raise an error if we omit a value when a record is created.

tbl.text('name', 128).unique().notNullable();

Next, we create a column for the record weight. This is a number, so we use a decimal column type using the .decimal() method.

tbl.decimal('weight');

Finally, we create a boolean column called 'delicious' that can be set to true or false.

tbl.boolean('delicius');

This is the complete code in the migration file:

exports.up = function(knex) {
  return knex.schema.createTable('fruits', tbl => {
    tbl.increments('id');
    tbl.text('name', 128).unique().notNullable();
    tbl.decimal('weight');
    tbl.boolean('delicius');
  })
};

Drop table

If you remember, we said we need to have a way to reverse our migrations, so in the down function of our migration file we add code that removes the whole table we created in the up function.

To remove a table, which will also remove all its columns, we call the dropTableIfExists() function, passing the table name as an argument.

exports.down = function(knex) {
  return knex.schema.dropTableIfExists('fruits')
};

Applying the migration

Now that we have set up the migration file, we might as well use it. The way we run the migration is to call the migrate:latest command of knex:

knex migrate:latest

Using environment: development
Batch 1 run: 1 migrations

This will create a new database, if it doesn't already exist, and run all the migrations that have not yet been run, in the correct order (remember, the order is set by the timestamp in the file name).

If the database doesn't exist yet, Knex will create it based on the name and location specified in knexfile.js

I write daily about web development. If you like this article, feel free to share it with your friends and colleagues.

You can receive articles like this in your inbox by subscribing to my newsletter.

How to use Knex migrations to change database schema

Cesare Ferrari — Tue, 18 Feb 2020 12:09:13 +0000

A look at how to create migration files with Knex

When we start developing an application, we have some initial ideas on how the underlying database needs to be structured. We design the database schema to fit the data we need to work with.

For example, we may initially think that a users table needs a username and password fields, but after a certain time we decide to add an email field to the users as well.
This means that we need to update the database to reflect this change.
Migrations are a way to apply and keep track of changes to a database as the application codebase evolves over time.

The role of migrations

Over time, as we make changes to our application business logic, we will notice that the initial schema, that we thought was appropriate for our needs, becomes out of sync with new features introduced in the application.

These new features may be incompatible with the existing data structure, so we need an easy way to update the database schema.

Migrations help this workflow in four ways:

they let us make incremental, sequential changes
they let us reverse changes made previously, in case we change our mind
they let us recreate the exact data structure in case we decide to use a new database or a new environment.
they keep a history of the changes made by the team over time

Knex migrations help us in the process of creating the database and keeping it updated over time.

Knex also provides the possibility to populate the database with seed data, for use in development.

Knex migrations

We write migration files in a particular folder which Knex needs to know about in order to be able to run the migrations.

The way Knex knows where migration files are located is by looking at its configuration file, knexfile.js. For this purpose, we add a migrations configuration section in the knexfile.

In our configuration object, under development, we add a section called migrations specifying which directory contains the migrations files:

// knexfile.js

  migrations: {
    directory: './data/migrations'
  }

In the example above, migrations live in data/migrations, at the root of our application.

Now that we have a directory, we can create our first migration file in it.
Knex has a command for this purpose, with this syntax: migrate:make followed by the file name we want to give to the migration.
At the command line we type:

knex migrate:make produce-schema

Created Migration: migrations/20191218081503_produce-schema.js

If you look at the project directory after running this command you will see that a new migrations directory has been added and a new migration file has been created in it. The migration file has the file name we entered, prefixed by a timestamp.

The timestamp gets added automatically by Knex based on the time the creation command is run.

When we create the next migration file, the timestamp will be higher, so migration files in the directory will keep a chronological order.

This sequence tells Knex the order in which those files need to be executed if we want to run the migrations again in the future.
It will also tell Knex the order in which to reverse the migrations when we roll back the changes.

Since migrations make assumptions on the state of our schema based on previous migrations, if we don't run (or revert) migrations in the same exact order every time, we end up with a corrupt database schema.

Migration file

If we look at the migration file we created, we notice that it already has some boilerplate code in it. In particular, it exports two functions, up and down

exports.up = function(knex) {

};

exports.down = function(knex) {

};

The up function is used to move the database schema up to the newest version.
Inside the up function we create tables, add columns, and so on.

The down function is used to do the inverse of what we did in the up function, so we can bring the database to the previous state if we roll back the migration.

If we created a table in the up function, we will make sure to remove this same table in the down function. If we added a column in the up function, we will remove that column in the down function, and so on.

For now we stop here. We'll see how to add code to migration files in the next article.

I write daily about web development. If you like this article, feel free to share it with your friends and colleagues.

You can receive articles like this in your inbox by subscribing to my newsletter.

Configuring Knex in an Express application

Cesare Ferrari — Mon, 17 Feb 2020 16:12:51 +0000

How to use a Knex configuration file in a REST API.

We have seen in a previous article how to generate a configuration file in order to connect Knex to a SQLite3 database.
The file, named knexfile.js, was generated with the knex init command in our terminal, and placed at the root of our application, with some initial default configurations.

We also created a second file, called db-config.js, with code that creates and exports a Javascript object that we can use to actually issue queries to the database.
This is the code in db-config.js

// db-config.js

const knex = require('knex');

const config = require('../knexfile.js');

const db = knex(config.development);

module.exports = db;

As you can see, we first require Knex, then the knexfile. We then create a new db variable and assign to it whatever is returned by a call to knex() with the development configuration defined in knexfile.

Finally, we export the db object.
Once we have a db object, we import it into a router file into our Express application.

Route handler

Let's imagine we have an application that provides an API with data about fruits. In our application we have configured a router file in fruits/fruit-router.js

This file defines the route handlers for access to our API endpoints. The route handlers will use the db object to query the database.
At the top of fruit-router.js we create a const named db and assign to it the object exported by db-config.js:

const db = require('../data/db-config.js');

Once we have this object, we use it to query the database. Let's create a route handler that responds to a GET request and returns all the fruit records stored in the database.

As you might have guessed, we use the get() method of Express, because this is a GET request.
We make sure we use the async/await syntax because we are dealing with promises:

router.get('/', async (req, res) => {
  try {
    const fruits = await db('fruits');
    res.status(200).json(fruits);
  } catch (err) {
    res.status(500).json({message: "Problem getting fruits"});
  }
})

The code db('fruits') is all we need to connect to the database and return all records from the fruits table.

These records are in turn sent back to the client that made the request. In case of an error, we handle it with an error message.

If we try out this request with the Insomia REST client, we confirm that everything is working correctly:

A possible issue

It is considered a best practice to put the knexfile at the root of our application.
We have seen how we can create a knexfile.js by issuing the knex init command at the terminal.

Before we issue this command, though, we need to make sure we are at the root of the project, because knex init will create a knexfile in the current location, which may inadvertently be inside a sub-folder in the project.

If you actually intend to put the knexfile in some other location besides the application root, you can do it, but make sure the paths that reference this file are adjusted accordingly.

Now that we have seen how to create and configure a knexfile, let's take a look at how to create database tables with knex migrations.

We'll see how to set up a migration file in the next article.

I write daily about web development. If you like this article, feel free to share it with your friends and colleagues.

You can receive articles like this in your inbox by subscribing to my newsletter.

Configuring Knex

Cesare Ferrari — Fri, 14 Feb 2020 12:21:02 +0000

How to set up a knexfile to connect Knex to a SQLite3 database.

Knex is a query builder that supports many types of databases including MySQL, Postgresql, SQLite, and Oracle.
In order to work with all these different databases, Knex uses different npm modules to connect to them.

If we work with a SQLite database in our project, for instance, we would have a dependency on the knex module and the sqlite module.
This is a sample of the package.json file in such an application that specifies these modules:

"dependencies": {
  "knex": "^0.20.4",
  "sqlite3": "^4.1.1"
},

When we configure Knex, we want to give it details on the database we'll use.
In the code below I show an example of calling knex with a configuration object for SQLite3.
The object, which is passed in as an argument to the knex() function call, tells Knex which database to use and where to find it (SQLite, unlike other databases, saves the data in a local file).

const knex = require('knex');

const db = knex({
  client: 'sqlite3',
  connection: {
    filename: './data/produce.db3'
  },
  useNullAsDefault: true
});

Although this code would work, it's not ideal as a configuration setting. We will see a better way to configure Knex using a separate file called knexfile.js.

Query builder

As we have seen in previous articles, when we use Knex we write our queries in Javascript instead of plain SQL.
When it's time to make the database connection, Knex uses the sqlite3 module available in our application to translate queries from the Javascript syntax to the specific version of the SQL language implemented by SQLite3.

The reason we normally use a query builder like Knex instead of writing SQL commands directly is so that our queries are not bound to a specific implementation of SQL but are portable across different databases.
If one day we decide to use a different database, for example in a production environment, we wouldn't have to worry that our queries may generate SQL syntax errors.
We can be confident that our code will work seamlessly, provided we have installed the module for the new database in our project.

Knex configuration

In the code above we have seen how to configure Knex passing a literal object as an argument to knex(). But the typical way to make this configuration is through a separate file called knexfile.js. The knexfile includes all the configuration necessary to run Knex in different environments.

We can create knexfile.js manually, but we can more easily have Knex create it for us by running knex init on the command line.

knex init will create a knexfile.js at the root of our application and include some default settings.
knexfile.js exports a configuration object with different sections for different environments such as development, production and staging. It also has a section with details about how to run migrations (we'll see what migrations are later).

The development section looks something like this:

// knexfile.js

module.exports = {

  development: {
    client: 'sqlite3',
    connection: {
      filename: './dev.sqlite3'
    }
  },

  // other configuration here...

}

It defaults to sqlite3, which is fine for our purposes, but we need to make sure that it's pointing to the right location for the database filename on our system.

Another setting we should add when using SQLite3 has to do with default values entered in the database. To make sure undefined keys are entered in the database as NULL values, we add this configuration:

useNullAsDefault: true

Now that we have a simple configuration file defined, we create a new empty file named db-config.js.

Database configuration

The reason we create a separate file for configuration is to separate concerns in our application.
We want each file to have code that does one single thing in one well defined place, so we don't have to go through several different files if we need to make changes in the future.

In db-config.js we first import the knex module.
We then require the knexfile and assign it to a config object which we pass as an argument to knex() as the configuration object.
We assign the result of this call to a db object that we can use in our application to interact with the database.
At the end of our file, we export the db object so it can be used by the rest of our code.

// db-config.js

const knex = require('knex');

const config = require('../knexfile.js');

const db = knex(config.development);

module.exports = db;

If you notice, we only pass the development property of the config object to knex, since we are interested only in the development environment for now.
We'll see how to use the db object to make database queries and additional Knex configurations in future articles.

I write daily about web development. If you like this article, feel free to share it with your friends and colleagues.

You can receive articles like this in your inbox by subscribing to my newsletter.

DEV Community: Cesare Ferrari

How to create an API endpoint query with Knex

Finding one single record with a User model in Knex

Creating a User model file in a REST API

Handle database logic in a model file with Knex

Users model

How to create a join query with Knex

Retrieving data from two join tables in a REST API

Using aliases

Query a database with Knex

How to retrieve data from two database tables with Knex

User router

Handling errors

Joining database tables

How to display records from different database tables with SQL

The role of foreign keys

Finding and displaying connected data

Database seeding with Knex

Why and how to seed a database in a REST API application

Why seed data?

Seeding

An example of a seed file

Changes to the template

Knex Migrations

How to add a column to a database through migrations

Rolling back a migration in Knex

Dropping and recreating a table in Knex.js

So far so good, right?

Rollback to the rescue.

Migration rollback

Migrations in Knex

How to create and apply migrations with Knex.js

Schema builder methods

The createTable() method

Creating columns

Drop table

Applying the migration

How to use Knex migrations to change database schema

A look at how to create migration files with Knex

The role of migrations

Knex migrations

Migration file

Configuring Knex in an Express application

How to use a Knex configuration file in a REST API.

Route handler

A possible issue

Configuring Knex

How to set up a knexfile to connect Knex to a SQLite3 database.

Query builder

Knex configuration

Database configuration

The `createTable()` method