Build a TypeScript App with CockroachDB and TypeORM

Warning:
This version of CockroachDB is no longer supported. For more details, see the Release Support Policy.

This tutorial shows you how run a simple application built with TypeORM.

Step 1. Start CockroachDB

Choose whether to run a temporary local cluster or a free CockroachDB cluster on CockroachDB Serverless (beta). The instructions below will adjust accordingly.

Create a free cluster

  1. If you haven't already, sign up for a CockroachDB Cloud account.
  2. Log in to your CockroachDB Cloud account.
  3. On the Clusters page, click Create Cluster.
  4. On the Create your cluster page, select Serverless.

    Unless you change your monthly budget, this cluster will be free forever.

  5. Click Create cluster.

    Your cluster will be created in a few seconds and the Create SQL user dialog will display.

Set up your cluster connection

Once your cluster is created, the Connect to cluster-name dialog displays. Use the information provided in the dialog to set up your cluster connection for the SQL user that was created by default:

  1. In your terminal, run the second command from the dialog to create a new certs directory on your local machine and download the CA certificate to that directory:

    icon/buttons/copy

    curl --create-dirs -o ~/.postgresql/root.crt -O https://cockroachlabs.cloud/clusters/<cluster-id>/cert
    

    Your cert file will be downloaded to ~/.postgresql/root.crt.

    icon/buttons/copy
    curl --create-dirs -o ~/.postgresql/root.crt -O https://cockroachlabs.cloud/clusters/<cluster-id>/cert
    

    Your cert file will be downloaded to ~/.postgresql/root.crt.

    icon/buttons/copy
    mkdir -p $env:appdata\.postgresql\; Invoke-WebRequest -Uri https://cockroachlabs.cloud/clusters/<cluster-id>/cert -OutFile $env:appdata\.postgresql\root.crt
    

    Your cert file will be downloaded to %APPDATA%/.postgresql/root.crt.

  2. Copy the connection string provided, which will be used in the next steps (and to connect to your cluster in the future).

    Warning:

    This connection string contains your password, which will be provided only once. If you forget your password, you can reset it by going to the SQL Users page.

    icon/buttons/copy

    cockroach sql --url 'postgresql://<username>:<password>@<serverless-host>:26257/defaultdb?sslmode=verify-full&sslrootcert='$HOME'/.postgresql/root.crt&options=--cluster=<routing-id>'
    

    icon/buttons/copy
    cockroach sql --url 'postgresql://<username>:<password>@<serverless-host>:26257/defaultdb?sslmode=verify-full&sslrootcert='$HOME'/.postgresql/root.crt&options=--cluster=<routing-id>'
    
    icon/buttons/copy
    cockroach sql --url "postgresql://<username>:<password>@<serverless-host>:26257/defaultdb?sslmode=verify-full&sslrootcert=$env:appdata/.postgresql/root.crt&options=--cluster=<routing-id>"
    

    Where:

    • <username> is the SQL user. By default, this is your CockroachDB Cloud account username.
    • <password> is the password for the SQL user. The password will be shown only once in the Connection info dialog after creating the cluster.
    • <serverless-host> is the hostname of the CockroachDB Serverless (beta) cluster.
    • <routing-id> identifies your tenant cluster on a multi-tenant host. For example, funny-skunk-123.
    • <cluster-id> is a unique string used to identify your cluster when downloading the CA certificate. For example, 12a3bcde-4fa5-6789-1234-56bc7890d123.

    You can find these settings in the Connection parameters tab of the Connection info dialog.

  1. If you haven't already, download the CockroachDB binary.
  2. Run the cockroach demo command:

    icon/buttons/copy
    $ cockroach demo \
    --empty
    

    This starts a temporary, in-memory cluster and opens an interactive SQL shell to the cluster. Any changes to the database will not persist after the cluster is stopped.

  3. Take note of the (sql/tcp) connection string in the SQL shell welcome text:

    # Connection parameters:
    #   (console) http://127.0.0.1:61009
    #   (sql)     postgres://root:admin@?host=%2Fvar%2Ffolders%2Fk1%2Fr048yqpd7_9337rgxm9vb_gw0000gn%2FT%2Fdemo255013852&port=26257
    #   (sql/tcp) postgres://root:admin@127.0.0.1:61011?sslmode=require    
    

    In this example, the port number is 61011. You will use the port number in your application code later.

