SQLite – Developer stories

How to solve gcc error in Golang -Windows

How to solve gcc compiler error in Golang on windows machine

On windows you may got the gcc compiler error while trying to add packages like “gorm.io/driver/sqlite” in Golang. The cause for the error is that, underlying package require the gcc C compiler which is not installed on Windows PC by default.

How to solve

We need to install the C compiler on Windows machine and also need to add the path to environment variable.

The easiest way to install the mingw package from tmd-gcc repository.

Also add the path to the bin folder as follows in environment variables.

C:\TDM-GCC-64\bin

Sqlite : Random rows

nippet for generating random rows in Sqlite

Snippet for generating random rows in Sqlite

	select * from usedes order by random() limit 5

	# each time you run the query will swap rows

view raw random_rows_sqlite hosted with ❤ by GitHub

Setup Prisma ORM for Nextjs

How to setup Prsma ORM for React and Nextjs projects

Prisma is a ORM for JavaScript and Typescript, it let developers easily configure, create / migrate databases using models. One of the cool feature I love mostly is that , it can be configured with few CLI commands like init, migrate

Setup Prisma

For initializing the Prisma install the developer dependency npm i -d prisma and initialize prisma with

npx prisma init

It will generate necessary files under Prisma folder, please open the file and configure database and models. For demonstration I have configured a Sqlite database, you can use other databases like mysql, postgres, Mongodb etc.

//schema.prisma

datasource db {
  provider = "sqlite"
  url      = "file:./dev.db"
}

generator client {
  provider = "prisma-client-js"
}

model Contact{
  id String @id @default(uuid())
  name String
  email String
}

Note the id field in the model, it is a primary key and also auto filled by the uuid() function. One you done models go to generate the real database with migrate command

npx prisam migrate dev --name init

This will generate the tables using the models we defined, to make sure we can use the prisma studio which runs on the port 555, also a standalone studio app is available.

In the future we can modify and rerun the migrate command for updating the tables, which will drop the tables and re-create for us.

// run in new terminal
npmx prisma studio

How to use in our Nextjs app

In the nextjs app we need the dependency @prisma/client, let’s add them to our project

nmp i -s @prisma/client

In our React file we can now create prisma object and call the fetch method which will get the data from database. Usually we done the fetching with getStaticProps method in React.

//index.js

export async function getStaticProps() {
  const contacts = await prisma.contact.findMany();
  return {
    props: {
      initialContacts: contacts,
    },
  };
}

export default function Home({initialContacts}) {
 const [contacts, setcontacts] = useState(initialContacts); 
....

In the similar way, we can use them in API or graphql end points too.

SQLite app using denodb in Deno

How to create SQLite deno app with Denodb ORM, an opensource module for 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: 12

How to auto create tables based on sequelize models

Auto create table 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

How to auto create tables based on sequelize models - Auto create table based on sequelize models
How to resolve sequelize-JSON.parse error in Node - How to resolve JSON.Parse error in sequelize
Create REST-API using sequelize -MariaDB ORM in Nodejs - How to create REST-API using sequelize ORM for MariaDB in Node-Expressjs
Create REST-API using sequelize-Mysql in Nodejs - How to create REST-API using sequelize-MySQL ORM in Node-Expressjs
Create REST-API using sequelize-Postgres in Nodejs - How to create REST-API using sequelize ORM in Node-Expressjs

Create REST-API using sequelize -MariaDB ORM in Nodejs

How to create REST-API using sequelize ORM for MariaDB in Node-Expressjs

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

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

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.

Create REST-API using sequelize-Mysql in Nodejs

How to create REST-API using sequelize-MySQL ORM in Node-Expressjs

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

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

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.

Create REST-API using sequelize-Postgres in Nodejs

How to create REST-API using sequelize ORM in Node-Expressjs

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

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

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.

SqlAlchemy[SQLite] databse connection in Python-Flask

How to use Flask and SQLAlchemy to build simple web application on the go

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: 12

How to solve

Share this:

Share this:

Setup Prisma

How to use in our Nextjs app

Share this:

Denodb-ORM

APP

Configuration

Share this:

Sequelize

Auto create tables

Share this:

Sequelize

MariaDB

Todo API

Share this:

Sequelize

MySQL

Todo API

Share this:

Sequelize

PostgreSQL

Todo API

Share this:

The connection string

Making of Database enabled Class

Share this: