SQLite and sequelize

[broken mason]

Error creating database & tables with associations: Error
    at Database.<anonymous> (/home/discordbot/node_modules/sequelize/lib/dialects/sqlite/query.js:185:27)
    at /home/discordbot/node_modules/sequelize/lib/dialects/sqlite/query.js:183:50
    at new Promise (<anonymous>)
    at Query.run (/home/discordbot/node_modules/sequelize/lib/dialects/sqlite/query.js:183:12)
    at /home/discordbot/node_modules/sequelize/lib/sequelize.js:315:28
    at async SQLiteQueryInterface.changeColumn (/home/discordbot/node_modules/sequelize/lib/dialects/sqlite/query-interface.js:43:7)
    at async Kontoinformation.sync (/home/discordbot/node_modules/sequelize/lib/model.js:984:11)
    at async Sequelize.sync (/home/discordbot/node_modules/sequelize/lib/sequelize.js:377:9) {
  name: 'SequelizeForeignKeyConstraintError',
  parent: [Error: SQLITE_CONSTRAINT: FOREIGN KEY constraint failed] {
    errno: 19,
    code: 'SQLITE_CONSTRAINT',
    sql: 'DROP TABLE `Kontoinformations`;'
  },
  original: [Error: SQLITE_CONSTRAINT: FOREIGN KEY constraint failed] {
    errno: 19,
    code: 'SQLITE_CONSTRAINT',
    sql: 'DROP TABLE `Kontoinformations`;'
  },
  sql: 'DROP TABLE `Kontoinformations`;',
  parameters: {},
  table: undefined,
  fields: undefined,
  value: undefined,
  index: undefined,
  reltype: undefined
}

Can someone explain to me where I can find the reason why a table is deleted?

[trail flicker]

SQLITE_CONSTRAINT: FOREIGN KEY constraint failed
means smth like your keys are not setup properly (look up rimary key & foreign key)

'Sequelize->ForeignKey Constraint->Error',
you can kinda read it like this. constraint = (kinda) the connection between primary & foreign keys are setup wrong
sequelize is your lib you use that errors thsi

[broken mason]

Sry for the late reply

Should I show my database model?

[trail flicker]

well as i stated, you should check your foreign and primary keys. (otherwise you will just get it pointed out, but dont learn much IMO)

btw its "kontoinformationen" or "accountinformations" 😂

[terse crag]

in English it'd be AccountInformation since information is a singular noun

🤓

[broken mason]

?

Does it make sense to name everything that is named in the background in English and to write everything the user (German) sees in German?

[terse crag]

i18n moment

If you expect only German developers with a PhD in physics to work on your codebase then having German variables and table names makes sense

Otherwise you should probably stick to English since that's what everyone everywhere does (even the Russians and the Chinese)

[broken mason]

Ok I edit them all

[broken mason]

I am so angry I cant fix my fucking error ::

Its always if I start my bot new

[terse crag]

Your database schema is broken

[broken mason]

const { Sequelize, DataTypes } = require('sequelize');
const sequelize = new Sequelize({
    dialect: 'sqlite',
    storage: './database/database.sqlite'
});

const AccountInformation = sequelize.define('AccountInformation', {
    ID: {
        type: DataTypes.INTEGER,
        autoIncrement: true,
        primaryKey: true
    },
    DiscordID: {
        type: DataTypes.STRING,
        allowNull: false
    },
    DiscordUsername: {
        type: DataTypes.STRING,
        allowNull: false
    },
    DiscordServerID: {
        type: DataTypes.STRING,
        allowNull: false
    },
    DiscordServerName: {
        type: DataTypes.STRING,
        allowNull: false
    },
    Balance: {
        type: DataTypes.INTEGER,
        allowNull: false
    },
    Timestamp: {
        type: DataTypes.DATE,
        defaultValue: Sequelize.NOW
    }
});

const Transaction = sequelize.define('Transaction', {
    AccountInformationID: {
        type: DataTypes.INTEGER,
        references: {
            model: AccountInformation,
            key: 'ID'
        },
        allowNull: false
    },
    TransactionType: {
        type: DataTypes.STRING,
        allowNull: false
    },
    TransactionValue: {
        type: DataTypes.INTEGER,
        allowNull: false
    },
    Timestamp: {
        type: DataTypes.DATE,
        defaultValue: Sequelize.NOW
    }
});

// Define associations
AccountInformation.hasMany(Transaction, {
    foreignKey: 'AccountInformationID',
    sourceKey: 'ID'
});
Transaction.belongsTo(AccountInformation, {
    foreignKey: 'AccountInformationID',
    targetKey: 'ID'
});

// Synchronize all defined models to the DB
sequelize.sync({ alter: true })
    .then(() => {
        console.log('Database & tables created with associations!');
    })
    .catch(error => {
        console.error('Error creating database & tables with associations:', error);
    });

module.exports = { AccountInformation, Transaction, sequelize };

[broken mason]

I cant see the error ::

:/

[broken mason]

https://github.com/NicolasMatheisen/discordbot

[trail flicker]

Key? is that correct? i know in mysql its foreign key:

