typescript-dbc

TypeScript Database Connectivity

This is a simple package that allows you to communicate with relational databases, currently only supporting MySQL, with an easy to use API that is inspired by Java's JDBC ( Java Database Connectivity).

Disclaimer: This is not a new and revolutionary technology. This is simple a library that leverages low-level drivers that are already in the JavaScript ecosystem and wraps them around an easy to use API.

How to use?

MySQL

1. First you need to establish a MySQLConnectionPool using the MySQLConnectionPoolBuilder.

   import { MySQLConnectionPool, MySQLConnectionPoolBuilder } from "typescript-dbc";
   
   const connectionPool: MySQLConnectionPool = new MySQLDatabaseConnectionPoolBuilder()
     .username("root")
     .password("password")
     .host("localhost")
     .database("some-database")
     .port(3306)
     .build();

2. Secondly, you have to create a MySQLDatabaseConnection by passing the connection pool object as a constructor parameter.

   import { DatabaseConnection, MySQLDatabaseConnection } from "typescript-dbc";
   
   const databaseConnection: DatabaseConnection = new MySQLDatabaseConnection(connectionPool);

3. Then, you can create a Statement by passing down an SQL string using the DatabaseConnection you just created.

   import { Statement } from "typescript-dbc";
   
   const SQL = "SELECT * FROM users;";
   const statement: Statement = databaseConnection.createStatement(SQL);

4. After that, you can execute the statement and get the QueryResult.

   import { QueryResult } from "typescript-dbc";
   
   const result: QueryResult = await statement.execute();
   
   const rowsAffected = result.countRowsAffected(); // Returns the number of affected rows by the query.
   const rowData = result.getRows(); // Returns an array of Row objects used to extract individual columns from the data.

5. Finally, you can extract the individual column values from the array of Rows in the QueryResult.

   import { Row } from "typescript-dbc";
   
   rowData.forEach(async (row: Row) => {
     const username = await row.getColumnAsString("username");
     const password = await row.getColumnAsString("password");
     const isAdmin = await row.getColumnAsBoolean("is_admin");
     const dateRegistered = await row.getColumnAsDate("date_registered");
   });

Prepared Statements

Prepared Statements work the same as regular statements but with a few extra features.

1. Using a DatabaseConnection, prepare a statement by passing down a parameterized SQL string.

   import { PreparedStatement } from "typescript-dbc";
   
   const SQL = "SELECT * FROM users WHERE id = ?"; // This is a parameterized SQL string. Notice the question mark.
   const preparedStatement: PreparedStatement = databaseConnection.prepareStatement(SQL);

2. Then, You can set the query parameters with the methods attached to the PreparedStatement object.

   preparedStatement.setNumber(1, 420); // The first parameter is the position of the parameter in the query and the second parameter is the value itself.
   
   // There are other methods to set the parameters in different datatypes.
   
   preparedStatement.setString(2, "Foo");
   preparedStatement.setBoolean(3, true);
   preparedStatement.setDate(4, new Date());
   
   // If the parameter positions do not exist in the query, they will be ignored

3. Finally, you can execute the PreparedStatement just like a regular statement.

   const queryResult = await preparedStatement.execute();

Contributing

If there are any issues please send them my way and I will work on them as soon as I can, or you can make a pull request and fix them yourself. Thank you for using this library.