Using TypeScript, how do I strongly type mysql query results

@types/mysql
typescript mssql example
select query in typescript
mysql promise typescript
typescript mysql2
javascript mysql query
import as mysql from mysql
nodejs mysql multiple queries

I'm new to Typescript and can't figure out if I'm strongly typing the results of my query correctly or not. Here's the essence of my code...

import mysql2, {Pool} from "mysql2";
const pool: Pool = mysql2.createPool({...}).promise();

interface IUser {
    uid   : number;
    uname : string;
}

class UserController {

    public async getUser(id: number): Promise<IUser> {
        const [rows]: Array<{rows: IUser}> = await pool.query(
            "SELECT * FROM `users` WHERE `email` = ?",["me@me.org"]);        
        return rows;
    }
}

The TypeScript compiler (3.3.1) complains about my return rows statement.

TS2740: Type '{rows: IUser;}' is missing the following properties from type 'IUser': uid and uname.

If I ignore the return with // @ts-ignore everything works great. I get my object back just fine without any errors.

Am I doing something wrong?


I made some changes, but I'm honestly confused as to why TypeScript doesn't complain. It doesn't seem right at all.

    class UserController {

        public async getUser(id: number): Promise<{rows: IUser}> {
            const [rows]: Array<{rows: IUser}> = await pool.query(
                "SELECT * FROM `users` LIMIT 3");        
            return rows;
        }
    }

Is this right???


So, no that's all wrong.

When I thought about query returning [rows, fields] it started too make a little more sense. I think @joesph-climber is correct with some tweaked syntax.

This works and makes sense to me...

    class UserController {

        public async getUser(id: number): Promise<Array<{rows: IUser}>> {
            const [rows]: [Array<{rows: IUser}>] = await pool.query(
                "SELECT * FROM `users` LIMIT 3");        
            return rows;
        }
    }

This also works and is probably more readily understandable.

    class UserController {

        public async getUser(id: number): Promise<IUser[]> {
            const [rows]: [IUser[]] = await pool.query(
                "SELECT * FROM `users` LIMIT 3");        
            return rows;
        }
    }
// the function should return a Promise that resolves to 
{ uid: number, uname: string }

// but typeof rows is 
{ rows: { uid: number, uname: string } }

Executing the example, the result I get is something like:

[ TextRow { uid: 1, uname: 'foo', email: 'foo@mail.com' } ]

So pool.query is returning an array with an array of IUser as first element.

Returning multiple users:

class UserController {
    // returns all users
    public async getUsers(): Promise<Array<IUser>> {
        const [rows]: [Array<IUser>] = await pool.query(
                "SELECT * FROM `user`", []);
        return rows; // rows is Array<IUser> so it matches the promise type
    }
}

Returning a specific user:

class UserController {
    public async getUser(id: number): Promise<IUser> { // Note the 
        const [rows]: [Array<IUser>] = 
            await pool.query("SELECT * FROM `user` WHERE `email` = ?",
                            ["foo@mail.com"]);
        // If email is unique as it would be expected it will 
        // return a single value inside an array
        // rows is still Array<IUser>
        return rows[0]; // rows[0] is an IUser 
    }
}

Using TypeScript with the MySQL Database, We'll explore using TypeScript with Node.js, MySQL, and TypeORM. TypeScript was introduced as a typed superset of JavaScript to ease some of the limitations of Run an SQL query and three rows of data get listed: causes, which we as a company, as well as our employees, strongly believe in. Using TypeScript with the MySQL Database Enables or Disables caching of query result. type annotations and type inference. TypeScript includes an experimental feature called Decorators to

Using mysql2@1.7.0, typescript@3.6.3, @types/mysql2

The interface must extend RowDataPacket:

interface IUser extends RowDataPacket {
  ssid: string
}

From there you can pass in the type:

const [rows]: [IUser[], FieldPacket[]] = await connection.query<IUser[]>("SELECT ssid FROM user", [])

or simply

const [rows] = await connection.query<IUser[]>("SELECT ssid FROM user", [])

Statically typed PostgreSQL queries in TypeScript, Starting with a strong type system provided by the database is definitely better for Book in TypeScript, we can use that interface to statically type the result value of our db query. Make it work on MySQL and SQL Server. Here’s how to implement such a construct in TypeScript. Let’s start with a simple type that can hold a value or Error. type Result<T> = T | Error; Now const something: Result<string> = 'something'; is valid, as is const somethingElse: Result<string> = new Error ();. The next step is to differentiate the two at runtime.

