Tutorials

How to Enable and Use Databases With Joystick

Learn how to configure and use databases in your Joystick app, including MongoDB, PostgreSQL, and Redis.

This tutorial was written by AI

The examples and explanations have been reviewed for accuracy. If you find an error, please let us know.

In this tutorial, you'll learn how to enable and use databases with Joystick. Joystick supports multiple database providers and can automatically download and manage database binaries for you in development.

What You'll Learn

By the end of this tutorial, you'll understand:

How to configure databases in your settings files
The different database providers supported by Joystick
How to connect to local and remote databases
How to use multiple databases in the same app
How to enable user accounts and queues on databases
How to access databases in your code

Prerequisites

Before starting this tutorial, make sure you have:

A Joystick app created with joystick create <app_name>
Basic understanding of databases and data storage
Completed the previous tutorials (recommended but not required)

Step 1: Understanding Database Configuration

Joystick uses settings.<env>.json files to configure databases. Let's look at the basic structure:

settings.development.json

{
  "config": {
    "databases": [
      {
        "provider": "mongodb",
        "users": true,
        "queues": false,
        "options": {}
      }
    ],
    "i18n": {
      "defaultLanguage": "en-US"
    },
    "middleware": {},
    "email": {
      "from": "",
      "smtp": {
        "host": "",
        "port": 587,
        "username": "",
        "password": ""
      }
    }
  },
  "global": {},
  "public": {},
  "private": {}
}

The config.databases array contains configuration objects for each database you want to use in your app.

Step 2: Supported Database Providers

Joystick currently supports three database providers:

Database	Provider ID	Auto-start	Node.js Driver	User Accounts	Queues	Cache
MongoDB	`mongodb`	✅	✅	✅	✅	❌
PostgreSQL	`postgresql`	✅	✅	✅	✅	❌
Redis	`redis`	✅	✅	❌	✅	✅

Let's configure each one:

MongoDB Configuration

settings.development.json

{
  "config": {
    "databases": [
      {
        "provider": "mongodb",
        "users": true,
        "queues": true,
        "options": {}
      }
    ]
  }
}

PostgreSQL Configuration

settings.development.json

{
  "config": {
    "databases": [
      {
        "provider": "postgresql",
        "users": true,
        "queues": true,
        "options": {}
      }
    ]
  }
}

Redis Configuration

settings.development.json

{
  "config": {
    "databases": [
      {
        "provider": "redis",
        "cache": true,
        "options": {}
      }
    ]
  }
}

Step 3: Using Multiple Databases

Joystick supports using multiple databases simultaneously. This is useful for using the right tool for each job:

settings.development.json

{
  "config": {
    "databases": [
      {
        "provider": "mongodb",
        "users": true,
        "queues": true,
        "options": {}
      },
      {
        "provider": "postgresql",
        "users": false,
        "queues": false,
        "options": {}
      },
      {
        "provider": "redis",
        "cache": true,
        "options": {}
      }
    ]
  }
}

In this setup:

MongoDB handles user accounts and queues
PostgreSQL stores structured business data
Redis provides caching capabilities

Step 4: Accessing Databases in Your Code

Once configured, databases are accessible via process.databases.<provider> in your server-side code:

Using MongoDB

api/books/getters.js

const getters = {
  books: {
    get: async (input = {}, context = {}) => {
      // Access MongoDB via process.databases.mongodb
      const books = await process.databases.mongodb
        .collection('books')
        .find({ category: input?.category })
        .toArray();
      
      return books;
    },
  },
};

export default getters;

Using PostgreSQL

api/analytics/getters.js

const getters = {
  user_stats: {
    get: async (input = {}, context = {}) => {
      // Access PostgreSQL via process.databases.postgresql
      const result = await process.databases.postgresql.query(`
        SELECT 
          COUNT(*) as total_users,
          AVG(age) as average_age
        FROM users 
        WHERE created_at >= $1
      `, [input?.since_date]);
      
      return result.rows[0];
    },
  },
};

export default getters;

Using Redis

api/cache/setters.js

const setters = {
  cache_data: {
    input: {
      key: {
        type: 'string',
        required: true,
      },
      value: {
        type: 'string',
        required: true,
      },
      ttl: {
        type: 'integer',
        required: false,
      },
    },
    set: async (input = {}, context = {}) => {
      // Access Redis via process.databases.redis
      if (input.ttl) {
        await process.databases.redis.setex(input.key, input.ttl, input.value);
      } else {
        await process.databases.redis.set(input.key, input.value);
      }
      
      return { success: true };
    },
  },
};

