Deno Fresh: Connect to the MySQL database

Last updated on December 21, 2022

In this tutorial, you will learn how to connect to the MySQL database using denodb library in your Deno Fresh application.

Create a new Fresh app

First, let’s create a new Fresh app.

$ deno run -A -r https://fresh.deno.dev deno-database-connect-demo

To keep things simple, let’s remove a bunch of things:

$ rm -rf islands/Counter.tsx routes/api/joke.ts routes/\[name\].tsx

Now, open your import_map.json file and import denodb by adding "denodb/": "https://deno.land/x/[email protected]/" to the JSON.

{
  "imports": {
    "$fresh/": "https://deno.land/x/[email protected]/",
    "preact": "https://esm.sh/[email protected]",
    "preact/": "https://esm.sh/[email protected]/",
    "preact-render-to-string": "https://esm.sh/*[email protected]",
    "@preact/signals": "https://esm.sh/*@preact/[email protected]",
    "@preact/signals-core": "https://esm.sh/*@preact/[email protected]",
    "denodb/": "https://deno.land/x/[email protected]/"
  }
}

Now create the models’ directory in the root of the project and create an index.ts file.

The folder structure should be similar to the below image

import { Database, MySQLConnector } from 'denodb/mod.ts';
import User from './User.ts';

const connector = new MySQLConnector({
  database: 'your_data_base_name',
  host: 'localhost',
  username: 'root',
  password: '',
  port: 3306, // optional
});

const db = new Database(connector, true);

db.link([User]);
db.sync();
//db.sync({ drop: true });

const models = {
  user: User
}
export {db, models};

Now create the user model as shown below, i.e create a file called User.ts in the model’s directory. All the available data types can be found in the data types section.

import { DataTypes, Model } from 'denodb/mod.ts';

class User extends Model {

    static table = 'users';

    static timestamps = true;

    // static defaults = {
    //     name: 'Something About Us',
    // };
      
    static fields = {
        id: {
            type: DataTypes.INTEGER,
            primaryKey: true,
            autoIncrement: true
        },
        firstName: DataTypes.STRING,
        lastName: DataTypes.STRING,
        email: {
            type: DataTypes.STRING,
            // unique: true,
            allowNull: false,
        },
        accountType:DataTypes.INTEGER,
        gender: DataTypes.STRING,
        dateOfBirth: DataTypes.DATE,
        password: DataTypes.STRING,
        phoneNumber: DataTypes.STRING,
        emailVerifiedAt: DataTypes.DATETIME,
        token: DataTypes.STRING,
        profilePicture: DataTypes.STRING
    };
}

export default User

Once you start the server if your database details are valid and connected properly it will create a user table in your database as per your model definition.

Update `index.tsx`
How to fetch data from the database.

If you follow properly above steps, you should be ready to use the above-created user model to fetch data from the user table. Let’s test it. Open your index.tsx file and import model. and call the appropriate available model methods.

import { Head } from "$fresh/runtime.ts";
import { Handlers, PageProps } from "$fresh/server.ts";
import {models} from "../models/index.ts"

export const handler: Handlers = {
  async GET(_req, ctx) {
    const users = await models.user.all();
    return ctx.render({users: users});
  },
};

export default function Home({ data }: PageProps) {
  return (
    <>
      <Head>
        <title>Fresh App</title>
      </Head>
      <div>
        {JSON.stringify(data.users)}
      </div>
    </>
  );
}

Enter the newly created project directory and run the following command to start the development server:

$ cd deno-database-connect-demo
$ deno task start

You can now open http://localhost:8000 in your browser to view the page.

full code at – https://github.com/iarjunphp/Deno-Fresh-MySQL-Data-feach

Create a new Fresh app

Update index.tsxHow to fetch data from the database.

Related tutoirals

Update `index.tsx`
How to fetch data from the database.