The approach in the accepted answer didn't work for me (typescript@3.5.2, mysql2@1.6.5). This code

interface IUserRow {
  ssid: string
}

const [rows]: [IUserRow[]] = await pool.query(
  'select ssid from user where id = ?',
  [session.userId],
)

gives me

error TS2322: Type '[RowDataPacket[] | RowDataPacket[][] | OkPacket | OkPacket[], FieldPacket[]]' is not assignable to type '[IUserRow[]]'.
  Types of property '0' are incompatible.
    Type 'RowDataPacket[] | RowDataPacket[][] | OkPacket | OkPacket[]' is not assignable to type 'IUserRow[]'.
      Type 'RowDataPacket[]' is not assignable to type 'IUserRow[]'.
        Property 'ssid' is missing in type 'RowDataPacket' but required in type 'IUserRow'.

with [rows] highlighted.

I was able to get everything working with:

interface IUserRow {
  ssid: string
}

const [rows] = await pool.query(
  'select ssid from user where id = ?',
  [session.userId],
)

// This first if statement is a type guard against rows possibly being OkPacket
if (Array.isArray(rows) && rows.length > 0) {
  // The casting works fine here
  const userRow = rows[0] as IUserRow
  if (userRow.ssid !== session.ssid) {
    ...
  }
}

I am no Typescript expert so there is probably a much better way to do this! Still, I hope I can potentially save someone frustration if they also have problems with the accepted answer.

emilioastarita/typed-rows: Extract metadata from mysql , Extract metadata from mysql tables to produce typescript interfaces for typed rows If you query the database the rows that you get using the default node-mysql  Adding `ResultSetHeader` as a type for `query` and `execute` May 31, 2020: package.json: update package.json to use master branch of types/mysql : Nov 15, 2019: promise.d.ts: Adding `ResultSetHeader` as a type for `query` and `execute` May 31, 2020: tsconfig.json: Update dependencies: Mar 14, 2017: tslint.json: Update dependencies: Mar 14, 2017

types/mysql: Typings for https://github.com/mysqljs/mysql, Contribute to types/mysql development by creating an account on GitHub. Typescript Typings for mysql. const connection = createConnection(process.​env['DB']); connection.query('SELECT 1 + 1 AS solution', (err: QueryError, rows: RowDataPacket[]) You can run them the tests with npm run build and npm run test . To use another database, simply change the type in the options to the database type you are using: mysql, mariadb, postgres, cockroachdb, sqlite, mssql, oracle, cordova, nativescript, react-native, expo, or mongodb. Also make sure to use your own host, port, username, password and database settings.

Strongly Typed Postgres Query Builder : typescript, I definitely want a typed query-builder for Typescript. I use JOOQ in Java-land and would very much like to have an equivalent that I can use from AWS Lambdas Though I think that's my Postgres bias at work - if you'd decided to focus on MySQL I'd probably be telling you it should be multi-DB :) The result should be like:. I definitely want a typed query-builder for Typescript. I use JOOQ in Java-land and would very much like to have an equivalent that I can use from AWS Lambdas written in Typescript. I think you've made the right choice in the short-term by focusing on a single DB.

Which ORMs/query builders/drivers are your favourite, and why , TypeScript is a typed superset of JavaScript that compiles to … I'm still interested in knowing what makes the library nice/bad to use, in your opinion. TS' type system is expressive enough that you can make this a compile-time error​. It aims to unify query building for MySQL 5.7, PostgreSQL 9.4, SQLite 3.28 (​the  Dismiss Join GitHub today. GitHub is home to over 40 million developers working together to host and review code, manage projects, and build software together.

Comments
  • {rows: IUser} is not compatible with IUser though.
  • I changed the query to return multiple rows and can see that it's returning.... [TextRow{},TextRow{},TextRow{}], which seems like an Array of TextRow objects, so maybe I'm not understanding what [Array<IUser>] means; when I look at that, I think An array with a single element that is an Array of IUser objects. When I tried what you suggested, TypeScript complained about the function return type, Promise<Array<ICwolDbUser>> saying "Array<T> is forbidden for simple types. use T[] instead". And it complained about return rows "Type {rows: IUser}[] is not assignable to type IUser[]
  • if you want to tweak your answer from Array<IUser> to IUser[], I'll mark your answer as correct. Thanks for the help.
  • Both have the same meaning. But I agree that not using the same terms can be a little confusing. :-)