export default setters;

Step 5: Database-Specific Features

User Accounts

To enable user accounts on a database, set "users": true:

settings.development.json

{
  "config": {
    "databases": [
      {
        "provider": "mongodb",
        "users": true,
        "options": {}
      }
    ]
  }
}

This enables Joystick's built-in user account system. You can then use the accounts API:

api/auth/setters.js

import { accounts } from '@joystick.js/node';

const setters = {
  create_user: {
    input: {
      email_address: {
        type: 'string',
        required: true,
      },
      password: {
        type: 'string',
        required: true,
      },
      name: {
        type: 'string',
        required: true,
      },
    },
    set: async (input = {}) => {
      const user = await accounts.signup({
        email_address: input.email_address,
        password: input.password,
        metadata: {
          name: input.name,
        },
      });
      
      return user;
    },
  },
};

export default setters;

Queues

To enable queues on a database, set "queues": true:

settings.development.json

{
  "config": {
    "databases": [
      {
        "provider": "mongodb",
        "queues": true,
        "options": {}
      }
    ]
  }
}

Then you can define and use queues in your app:

index.server.js

import joystick from '@joystick.js/node';

joystick.app({
  queues: {
    email_queue: {
      run_on_startup: true,
      concurrent_jobs: 5,
      jobs: {
        send_welcome_email: {
          run: (payload = {}, job = {}) => {
            // Send welcome email logic here
            console.log('Sending welcome email to:', payload.email);
            job.completed();
          },
        },
      },
    },
  },
  routes: {
    '/': (req = {}, res = {}) => {
      res.render('ui/pages/index/index.js');
    },
  },
});

Step 6: Remote Database Connections

For production or when connecting to remote databases, use the connection object:

Remote MongoDB

settings.production.json

{
  "config": {
    "databases": [
      {
        "provider": "mongodb",
        "users": true,
        "connection": {
          "username": "myuser",
          "password": "mypassword",
          "database": "myapp",
          "hosts": [
            {
              "hostname": "cluster0.mongodb.net",
              "port": 27017
            }
          ]
        },
        "options": {
          "ssl": true,
          "authSource": "admin"
        }
      }
    ]
  }
}

Remote PostgreSQL

settings.production.json

{
  "config": {
    "databases": [
      {
        "provider": "postgresql",
        "users": true,
        "connection": {
          "username": "postgres",
          "password": "mypassword",
          "database": "myapp",
          "hosts": [
            {
              "hostname": "mydb.amazonaws.com",
              "port": 5432
            }
          ]
        },
        "options": {
          "ssl": true
        }
      }
    ]
  }
}

Step 7: Database Helpers and Utilities

PostgreSQL Helpers

Joystick provides helper functions for PostgreSQL to make SQL operations easier:

api/users/setters.js

const setters = {
  create_user_profile: {
    input: {
      name: {
        type: 'string',
        required: true,
      },
      email: {
        type: 'string',
        required: true,
      },
      age: {
        type: 'integer',
        required: true,
      },
    },
    set: async (input = {}) => {
      // Create table if it doesn't exist
      await process.databases.postgresql.create_table({
        table: 'user_profiles',
        columns: {
          id: 'serial primary key',
          name: 'text',
          email: 'text unique',
          age: 'integer',
          created_at: 'timestamp default now()',
        },
      });
      
      // Insert new user profile
      await process.databases.postgresql.insert({
        table: 'user_profiles',
        data: {
          name: input.name,
          email: input.email,
          age: input.age,
        },
      });
      
      return { success: true };
    },
  },
  
  update_user_profile: {
    input: {
      user_id: {
        type: 'integer',
        required: true,
      },
      name: {
        type: 'string',
        required: false,
      },
      age: {
        type: 'integer',
        required: false,
      },
    },
    set: async (input = {}) => {
      const update_data = {};
      if (input.name) update_data.name = input.name;
      if (input.age) update_data.age = input.age;
      
      await process.databases.postgresql.update({
        table: 'user_profiles',
        data: update_data,
        where: {
          id: input.user_id,
        },
      });
      
      return { success: true };
    },
  },
};

export default setters;

Step 8: Database Indexes

For better performance, you can define database indexes:

index.server.js

import joystick from '@joystick.js/node';