const Transaktionen = sequelize.define('Transaktionen', {
    KontoinformationID: {
        type: DataTypes.INTEGER,
        references: {
            model: Kontoinformationen,
            key: 'ID'
        },
        allowNull: false
    },

But from what i see ive 2 questions:

1. Why using sqlite? Normaly thats used when you plan to run smth localy, but in your case a discord bot, which accesses from outside there are better solutions (also from what i see data safety should be super important! Cuz it seems like you want to handle important account data (for banks and such)
2. Have you tryed setting the database up from the scratch again? Many times if yo are new with databases it helps to fix errors you can figure out. (Means make a concept again, write from the scratch, no copy pasting) 😉

[trail flicker]

There i want to give you the advice, learn handeling that you get stuck. If you get angry or give up because of that, maybe programming is not for you? Because many times you get hours over hours stuck on small stupid errors xD and the most annoying is allways setting up new tools... oh hell... how many times i needed a break to gather new thoughts (here also a trick, dont stare at the screen and try to solve it, ignore it, do smth. else [sports & communication with others helps good here] and many times you get new thoughts then and can figure it out 😉 )

[broken mason]

Nr1 I see that discordjs use sequelize and sqlite thats the reason why I use this too

And what do u mean its not a true bank its just a bank for my game ^^

Or does this also count as sensieble data?

[trail flicker]

that i didnt know^^ seems like you wanted to save some transaction data, like for real money bought stuff xD

[broken mason]

Nr2 yes

I delete my database then I start my bot

[trail flicker]

wont work

[broken mason]

And then I restart it and then the trouble begin

[trail flicker]

if you integreated scripts where teh bot wwant smth from the DB, it cant work

[trail flicker]

thats not what i said

[broken mason]

ohhhhhwww nooo xD its just a game!!!!

[trail flicker]

database is not discord bot

[broken mason]

?

[trail flicker]

because its sqlite anyhow it is :/
Therefore you need to rewrite ALL your scripts that include doing smth in teh databse

[broken mason]

wait ur text I need to read it

[broken mason]

ahh yeah I know I try to do my best but its better to do a break

[broken mason]

But it can't be that sqlite doesn't work?

[trail flicker]

https://github.com/NicolasMatheisen/discordbot/blob/main/events/useractivitylog.js
thats your file that accesses your DB

[trail flicker]

the point is not, that SQLite is not working.
Your code is ust wrong at some point, which creates the database tables & connection to other database tables.

[broken mason]

wait a sec thats old code

I uploadet my code at 12.00

https://github.com/NicolasMatheisen/discordbot

wait I am hard confused why github say 2 days ago?

[trail flicker]

to debug properly in your future:
Save code when it works,
allways make some smaller commits in between on branches you work on (so you dont destroy the working main branch)
if you have any problems after changing smth, compare to when it was working what you changed.

Solves a lot of problems + the error description, if you can analyz/read it helps you a ton!

[broken mason]

jeah normaly I create my own save files localy on a usb

[trail flicker]

you didnt update your github, maybe only commited, but didnt pushed it 😄 (Commit = save point liek in a game, push = share the save with other people)

[broken mason]

I know github die the same

[trail flicker]

ok we get away form the problem 😂

[broken mason]

But the first time committing does it play a role?

jup 🙂

[trail flicker]

You have a discord bot,
that bot writes into a database.
You choose SQLite, which runs localy = only when the discord bot runs

That means all your database creation & putting it into the database happen in your discord bot.

1. I recommend you make a file only for database querrys (writing/reading/updating/deleting)
2. Access it through your discord bot(load it and call it in the bots script)

so you have a better overview abotu what is done in your database 😉

The problem in is in your database, which gets over and over created by your bot wrong.
So only deleting it wont fix it, you need to fix the script thats working wrong.
With the above method, you can find the error easyer.

So make a bot that is plain just starting and can run a command.
Make the new file for database access, load it into the bot script (but only call a test function)
then create the database creation function and test it (before delet teh odl DB ;))
then databse entry add function .... test...
DB update entry ... test ...
DB Delete entry ... test ....
and so on^^

(if you work with another databse like mysql & have a GUI for it to test, you would fast see, your key references are making th problem. Keys are also tehre to prevent altering data without altering all connected data, like a dump/ delete)

[broken mason]

Do you know an example repo?

https://pastebin.com/kQVGV88F

and here is what I see if I restart my bot for the first and 2nd time

[trail flicker]

as stated before all your sql stuff is wrong 😄

do you know what a foreign key is? And what a primary key is?
If not, why did you use it? ^^
You should look up some basics of that before trying further, because with teh knowledge you have you cant fix the error.

Or you rewrite all step by step. Then you could manage, but fixing in between seems really hard for you.

[broken mason]

I think It describes the relationship between the tables and connects them?

[trail flicker]

it sets the relationship 😄
Meaning it fixes how they can interact and what you can do with which entrys.

example:
you have a table students with name & other infos
then a table courses with course infos.
Lastly a connect_student_course table, which holds the id of the course & id of the student.

Now you set the foreign key in the connect table to the student. If you try to change the student id in the connect to a id not existing the DB will error.
If you try to delete the student it will error, because you need first to delete all connect entrys where the student is in.
Thats basically how a foreign key wors (a lil complexer but this should sum it up, if im not mistaken)

[broken mason]

So you can create an FK without a PK? I would like the connection between account and transactions where a relationship should be 1:n

[trail flicker]

no maybe i wrote that wrong, obviously all your tables have a PK xD

otherwiese you cant connect a FK IIRC

[broken mason]

A PK connect always to a FK?

ok ok

[broken mason]

I do verything right only the user.sync({alter: true}) create always the fucking error

how its false gg easy