mysql2-query-builder

Simple abstraction for create composable sql queries base on mysql2 repository.

⚠️ This repo is part of a course that is in progress and maybe you can see that there is just a couple features at the moment, that is because while the course advance more features will be added. Check current one. Remember all extra queries born from a base function provided by mysql2 and I refer to #query function, from this we extends and get all those custom stuffs. 😉 Software is like that: we get base and plain features and we can create or extends for making the software evolution. I know you notice a lot of principles there and you will more with the help or software principles and software design patterns.

⚠️ This is a repo for teaching my junior students and I recommend to use more powerful solution like a ORM. However you can use this that is ready for production, more for those legacy projects that use direct string sql commands in the persistance layer.

Recomended repositories for this porpuse

• Knex
• sequelize
• typeorm

Connection Configuration

createDataSource(options?: ConnectionOptions): DataSource

By the default the when we create the DataSource we can pass or not the connection options, so here we have two scenarios:

1. If connection options are not passed to the createDataSource function, so that's why we want to use envariments variables for that connection.

| Variable | Description | | ----------------- | ----------------- | | DATABASE_HOST | Database host | | DATABASE_USER | Database username | | DATABASE_PASSWORD | Database password | | DATABASE_PORT | Database port | | DATABASE_NAME | Database name |

So we can get the datasource like:

const datasource = DataSource.createDataSource();

2. We want to pass the connection options maybe because we want to connect to another database with different options:

So we can get the datasource like:

const datasource = DataSource.createDataSource({
  port: 3306,
  password: '',
  user: 'root',
  database: 'test',
  host: 'localhost',
});

Select Query

Select

const connection = await datasource.getConnection();

const tracks = await connection.select('*').from('tracks');

Where

const trackId = 7;
const connection = await datasource.getConnection();

// SELECT * FROM tracks WHERE id = 7;
const tracks = await connection
  .select('*')
  .from('tracks')
  .where('id = ?')
  .execute([trackId]);

// SELECT title as trackTitle, album_id as albumId FROM tracks WHERE id = 7;
const tracks = await connection
  .select('title as trackTitle', 'album_id as albumId')
  .from('tracks').where('id = ?')
  .execute([trackId]);

// SELECT t.title as trackTitle, t.album_id as albumId FROM t WHERE id = 7;
const tracks = await connection
  .select('t.title as trackTitle', 't.album_id as albumId')
  .from('tracks as t').where('id = ?')
  .execute([trackId]);

// SELECT * FROM tracks WHERE id = 7 AND deleted_at IS NULL;
const tracks = await connection
  .select('*')
  .from('tracks')
  .where('id = ?', 'deleted_at IS NULL')
  .execute([trackId]);

Join

You can use this join with all the posibilities that where and select (previously explained) provide you.

const fromAlbumId = 7;
const connection = await datasource.getConnection();

const artists = await connection
  .select('users.username', 'users.id', 'users.email')
  .from('album_artists')
  .join('users')
  .on('album_artists.artist_id = users.id')
  .where('album_artists.album_id = ?')
  .execute([fromAlbumId]);

Here some ideas

class Mysql2Repository {
  constructor(private datasource: DataSource) {}

  public async findAlbumById(id: number): Promise<Album> {
    const connection = await this.datasource.getConnection();
    const [album] = await connection.select('*').from('albums').where('id = ?').execute([id]);

    if (!album) throw new CustomError(404, 'RECORD_NOT_FOUND', `Album with id ${id} not found`);
    return album;
  }

  public async findAllAlbumArtists(albumId: number): Promise<Artist[]> {
    const connection = await this.datasource.getConnection();
    return await connection
      .select('users.username', 'users.id', 'users.email')
      .from('album_artists')
      .join('users')
      .on('album_artists.artist_id = users.id')
      .where('album_artists.album_id = ?')
      .execute([albumId]);
  }
}