Tag: SQLite

SQLite app using denodb in Deno


Let’s move into another interesting sections, the database where we store information processed. In this post we are store data into local SQLite database/

Denodb-ORM

This is a third party ORM module for deno which will help us to connect MySQL, MariaDB, SQLite and Postgres databases. The ORM module work through Model, so we can perform operations using Model, no worry about confusing, queries and statements.

APP

Our app is a simple Todo application, which store Todo’s in a MySQL database.

  • create a configuration
  • Create a SQLite file
  • Create Models
  • Link Model and Sync

Configuration

Usually we kept the database file under a config folder, the name of our file will database.ts and content s follows. In the final steps of the configuration we export the model. 

import { Model, Database, SQLite3Connector, DataTypes } from "../deps.ts";

const connector = new SQLite3Connector({
    filepath: './database.sqlite',
});

const db = new Database(connector)

// NOTE Models 
class Todo extends Model {
    static table = 'todos';
    static fields = {
        id: {
            type: DataTypes.INTEGER,
            primaryKey: true,
        },
        item: {
            type: DataTypes.STRING,
        }
        ,
        description: {
            type: DataTypes.STRING,
        }
    };
}
// NOTE Linking Model with DB
db.link([Todo])
await db.sync()

 export default Todo;

Sync () – will create the tables for you. You have to create the database which in not created by the sync.

Pages: Page 1, Page 2

How to auto create tables based on sequelize models


We had discussed how to auto create API using sequelize, on thing I missed is auto creation of tables.

Sequelize

Sequelize is a promise-based Node.js ORM for Postgres, MySQL, MariaDB, SQLite and Microsoft SQL Server. It features solid transaction support, relations, eager and lazy loading, read replication and more.

Auto create tables

Sequelize module has provided a function called sync in order to create tables based on the Sequelize models we created.

Place the call in the authenticate method of the Sequelize configuration as follows

const Sequelize=require('sequelize')
module.exports=new Sequelize('todo_collections','root','123',{
    host:'127.0.0.1',
    port:3306,
    dialect:'mysql',
    pool:{
        max:5,
        min:0,
        acquire:30000,
        idle:10000
    }
})

.......
.......

const db = require("./config/database");
db.authenticate()
  .then(() => {
      console.log("Database connected")
    db.sync()
    })
  .catch((e) => console.log("Error:" + e));
app.get("/", (req, res) => {
  res.send("Hello world");
});

We can use sync({force:true}) for drop the tables already created

You may like to read these sequelize posts too

Create REST-API using sequelize -MariaDB ORM in Nodejs


We had many posts on API and I am moving on sequelize ORM which allows us to perform CURD operation in REST manner or using Modals, just like we done with mongoose.

Sequelize

Sequelize is a promise-based Node.js ORM for Postgres, MySQL, MariaDB, SQLite and Microsoft SQL Server. It features solid transaction support, relations, eager and lazy loading, read replication and more.

MariaDB

MariaDB is a community-developed, commercially supported fork of the MySQL relational database management system, intended to remain free and open-source software under the GNU General Public License. The standard installation come with Heidi SQL GUI which help us create and manage database and schemas.

You can download and install it from the official website

Todo API

todos table in Heidi SQL

Our API is used to create the todos ,store in MariaDB , and perform all CURD operations using sequelize modal.

First we need to create the above table in your Maria. Now lets create a basic Nodejs project.

Continue reading Create REST-API using sequelize -MariaDB ORM in Nodejs

Create REST-API using sequelize-Mysql in Nodejs


We had many posts on API and I am moving on sequelize ORM which allows us to perform CURD operation in REST manner or using Modals, just like we done with mongoose.

Sequelize

Sequelize is a promise-based Node.js ORM for Postgres, MySQL, MariaDB, SQLite and Microsoft SQL Server. It features solid transaction support, relations, eager and lazy loading, read replication and more.

MySQL

MySQL is an open-source relational database management system. Its name is a combination of “My”, the name of co-founder Michael Widenius’s daughter, and “SQL”, the abbreviation for Structured Query Language. A workbench , which GUI tool to manage database object is available at MySQL website along with MySQL Downloads

Todo API

todos table in Workbench

Our API is used to create the todos ,store in MySQL DB , and perform all CURD operations using sequelize modal.

First we need to create the above table in your MySQL. Now lets create a basic Nodejs project.

Continue reading Create REST-API using sequelize-Mysql in Nodejs

Create REST-API using sequelize-Postgres in Nodejs


We had many posts on API and I am moving on sequelize ORM which allows us to perform CURD operation in REST manner or using Modals, just like we done with mongoose.

Sequelize

Sequelize is a promise-based Node.js ORM for Postgres, MySQL, MariaDB, SQLite and Microsoft SQL Server. It features solid transaction support, relations, eager and lazy loading, read replication and more.

PostgreSQL

Postgres is an opensource DBMS, it is free and perform faster results. It come with pg-admin web interface which allows navigate through database. You can download and install it from the official website

Todo API

todos table in pg admin

Our API is used to create the todos ,store in pg (PostgreSQL) , and perform all CURD operations using sequelize modal.

First we need to create the above table in your pg. Now lets create a basic Nodejs project.

Continue reading Create REST-API using sequelize-Postgres in Nodejs

SqlAlchemy[SQLite] databse connection in Python-Flask


Flask is a Python framework which can be used to build the web application from scratch. Flask is micro frame work which capable to build websites like Instagram, twitter and anything at programmers will.

SqlAlchemy is a package in Python Flask which simplifies data base connections. The following simple Application will explain ….. how.

This project was built on windows OS and Pycharm IDE

The connection string

from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
 app.config['DATABASE_FILE'] = 'app2.db'
 app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///' + app.config['DATABASE_FILE']
 app.config['SECRET_KEY'] = '123456790'
 db = SQLAlchemy(app)

So the db is like the cursor to the database.

Making of Database enabled Class

The User class exclusively has some database capabilities, such as querying tables. It is a fine example of how inheritance helping to build better applications.

class User(db.Model, UserMixin):
 id = db.Column(db.Integer, primary_key=True)
 username = db.Column(db.String(80), unique=True)
 email = db.Column(db.String(120), unique=True)
Pages: Page 1, Page 2