SQL Integration

import { SQLService, SUPPORTED_DBS } from "@inceptools/db";
import { Sequelize, DataTypes } from "sequelize";

// Define your SQL models
const models = {
  users: (sequelize) => {
    return sequelize.define("User", {
      name: {
        type: DataTypes.STRING,
        allowNull: false,
      },
      email: {
        type: DataTypes.STRING,
        allowNull: false,
        unique: true,
      },
      createdAt: {
        type: DataTypes.DATE,
        defaultValue: DataTypes.NOW,
      },
    });
  },
  posts: (sequelize) => {
    return sequelize.define("Post", {
      title: {
        type: DataTypes.STRING,
        allowNull: false,
      },
      content: {
        type: DataTypes.TEXT,
      },
      createdAt: {
        type: DataTypes.DATE,
        defaultValue: DataTypes.NOW,
      },
    });
  },
};

// Create a SQL service instance
const sqlService = new SQLService({
  type: SUPPORTED_DBS.SQL,
  connectionString: "postgresql://user:password@localhost:5432/myapp",
  models: models,
  configOptions: {
    dialect: "postgres",
  },
});

// Connect to SQL database
await sqlService.connect();

// Define associations (after connection)
sqlService.users.hasMany(sqlService.posts);
sqlService.posts.belongsTo(sqlService.users);

// Use your models
const newUser = await sqlService.users.create({
  name: "John Doe",
  email: "[email protected]",
});

const users = await sqlService.users.findAll();

// Create a post with reference to a user
const newPost = await sqlService.posts.create({
  title: "My First Post",
  content: "Hello world!",
  UserId: newUser.id,
});

// Find posts with included user
const posts = await sqlService.posts.findAll({
  include: sqlService.users,
});

// Close the connection when done
await sqlService.closeConnection();

const sqlService = new SQLService({
  type: SUPPORTED_DBS.SQL,
  connectionString: "postgresql://user:password@localhost:5432/myapp",
  models: models,
  configOptions: {
    dialect: "postgres",
    pool: {
      max: 10,
      min: 0,
      acquire: 30000,
      idle: 10000
    },
    logging: false,
    timezone: "+00:00"
  },
});

// Start a transaction
const transaction = await sqlService.connection.transaction();

try {
  // Perform operations
  const user = await sqlService.users.create({
    name: "Jane Doe",
    email: "[email protected]"
  }, { transaction });

  const post = await sqlService.posts.create({
    title: "Jane's Post",
    content: "Hello!",
    UserId: user.id
  }, { transaction });

  // Commit the transaction
  await transaction.commit();
} catch (error) {
  // Rollback the transaction on error
  await transaction.rollback();
  throw error;
}