joystick.app({
  indexes: async () => {
    // MongoDB indexes
    if (process.databases.mongodb) {
      await process.databases.mongodb.collection('books').createIndex({ title: 1 });
      await process.databases.mongodb.collection('books').createIndex({ author: 1 });
      await process.databases.mongodb.collection('books').createIndex({ category: 1 });
    }
    
    // PostgreSQL indexes
    if (process.databases.postgresql) {
      await process.databases.postgresql.query(`
        CREATE INDEX IF NOT EXISTS user_profiles_email_idx ON user_profiles(email)
      `);
      await process.databases.postgresql.query(`
        CREATE INDEX IF NOT EXISTS user_profiles_name_idx ON user_profiles(name)
      `);
    }
  },
  routes: {
    '/': (req = {}, res = {}) => {
      res.render('ui/pages/index/index.js');
    },
  },
});

Step 9: Database Fixtures

For development and testing, you can create database fixtures:

fixtures/books.js

import joystick, { fixture } from '@joystick.js/node';

const books_fixture = fixture({
  target: 'books',
  quantity: 10,
  template: (fixture = {}, index = 0) => {
    const categories = ['fiction', 'non-fiction', 'science', 'history', 'biography'];
    const authors = ['Jane Doe', 'John Smith', 'Alice Johnson', 'Bob Wilson', 'Carol Brown'];
    
    return {
      _id: joystick.id(),
      title: `Sample Book ${index + 1}`,
      author: authors[index % authors.length],
      category: categories[index % categories.length],
      description: `This is a sample book description for book ${index + 1}.`,
      created_at: new Date().toISOString(),
      updated_at: new Date().toISOString(),
    };
  },
  skip: async (fixture = {}) => {
    const count = await process.databases.mongodb.collection('books').countDocuments();
    return count >= 10;
  },
  on_create: async (fixture = {}, data_to_create = []) => {
    await process.databases.mongodb.collection('books').insertMany(data_to_create);
  },
});

export default books_fixture;

Then load fixtures in your app:

index.server.js

import joystick from '@joystick.js/node';
import books_fixture from './fixtures/books.js';

joystick.app({
  fixtures: () => {
    books_fixture();
  },
  routes: {
    '/': (req = {}, res = {}) => {
      res.render('ui/pages/index/index.js');
    },
  },
});

Step 10: Testing with Databases

Create a separate settings file for testing:

settings.test.json

{
  "config": {
    "databases": [
      {
        "provider": "mongodb",
        "users": true,
        "queues": true,
        "options": {}
      }
    ],
    "i18n": {
      "defaultLanguage": "en-US"
    },
    "middleware": {},
    "email": {
      "from": "test@example.com",
      "smtp": {
        "host": "localhost",
        "port": 1025,
        "username": "",
        "password": ""
      }
    }
  },
  "global": {},
  "public": {},
  "private": {}
}

Then write tests that use your database:

tests/api/books/getters.test.js

import test from '@joystick.js/test';

test.that('books getter returns books from database', async (assert = {}) => {
  // Create test data
  await process.databases.mongodb.collection('books').insertOne({
    _id: 'test-book-1',
    title: 'Test Book',
    author: 'Test Author',
    category: 'fiction',
  });
  
  // Test the getter
  const books = await test.api.get('books', {
    input: { category: 'fiction' },
  });
  
  assert.is(books.length >= 1, true);
  assert.is(books[0].title, 'Test Book');
});

test.after(async () => {
  // Clean up test data
  await process.databases.mongodb.collection('books').deleteMany({});
});

Key Concepts Learned

In this tutorial, you learned:

Database Configuration: How to configure databases in settings.<env>.json files
Multiple Providers: Using MongoDB, PostgreSQL, and Redis together
Database Access: Accessing databases via process.databases.<provider>
User Accounts: Enabling and using Joystick's built-in user system
Queues: Setting up background job processing with databases
Remote Connections: Connecting to production databases
Database Helpers: Using PostgreSQL helper functions
Indexes: Creating database indexes for performance
Fixtures: Seeding databases with test data
Testing: Writing tests that interact with databases

Next Steps

Now that you understand databases in Joystick, you can:

Set up different databases for different environments
Implement complex data relationships across multiple databases
Use Redis for caching frequently accessed data
Set up database replication and clustering
Implement database migrations for schema changes
Add database monitoring and performance optimization

Databases are the foundation of most applications. With Joystick's database support, you can build scalable, data-driven applications that use the right database for each specific need.