Step 2. Create a database

  1. In the SQL shell, create the bank database that your application will use:

    icon/buttons/copy
    > CREATE DATABASE bank;
    
  2. Create a SQL user for your app:

    icon/buttons/copy
    > CREATE USER <username> WITH PASSWORD <password>;
    

    Take note of the username and password. You will use it in your application code later.

  3. Give the user the necessary permissions:

    icon/buttons/copy
    > GRANT ALL ON DATABASE bank TO <username>;
    
  1. If you haven't already, download the CockroachDB binary.
  2. Start the built-in SQL shell using the connection string you got from the CockroachDB Cloud Console earlier:

    icon/buttons/copy
    $ cockroach sql \
    --url='postgres://<username>:<password>@<global host>:26257/<cluster_name>.defaultdb?sslmode=verify-full&sslrootcert=<certs_dir>/cc-ca.crt'
    

    In the connection string copied from the CockroachDB Cloud Console, your username, password and cluster name are pre-populated. Replace the <certs_dir> placeholder with the path to the certs directory that you created earlier.

  3. In the SQL shell, create the bank database that your application will use:

    icon/buttons/copy
    > CREATE DATABASE bank;
    

Step 3. Get the code

  1. Clone the code's GitHub repository:

    icon/buttons/copy
    $ git clone git@github.com:cockroachlabs/example-app-typescript-typeorm.git
    
  2. Navigate to the repo directory and install the application dependencies:

    icon/buttons/copy
    $ cd example-app-typescript-typeorm
    
    icon/buttons/copy
    $ npm install
    

Step 4. Configure your CockroachDB connection

  1. Open the datasource.ts file, and comment out the ssl: true, extra and options configuration properties.

  2. In the datasource.ts file, uncomment ssl: { rejectUnauthorized: false }.

    Warning:

    Only use ssl: { rejectUnauthorized: false } in development, for insecure connections.

    The DataSource configuration should look similar to the following:

    export const AppDataSource = new DataSource({
        type: "cockroachdb",
        url: process.env.DATABASE_URL,
        ssl: { rejectUnauthorized: false }, // For insecure connections only
        /* ssl: true,
        extra: {
            options: "--cluster=<routing-id>"
        }, */
        synchronize: true,
        logging: false,
        entities: ["src/entity/**/*.ts"],
        migrations: ["src/migration/**/*.ts"],
        subscribers: ["src/subscriber/**/*.ts"],
    })
    
  3. Set the DATABASE_URL environment variable to the connection string provided in the cockroach demo welcome text.

  1. Open the datasource.ts file, and edit the --cluster=<routing-id> configuration property to specify the routing ID to your serverless cluster.

  2. Set the DATABASE_URL environment variable to a CockroachDB connection string compatible with TypeORM.

    TypeORM accepts the following format for CockroachDB Serverless (beta) connection strings:

    icon/buttons/copy
    postgresql://<username>:<password>@<host>:<port>/<database>
    

Step 5. Run the code

Start the application:

icon/buttons/copy
$ npm start

You should see the following output in your terminal:

Inserting a new account into the database...
Saved a new account.
Printing balances from account 1db0f34a-55e8-42e7-adf1-49e76010b763.
[
  Account { id: '1db0f34a-55e8-42e7-adf1-49e76010b763', balance: 1000 }
]
Inserting a new account into the database...
Saved a new account.
Printing balances from account 4e26653a-3821-48c8-a481-47eb73b3e4cc.
[
  Account { id: '4e26653a-3821-48c8-a481-47eb73b3e4cc', balance: 250 }
]
Transferring 500 from account 1db0f34a-55e8-42e7-adf1-49e76010b763 to account 4e26653a-3821-48c8-a481-47eb73b3e4cc.
Transfer complete.
Printing balances from account 1db0f34a-55e8-42e7-adf1-49e76010b763.
[
  Account { id: '1db0f34a-55e8-42e7-adf1-49e76010b763', balance: 1000 }
]
Printing balances from account 4e26653a-3821-48c8-a481-47eb73b3e4cc.
[
  Account { id: '4e26653a-3821-48c8-a481-47eb73b3e4cc', balance: 250 }
]

What's next?

Read more about using the TypeORM.

You might also be interested in the following pages:

YesYes NoNo