Inserting data into Postgres database with dart

Question

I have a postgres database, containing a table called "users". The table has four fields:

id BIGSERIAL PRIMARY KEY
display_name TEXT NOT NULL,
email TEXT NOT NULL
phone_number TEXT

I would like to add data to it from a dart code. My attempt looked like this:

import "dart:io";
import "package:postgres/postgres.dart";
void main() async{
  var connection = PostgreSQLConnection(
        "localhost", 5432, "test",
        username: "something", password: "notTellingYou");
    try {
      await connection.open();
    } catch (e) {
      print(e);
    }

  Map<String, dynamic> data = {
    "display_name": "foo",
    "email": "[email protected]"
  };
  await setData(connection, "users", data);
  
}

Future<void> setData(PostgreSQLConnection connection, String table, Map<String, dynamic> data) async {
  await connection.query("""
    INSERT INTO $table(${data.keys})
    VALUES ${data.values};
  """);
}

The problem is that I get an exception

(PostgreSQLSeverity.error 42601: syntax error at or near "display_name" )

julemand101 · Accepted Answer · 2020-07-17 10:25:12Z

Try use variable substitution:

import "dart:io";
import "package:postgres/postgres.dart";

Future<void> main() async {
  final connection = PostgreSQLConnection("localhost", 5432, "test",
      username: "something", password: "notTellingYou");
  await connection.open();

  final data = <String, dynamic>{
    "display_name": "foo",
    "email": "[email protected]"
  };
  await setData(connection, "users", data);
  await connection.close();
}

Future<void> setData(PostgreSQLConnection connection, String table,
    Map<String, dynamic> data) async {
  await connection.execute(
      'INSERT INTO $table (${data.keys.join(', ')}) VALUES (${data.keys.map((k) => '@$k').join(', ')})',
      substitutionValues: data);
}

The generated text will here be: "INSERT INTO users (display_name, email) VALUES (@display_name, @email)" and each variable (beginning with @) will be automatically replaced by the value for each key inside your map.

This will also ensure correct escaping of special characters in your values.

Collectives™ on Stack Overflow

Inserting data into Postgres database with dart

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related